---
title: Migrate the entire database at once | Tiger Data Docs
description: Migrate a small database to self-hosted TimescaleDB in one go with pg_dump and pg_restore
---

Migrate smaller databases by dumping and restoring the entire database at once. This method works best on databases smaller than 100 GB. For larger databases, consider [migrating your schema and data separately](/docs/deploy/self-hosted/migration/schema-then-data/index.md).

Warning

Depending on your database size and network speed, migration can take a very long time. You can continue reading from your source database during this time, though performance could be slower. To avoid this problem, fork your database and migrate your data from the fork. If you write to tables in your source database during the migration, the new writes might not be transferred to TimescaleDB. To avoid this problem, see [Live migration](/docs/migrate/live-migration/index.md).

## Prerequisites

Before you begin, check that you have:

- Installed the PostgreSQL [`pg_dump`](https://www.postgresql.org/docs/current/app-pgdump.html) and [`pg_restore`](https://www.postgresql.org/docs/current/app-pgrestore.html) utilities.
- Installed a client for connecting to PostgreSQL. These instructions use `psql`, but any client works.
- Created a new empty database in your self-hosted TimescaleDB instance. Provision your database with enough space for all your data.
- Checked that any other PostgreSQL extensions you use are compatible with Timescale.
- Checked that you’re running the same major version of PostgreSQL on both your target and source databases. For information about upgrading PostgreSQL on your source database, see the [upgrade instructions](/docs/deploy/self-hosted/upgrades/upgrade-pg/index.md).
- Checked that you’re running the same major version of TimescaleDB on both your target and source databases. For more information, see [upgrade self-hosted TimescaleDB](/docs/deploy/self-hosted/upgrades/major-upgrade/index.md).

Note

To speed up migration, compress your data into the columnstore. You can compress any chunks where data is not currently inserted, updated, or deleted. When you finish the migration, you can decompress chunks back to the rowstore as needed for normal operation.

1. **Dump all the data from your source database**

   Dump into a `dump.bak` file, using your source database connection details. If you are prompted for a password, use your source database credentials:

   Terminal window

   ```
   pg_dump -U <SOURCE_DB_USERNAME> -W \
   -h <SOURCE_DB_HOST> -p <SOURCE_DB_PORT> -Fc -v \
   -f dump.bak <SOURCE_DB_NAME>
   ```

2. **Connect to your self-hosted TimescaleDB instance**

   Terminal window

   ```
   psql "postgres://<USERNAME>:<PASSWORD>@<HOST>:<PORT>/<DATABASE>?sslmode=require"
   ```

3. **Prepare your self-hosted TimescaleDB instance for data restoration**

   Use [`timescaledb_pre_restore`](/docs/reference/timescaledb/administration/timescaledb_pre_restore/index.md) to stop background workers:

   ```
   SELECT timescaledb_pre_restore();
   ```

4. **Restore the dumped data**

   At the command prompt, restore the dumped data from the `dump.bak` file into your self-hosted TimescaleDB instance, using your connection details. To avoid permissions errors, include the `--no-owner` flag:

   Terminal window

   ```
   pg_restore -U tsdbadmin -W \
   -h <CLOUD_HOST> -p <CLOUD_PORT> --no-owner \
   -Fc -v -d tsdb dump.bak
   ```

5. **Return your self-hosted TimescaleDB instance to normal operations**

   Use [`timescaledb_post_restore`](/docs/reference/timescaledb/administration/timescaledb_post_restore/index.md):

   ```
   SELECT timescaledb_post_restore();
   ```

6. **Update your table statistics**

   Run `ANALYZE` on your entire dataset:

   ```
   ANALYZE;
   ```