PostgreSQL Database Backups

Backups can be one of the most critical aspects of administering any database, and PostgreSQL is no exception. While the PostgreSQL database is very versatile and resilient, problems can happen. A power failure could occur corrupting the database or the hard-drive could fail. You could also have problems with users, authorized or unauthorized, changing or destroying data.

PostgreSQL database provides easy backup and restore utilities. There are two different types of backups you can use:

Important: The following information is provided to point you in the right direction only. Please refer to the PostgreSQL database documentation for full procedures.
SQL text backups
SQL text backups allow you to backup your entire server including user and passwords but you cannot restore a single database or just the users unless you edit the file. Both of these files are created using the same pg_dump command depending on the options you specify. Both are restored using different commands. Another thing to consider is that binary backups can be restored using pgAdmin.
Binary backups
Binary backups are convenient and let you do a couple different things when you re-load them, however, they are limited to backing up a single database and do not include users and passwords.

Use the following commands to manage your PostgreSQL databases.

 pg_dump -U postgres -h <hostname> -p 5432 -F c -f Atlas.backup <Database Name>
  • <Hostname> is the name of the database machine.
  • <Database Name> is the name of the ST database.
  • -F c would create a compressed binary backup
createdb -U postgres -h <New Hostname> -p 5432 <New Db Name>
 pg_restore -U postgres -h <New Hostname> -p 5432 -d <New Db Name> Atlas.backup