Skip to main content
Skip to main content
Edit this page

Quickstart for Managed Postgres

This is a quickstart guide to help you create your first Managed Postgres service and integrate it with ClickHouse. Having an existing ClickHouse instance will help you explore the full capabilities of Managed Postgres.

Private preview in ClickHouse Cloud

Create a database​

To create a new Managed Postgres service, click on the New service button in the service list of the Cloud Console. You should then be able to select Postgres as the database type.

Enter a name for your database instance and click on Create service. You will be taken to the overview page.

Your Managed Postgres instance will be provisioned and ready for use in a few minutes.

Setup integration with ClickHouse​

Now that we have tables and data in Postgres, let's replicate the tables to ClickHouse for analytics. We start by clicking on ClickHouse integration in the sidebar. First, to have some data in Postgres to move over, click on the dropdown in the button and click on Create sample data in Postgres.

You will see a success toast. This creates two tables, users and events, in the public schema with some sample data.

Then, you can click on Replicate data in ClickHouse.

In the form that follows, you can enter a name for your integration and select an existing ClickHouse instance to replicate to. If you don't have a ClickHouse instance yet, you can create one by following the Quickstart for ClickHouse Cloud guide.

Important

Make sure the ClickHouse service you select is Running before proceeding.

Click on Next, to be taken to the table picker. Here all you need to do is:

  • Select a ClickHouse database to replicate to.
  • Expand the public schema and select the users and events table we created earlier.
  • Click on Replicate data to ClickHouse.

The replication process will start, and you will be taken to the integration overview page. Being the first integration, it can take 2-3 minutes to setup the initial infrastructure. In the meantime let's check out the new pg_clickhouse extension.

pg_clickhouse extension​

pg_clickhouse is a Postgres extension we built which enables you to query ClickHouse data from a Postgres interface. A full introduction can be found here. To use the extension, connect to your Managed Postgres instance using any Postgres-compatible client and run the following SQL commands:

CREATE EXTENSION pg_clickhouse;

Then, we create what is known as a foreign data wrapper (FDW) to connect to ClickHouse:

CREATE SERVER ch FOREIGN DATA WRAPPER clickhouse_fdw
       OPTIONS(driver 'binary', host '<clickhouse_cloud_host>', dbname 'default');

You can get the host for the above by going to your ClickHouse service, clicking on Connect in the sidebar, and choosing Native.

Now, we map the Postgres user to the ClickHouse service's credentials:

CREATE USER MAPPING FOR CURRENT_USER SERVER ch 
OPTIONS (user 'default', password '<clickhouse_password>');

It's time to import data! Add the organization schema, just import it all of the tables from the remote ClickHouse database into a Postgres schema:

CREATE SCHEMA organization;
IMPORT FOREIGN SCHEMA "default" FROM SERVER ch INTO organization;

Done! You can now see all the ClickHouse tables in your Postgres client:

postgres=# \det+ organization.*

Analytics after integration​

Let's check back in on the integration page. You should see that the initial replication is complete. You can click on the name of the integration to view more details on it.

If you click on the service name, you will be taken to the ClickHouse console where you can see the two tables we replicated.