PostgreSQL is free, open-source, and one of the most popular relational database management systems, powering millions of applications. If you are a database administrator using PostgreSQL in a production environment, it is essential to back up your database so you can quickly restore your system if your database is lost.
By default, PostgreSQL comes with built-in utilities to create or restore the database backup. There are several ways to backup and restore your PostgreSQL databases. With PostgreSQL, you can take a full, incremental, or continuous backup easily. You can use pg_dump utility to back up the PostgreSQL database. This function allows you to back up a local database and restore it on a remote database at the same time.
In this tutorial, we will show you how to back up and restore the PostgreSQL database in Linux.
- A fresh Ubuntu 20.04 VPS with PostgreSQL installed on the Atlantic.net Cloud Platform.
- If you do not already have PostgreSQL installed, you can check out this blog to learn how to install and configure Postgres.
- A root password configured on your server.
1 – Create Atlantic.Net Cloud Server
First, log in to your Atlantic.Net Cloud Server. Create a new server, choosing Ubuntu 20.04 as the operating system with at least 1GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.
Once you are logged into your Ubuntu 20.04 server, run the following command to update your base system with the latest available packages.
apt-get update -y
2 – Understand the Basic Syntax of pg_dump
PostgreSQL comes with built-in utilities called pg_dump for easily creating and restoring backups.
The basic syntax of pg_dump command is shown below:
pg_dump [OPTION]... [DBNAME]
A brief explanation of each option is shown below:
-d, –dbname=DATABASENAME : Used to specify the database that you want to back up.
-h, –host=HOSTNAME : Used to specify the hostname of your database server.
-U, –username=USERNAME : Used to specify the PostgreSQL username.
-w, –no-password : Used to ignore the password prompt.
-p, –port=PORT : Used to specify the PostgreSQL server port number.
-W, –password : Used to force password prompt.
–role=ROLENAME : SET ROLE before the dump.
3 – Backup and Restore a Single Database
You can back up and restore a single database using the pg_dump utility. The basic syntax to back up a single database is shown below:
pg_dump -d [source-database] -f [database_backup.sql]
For example, to back up a single database named testdb and generate a backup file named testdb_backup.sql, run the following command:
su - postgres pg_dump -d testdb -f testdb_backup.sql
You can also restore a single database using psql command. The basic syntax to restore a single database is shown below:
psql -d [destination_database] -f [database_backup.sql]
For example, to restore a single database named testdb from a backup file named testdb_backup.sql, run the following command:
su - postgres psql -d testdb -f testdb_backup.sql
You should get the following output:
SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET
If you want to stop the database restore process in case an error occurs, run the following command:
psql -d testdb --set ON_ERROR_STOP=on -f testdb_backup.sql
Note : Before restoring any database, it is recommended to terminate all connections to that database and prepare the backup file.
Backup and Restore All Databases
You can back up all databases in PostgreSQL using pg_dumpall utility. The basic syntax to backup all databases as shown below:
pg_dumpall -f [alldatabase_backup.sql]
For example, to back up all databases in PostgreSQL and generate a backup file named alldb_backup.sql, run the following command:
pg_dumpall -f alldb_backup.sql
To restore all databases from a backup file named alldb_backup.sql, run the following command:
psql -f alldb_backup.sql
Backup and Restore Single Table
PostgreSQL also allows you to back up a single table from the specific database. You can achieve this using the following syntax:
pg_dump -d [source-database] -t [table_name]-f [dbtable_backup.sql]
For example, to back up a table named mytab from the database named testdb and generate a backup file named testdb_mytab.sql, run the following command:
pg_dump -d testdb -t mytab -f testdb_mytab_backup.sql
If you want to restore this table from the backup file, run the following command:
psql -d testdb -f testdb_mytab_backup.sql
Backup and Restore Compressed Database
You can also back up the PostgreSQL database and compress it in .gz format to reduce the backup size.
To take a back up of database named testdb and generate a compressed backup file named testdb_compressed.sql.gz, run the following command:
pg_dump -d testdb | gzip > testdb_compressed.sql.gz
You can also restore the backup from the compressed file using the following command:
gunzip -c testdb_compressed.sql.gz | psql -d testdb
In the above guide, you learned several ways to backup and restore a PostgreSQL database to help you perform day-to-day database backup operations. Get started today with a PostgreSQL database on VPS hosting from Atlantic.Net!