We have a software using SQL Server. We provide a special RDS edition of our database because of SQL Server limitations in RDS.
Now I am going to migrate the software to PostgreSQL and in RDS too.
What limitations, special requirements has PostgreSQL in Amazon RDS in comparison with usual installations?
Similar to other RDS offerings, there are limitations that are specific to PostgreSQL and some which are simply inherent to RDS. Specific to RDS, database upgrades are limited to versions which are supported by RDS - so availability may lag behind the community release dates. Also, OS upgrades, which are related to Amazon's Machine Image (in this case, their Linux AMI), are managed by Amazon. You're able to configure the "when" element, via the usual RDS inputs (console, API, or AWS's CLI), though the image is hardened to their Linux AMI (more on that here if necessary).
Storage-wise, instances require a minimum of 5GB, with a max of 6TB per instance. You're capped at 40 instances of PostgreSQL overall.
Connections to that storage require a *minimum* of three reserved for RDS-level actions by Amazon, so when setting the value for max_connections, you have to add those three to your overall maximum. Given that you're already in RDS, it may not be worth mentioning, but from a tablespace perspective, you can't use those parameters to increase IO efficiency (IE, splitting the IOPs, isolating tablespaces for performance requirements). Tablespaces *are* supported, but they're all housed on a single LV. Finally, altering data types (alter enum) has been around on PostgreSQL since 9.1, but isn't supported on RDS on versions lower than 9.5.6/9.6.2.
Many of the differences or contrasts are really "How do we know what is best on RDS" questions based on needs, such as using Logical Replication versus read replicas, failover requirements versus multi-zone HA, connection limitations based on memory defined for the RDS instance, etc. Some of these are dealt with here in the Amazon documentation for RDS at large.
Are there are any things in particular you're concerned with while going through the migration? Hopefully these are a decent start in getting some answers to questions you may have.
Thank you for your answer.
Yes, I have 2 important steps:
1. Migration SQL Server->PostgreSQL
2. Creation the RDS compatible PostgreSQL database from existing(p.1.) PostgreSQL database.
The p.1. I am completing, PG database migration is near finish, only some bottlenecks are: hierarchyid, service broker, sql_variant fields and some other.
And my question was about p.2: i.e. are there any database specific requirements, limitations etc, for exapmple: datatypes, db features which are different for RDS PG databases and so on.
Following link contains the Features and Extensions supported by Amazon RDS::
As such it does not state any limitation on datatypes or for that matter other db specfic features.
Hope this helps, Amit
Thank you for writing question. Many of people come across similar query now days. I'll also add some general overview about concept of RDS.
RDS stands for Remote Database Services. Here, database would be available as service rather infrastructure. In other words, database port and global host name would be available. Server access via ssh/rdp is not allowed. Management/maintenance of server(s) would be managed by vendor(Amazon). Amazon RDS is a part of low-cost services, when a customer doesn't wish to manage infrastructure.
There are certain limitations of using Amazon RDS.
1) Amazon RDS doesn't provide super user access to the environment. So, admin level operations are restricted to user. However, certain required privileges are provisioned via rds database user.
2) For major upgrade, Amazon RDS provides upgrade to immediate next version. One may not upgrade to next second or third major version. e.g. 9.3 -> 9.6 is not allowed. But, 9.3 -> 9.4 is allowed.
3) Table space creation is not feasible as We don't have access to file system.
4) For PostgreSQL extensions, only certain pre-configured extensions can be installed. Extensions which are not configured can'b be installed. e.g. plpythonu
5) Access to pg_hba.conf is not provisioned. This needs to taken care through security groups.
Thanks & Regards,