Background
I recently moved from a Heroku Postgres database to Amazon RDS Postgres, and here are my notes.
Backing up a Postgres database
Backup Postgres database to a .dump file using pg_dump.
pg_dump -Fc -f your-db-dump-file.dump postgres://username:password@host.com:5432/database
Create a Postgres instance on Amazon RDS
- You can figure this part out at rds.amazonaws.com
Connect to Postgres instance
Once your Postgres database is running on Amazon RDS, you can connect to it using psql, assuming you have Postgres installed.
psql postgres://username:password@host.com:5432/database
Updating a basic Amazon RDS Postgres instance to Postgis
When creating a Postgres database through the Amazon RDS site, the database doesn't support GIS out of the box. But, it is pretty easy to convert your base Postgres db to Postgis.
Once logged into your database using psql, issue the following commands (which are also noted on the official RDS Postgis page).
``` create extension postgis; create extension fuzzystrmatch; create extension postgistigergeocoder; create extension postgis_topology;
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
SELECT exec('ALTER TABLE ' || quoteident(s.nspname) || '.' || quoteident(s.relname) || ' OWNER TO rdssuperuser;')
FROM (
SELECT nspname, relname
FROM pgclass c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname in ('tiger','topology') AND
relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;
```
Restore a Postgis database
And finally, now you have your .dump file from your old database, you've created a new Postgres database on Amazon RDS and converted it to Postgis, you can now load the .dump of your old Postgis database to your new database. Issue the following command.
pg_restore -d postgres://username:password@host.com:5432/database your-db-dump-file.dump
For example, this works well with Ruby on Rails.
pg_restore --verbose --clean --no-acl --no-owner -d postgres://user:password@localhost:5432/db_name db-backup.dump
When I ran this, I saw some warnings about user names, and existing tables, but my .dump loaded successfully, and I was able to use my new Amazon RDS database from my website in the same way I did previously. Success!
Export remote database to a local .sql file
pg_dump postgres://remote_username:remote_password@remote_host.com:5432/remote_database_name -f sql-backup.sql
Import local. sql file into a local database
psql local_database_name < sql-backup.sql