Migrate to Managed Postgres using pg_dump and pg_restore

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/managed-postgres/migrations/pg_dump-pg_restore.md)

This guide provides step-by-step instructions on how to migrate your PostgreSQL database to ClickHouse Managed Postgres using the `pg_dump` and `pg_restore` utilities.

[Private preview](https://clickhouse.com/cloud/postgres)

## Prerequisites[​](#migration-pgdump-pg-restore-prerequisites "Direct link to Prerequisites")

- Access to your source PostgreSQL database.
- `pg_dump` and `pg_restore` installed on your local machine. These are typically included with PostgreSQL installations. If not, you can download them from the [PostgreSQL official website](https://www.postgresql.org/download/).

## The setup[​](#migration-pgdump-pg-restore-setup "Direct link to The setup")

To go through the steps, let's use a sample RDS Postgres database as the source database. Something like this:

Here's what we're working with:

- Two tables - `events` and `users`. `events` has a million rows, and `users` has a thousand rows.
- `events` has an index.
- A view on top of the `events` table.
- Couple of sequences

## Create a dump of the source database[​](#migration-pgdump-pg-restore-dump "Direct link to Create a dump of the source database")

Now let's use `pg_dump` to create a dump file of the above objects. It's a simple command:

```
pg_dump \
  -d 'postgresql://<user>:<password>@<host>:<port>/<database>' \
  --format directory \
  -f rds-dump
```

Here:

- Replace `<user>`, `<password>`, `<host>`, `<port>`, and `<database>` with your source database credentials. Most Postgres providers give you a connection string that you can use directly.
- `--format directory` specifies that we want the dump in a directory format, which is suitable for `pg_restore`.
- `-f rds-dump` specifies the output directory for the dump files. Note that this directory will be created automatically and shouldn't exist beforehand.
- You can also parallelize the dump process by adding the `--jobs` flag followed by the number of parallel jobs you want to run. For more details, refer to the [pg\_dump documentation](https://www.postgresql.org/docs/current/app-pgdump.html).

Tip

You can test this process once to get a sense of how long it takes and the size of the dump file.

Here's what running this command looks like:

## Migrate the dump to ClickHouse Managed Postgres[​](#migration-pgdump-pg-restore-restore "Direct link to Migrate the dump to ClickHouse Managed Postgres")

Now that we have the dump file, we can restore it to our ClickHouse Managed Postgres instance using `pg_restore`.

### Create a Managed Postgres instance[​](#migration-pgdump-pg-restore-create-pg "Direct link to Create a Managed Postgres instance")

First, ensure you have a Managed Postgres instance set up, preferably in the same region as the source. You can follow the quick guide [here](/docs/cloud/managed-postgres/quickstart#create-postgres-database). Here's what we're going to spin up for this guide:

### Restore the dump[​](#migration-pgdump-pg-restore-restore-dump "Direct link to Restore the dump")

Now, heading back to our local machine, we can use the `pg_restore` command to restore the dump to our Managed Postgres instance:

```
pg_restore \
  -d 'postgresql://<user>:<password>@<pg_clickhouse_host>:5432/<database>' \
  --verbose \
  rds-dump
```

You can get the connection string for your Managed Postgres instance from the ClickHouse Cloud console, explained very simply [here](/docs/cloud/managed-postgres/connection).

Here too there are a couple of flags to note:

- `--verbose` provides detailed output during the restore process.
- You can also use the `--jobs` flag here to parallelize the restore process. For more details, refer to the [pg\_restore documentation](https://www.postgresql.org/docs/current/app-pgrestore.html).

In our case, it looks like this:

## Verify the migration[​](#migration-pgdump-pg-restore-verify "Direct link to Verify the migration")

Once the restore process is complete, you can connect to your Managed Postgres instance and verify that all your data and objects have been migrated successfully. You can use any PostgreSQL client to connect and run queries.
Here's what our Managed Postgres setup looks like after the migration:

We see that we have all our tables, indexes, views, and sequences intact, along with the data counts matching.

## Considerations[​](#migration-pgdump-pg-restore-considerations "Direct link to Considerations")

- Ensure that the PostgreSQL versions of the source and target databases are compatible.
  Using a pg\_dump version older than the source server may lead to missing features or restore issues. Ideally, use the same or newer major version of pg\_dump than the source database.
- Large databases may take a significant amount of time to dump and restore.
  Plan accordingly to minimize downtime, and consider using parallel dumps/restores (--jobs) where supported.
- Note that pg\_dump / pg\_restore don't replicate all database-related objects or runtime state.
  These include roles and role memberships, replication slots, server-level configuration (e.g. postgresql.conf, pg\_hba.conf), tablespaces, and runtime statistics.

## Next steps[​](#migration-pgdump-pg-restore-next-steps "Direct link to Next steps")

Congratulations! You have successfully migrated your PostgreSQL database to ClickHouse Managed Postgres using pg\_dump and pg\_restore. You're now all set to explore Managed Postgres features and its integration with ClickHouse. Here's a 10 minute quickstart to get you going:

- [Managed Postgres Quickstart Guide](/docs/cloud/managed-postgres/quickstart)

- [Prerequisites](#migration-pgdump-pg-restore-prerequisites)- [The setup](#migration-pgdump-pg-restore-setup)- [Create a dump of the source database](#migration-pgdump-pg-restore-dump)- [Migrate the dump to ClickHouse Managed Postgres](#migration-pgdump-pg-restore-restore)
        - [Create a Managed Postgres instance](#migration-pgdump-pg-restore-create-pg)- [Restore the dump](#migration-pgdump-pg-restore-restore-dump)- [Verify the migration](#migration-pgdump-pg-restore-verify)- [Considerations](#migration-pgdump-pg-restore-considerations)- [Next steps](#migration-pgdump-pg-restore-next-steps)

Was this page helpful?