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.
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.
Before starting, update the packages on your server.
sudo apt-get update -y
sudo apt-get upgrade -y
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
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
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
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.