Notes on Postgres backup

We'll back up through a dedicated 'backup' database user. Here are the config steps needed to give this user read access:

GRANT USAGE ON SCHEMA schema TO backup;
GRANT SELECT ON ALL TABLES IN SCHEMA schema TO backup;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA schema TO backup;

Then we can run pg_dump from the command line like so:

pg_dump --username=backup recogito > backup.sql

Since we want to use this for unattended scheduled backups, we need to get around the password prompt. This can be done by placing a .pgpass file in the user home directory, with the following line:

localhost:5432:recogito:backup:{password}

Permissions on .pgpass must disallow any access to world or group. Set permissions as follows, otherwise pg_dump will ignore the password file.

chmod 0600 .pgpass

See also: https://www.postgresql.org/docs/current/static/libpq-pgpass.html