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.

For example:

$ 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 1.1.1.1 is the remote database server (replace 1.1.1.1 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 1.1.1.1 -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.