Postgresql – migrating from a local PostgreSQL database to Amazon Aurora

@joni correctly points out that pg_dump is what you want. However, I recommend not doing a simple SQL dump. Instead, use a PostgreSQL "custom" format dump that can be fed into pg_restore. This allows parallel dump/restore (faster) if you use directory format, but it's mostly useful because it offers control over the details of the restore without taking a new dump.

Custom-format dump/restore

For example, assuming you've already done a CREATE DATABASE on the destination so there's an empty DB named mydb there:

# dump the local db
pg_dump -Fc -o mydb.dump mydb

# restore to Amazon RDS
pg_restore -h my.amazon.host -U rds_superuser -d mydb --no-owner --no-privileges --no-tablespaces

Roles, tablespaces, foreign servers, GRANTs, etc

Note that roles (users and groups) are not included in the dump. Neither are foreign data wrapper servers or some other global objects. Thus, things that depend on them will fail to restore. In the above I omit restoration of the owner and of privileges for that reason - meaning you'll need to redo your GRANTs and REVOKEs if you have non-default privileges in your database.

Similarly, all objects are restored to the default tablespace, because any tablespaces you may have locally won't be on RDS.

No remote access to rds_superuser?

If you don't allow your rds_superuser to log in remotely, but have done a GRANT rds_superuser TO myusername for some other role, then instead of -U rds_superuser above you can use:

-U myusername --role=rds_superuser

to get pg_restore to authenticate as myusername then SET ROLE rds_superuser;.

Replication

Most production PostgreSQL database migrations are performed using replication and failover. This isn't currently possible with Amazon RDS. RDS does WAL archiving and streaming replication internally, but offers no way to get at the WAL archives or to create a replication entry in pg_hba.conf for replication for clients outside RDS.

For that reason, at time of writing you have to dump and reload to move data into or out of RDS. There's no low-downtime option.

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.