I have a PostgreSQL database that I want to back-up periodically in case my server crashes suddenly. In this post I’m going to explore a simple but efficient way to create a back-up and how to apply it.

SQL Dump

When we take an SQL dump from a database, we will get a file with the SQL commands necessary to recreate the database to the current state.

To take an SQL dump for a PostgreSQL database, we can use pg_dump:

pg_dump somedb > filename.sql

pg_dump is a client application much like psql. It can be run from remote hosts similarly to other client applications:

pg_dump -h my.database.com -U someuser somedb > filename.sql

The user creating the dump must have read permissions on all tables for the database being backed-up.

Restoring the dump

Applying the dump file is as easy as creating it:

psql somedb < filename.sql

The user running this command must have permissions to create tables, modify them and insert records on those tables.

By default, if a command fails, the dump will continue running. This might leave the database in a weird state. I prefer running the whole dump as a transaction and stopping if there is any error. This command does that:

psql --single-transaction --set ON_ERROR_STOP=on somedb < filename.sql

If an error occurs, all the changes will be reverted and the database will be left in the same state as before the command was run.

[ postgresql  linux  ]
PostgreSQL user management
PostgreSQL - Connection refused
Introduction to PostgreSQL