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.
- Backup
-
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
- Create
-
createdb -U postgres -h <New Hostname> -p 5432 <New Db Name>
- Restore
-
pg_restore -U postgres -h <New Hostname> -p 5432 -d <New Db Name> Atlas.backup