Atlantic.Net Blog

How to Backup and Restore Database in PostgreSQL

Hitesh Jethva
by Atlantic.Net (64posts) under VPS Hosting
0 Comments

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.

Prerequisites

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

Conclusion

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!

Get A Free To Use Cloud VPS

Free Tier Includes:
G2.1GB Cloud VPS Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


Looking for a Hosting Solution?

We Provide Cloud, Dedicated, & Colocation.

  • Seven Global Data Center Locations.
  • Flexible Private, Public, & Hybrid Hosting.
  • 24x7x365 Security, Support, & Monitoring.
Contact Us Now! Med Tech Award FTC
SOC Audit HIPAA Audit HITECH Audit

Recent Posts

Get started with 12 months of free cloud VPS hosting

Free Tier includes:
G2.1GB Cloud VPS Server Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


New York, NY

100 Delawanna Ave, Suite 1

Clifton, NJ 07014

United States

San Francisco, CA

2820 Northwestern Pkwy,

Santa Clara, CA 95051

United States

Dallas, TX

2323 Bryan Street,

Dallas, Texas 75201

United States

Ashburn, VA

1807 Michael Faraday Ct,

Reston, VA 20190

United States

Orlando, FL

440 W Kennedy Blvd, Suite 3

Orlando, FL 32810

United States

Toronto, Canada

20 Pullman Ct, Scarborough,

Ontario M1X 1E4

Canada

London, UK

14 Liverpool Road, Slough,

Berkshire SL1 4QZ

United Kingdom

Resources