PostgreSQL Backups and Migration
Database snapshot[edit]
Local PostgreSQL database[edit]
$ 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
AWS RDS PostgreSQL[edit]
- 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]
Restoring a dump[edit]
$ psql dbname < mysnapshot.sql