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.
Best Answer
Backup without password
If you run the script as OS user
postgres
it should not request a password in a standard setup, because password-lesspeer
access (orident
on older versions) is enabled inpg_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:
To restore a plain SQL backup execute it with psql similar to this:
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.