PostgreSQL Backups and Migration

Database snapshot

$ pg_dump -h hostname - d dbname -U username > mysnapshot.sql

The password can be included in the command above, otherwise you'll be prompted for the password. ^[1]

The -a option will dump only the data in the database, not the structure. ^[2]

It's also possible to compress the dump:

$ pg_dump -h hostname - d dbname -U username | gzip > mysnapshot.gz
$ gunzip -c mysnapshot.gz | psql dbname

Change your database RDS instance security group to allow your machine to access it.
- Add your ip to the security group to access the instance via Postgres.
- AWS Management Console > Services > Database > RDS > Database Instances > Click on an instance name to view its details
- Connectivity & Security tab > Security > VPC security groups > Click on group name
- Inbound Rules tab > Edit inbound rules > Add IP of local machine
In a local terminal, make a copy of the database using pg_dump.
- $ pg_dump -h <public dns> -U <my username> -f <name of dump file .sql> <name of my database>
- Public DNS is available via RDS Management Console > Database Instances > Click on an instance name to view its details
- Connectivity & security > Endpoint & port > Endpoint > The public DNS.
- The port number (default port for PostgreSQL is 5432) is listed below. ^[3]

$ psql dbname < mysnapshot.sql