> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-fbfa8bee.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Migrate to Managed Postgres using PeerDB
> Learn how to migrate your PostgreSQL data to ClickHouse Managed Postgres using PeerDB
export const Image = ({img, alt, size}) => {
return
;
};
export const galaxyOnClick = eventName => () => {
try {
if (typeof window !== "undefined" && window.galaxy && eventName) {
window.galaxy.track(eventName, {
interaction: "click"
});
}
} catch (e) {}
};
export const BetaBadge = ({link, galaxyTrack, galaxyEvent}) => {
if (link) {
return
Beta
;
}
return ;
};
This guide provides step-by-step instructions on how to migrate your PostgreSQL database to ClickHouse Managed Postgres using PeerDB.
Prerequisites
* Access to your source PostgreSQL database.
* A ClickHouse Managed Postgres instance where you want to migrate your data.
* PeerDB installed on a machine. You can follow the installation instructions on the [PeerDB GitHub repository](https://github.com/PeerDB-io/peerdb?tab=readme-ov-file#get-started). You just need to clone the repository and run `docker-compose up`. For this guide, we will be using **PeerDB UI**, which will be accessible at `http://localhost:3000` once PeerDB is running.
Considerations before migration
Before starting your migration, keep the following in mind:
* **Database objects**: PeerDB will create tables automatically in the target database based on the source schema. However, certain database objects like indexes, constraints, and triggers won't be migrated automatically. You'll need to recreate these objects manually in the target database after the migration.
* **DDL changes**: If you enable continuous replication, PeerDB will keep the target database in sync with the source for DML operations (INSERT, UPDATE, DELETE) and will propagate ADD COLUMN operations. However, other DDL changes (like DROP COLUMN, ALTER COLUMN) aren't propagated automatically. More on schema changes support [here](/integrations/clickpipes/postgres/schema-changes)
* **Network connectivity**: Ensure that both the source and target databases are reachable from the machine where PeerDB is running. You may need to configure firewall rules or security group settings to allow connectivity.
Create peers
First, we need to create peers for both the source and target databases. A peer represents a connection to a database. In PeerDB UI, navigate to the "Peers" section by clicking on "Peers" in the sidebar. To create a new peer, click on the `+ New peer` button.
Source peer creation
Create a peer for your source PostgreSQL database by filling in the connection details such as host, port, database name, username, and password. Once you have filled in the details, click on the `Create peer` button to save the peer.
Target peer creation
Similarly, create a peer for your ClickHouse Managed Postgres instance by providing the necessary connection details. You can get the [connection details](/products/managed-postgres/connection) for your instance from the ClickHouse Cloud console. After filling in the details, click on the `Create peer` button to save the target peer.
Now, you should see both the source and target peers listed in the "Peers" section.
Obtain source schema dump
To mirror the setup of the source database in the target database, we need to obtain a schema dump of the source database. You can use `pg_dump` to create a schema-only dump of your source PostgreSQL database:
**Ubuntu:**
Update package lists:
```shell theme={null}
sudo apt update
```
Install PostgreSQL client:
```shell theme={null}
sudo apt install postgresql-client
```
**macOS:**
Method 1: Using Homebrew (Recommended)
Install Homebrew if you don't have it:
```shell theme={null}
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
```
Install PostgreSQL:
```shell theme={null}
brew install postgresql
```
Verify installation:
```shell theme={null}
pg_dump --version
```
```shell theme={null}
pg_dump -d 'postgresql://:@:/' -s > source_schema.sql
```
Remove unique constraints and indexes from the schema dump
Before applying this to the target database, we need to remove UNIQUE constraints and indexes from the dump file so that PeerDB ingestion to target tables is not blocked by these constraints. These can be removed using:
```shell theme={null}
# Preview
grep -n "CONSTRAINT.*UNIQUE"
grep -n "CREATE UNIQUE INDEX"
grep -n -E "(CONSTRAINT.*UNIQUE|CREATE UNIQUE INDEX)"
# Remove
sed -i.bak -E '/CREATE UNIQUE INDEX/,/;/d; /(CONSTRAINT.*UNIQUE|ADD CONSTRAINT.*UNIQUE)/d'
```
Apply schema dump to target database
After cleaning up the schema dump file, you can apply it to your target ClickHouse Managed Postgres database by [connecting](/products/managed-postgres/connection) via `psql` and running the schema dump file:
```shell theme={null}
psql -h -p -U -d -f source_schema.sql
```
Here on the target side, we do not want PeerDB ingestion to be blocked by foreign key constraints. For this, we can alter the target role (used above in the target peer) to have `session_replication_role` set to `replica`:
```sql theme={null}
ALTER ROLE SET session_replication_role = replica;
```
Create a mirror
Next, we need to create a mirror to define the data migration process between the source and target peers. In PeerDB UI, navigate to the "Mirrors" section by clicking on "Mirrors" in the sidebar. To create a new mirror, click on the `+ New mirror` button.
1. Give your mirror a name that describes the migration.
2. Select the source and target peers you created earlier from the dropdown menus.
3. Make sure that:
* Soft delete is OFF.
* Expand `Advanced settings`. Make sure that the **Postgres type system is enabled** and **PeerDB columns are disabled**.
4. Select the tables you want to migrate. You can choose specific tables or select all tables from the source database.
**Selecting tables**
Make sure the destination table names are the same as the source table names in the target database, as we have migrated the schema as is in the earlier step.
5. Once you have configured the mirror settings, click on the `Create mirror` button.
You should see your newly created mirror in the "Mirrors" section.
Wait for the initial load
After creating the mirror, PeerDB will start the initial data load from the source to the target database. You can click on the mirror and click on the **Initial load** tab to monitor the progress of the initial data migration.
Once the initial load is complete, you should see a status indicating that the migration is finished.
Monitoring initial load and replication
If you click on the source peer, you can see a list of running commands which PeerDB is running. For instance:
1. Initially we run a COUNT query to estimate the number of rows in each table.
2. Then we run a partitioning query using NTILE to break down large tables into smaller chunks for efficient data transfer.
3. We then do FETCH commands to pull data from the source database and then PeerDB syncs them to the target database.
Post-migration tasks
These steps may vary based on your specific use case and application requirements. The key is to ensure data consistency, minimize downtime, and validate the integrity of the migrated data before fully switching over to the new system.
After the migration is complete:
* **Run pre-cutover validation checks**
Compare key tables between source and target before switching traffic:
```sql theme={null}
-- Row count comparison for critical tables
SELECT 'public.orders' AS table_name, COUNT(*) AS row_count FROM public.orders;
SELECT 'public.customers' AS table_name, COUNT(*) AS row_count FROM public.customers;
-- Spot-check latest records in high-activity tables
SELECT MAX(updated_at) FROM public.orders;
SELECT MAX(id) FROM public.orders;
```
* **Stop writes on the source system**
Pause application writes first. As an additional safeguard, set the source database to read-only during cutover:
```sql theme={null}
ALTER DATABASE SET default_transaction_read_only = on;
```
If rollback is needed, you can re-enable writes:
```sql theme={null}
ALTER DATABASE SET default_transaction_read_only = off;
```
* **Confirm replication is fully caught up**
Check that the latest row in one or more high-write tables matches on source and target:
```sql theme={null}
-- Run on both source and target and compare results
SELECT MAX(id) AS latest_id, MAX(updated_at) AS latest_ts FROM public.orders;
```
* **Recreate and enable constraints, indexes, and triggers**
If you removed or deferred constraints/indexes for ingestion, re-apply them now. Also reset the replication role on target if you previously set it to `replica`:
```sql theme={null}
ALTER ROLE SET session_replication_role = origin;
```
```shell theme={null}
# Example: apply a SQL file containing constraints/indexes/triggers
psql -h -p -U -d -f post_migration_objects.sql
```
* **Reset sequences on target tables**
After data load, align sequences with current table values:
```sql theme={null}
-- Generic sequence reset for all serial/identity-backed columns in non-system schemas
DO $$
DECLARE r RECORD;
BEGIN
FOR r IN
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
a.attname AS column_name,
pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) AS seq_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relkind = 'r'
AND a.attnum > 0
AND NOT a.attisdropped
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
LOOP
IF r.seq_name IS NOT NULL THEN
EXECUTE format(
'SELECT setval(%L, COALESCE((SELECT MAX(%I) FROM %I.%I), 0) + 1, false)',
r.seq_name, r.column_name, r.schema_name, r.table_name
);
END IF;
END LOOP;
END $$;
```
* **Cut over application traffic**
Once validation passes and sequences/constraints are in place:
1. Point read traffic to ClickHouse Managed Postgres.
2. Point write traffic to ClickHouse Managed Postgres.
3. Monitor application errors, constraint violations, and database health.
* **Clean up resources**
Once you're satisfied with the migration and have switched your application to use ClickHouse Managed Postgres, you can delete the mirror and peers in PeerDB.
**Replication slots**
If you enabled continuous replication, PeerDB will create a **replication slot** on the source PostgreSQL database. Make sure to drop the replication slot manually from the source database after you're done with the migration to avoid unnecessary resource usage.
References
* [ClickHouse Managed Postgres Documentation](/products/managed-postgres/overview)
* [PeerDB guide for CDC creation](https://docs.peerdb.io/mirror/cdc-pg-pg)
* [Postgres ClickPipe FAQ (holds true for PeerDB as well)](/integrations/clickpipes/postgres/faq)
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](/products/managed-postgres/quickstart)