Postgres Backup

README.md

PostgreSQL Database Archiving & Backup

Postgres includes the command line tools pg_dump and pg_restore to store a copy of a database as a file on disk.

Methods for archiving Postgres databases

1. Use pg_dump/pg_restore to create binary files in the native postgres dump format

• this method is efficient, but because the resulting .backup file is binary it can't be edited once it is created which can lead to problems when restoring. Read more: https://www.postgresql.org/docs/current/static/app-pgdump.html
• this is the preffed method if working locally, and you have no reason to edit the backup file
• if working with a cloud hosted service such as AWS RDS then see option (2) below. AWS uses specialist permissions and usernames which are typically are not available on a local system and so it is helpful to be able to change these in the backup file prior to restoring.

2. Use pg_dump to create plain SQL files containing database structure and data as text.

• this method is useful as you can edit the file prior to restoring. This is sometimes required if there are elements in the database that you can't recreate locally (e.g. specific users). A text editor can be used to tweak the schema prior to restoring the database.
• disadvantage is that files can get large, especially if there are many rows etc.
• also note that PostGIS geometries will be dumped as binary hex strings, not normally a problem unless you have weird projection parameters or nested geometries.
• files can be restored using psql -f my_database.sql. Note that if the dump has been created with the "create" flag you don't need to install the schema prior to restoring, a new database will be created.
• caution dumps can drop existing databases of the same name (if you use the -c option when using pg_dump) so be careful not to write over local copies when restoring.

Steps to make a database copy

1. Connect to the database
2. Run the pg_dump command (see above) to create dump file locally
3. Move file to where you need it, and optionally restore it to a different database service.

Example using pg_dump to create binary backup (Method 1)

-- Make a database dump from the server with the create option (to create a new copy of the archived database)
pg_dump my_db -h my_server -U postgres -C > my_db.backup

Example using pg_dump to create a plain backup (Method 2)

-- Make a database dump from server with create option, no owner and in plain SQL format
pg_dump my_db -h my_server -U postgres -W -C -O -f database.sql

Restoring a binary backup

-- Restore from binary dump by creating a new database with the name specified in pg_dump (e.g. my_db)
pg_restore -d postgres -C db1.backup

Restoring from plain backup

-- Restore to localhost (will be created with name my_db)
psql -h localhost -U postgres -W -f database.sql