Automatic postgres backup

What is the best way to automatically backup a Postgres database on Linux every day?

Solution 1:

You can use pg_dump like this:

$ pg_dump -h db_host -U user_name db_name > dump_file.sql

Please think first to set the .pgpass file, that contain passwords to be used if the connection requires a password. This file should have lines of the following format:

hostname:port:database:username:password

And each of the first four fields may be a literal value, or *, which matches anything. For example: *:*:*:postgres:pg_password.

This .pgpass file must reside in the home directory ~/ and the permissions on it must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass.

Solution 2:

Run pg_dumpall from cron.

Solution 3:

Try AutoPostgreSQLBackup. It is a single script file, can be easily configured to your needs, does daily, weekly and monthly scheduling, logs per email, log file or stdout, etc.

Solution 4:

If it's a reasonably small database, and such low requirements on the backup as just once a day, just run pg_dump from cron to dump to a local file, and then use whatever you have to backup the files on the machine to archive the dump away.