How to Backup and Restore PostgreSQL Databases on Ubuntu 16.04
👋 This article needs updates.
Please help us update it! We pay for new and updated articles.
See Vultr's Request for Article (RFA) System for details and include RFA# 1268 in your request.
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.