Stelo Technical Documents

PostgreSQL Tips

Last Update: 22 March 2024
Product: SQDR & SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL053

Abstract

This technical document contains tips and troubleshooting hints when using SQDR for replication to and from PostgreSQL.

See also

• Quickstart Guide for using SQDR on Windows with PostgreSQL
• Quickstart Guide for using SQDR on Linux with PostgreSQL
• SQDR PostgreSQL Resource Utilization

Contents:

• Dropping Inactive Replication Slots
• Memory exhaustion on EDB PostgreSQL destination
• SSL from Tier 2 (Stelo Capture aka SQDR Plus)
• SSL from Tier 3 (Stelo Apply aka SQDR)

Dropping Inactive Replication Slots

If an agent is deleted, be sure to drop any replication slots it may have created. Failure to drop inactive replication slots could result in a storage shortage on the source system.

Connect to the database

# psql -h localhost -U postgres

and ran this query:

select
pg_size_pretty(pg_current_wal_lsn() - restart_lsn) as lag,
slot_name,
wal_status,
active
from pg_replication_slots
order by 1 desc;

lag | slot_name | wal_status | active
----+-----------+------------+--------
6621 MB | stelo_slot | extended | f
296 bytes | sqdr | reserved | t

In the above example, stelo_slot is inactive (active=false) and using 6gb of storage.

Drop it with
SELECT pg_drop_replication_slot('stelo_slot'');

Once the problematic slot(s) are dropped, Postgres will remove old WALs (Write-Ahead logs) and disk usage will drop.

Memory exhaustion on Postgres destination

Symptom:
While performing baselines of large tables to an EDB Advanced Postgres destination, the RAM usage of the postgres process on the destination system continued increasing until it reached a limit and the process aborted.

Solution:
It was discovered that auditing was enabled. Disable auditing by setting edb_audit = 'none' in the postgresql.conf file. This option is available only in EDB Postgres versions.

SSL from Tier 2 (Stelo Capture aka SQDR Plus)

To connect via TLS/SSL without validating the certificate, leave the Use SSL checkbox in the Add Agent wizard as unchecked. The JDBC driver defaults to a non-validating SSL connection.

To enable certificate validation either

• Create an agent without certificate validation. Then edit the configuration, adding the following to the sourceDbUrl property. Saving the configuration will restart the agent.
  ?ssl=true;sslrootcert=C:\ProgramData\StarQuest\sqdrplus\keystore (Windows)
  ?ssl=true;sslrootcert=C:/var/sqdrplus/keystore (Linux)
• Or create a root certificate file in the default location expected by the JDBC driver (see below) and select the Use SSL checkbox in the Add Agent Wizard.

The default location of the root certifcate store is %APPDATA%\postgresql\root.crt (Windows) or ~/.postgresql/root.crt (Linux). Examples:

• C:\Windows\system32\config\systemprofile\AppData\Roaming\postgresql\root.crt
• /home/sqdr/.postgresql/root.crt

Troubleshooting

If you encounter this error when connecting to the PostgreSQL server during creation of an agent, refer to the jetty wrapper log for details.

Request Failed 500 Server Error: the call failed on the server; see server for details

SSL from Tier 3 (Stelo Apply aka SQDR)

Refer to the documentation for the ODBC driver for details.

Here are some connection string examples extracted from the Using Connection Strings chapter of the SQDR Help file.

You can also use ODBC Configuration to configure ODBC data sources.

PostgreSQL (SQDR Postgres bundled ODBC driver)

This example shows connecting via TLS/SSL to Azure Database for Postgres without validating the certificate:

HostName=mypostgres.postgres.database.azure.com;Database=postgres;PortNumber=5432;EncryptionMethod=1;ValidateServerCertificate=0,

This example shows connecting via TLS/SSL to AWS RDS for Postgres, validating the certificate downloaded from AWS, and requiring TLS 1.2:

HostName=postgres.xxxx.us-east-1.rds.amazonaws.com;Database=postgres;PortNumber=5432;EM=1;VSC=1;TrustStore=C:\aws\global-bundle.pem;CPV=TLSV1.2

 

PostgreSQL (psqlODBC driver)

Servername=mypostgres.postgres.database.azure.com;Database=postgres;Port=5432;MaxVarcharSize=32767;MaxLongVarcharSize=214748364;SSLMode=require

Servername=postgres.xxxx.us-east-1.rds.amazonaws.com;Database=postgres;Port=5432;MaxVarcharSize=32767;MaxLongVarcharSize=214748364;SSLMode=verify-ca;pqopt={sslrootcert=C:\\aws\\global-bundle.pem}