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.
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
sudo su - postgres pg_dump postgres > postgres.bak
The above command will take a backup of the default database called
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
First, create an empty database with
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 -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.