A civic-minded citizen seeking the singularity

An entry

Postgres/Postgis on Amazon RDS

Date: 2016-04-16
Status: release
Tags: notes snippets


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