PostgreSQL cluster

This guide explains how to add standby PostgreSQL 16 database to existing YSoft SafeQ 6 installation running with embedded PostgreSQL 16 database.

Overview

PostgreSQL offers various ways how to achieve high availability, replication and archiving of PostgreSQL databases. This guide is aimed to describe how to setup a database cluster with one primary and one standby database (in hot-standby mode), that will use Streaming Replication mechanism to replay all data from primary to standby database. It will also describes how to configure automatic fail-over service with user notification and describe how to recover failed databases in order to restore the replication feature. 

More information about Streaming Replication, WAL files and others PostgreSQL concepts could be seen in official documentation here  https://www.postgresql.org/docs/16/admin.html

Supported database architectures

There is possibility to use replication for all database deployment types. Now we support Single Server Single Database deployment only.

Single Server Single Database deployment (SSSD)

Basic deployment with all schemes placed in one database. We replicate all data. Supported embedded and external PostgreSQL installation.

Single Server Multiple Database deployment (SSMD)

Enterprise deployment with separated warehouse database. We replicate all data. Inter database connectivity is provided by db_link via localhost IP, because of replication needs. Supported embedded and external PostgreSQL installation.

Multi Server Multiple Database deployment (MSMD) - not supported yet

Special deployment with separated warehouse server. We replicate only working database. Warehouse database fail-over is provided by regular database backup. Supported external PostgreSQL installation only.

Limitations

Database validation

Running database validation within PostgreSQL cluster requires that database referenced in DBValidator.properties file is current master (read/write mode). Before running validation it is recommended to check out which database is current master, then adjust DBValidator.properties according these findings and then run database validation.

External data warehouse database replication is not supported

Streaming Replications, Automatic fail-over and Recovery scenarios are not designed to be used on external data warehouse database. Hence in the deployment scenario  First server installation with standalone data warehouse database only production database could be setup using Streaming Replications and automatic fail-over.

Network connectivity

Guaranteed network connectivity between database nodes is required.

YSoft SafeQ 6 server update

In case of Updating YSoft SafeQ 6, the database role has to be the same as it was in the time of the installation - meaning the database, that was configured as master has to be master again, and previously configured standby database has to be also standby database now. In case the Fail-over and subsequent Recovery had been applied, the roles of the databases has been switched in which case one of the following action has to be taken to restore the original database deployment, either:

• Promote current slave to primary master and recover current slave to primary master.

• Change etcd configuration to follow current database cluster state ( ).

Configuration

Prerequisites

• Windows administrator privilege to give Full control permission on database folders

• Sufficient disk space on both primary and standby database servers. In addition to database size, the WAL replication files may require several GB, depending on the configuration, see bellow examples for wal_keep_size configuration property

• PostgreSQL 16 binaries 

Both single node and clustered installations are supported. This guide refers to the original database created during installation as a primary database and newly added database is referred as a standby database.

This guide will use following placeholders with sample values:

This guide refers to pg_hba.conf and postgresql.conf files. All these reside in PostgreSQL data directory (e.g. in case of embedded PostgreSQL installation the path is <safeq_folder>\Management\PGSQL-data). All SQL command in this guide could be executed either using psql CLI tool (located in PGSQL/bin folder) or using pgAdmin 4 web interface.

PostgreSQL cluster setup

1. Stop YSoft SafeQ services. In clustered environment this needs to be done on each cluster node.

2. Use binary database files from full installation package (path <installation package\Complementary Solutions\PostgreSQL\) or download PostgreSQL 16 binaries.

3. Extract database binaries to target directory on server for standby database, for example: c:/PGSQL.

4. Create data directory for standby database, for example: c:/PGSQL-data.

5. Make sure Administrator account has Full control permission granted on both directories. 
   On each directory: right click and select Properties → switch to Security tab → click Advanced → on Permissions tab → click Add
   
   Click on Select a principal link → fill in Administrator account (in our example it is RND0171\Administrator, this might differ on production environment) → click OK.
   
   Check Full control checkbox → click OK.
   
   
   

6. On primary database create user for replication,

   CREATE USER replicator
   WITH REPLICATION
   ENCRYPTED PASSWORD 'replicator'
   

7. On primary database, configure following options in postgresql.conf file.  The lines will be commented, uncomment them as below. 

   listen_addresses = '*'
   max_wal_senders = 2
   wal_level = replica
   wal_keep_size = 180
   

8. On primary database, add following entries in pg_hba.conf directly underneath the #TYPE DATABASE USER ADDRESS METHOD 

   # TYPE  DATABASE        USER            ADDRESS                                     METHOD
   host    all             all             <PRIMARY_DATABASE_IP_ADDRESS>/32            md5
   host    replication     replicator      <PRIMARY_DATABASE_IP_ADDRESS>/32            md5
   host    replication     replicator      <STANDBY_DATABASE_IP_ADDRESS>/32            md5
   host    all             all             <STANDBY_DATABASE_IP_ADDRESS>/32            md5
   

   with example values in place entries in pg_hba.conf will like this:

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   host    all             all             10.0.124.110/32            md5
   host    replication     replicator      10.0.124.110/32            md5
   host    replication     replicator      10.0.124.151/32            md5
   host    all             all             10.0.124.151/32            md5
   

   Please note, that allowing connection for replicator user on both primary and standby databases is required by recovery scenarios.

9. On standby database, take base backups with following command (port needs to be same port as was originally used when setting up management server DB connection in installer):

   pg_basebackup -h <PRIMARY_DATABASE_IP> -U replicator -p 5433 -D <PATH_TO_STANDBY_DATABASE_DATA_FOLDER> -Xs -R -P -v 
   

   with example values in place, pg_basebackup will execute as

   pg_basebackup -h 10.0.124.110 -U replicator -p 5433 -D "c:/PGSQL-data/" -Xs -R -P -v 
   

   The default location of pg_basebackup CLI tool is in  <PATH_TO_STANDBY_DATABASE_ROOT_FOLDER>/bin.

   In the PostgreSQL data directory, add the following content to the postgresql.conf file (assuming 10.0.124.110 was used as primary database IP address - PRIMARY_DATABASE_IP_ADDRESS). See official Standby Server Operation for more details.

   PostgreSQL 11 and below used a configuration file named recovery.conf to manage replicas and standbys. Support for this file was removed in PostgreSQL 12.

   The server will not start if a recovery.conf exists.

   See official guide for more details.

   primary_conninfo = 'user=replicator password=replicator host=10.0.124.110 port=5433 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'
   

   sslmode=prefer in postgresql.conf is default and backward compatible, but it is not recommended for secure deployments. Use sslmode=verify-full for data encryption and for protection against man-in-the-middle attacks. See official SSL guide for more details.

   hot_standby option should be enabled in postgresql.conf on standby database

   hot_standby = on
   

   pg_hba.conf on standby database should have following entries

   # TYPE  DATABASE        USER            ADDRESS                                     METHOD
   host    all             all             <STANDBY_DATABASE_IP_ADDRESS>/32            md5
   host    replication     replicator      <STANDBY_DATABASE_IP_ADDRESS>/32            md5
   host    replication     replicator      <PRIMARY_DATABASE_IP_ADDRESS>/32            md5
   host    all             all             <PRIMARY_DATABASE_IP_ADDRESS>/32            md5
   

   with example values in place, pg_hba.conf entries will look like:

   # TYPE  DATABASE        USER            ADDRESS                    METHOD
   host    all             all             10.0.124.151/32            md5
   host    replication     replicator      10.0.124.151/32            md5
   host    replication     replicator      10.0.124.110/32            md5
   host    all             all             10.0.124.110/32            md5
   

10. Update safeq.properties. (C:\SafeQ6\Management\conf\safeq.properties)

    database.host = <PRIMARY_DATABASE_IP_ADDRESS>
    database.url = jdbc:postgresql://${database.host}:${database.port},<STANDBY_DATABASE_IP_ADDRESS>:5433/${database.name}?charSet=UTF-8&targetServerType=master
    

    In case of Management clustered environment, update safeq.properties each Management cluster node.

    with example values in place:

    database.host = 10.0.124.110
    database.url = jdbc:postgresql://${database.host}:${database.port},10.0.124.151:5433/${database.name}?charSet=UTF-8&targetServerType=master
    

    For multidatabase deployments - deployments with separate database for warehouse - make sure that connection URL for warehouse database is configured with both primary database and standby database.

    databaseWarehouse.url = jdbc:postgresql://${database.host}:${database.port},<STANDBY_DATABASE_IP_ADDRESS>:5433/${databaseWarehouse.name}?charSet=UTF-8&targetServerType=master
    

    with example values in place

    databaseWarehouse.url = jdbc:postgresql://${database.host}:${database.port},10.0.124.151:5433/${databaseWarehouse.name}?charSet=UTF-8&targetServerType=master
    

11. Update application.properties for IMS service. In clustered environment this needs to be done on each cluster node.

    spring.datasource.url = jdbc:postgresql://<PRIMARY_DATABASE_IP_ADDRESS>:5433,<STANDBY_DATABASE_IP_ADDRESS>:5433/SQDB6_IMS?charSet=UTF-8&targetServerType=master
    

    with example values in place

    spring.datasource.url = jdbc:postgresql://10.0.124.110:5433,10.0.124.151:5433/SQDB6_IMS?charSet=UTF-8&targetServerType=master
    
    

12. If you have installed YSoft SafeQ Payment System update environment-configuration.properties. 

    database.url=jdbc:postgresql://<PRIMARY_DATABASE_IP_ADDRESS>:5433,<STANDBY_DATABASE_IP_ADDRESS>:5433/SQDB6_YPS?targetServerType=master
    

    with example values in place

    database.url=jdbc:postgresql://10.0.124.110:5433,10.0.124.151:5433/SQDB6_YPS?targetServerType=master
    

13. Setup PostgreSQL database service Startup type to Auto on both primary database and standby database. 

    
    
    

14. Start database services on both primary database and standby database. On standby database there is no PostgreSQL service registered in local services. Switch to directory with extracted PostgreSQL 16 binaries (this guide uses: C:\PGSQL), navigate to its bin subdirectory. Use following command to start PostgreSQL service from command line.

    pg_ctl.exe start -D "<PATH_TO_STANDBY_DATABASE_DATA_FOLDER>" -w
    

    with example values in place

    pg_ctl.exe start -D "c:/PGSQL-data" -w
    

    For ease of use, you can register a service from pg_ctl executable:

    SC CREATE "YSoftPGSQL" start=auto binpath=""<PATH_TO_STANDBY_DATABASE_ROOT_FOLDER>\bin\pg_ctl.exe" runservice -N "YSoftPGSQL" -D "<PATH_TO_STANDBY_DATABASE_DATA_FOLDER>" -w
    

    with example values in place

    SC CREATE "YSoftPGSQL" start=auto binpath=""C:\PGSQL\bin\pg_ctl.exe" runservice -N "YSoftPGSQL" -D "C:\PGSQL-data" -w"
    

    The new service may need to be started manually. After starting it on standby database, check PostgreSQL logs. In newest log following log entries should indicate that database cluster is up and running. 

    LOG:  entering standby mode
    LOG:  redo starts at 0/945AFF8
    LOG:  consistent recovery state reached at 0/945B0D8
    LOG:  database system is ready to accept read only connections
    LOG:  invalid record length at 0/945B0D8: wanted 24, got 0
    LOG:  started streaming WAL from primary at 0/9000000 on timeline 1
    

15. Start YSoft SafeQ services. There should be no errors in service logs. Data created by services should be present in both databases.