/
31: PostgreSQL Slave GI Creation

31: PostgreSQL Slave GI Creation

Owned by Michael Litner
Jun 09, 2025

General

Updating the Postgres Live GI database is the responsibility of the end user and is performed separately from Accelario Virtualization.

This document provides a suggestion to implement a built-in Postgres streaming replication feature and create the Live GI database as a “slave” to the customer database.

Architecture

PostgreSQL Slave GI.drawio (1)-20250511-100808.png

Pre requisites

  1. Install source agent

  2. Create Source, up to “Create empty Postgres cluster” step - do not perform it

Configuration

  1. If the customer database is Master then need to configure its database parameters, so it will be possible to add Live GI database as a slave.

alter system set listen_addresses = 'master db host IP address';
alter system set wal_keep_size= '300'; – Should set this parameter for deletion WALs automatically and saving max 300MB of WAL files.
alter system set archive_mode = on;
alter system set archive_command = 'cp %p /any_directory/pg_archive/%f';
alter system set max_wal_senders = 10;
alter system set wal_level = replica;

  1. Always configure customer database db pg_hba.conf file to accept connection from the Live GI side.

  2. Check the locations of all user-defined tablespaces. Write them down, it will be used in the further step.
    Execute on the customer database:
    psql
    \db+

    Sample output:

    List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Description ------------+----------+--------------------------+-------------------+---------+---------+------------- pg_default | postgres | | | | 24 MB | pg_global | postgres | | | | 559 kB | tbs1 | postgres | /var/lib/pgsql/data/tbs1 | | | 0 bytes | tbs2 | postgres | /var/lib/pgsql/data/tbs2 | | | 0 bytes | tbs3 | postgres | /var/lib/pgsql/data/tbs3 | | | 0 bytes |

  3. On the Live GI server create new SLAVE database.
    pg_basebackup -Xs -h my_customer_db_host -p my_customer_db_port -U my_user --checkpoint=fast -D my_live_gi_directory -R --slot=my_slot –C -T customer_db_location=live_gi_location
    Example:

    pg_basebackup -Xs -h 172.31.34.215 -p 5434 -U rep_user --checkpoint=fast -D /main_pool/00000010/13/mycluster -R --slot=main_slot_gi –C -T /var/lib/pgsql/data/tbs1=/main_pool/00000010/13/mycluster/tbs1 -T /var/lib/pgsql/data/tbs2=/main_pool/00000010/13/mycluster/tbs2 -T /var/lib/pgsql/data/tbs3=/main_pool/00000010/13/mycluster/tbs3

    Omit the -T parameter if there were no user-defined tablespaces on the customer database.

  4. On the Live GI server edit postgresql.conf file - change the port to a number that was specified when creating Source DB

  5. On the Live GI server start the Live GI Postgres cluster
    pg_ctl -D /main_pool/00000010/13/mycluster/ start

  6. In the Accelario Virtualization application, goto the GI and activate it