ryanwold.net - Civic Tech, Bitcoin, and Open Government Innovation

Title

Text

#### 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 postgis_tiger_geocoder;
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 ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser;')
  FROM (
    SELECT nspname, relname
    FROM pg_class 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`

An entry