PostgreSQL Scheduled Database Backup

Question

For scheduling a daily back up on a postgres database we execute the following command:

0 2 * * * /usr/local/pgsql/bin/pg_dump -Fp -b -U database_name \
 > $HOME/db_backups/database_name-`date +\%Y\%m\%d`.sql \
 2>> $HOME/db_backups/cron.log

After the code executes, the log says "invalid password". Is there a way to specify a password or avoid the requirement when creating a dump?

Also we would like to retrieve the dump and pop it on separate Windows server using pgadmin3. When restoring using pgadmin dbase -> database restore the 'OK' button is disabled and we are unable to restore the database.

• Richard Huxton: Check the manuals for .pgpass - you can put login credentials in a file. I'm not sure I understand what the exact problem is with pgadmin I'm afraid. Are you saying you can't restore any backups to the Windows server, or just this particular one?

Answer 1

Backup without password

If you run the script as OS user postgres it should not request a password in a standard setup, because password-less peer access (or ident on older versions) is enabled in pg_hba.conf for the postgres user.

So make it a cronjob of postgres if you can. Or enable password-less access for the OS user running the job. You can do that in pg_hba.conf but, as @Richard already pointed out, the password file .pgpass file may be a more elegant solution.

More on how to connect without password in this related question.

Restore in pgAdmin

I quote the pgAdmin3 FAQ:

pgAdmin III uses PostgreSQL's pg_restore tool, which supports only the COMRESS and TAR options of pg_dump which is used for backup creation. The PLAIN format can't be interpreted by pgAdmin III and pg_restore (it can be edited manually, and executed with psql and pgAdmin III's query tool in many cases), and thus isn't accepted as valid file.

We recommend using the COMPRESS format for daily backup tasks. The PLAIN format is for advanced manual processing before executing as SQL script, and has some restrictions (no blobs) which makes it less usable for standard backup tasks.

To restore a plain SQL backup execute it with psql similar to this:

psql mydb -p 5432 -U myuser -f backup.sql

For a single database backup you have to connect to the right database. For a cluster-backup with pg_dumpall, you might as well connect to the maintenance db "postgres".

You could also load and run such a backup with the pgAdmin query tool. It's plain SQL.