> For the complete documentation index, see [llms.txt](https://upsolver.gitbook.io/content/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://upsolver.gitbook.io/content/reference-1/sql-commands/jobs/create-job/replication/postgresql.md).

# PostgreSQL

## Syntax

```sql
CREATE REPLICATION JOB <job_identifier>
    [{ job_options }]  
FROM <source_connection_name> 
    [{ source_options }]
WITH REPLICATION GROUP <group_name>
    [{ replication_group_options }]
[ WITH REPLICATION GROUP ... ];
```

See [Replication](/content/reference-1/sql-commands/jobs/create-job/replication.md) jobs for a full list of job options.

***

## Source options

#### **PostgreSQL source options**

```sql
[ HEARTBEAT_TABLE = '<heartbeat_name>' ]
[ PUBLICATION_NAME = ('regexFilter1', 'regexFilter2') ]
```

* [`HEARTBEAT_TABLE`](#heartbeat_table)
* [`PUBLICATION_NAME`](#publication_name)

#### **`HEARTBEAT_TABLE`**

Type: `string`&#x20;

The name of the heartbeat table to use as described in [Setting up a Heartbeat Table](/content/how-to-guides-1/connectors/enable-cdc/postgresql.md#setting-up-a-heartbeat-table).

(Optional) If it is not set, no heartbeat table is used. Using a heartbeat table is recommended to avoid the replication slot growing indefinitely when no CDC events are captured for the subscribed tables.&#x20;

#### `PUBLICATION_NAME`

Type: `text`

Adds a new publication to the current database. The publication name must be distinct from the name of any existing publication in the current database. DDL will be filtered.

***

## Examples

This example shows you how to create a replication job that ingests to Snowflake. To use a different target, replace the value in the `REPLICATION_TARGET` option with the name of your your target connection.&#x20;

### Ingest CDC data to Snowflake example&#x20;

The following example creates a job named **postgres\_replication\_to\_snowflake** that writes to two replication groups in Snowflake: **replicate\_to\_snowflake\_prod** for production use and **replicate\_to\_snowflake\_dev** for development.

Each group has its own set of options that can be configured differently while streaming from the same data source.&#x20;

```sql
CREATE REPLICATION JOB postgres_replication_to_snowflake
  COMMENT = 'Replicate Postgres CDC data to Snowflake groups'
  COMPUTE_CLUSTER = "Default Compute (Free)"
  INTERMEDIATE_STORAGE_CONNECTION = s3_connection
  INTERMEDIATE_STORAGE_LOCATION = 's3://upsolver-integration-tests/test/' 
FROM my_postgres_connection 
  PUBLICATION_NAME = 'orders_publication' 
  HEARTBEAT_TABLE = 'orders.heartbeat'
WITH REPLICATION GROUP replicate_to_snowflake_prod 
  INCLUDED_TABLES_REGEX = ('orders\..*')
  EXCLUDED_COLUMNS_REGEX = ('.*\.creditcard') -- exclude creditcard columns 
  COMMIT_INTERVAL = 5 MINUTES
  LOGICAL_DELETE_COLUMN = "is_deleted"
  REPLICATION_TARGET = my_snowflake_connection
  TARGET_SCHEMA_NAME_EXPRESSION = 'ORDERS'
  TARGET_TABLE_NAME_EXPRESSION = $table_name
  WRITE_MODE = MERGE 
WITH REPLICATION GROUP replicate_to_snowflake_dev 
  INCLUDED_TABLES_REGEX = ('orders\..*') 
  COMMIT_INTERVAL = 1 HOUR
  REPLICATION_TARGET = my_snowflake_connection
  TARGET_TABLE_NAME_EXPRESSION = 'history_' || $table_name
  TARGET_SCHEMA_NAME_EXPRESSION = 'ORDERS_DEV'
  WRITE_MODE = APPEND;
```

Note that the source options set the `PUBLICATION_NAME` and `HEARTBEAT_TABLE` for the PostgreSQL source connection, which is shared by both groups.&#x20;

The production replication group has a `COMMIT_INTERVAL` of **5 MINUTES** to keep the target frequently updated, whereas the development group has an interval of **1 HOUR** to update less often. Furthermore, the production group will `MERGE` the data, using the column **is\_deleted** to flag if a row is deleted in the source, and the development group will `APPEND` the data.&#x20;

Both groups replicate all tables in the **orders** schema, as specified in the `INCLUDED_TABLES_REGEX` option. However, all **creditcard** columns are excluded from the production target in the `EXCLUDED_COLUMNS_REGEX` option to remove PII.

In the development group, the `TARGET_SCHEMA_NAME_EXPRESSION` option includes the prefix value of **history\_**. This means that Upsolver will create the target table using the name of the source table with the prefix so it is clear that the target tables in this group are used for development.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://upsolver.gitbook.io/content/reference-1/sql-commands/jobs/create-job/replication/postgresql.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.