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.

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 (Reconfiguration or recovery of etcd cluster in Management Service).

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_segments configuration property

• PostgreSQL 11 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, recovery.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 11 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.

   postgresql.conf

   listen_addresses = '*'

   max_wal_senders = 2

   wal_level = replica

    

   # Set the wal_keep_segments property according to your deployment size. These settings are approximated for 6 hour outage.

   #wal_keep_segments = 36 # For deployment with throughput 1 job per second (Extra space 600 MB for logfiles)

   wal_keep_segments = 180 # For deployment with throughput 5 jobs per second (Extra space 3 GB for logfiles)

   #wal_keep_segments = 360 # For deployment with throughput 10 jobs per second (Extra space 6 GB for logfiles)

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

   pg_hba.conf

   # 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:

   pg_hba.conf

   # 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 execution

   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 with real values

   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. After the execution, a recovery.conf file should be generated in PostgreSQL data directory with the following content (assuming 10.0.124.110 was used as primary database IP address - PRIMARY_DATABASE_IP_ADDRESS)

   recovery.conf

   standby_mode = 'on'

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

   SSL support

   sslmode=prefer in recovery.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

   postgresql.conf

   hot_standby = on

   pg_hba.conf on standby database should have following entries

   pg_hba.conf for standby database

   # 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:

   pg_hba.conf on standby database

   # 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)

    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

    Management cluster

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

    with example values in place:

    safeq.properties

    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.

    safeq.properties

    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

    safeq.properties

    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.

    application.properties

    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

    application.properties

    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.

    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

    environment-configuration.properties

    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 Manual 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 11 binaries (this guide uses: C:\PGSQL), navigate to its bin subdirectory. Use following command to start PostgreSQL service from command line.

    Starting PostgreSQL from command line

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

    with example values in place

    Starting PostgreSQL from command line

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

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

    Service registration example

    SC CREATE "YSoftPGSQL" start=demand 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

    Service registration example

    SC CREATE "YSoftPGSQL" start=demand 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.