What is PostgreSQL?
How to Backup a PostgreSQL database?
We should have a backup policy for any Database, as it is one of the major activity to safeguard against data loss. Let's Checkout some of the backup Methods used for PostgreSQL database. PostgreSQL includes a utility "pg_dump" which will dump the Database information to a file. We can run the utility from Linux Command Line and the general command syntax is as follows:pg_dump name_of_database > backup-file
This command should run as a user who has read privileges to all the Database Information. In Practical we log in as user postgres and run these commands.
su - postgres
pg_dump name_of_database > backup-file
We can backup a remote system as well. The syntax would be as follows:
pg_dump -U username -h remote-host -p remote-port name_of_database > backup-file
Where "-U" is used to specify a username , which is optional. It'll take the same user as you have logged in. in this case it will be postgres. The "-h" option will be used for remote host ip or FQDN and the "-p" option will specify the remote port.
Now its Time for the Restore
To restore a Database from the backup we need redirect the backup file to the psql standard input. We need to create a Database and a user having privileges to RW to database before restoring to it. Let see how to do that:
createdb -T template0 new_databse
createuser test
psql new_databse < backup-file
How to Perform backup/Restore in multiple Database?
If you do not want to take dump of each database one by one, then pg_dumpall is for you. To support convenient dumping of the entire contents of a database cluster, the pg_dumpall program is provided. pg_dumpall backs up each database in a given cluster, and also preserves cluster-wide data such as role and tablespace definitions. The basic usage of this command is:
pg_dumpall > backup-file
The resulting dump can be restored with psql:
psql -f backup-file postgres
(Actually, you can specify any existing database name to start from, but if you are loading into an empty cluster then postgres should usually be used.) It is always necessary to have database superuser access when restoring a pg_dumpall dump, as that is required to restore the role and tablespace information. pg_dumpall works by emitting commands to re-create roles, tablespaces, and empty databases, then invoking pg_dump for each database.
0 Comments