PostgreSQL uses the pg_dump utility to back up the database.
To backup a PostgreSQL database, log into the database server, then switch to the Postgres user account, and run pg_dump as follows:
$ pg_dump racknerd > racknerd.sql
The pg_dump supports other output format by using the “-F” option, where c means custom format archive file, d means directory format, and t means tar format file. All formats are suitable for input into pg_restore.
$ pg_dump -F c racknerd > racknerd.dump
$ pg_dump -F t racknerd > racknerd.tar
To dump output in the directory output format, use the -f flag to specify target directory.
$ pg_dump -F d racknerd -f racknerddirectory
To back up all PostgreSQL databases:
$ pg_dumpall > all_pg_dbs.sql
You can restore the dump using psql as shown in command below:
$ pgsql -f all_pg_dbs.sql postgres
Restoring a PostgreSQL Database
Example of how to restore a database
$ psql racknerd < racknerd.sql
so it must be restored using pg_restore as shown:
$ pg_restore -d racknerd racknerd.dump
$ pg_restore -d racknerd racknerd.tar
$ pg_restore -d racknerd racknerddirectory
Backup Large PostgreSQL Databases
If your database is large then you can make the backup by compressing it.
$ pg_dump racknerd | gzip > racknerd.gz
Backup Remote PostgreSQL Databases
If you are looking to backup a remote PostgreSQL database, you can use the below command. In the example below, IP address 22.214.171.124 is the remote database server (replace 126.96.36.199 with your remote database server IP address), rack is the user, 1123 is the port, and racknerd is the database. Replace those values accordingly:
$ pg_dump -U rack -h 188.8.131.52 -p 1123 racknerd > racknerd.sql
Ensure that the user connecting remotely has the required privileges to access the database, and the appropriate database authentication method is configured on the database server, otherwise, you will not be able to connect.
We hope that this tutorial provided you with some insight on how to backup and restore PostgreSQL databases. If you are looking for a reliable hosting provider, take a look at our available server hosting solutions below.