Skip to main content
4 of 4
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/

###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.

Erwin Brandstetter
  • 182.2k
  • 28
  • 457
  • 620