> ## 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.
> Set up Postgres with the TimescaleDB extension as a source for ClickPipes
# Postgres with TimescaleDB source setup guide
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 ;
};
Background
[TimescaleDB](https://github.com/timescale/timescaledb) is an open-source Postgres extension developed by Timescale Inc
that aims to boost the performance of analytics queries without having to move away from Postgres. This is achieved by
creating "hypertables" which are managed by the extension and support automatic partitioning into "chunks".
Hypertables also support transparent compression and hybrid row-columnar storage (known as "hypercore"), although these
features require a version of the extension that has a proprietary license.
Timescale Inc also offers two managed services for TimescaleDB:
* `Managed Service for Timescale`
* `Timescale Cloud`.
There are third-party vendors offering managed services that allow you to use the TimescaleDB extension, but due to
licensing, these vendors only support the open-source version of the extension.
Timescale hypertables behave differently from regular Postgres tables in several ways. This poses some complications
to the process of replicating them, which is why the ability to replicate Timescale hypertables should be considered as
**best effort**.
Supported Postgres versions
ClickPipes supports Postgres version 12 and later.
Enable logical replication
The steps to be follow depend on how your Postgres instance with TimescaleDB is deployed.
* If you're using a managed service and your provider is listed in the sidebar, please follow the guide for that provider.
* If you're deploying TimescaleDB yourself, follow the generic guide.
For other managed services, please raise a support ticket with your provider to help in enabling logical replication if
it isn't already.
Timescale Cloud doesn't support enabling logical replication, which is needed for Postgres pipes in CDC mode.
As a result, users of Timescale Cloud can only perform a one-time load of their data (`Initial Load Only`) with the
Postgres ClickPipe.
Configuration
Timescale hypertables don't store any data inserted into them. Instead, the data is stored in multiple corresponding
"chunk" tables which are in the `_timescaledb_internal` schema. For running queries on the hypertables, this isn't an
issue. But during logical replication, instead of detecting changes in the hypertable we detect them in the chunk table
instead. The Postgres ClickPipe has logic to automatically remap changes from the chunk tables to the parent hypertable,
but this requires additional steps.
If you'd like to only perform a one-time load of your data (`Initial Load Only`), please skip steps 2 onward.
1. Create a dedicated user for ClickPipes:
```sql theme={null}
CREATE USER clickpipes_user PASSWORD 'some-password';
```
2. Grant schema-level, read-only access to the user you created in the previous step. The following example shows permissions for the `public` schema. Repeat these commands for each schema containing tables you want to replicate:
```sql theme={null}
GRANT USAGE ON SCHEMA "public" TO clickpipes_user;
GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO clickpipes_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO clickpipes_user;
```
3. Grant replication privileges to the user:
```sql theme={null}
ALTER USER clickpipes_user WITH REPLICATION;
```
4. As a Postgres superuser/admin, create a [publication](https://www.postgresql.org/docs/current/logical-replication-publication.html) with the hypertables you want to replicate. The publication **must also include the entire `_timescaledb_internal` schema** so the pipe can receive changes from the underlying chunks. We strongly recommend only including the tables you need in the publication to avoid performance overhead.
Any table included in the publication must either have a **primary key** defined *or* have its **replica identity** configured to `FULL`. See the [Postgres FAQs](/integrations/clickpipes/postgres/faq#how-should-i-scope-my-publications-when-setting-up-replication) for guidance on scoping.
```sql theme={null}
-- When adding new tables to the ClickPipe, you'll need to add them to the publication manually as well.
CREATE PUBLICATION clickpipes FOR TABLE table_to_replicate, table_to_replicate2, TABLES IN SCHEMA _timescaledb_internal;
```
The `clickpipes` publication will contain the set of change events generated from the specified tables, and will later be used to ingest the replication stream.
Some managed services don't give their admin users the required permissions to create a publication for an entire schema. If this is the case, raise a support ticket with your provider. Alternatively, you can skip this step (and the following steps) and perform a one-time load of your data instead.
After these steps, you should be able to proceed with [creating a ClickPipe](/integrations/clickpipes/postgres).
If you want to restrict traffic to your Timescale instance, please allowlist the [documented static NAT IPs](/integrations/clickpipes/home#list-of-static-ips).
Instructions to do this will vary across providers, please consult the sidebar if your provider is listed or raise a
ticket with them.