Stelo Technical Documents

SQDR Plus PostgreSQL Resource Utilization

Last Update: 25 May 2023
Product: SQDR Plus
Version: 4.0 and later
Article ID: SQV00PL077

Abstract

This technical document describes the interactions of SQDR Plus and SQDR with a PostgreSQL source database.

Solution

SQDR technology is designed to have a minimal impact on PostgreSQL operations. To accomplish real time replication, the data layout (schema) and image of the data (copy) must first be acquired. Subsequently, the changes to the database are obtained by monitoring the PostgreSQL database logs for changes.

Because no application software is installed on the source system and all access is performed using industry-standard protocols, SQDR Plus and SQDR can work with PostgreSQL running on any supported platform (e.g. Windows, Linux, MacOS, Solaris, and cloud services such AWS RDS, AWS Aurora, and Azure Database for PostgreSQL).

Schemas and baseline images are acquired by the SQDR client using an ODBC driver, either the bundled SQDR Postgres driver or psqlODBC. The default port is 5432. The source user specified in the Data Replicator Manager does not require any special privileges; it simply needs read access to the source tables to be replicated and to the SQDR Plus-created schema and control tables.

SQDR Plus accesses the source system using the PostgreSQL JDBC driver. The userID used by SQDR Plus must either be a superuser or the owner of the files to be replicated and of the database itself; in addition, the replication role must be granted to the userID (e.g. with pgAdmin with the SQL statement ALTER ROLE myuser WITH REPLICATION.

The source database must be configured for wal_level = logical; this is done either by editing the postgresql.conf (for systems where you have access to the underlying filesystem) or, in the case of cloud services such as AWS & Azure, by modifying the configuration as documented in the QuickStart Guide or the AWS/Azure/etc documentation. A restart of the instance is required after making the configuration change.

Users

When working with a PostgreSQL source, SQDR Plus does not create any new users on the source system. Instead, it communicates with the source system using an existing userID supplied during agent creation. As mentioned above, this user must either be a superuser or the owner of the files to be replicated and of the database itself and the replication role must be granted to the userID.

The user specified when configuring a source in Data Replicator Manager (for use by SQDR) does not require any special privileges; it simply needs read access to the source tables to be replicated and to the schema and control tables created by SQDR Plus.

Schema and Objects

During creation of an SQDR Plus Staging Agent, you can specify the name of the control schema (default sqdr). When the agent is started for the first time, the schema is created on the host and populated with the following control tables and stored procedures:

Control Tables:
SQ_BASELINES SQ_CATALOG SQ_CQUEUE SQ_PROPERTIES SQ_READERS SQ_WORKERS

A publication (PostgreSQL term) with the name of the schema is created in the database, and a replication slot by the same name is created in the instance. This name must be unique across all databases in the PostgreSQL instance. For example, if you create two agents to replicate from two different databases in the same instance, use different names for the schema.

If you are deleting and recreating agents while testing, you may need to clean up the publications and slots in order to re-use the names e.g.

SELECT pg_drop_replication_slot('sqdr');
DROP PUBLICATION sqdr;

Publications can also be viewed and deleted using pgAdmin.

When a table is selected for replication, the agent performs the following ALTER:

ALTER TABLE schema.table REPLICA IDENTITY FULL

 

Several configuration parameters affect replication:

• wal_level = logical
• max_replication_slots: Specifies the maximum number of replication slots. The default is 10. This may affect the number of agents that can be created for a PostgreSQL instance.
• max_wal_senders: Specifies the maximum number of concurrent connections from standby servers or streaming base backup clients (i.e., the maximum number of simultaneously running WAL sender processes). The default is 10. This may affect the number of agents that can be created for a PostgreSQL instance.
• wal_keep_size: Specifies the minimum size of past log file segments kept in the pg_wal directory
• max_slot_wal_keep_size: Specify the maximum size of WAL files that replication slots are allowed to retain in the pg_wal directory at checkpoint time. If max_slot_wal_keep_size is -1 (the default), replication slots may retain an unlimited amount of WAL files.

See Replication in the PostgreSQL documentatation for details.