How to Backup and Restore PostgreSQL Databases on Ubuntu 16.04

Introduction

PostgreSQL is a free and open source database management system that can be used to store information related to websites. It is also known as Postgres. The software itself features a high performance object-relational database that is well suited for large databases and web applications.

That being said, one of the most important tasks of the any database administrator is to backup their databases regularly. Here, you will learn how to backup and restore a PostgreSQL database on Ubuntu 16.04.

Prerequisites

• A server running Ubuntu 16.04.
• A non-root user with sudo privileges.

Step 1: Update system packages

Before starting, update the packages on your server.

sudo apt-get update -y
sudo apt-get upgrade -y

Step 2: Install PostgreSQL

Fortunately, PostgreSQL is available in the Ubuntu 16.04 repository. You can install it easily with the following command.

sudo apt-get install postgresql postgresql-contrib

Once the installation has completed, it will create a user named postgres with the role postgres. A system account named postgres will also be created.

Start the postgresql service and enable it to run on boot.

sudo systemctl start postgresql
sudo systemctl enable postgresql

Step 3: Backup instructions

PostgreSQL comes with a utility called pg_dump to backup database information to a file.

You can run this utility from a command line interface. For example, if you want to backup a single database, run the following command:

sudo pg_dump -U user_name database_name > backup_file

Note: The command must be run as the superuser.

You can also backup a database by logging in as the postgres user.

sudo su - postgres
pg_dump postgres > postgres.bak

The above command will take a backup of the default database called postgres.

You can also take a backup of a remote database.

pg_dump -U user_name -h remote_ip_address -p remote_port database_name > backup_file

If you want to backup all of the databases on your system, then pg_dumpall is used.

sudo pg_dumpall -U user_name > backup_file

Step 4: Restore instructions

You can restore a database created by pg_dump command using the psql command.

First, create an empty database with name new_database.

sudo -u postgres psql
createdb -T template0 new_database

Next, redirect a database dump called backup_file with the following command:

psql new_database < backup_file

If you want to stop the restoration process immediately when an error is encountered, run the following command:

psql --set ON_ERROR_STOP=on new_database < backup_file

You can restore the databases created by the pg_dumpall command by passing the file to psql.

psql -U user_name -f backup_file   

Conclusion

You have now enough knowledge to backup and restore PostgreSQL databases. It is important to keep regular backups, as they are useful in recovering a system.