PostgreSQL as a Source Database

Home / Repstance User Guide / DATABASE CONFIGURATION COMMANDS / Prepare Source Database / PostgreSQL as Source

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

These parameters are used by prepare, validate and remove with database=source dbtype=postgresql (see Prepare Source Database, Validate Database and Remove Repstance Objects from Database). The login that is given in user needs the privileges that are described in Database Privileges. PostgreSQL 12 or later is supported, on a server of your own, on Amazon RDS and on Amazon Aurora PostgreSQL.

Requirements of the Server

The Capture Process reads the changes through logical replication. validate checks the settings below, and the Capture Process checks them again every time it starts:

Setting	Value	Remark
version	12 or later
wal_level	logical	On Amazon RDS and Aurora set rds.logical_replication to 1 in the parameter group and restart the instance.
max_replication_slots	more than 0, and a slot that is free	Every Capture Process has a slot of its own, repi_cap_{id}.
max_wal_senders	more than 0
the login	the attribute REPLICATION	On Amazon RDS and Aurora the role rds_replication; a superuser also qualifies.

The settings can be read with:

select current_setting('wal_level') wal_level, current_setting('max_replication_slots') max_slots, (select count(*) from pg_replication_slots) used_slots, current_setting('max_wal_senders') max_senders;

Amazon Aurora PostgreSQL. Set rds.logical_replication to 1 in the DB cluster parameter group, reboot the writer instance and connect to the writer, the endpoint of the cluster: the changes are not available on a reader.

The slot keeps the log of the server. The slot of a Capture Process holds the write-ahead log from the position that the Capture Process has confirmed. While the Capture Process is stopped the log piles up on the server. Remove a Capture Process that is not needed any more (see Remove Process): it drops the slot. When the server has dropped the log of a slot (max_slot_wal_keep_size), the Capture Process reports it and has to be removed and prepared again.

Parameters

Parameter	Description
database	source. Required.
dbtype	postgresql. Required.
generatesql	1 makes prepare return the SQL script and change nothing. The default is 0: prepare creates the objects. validate does not use the parameter, and remove removes the objects whatever its value.
server	The host name or the IP address of the PostgreSQL server. For Aurora the endpoint of the cluster. Required.
port	The TCP port of the PostgreSQL server, usually 5432. Required.
user	The login that Repstance connects with. Required. For prepare it needs the right to create the schema repi and event triggers (see below).
password	The password of the login. Required.
dbname	The database to prepare and to capture from: the schema repi is created in it. Required: without it the command is answered with No database is provided. A Capture Process captures the tables of one database.

The parameters of the other Source Databases that PostgreSQL has no use for, for example housekeeping of SQL Server, are refused.

Examples

Check the server, the login and the Repstance objects:

repcli validate database=source dbtype=postgresql server=pg-src.example.com port=5432 dbname=salesdb user=repuser password=Passw0rd123

The answer depends on what is missing. The objects of Repstance do not exist yet (the number, from 1 to 6, names the first object that is missing):

[Invalid]: Database is not prepared for capture: [Schema objects do not exist: 1]

The server does not log the changes:

[Invalid]: wal_level is "replica", it has to be logical (Amazon RDS: set rds.logical_replication to 1 in the parameter group and restart the instance)

The login cannot use the replication protocol:

[Invalid]: the user cannot read the replication stream: it needs the attribute REPLICATION, or the role rds_replication (Amazon RDS), or to be a superuser

Everything is in place:

[Valid]: Database SALESDB is valid

Prepare the database:

repcli prepare database=source dbtype=postgresql server=pg-src.example.com port=5432 dbname=salesdb user=repuser password=Passw0rd123
[Completed]: Database [pg-src.example.com:5432,salesdb] has been prepared.

Return the script instead of running it. The script goes on for about 170 lines; an administrator runs it with psql:

repcli prepare database=source dbtype=postgresql server=pg-src.example.com port=5432 dbname=salesdb user=repuser password=Passw0rd123 generatesql=1
[Completed]:
---- SQL ----
create schema if not exists repi;
create table if not exists repi.ld_capture (
  id int generated by default as identity primary key,
  capture_id int not null,
  schema_name varchar(255) not null,
...

Remove the objects:

repcli remove database=source dbtype=postgresql server=pg-src.example.com port=5432 dbname=salesdb user=repuser password=Passw0rd123
[Completed]: Capture objects have been removed [pg-src.example.com:5432,salesdb]

A database that is not given, a parameter that is missing and a server that cannot be reached:

repcli prepare database=source dbtype=postgresql server=pg-src.example.com port=5432 user=repuser password=Passw0rd123
[Failed]: No database is provided

repcli validate database=source dbtype=postgresql server=pg-src.example.com port=5432 dbname=salesdb user=repuser
[Failed]: Missed password parameter
  password                Password of the login. [required]

repcli validate database=source dbtype=postgresql server=127.0.0.1 port=1 dbname=salesdb user=repuser password=Passw0rd123
[Invalid]: failed to connect to `user=repuser database=salesdb`: 127.0.0.1:1 (127.0.0.1): dial error: dial tcp 127.0.0.1:1: connect: connection refused

What the Commands Do

prepare creates the schema repi in the database with the tables ld_capture (the tables of the Initial Load), ValidateTables (the tasks of the Data Comparison), capture_scope (the tables of the publication of every Capture Process) and ddl_audit (the DDL statements), the functions table_cols, log_ddl and log_drop, and the event triggers repi_ddl and repi_drop. Logical replication carries no DDL, so the event triggers write the CREATE and ALTER statements that change a table, its columns or its indexes, and the DROP of a table, into repi.ddl_audit, and the Capture Process reads them from there in the order of the transactions. A trigger that fails raises a warning and does not fail the statement of the user. A new table that a Capture Process captures and that has no primary key is given a replica identity. Creating event triggers needs a superuser; on Amazon RDS and Aurora a member of rds_superuser. The schema needs CREATE on the database. The command can be run again: it creates what is missing and replaces the functions and the triggers. The tables that are replicated are not changed, and the command creates no login. The slot and the publication of a Capture Process are not made by prepare: the Capture Process makes them when it runs for the first time. With generatesql=1 no connection is made.
validate connects to the database, checks the settings of the table above and then the objects of Repstance: the tables ld_capture, ddl_audit, ValidateTables and capture_scope, the function log_ddl and the two event triggers (both have to be enabled). The number in the answer tells which one is missing; the list is in Capture and Source Databases.
remove drops the event triggers, the functions and the tables and then the schema repi, unless something else is in it (the objects of an Apply Process of the same database stay). The tables that are replicated are not touched. The slots and the publications of the Capture Processes are not touched either: remove process=capture drops them (see Remove Process).

The parameters of the Capture Process are described in PostgreSQL Capture Parameters.

Web UI

PostgreSQL is offered as a Source in Repstance Advanced Edition only. Click “Add Capture” on the dashboard, enter the id and the name of the process and click the logo of PostgreSQL. The form “Database Settings” has the fields “Server”, “Port” (5432 is entered), “Username”, “Password” and “Database Name”, which is required. The settings of SQL Server (“Housekeeping”, “Secondary” and “Full DDL Capture”) do not apply to PostgreSQL. “Test connection” is validate. The buttons “Prepare for Replication” and “Show Prepare SQL” appear after it and work as described in Prepare Source Database.

MySQL as Source

Prepare Target Database