​​​​​​​Note – PostgreSQL, including Amazon Aurora PostgreSQL, is supported as a Source Database since version 6, by Repstance Advanced Edition only (see Supported Database Requirements).

The following parameters are valid for the PostgreSQL Capture Process. The Capture Process reads the changes through logical replication; the settings of the server and of the database are described in PostgreSQL as Source.

Process Parameters

• process – The only possible value is capture
• id – The Capture Process id
• dbtype – The value is postgresql
• name – Name of the Capture Process. It is the first part of the names of its trail files and cannot be changed later

Database Connectivity

• server – Host name or IP address of the PostgreSQL server. For Amazon Aurora the endpoint of the cluster
• port – Database port. The default value is 5432
• dbname – The database to capture from. It is required: the schema repi, the publication and the slot of the Capture Process are in it
• user – Database User name. The Capture Process needs the attribute REPLICATION (on Amazon RDS and Aurora the role rds_replication), SELECT on the tables to be replicated, and it has to own them: it adds them to its publication and, for a table without a primary key, sets the replica identity (see Database Privileges)
• password – Database User password

DML/DDL Replication Objects Specification

• ddlinclude – Mask of the tables whose DDL is captured. Nothing is captured unless it is listed
• ddlexclude – Mask of the tables whose DDL is skipped by the Capture Process
• dmlinclude – Mask of the tables whose changes (INSERT, UPDATE and DELETE) are captured. Nothing is captured unless it is listed
• dmlexclude – Mask of the tables whose changes are skipped by the Capture Process
• skiploopback – Specifies which changes made by Apply Processes the Capture Process skips, so that they are not replicated back in a two-way replication. The DDL statements of the Apply Processes are skipped by the Capture Process; their rows are skipped by the parameter skiploopback of the Apply Process. The Apply Processes are given by their ids, separated by commas. The value all is all of them, the value none is none of them. The default value is all
• map – The set of the parameters to determine global objects’ transformation

In PostgreSQL schema.table is the schema and the table of the database in dbname. See Capture Objects Specification for the DML and DDL objects specification details and Objects Mapping and Possible Transforms for the objects’ transformation details.

Initial Load Objects Specification

• loadinclude – Mask of the tables to be included into the Initial Load, with the optional load option, filter and chunk: schema.table[:option[:filter[:chunk]]]. The options are A (load the rows and keep the rows that the table has, the default), D (delete the rows first), T (truncate first), C (create the table), R (recreate the table) and M (merge)
• loadexclude – Mask of the tables to be skipped during the Initial Load

See chapter Initial Load for more details.

Note – The Initial Load of a table reads the rows from the snapshot of the replication slot, and the changes after the snapshot are read from the slot, so no change is lost or applied twice.

Performance Tuning Parameters

• readrows – Number of the rows of changes which the Capture Process holds at once. A transaction larger than this number is sent and written to the trail files in parts. A smaller value reduces the memory usage of the Capture Process, a larger value reduces the number of cycles. The value must have at least 3 digits. The default value is 150000
• maxtrailbytes – Size, in bytes, after which the Capture Process closes the current trail file and starts the next one. A trail file can be larger than this size by the size of the last written batch. The default value is 52428800 (50 MiB)

Note – A very small value results in a large number of small trail files.

Other Parameters

• includecomputedcols – Determines if the generated columns of the Source tables are included into the list of the replicated columns. The possible values are:
  • 0 – do not include the generated columns (default value)
  • 1 – include the generated columns
• pollingdelay – The number of seconds the Capture Process waits after each cycle, to ease the load on the Source Database. The default value is 0
• preview – This parameter is used to validate the list of the tables to be included into the replication along with the transformation rules. The possible values are:
  • 0 – do not use “preview” mode (default)
  • 1 – do not apply the configuration to the Capture Process but provide the list of the tables to be included into the replication along with the transformation
• autostart – Determines if the Process must be run automatically. The possible values are:
  • 0 – do not run the Process automatically (default value)
  • 1 – run the Process automatically, when the server starts and when the process is created
• restartattempts – Number of attempts to restart the failed Capture Process automatically. The server checks the processes every 5 minutes. The value 0 disables the automatic restart. When the attempts are used up, the notification channels (see the notification parameter) are informed. The default value is 5. The number of attempts is restored to the full value as soon as the process makes progress after a failure
• notification – The ids of the notification channels, separated by comma (see Monitoring Replication for the details on the channels), which are informed by e-mail when the process has failed and can not be restarted automatically any more. By default nobody is notified
• debuglevel – The level of debugging, possible values are 0-16, the default value is 0

The parameters secondary, cdcrestart and minimizepayload of the SQL Server Capture Process do not exist for PostgreSQL and are refused.

The Slot, the Publication and the Replica Identity

When the Capture Process runs for the first time it creates the replication slot repi_cap_{id} and the publication repi_pub_{id} with the tables that dmlinclude and dmlexclude select. A slot with this name that exists already stops a new Capture Process; drop it, or remove the old Capture Process. remove process=capture drops the slot and the publication. PostgreSQL refuses the UPDATE and the DELETE of a published table that has no replica identity, so when the Capture Process starts it gives such a table one (a table with the default identity and no primary key, and a table whose identity is nothing): a unique index of columns that are NOT NULL, or else replica identity full, which logs the whole old row. A table that has a primary key is not changed. The tables that will get one are listed by the preview as a note. A table that is created later and that the Capture Process captures gets its replica identity from the event trigger of prepare.

Note – The DDL statements that the Capture Process reads are recorded in the dictionary of the trail. An Apply Process for a Target Database of another type than PostgreSQL needs ddlprocessing=dictionary (see Prepare Apply Process).

Examples

Add a Capture Process that captures the changes and the DDL of the tables of the schema public and copies them at the first start:

Show the parameters of the process and change the Initial Load list:

A parameter that belongs to another type of Source Database is refused, and the reply lists the valid ones:

A Capture Process can be added without dbname, but it is invalid:

Check the connection and the objects of the process:

See Validate Process for the checks, and Remove Process to remove the process.