PostgreSQL is an object-relational Database management system (ORDBMS) that is suited for large databases and has many advanced features. Its emphasis is on extensibility and standards-compliance. PostgreSQL can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.It has been developed actively for more than 15 years by a team of volunteers all around the world.

In this tutorial, we will take a look at how we can back up and restore a PostgreSQL database.

Backing up a database in PostgreSQL server

Log in to the postgres user so you can give commands to the PostgreSQL server:

su postgres

List the databases we have stored inside the server:

psql -l

Your output should look something like this:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 test2     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

Now we will use the utility pg_dump to backup the databases we want.The syntax for the tool is pretty simple:

pg_dump database_name > /path/to/backup_file

We want to back up the database named test and save it in the current working directory, so we’ll issue the following command:

pg_dump test > test.sql

Sometimes you may want to save the backup file in a compressed tarball format, pg_dump can do that as well with the following command:

pg_dump -Ft database_name > /path/to/backup_file.tar

You can also back up a database remotely by using the following command syntax:

pg_dump -U username -h server_hostname -p server_port database_name > /path/to/backup_file

Restoring a database in PostgreSQL server

If the database we want to restore our database backup file to exists, we run the following command:

psql database_name 
Note: This command must be run on an empty database otherwise it won’t work.
In our case that will be:
psql test 
If the database doesn’t exist then we run the following command first and then the command above:
psql -c 'CREATE DATABASE database_name;'
psql database_name 
Our example of the command above:
psql -c 'CREATE DATABASE test;'
psql test 
Now if we saved the database using the tarball format we mentioned earlier, PostgreSQL has a utility called pg_restore that can restore a database saved in this format:
pg_restore -Ft /path/to/backup_file.tar
And if the database doesn’t exist then we can create it from the restore using this command:
pg_restore -Ft -C /path/to/backup_file.tar
Backing up and restoring all databases in PostgreSQL server
Sometimes we may need to backup and restore all the databases in the server, we’ll start by dumping all of the databases with pg_dumpall:
pg_dumpall > /path/to/backup_file
And then we can restore them with the following command:
psql -f /path/to/backup_file postgres
That’s it, now you know how to backup and restore your own PostgreSQL database.
Of course you don’t have to do any of this if you use one of our SSD virtual servers, in which case you can simply ask our expert Linux admins to backup and restore your PostgreSQL databases for you. They are available 24×7 and will take care of your request immediately.
PS. If you liked this post please share it with your friends on the social networks using the buttons on the right or simply leave a reply below. Thanks.