---
title: Logical backup with pg_dump and pg_restore | Tiger Data Docs
description: Back up and restore a hypertable or an entire database using native PostgreSQL commands
---

You back up and restore each self-hosted PostgreSQL database with TimescaleDB enabled using the native PostgreSQL [`pg_dump`](https://www.postgresql.org/docs/current/app-pgdump.html) and [`pg_restore`](https://www.postgresql.org/docs/current/app-pgrestore.html) commands. This also works for compressed hypertables, you don’t have to decompress the chunks before you begin.

If you are using `pg_dump` to backup regularly, make sure you keep track of the versions of PostgreSQL and TimescaleDB you are running.

This page shows you how to:

- [Back up and restore an entire database](#back-up-and-restore-an-entire-database)
- [Back up and restore individual hypertables](#back-up-and-restore-individual-hypertables)

You can also [upgrade between different versions of TimescaleDB](/docs/deploy/self-hosted/upgrades/index.md).

## Prerequisites

- A source database to backup from, and a target database to restore to.
- Install the `psql` and `pg_dump` PostgreSQL client tools on your migration machine.

## Back up and restore an entire database

You backup and restore an entire database using `pg_dump` and `psql`.

1. **Set your connection strings**

   These variables hold the connection information for the source database to backup from and the target database to restore to:

   Terminal window

   ```
   export SOURCE=postgres://<user>:<password>@<source host>:<source port>/<db_name>
   export TARGET=postgres://<user>:<password>@<source host>:<source port>
   ```

2. **Back up your database**

   Terminal window

   ```
   pg_dump -d "$SOURCE" \
     -Fc -f <db_name>.bak
   ```

   You may see some errors while `pg_dump` is running. These can usually be safely ignored.

3. **Restore your database from the backup**

   1. Connect to your target database:

      Terminal window

      ```
      psql -d "$TARGET"
      ```

   2. Create a new database and enable TimescaleDB:

      ```
      CREATE DATABASE <restoration database>;
      \c <restoration database>
      CREATE EXTENSION IF NOT EXISTS timescaledb;
      ```

   3. Put your database in the right state for restoring:

      ```
      SELECT timescaledb_pre_restore();
      ```

   4. Restore the database:

      ```
      pg_restore -Fc -d <restoration database> <db_name>.bak
      ```

   5. Return your database to normal operations:

      ```
      SELECT timescaledb_post_restore();
      ```

      Do not use `pg_restore` with the `-j` option. This option does not correctly restore the TimescaleDB catalogs.

## Back up and restore individual hypertables

`pg_dump` provides flags that allow you to specify tables or schemas to back up. However, using these flags means that the dump lacks necessary information that TimescaleDB requires to understand the relationship between them. Even if you explicitly specify both the hypertable and all of its constituent chunks, the dump would still not contain all the information it needs to recreate the hypertable on restore.

To backup individual hypertables, backup the database schema, then backup only the tables you need. You also use this method to backup individual plain tables.

1. **Set your connection strings**

   These variables hold the connection information for the source database to backup from and the target database to restore to:

   Terminal window

   ```
   export SOURCE=postgres://<user>:<password>@<source host>:<source port>/<db_name>
   export TARGET=postgres://<user>:<password>@<source host>:<source port>/<db_name>
   ```

2. **Back up the database schema and individual tables**

   1. Back up the hypertable schema:

      Terminal window

      ```
      pg_dump -s -d $SOURCE --table <table-name>  > schema.sql
      ```

   2. Back up hypertable data to a CSV file. For each hypertable to backup:

      Terminal window

      ```
      psql -d $SOURCE \
      -c "\COPY (SELECT * FROM <table-name>) TO <table-name>.csv DELIMITER ',' CSV"
      ```

3. **Restore the schema to the target database**

   Terminal window

   ```
   psql -d $TARGET < schema.sql
   ```

4. **Restore hypertables from the backup**

   For each hypertable to backup:

   1. Recreate the hypertable:

      Terminal window

      ```
      psql -d $TARGET -c "SELECT create_hypertable(<table-name>, <partition>)"
      ```

      When you [create the new hypertable](/docs/reference/timescaledb/hypertables/create_hypertable/index.md), you do not need to use the same parameters as existed in the source database. This can provide a good opportunity for you to re-organize your hypertables if you need to. For example, you can change the partitioning key, the number of partitions, or the chunk interval sizes.

   2. Restore the data:

      Terminal window

      ```
      psql -d $TARGET -c "\COPY <table-name> FROM <table-name>.csv CSV"
      ```

      The standard `COPY` command in PostgreSQL is single threaded. If you have a lot of data, you can speed up the copy using the [timescaledb-parallel-copy](https://github.com/timescale/timescaledb-parallel-copy).

Best practice is to back up and restore a database at a time. However, if you have superuser access to PostgreSQL instance with TimescaleDB installed, you can use `pg_dumpall` to back up all PostgreSQL databases in a cluster, including global objects that are common to all databases, namely database roles, tablespaces, and privilege grants. You restore the PostgreSQL instance using `psql`. For more information, see the [PostgreSQL documentation](https://www.postgresql.org/docs/17/backup-dump.html#BACKUP-DUMP-ALL).