> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8a08bda2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Migrate to Managed Postgres using logical replication
> Learn how to migrate your PostgreSQL data to ClickHouse Managed Postgres using logical replication
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 Postgres native logical replication.
Prerequisites
* Access to your source PostgreSQL database.
* `psql`,`pg_dump` and `pg_restore` installed on your local machine. This is for creating empty tables in your target database. These are typically included with PostgreSQL installations. If not, you can download them from the [PostgreSQL official website](https://www.postgresql.org/download/).
* Your source database must be reachable from ClickHouse Managed Postgres. Ensure that any necessary firewall rules or security group settings allow for this connectivity. You can get the egress IP of your Managed Postgres instance by doing:
```shell theme={null}
dig +short
```
The setup
For logical replication to work, we need to ensure that the source database is set up correctly. Here are the key requirements:
* The source database must have `wal_level` set to `logical`.
* The source database must have `max_replication_slots` set to at least `1`.
* For RDS (which this guide uses as an example), you need to ensure that your parameter group has `rds.logical_replication` set to `1`.
* The source database user must have the `REPLICATION` privilege. In the case of RDS, you would run:
```sql theme={null}
GRANT rds_replication TO ;
```
* The role you use for the target database must have write privileges on the target database's objects:
```sql theme={null}
GRANT USAGE ON SCHEMA TO subscriber_user;
GRANT CREATE ON DATABASE destination_db TO subscriber_user;
GRANT pg_create_subscription TO subscriber_user;
-- Grant table rights
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA TO subscriber_user;
```
Make sure your source database is set up like this:
Schema-only dump of the source database
Before setting up logical replication, we need to create the schema in the target ClickHouse Managed Postgres database. We can do this by creating a schema-only dump of the source database using `pg_dump`:
```shell theme={null}
pg_dump \
-d 'postgresql://:@:/' \
-s \
--format directory \
-f rds-dump
```
Here:
* Replace ``, ``, ``, ``, and `` with your source database credentials.
* `-s` specifies that we want a schema-only dump.
* `--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.
In our case, we have two tables - `events` and `users`. `events` has a million rows, and `users` has a thousand rows.
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](/products/managed-postgres/quickstart#create-postgres-database). Here's what we're going to spin up for this guide:
Restore the schema to ClickHouse Managed Postgres
Now that we have the schema dump, we can restore it to our ClickHouse Managed Postgres instance using `pg_restore`:
```shell theme={null}
pg_restore \
-d 'postgresql://:@:/' \
--verbose \
rds-dump
```
Here:
* Replace ``, ``, ``, ``, and `` with your target ClickHouse Managed Postgres database credentials.
* `--verbose` provides detailed output during the restore process.
This command will create all the tables, indexes, views, and other schema objects in the target database without any data.
In our case, after running this command, we have our two tables and they're empty:
Set up logical replication
With the schema in place, we can now set up logical replication from the source database to the target ClickHouse Managed Postgres database. This involves creating a publication on the source database and a subscription on the target database.
Create a publication on the source database
Connect to your source PostgreSQL database and create a publication that includes the tables you want to replicate.
```sql theme={null}
CREATE PUBLICATION FOR TABLE table1, table2...;
```
Creating a publication FOR ALL TABLES can incur network overhead if there are many tables. It's recommended to specify only the tables you want to replicate.
Create a subscription on the target ClickHouse Managed Postgres database
Next, connect to your target ClickHouse Managed Postgres database and create a subscription that connects to the publication on the source database.
```sql theme={null}
CREATE SUBSCRIPTION demo_rds_subscription
CONNECTION 'postgresql://:@:/'
PUBLICATION ;
```
This will automatically create a replication slot on the source database and start replicating data from the specified tables to the target database. Depending on the size of your data, this process may take some time.
In our case, after setting up the subscription, the data flowed in:
New rows inserted into the source database will now be replicated to the target ClickHouse Managed Postgres database in near real-time.
Caveats and considerations
* Logical replication only replicates data changes (INSERT, UPDATE, DELETE). Schema changes (like ALTER TABLE) need to be handled separately.
* Ensure that the network connection between the source and target databases is stable to avoid replication interruptions.
* Monitor the replication lag to ensure that the target database is keeping up with the source database. Setting a suitable value for `max_slot_wal_keep_size` on the source database can help manage a growing replication slot and prevent it from consuming too much disk space.
* Depending on your use case, you might want to set up monitoring and alerting for the replication process.
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)