# How to Backup and Restore a Database in PostgreSQL

> URL: https://www.atlantic.net/vps-hosting/how-to-backup-and-restore-database-in-postgresql/ | Published: 2020-06-22 | Updated: 2023-11-21 | Author: Hitesh Jethva

PostgreSQL is free, open-source, and one of the most popular relational database management systems, powering millions of applications. Suppose you are a database administrator using PostgreSQL in a production environment. In that case, it is essential to back up your database so you can quickly restore your system if your database is lost.

PostgreSQL has built-in utilities to create or restore the database backup by default. There are several ways to backup and restore your PostgreSQL databases. With PostgreSQL, you can easily take a full, incremental, or continuous backup. You can use the 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 simultaneously.

In this tutorial, we will show you how to back up and restore the PostgreSQL database in Linux.

## Understand the Basic Syntax of pg_dump

PostgreSQL comes with built-in utilities called pg_dump for quickly creating and restoring backups.

The basic syntax of the 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.

## 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](https://airbyte.com/tutorials/postgres-replication) 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](https://www.atlantic.net/vps-hosting/) from Atlantic.Net!