Postgresql – Moving Local PostgreSQL 8.4.20 Database to Amazon EC2 or RDS

amazon ec2amazon-rdspostgresql

I'm running PostgresSQL 8.4.20 on a local server and plan to migrate the data to either Amazon EC2 or RDS. I have working knowledge of databases and need advice from experts. I've been researching this topic and I've come across some helpful and similar answers like this and this already.

I'm leaning towards Amazon RDS because of automatic backups and less maintenance. But will I be able to install v8.4 on RDS? Or do I need to upgrade to v9.3+? Will moving data from v8.4 to v9.3 cause any issues? If so, maybe it is best to install v8.4 on EC2?

Best Answer

Amazon's RDS only offers PostgreSQL versions 9.3.x, and it seems unlikely that they'll ever offer to host older versions of Postgres. So by jumping from a local 8.4 install directly to RDS, you would in effect be making two significant changes at once (jumping up several Postgres versions, as well as switching to managed hosting). That may be alright or not -- it all depends on what features you're using and depending on.

You should do some reading on RDS's limitations (no external hot standby, limited extensions, no shell access to the database instance, etc.) and benefits (hopefully much less maintenance work) and decide whether it's right for you. Also, I suggest you walk through the steps of dumping and restoring your data into RDS and ensure your application works OK, as well as reading through the Postgres major-version release notes for 9.0, 9.1, 9.2, and 9.3, paying particular note to the incompatibilities listed to see if any of them would affect you.