EDB - Replication Server (xDB) - Important Concepts

Snapshot and synchronization overview

Replication Server performs two different types of replications. These two main types are called snapshot replication and synchronization replication.

In either method, the source tables refer to the tables from which the replication data is originating. In a single-master replication system, it's the publication. In a multi-master replication system it's the primary node whose changes are being replicated to another primary node.

The target tables are the tables that are receiving the replication data from the source tables. In a single-master replication system, it's the subscription tables. In a multi-master replication system, it's the primary node receiving changes from another primary node.

In snapshot replication, all existing rows in the target tables are deleted using the database system’s TRUNCATE command. The tables are then completely reloaded from the source tables of the publication.

In synchronization replication, only the changes (inserts, updates, and deletions) to the rows in the source tables since the last replication are applied to the target tables.

Note
Deleting all rows in a source table executed by the SQL TRUNCATE command results in replication to the target tables only if the log-based method of synchronization replication is used. If the trigger-based method of synchronization replication is used, executing the TRUNCATE command on a source table doesn't replicate the effect to the target tables. You must perform a snapshot from the source table to the target tables if you use the trigger-based method.

Synchronization replication is implemented using two different methods: the trigger-based method and the log-based method.

In the trigger-based method, changes to rows in the source tables result in row-based triggers executing. These triggers record the changes in shadow tables. The changes recorded in the shadow tables are then periodically extracted from the shadow tables, converted to an in-memory data structure, and applied to the target tables by means of SQL statements executed using JDBC. See Synchronization replication with the trigger-based method for information on the trigger-based method.

In the log-based method, changes to rows in the source tables are extracted from the write-ahead log segments (WAL files) using asynchronous streaming replication implemented by the logical decoding feature available in Postgres database servers. The extracted changes are converted to an in-memory data structure and applied to the target tables by means of SQL statements executed using JDBC. See Synchronization replication with the log-based method for information on the log-based method.

EDB - Replication Server (xDB) - Installation

Introduction

Replication Server is an asynchronous replication system available for PostgreSQL and for EDB Postgres Advanced Server.

You can use Replication Server to implement replication systems based on either of two different replication models: single-master (primary-to-secondary) replication or multi-master replication. Regardless of the replication model, Replication Server is flexible and easy to use.

For single-master replication, PostgreSQL, EDB Postgres Advanced Server, Oracle, and Microsoft SQL Server are supported in an assortment of configurations (including cascading replication), allowing organizations to use it in multiple use cases with a variety of benefits. The following are some combinations of cross database replications that Replication Server supports for single-master replication:

• From Oracle to PostgreSQL
• From Oracle to EDB Postgres Advanced Server
• From SQL Server to PostgreSQL
• From SQL Server to EDB Postgres Advanced Server
• From EDB Postgres Advanced Server to Oracle
• From PostgreSQL to SQL Server
• From EDB Postgres Advanced Server to SQL Server
• Between PostgreSQL and EDB Postgres Advanced Server
• From PostgreSQL to Oracle (WAL mode)
• From PostgreSQL to Oracle (trigger mode)

EDB - PEM - Installation

Theory

• You can install PEM on a single server, or you can install the web application server and the backend database on two separate servers. In this post, we will install PEM on a single server.
• Because installing without internet, so we must config LOCAL yum server. Please check via Create Local HTTP Yum/DNF Repository

Install the package

[root@edb-pem ~]# dnf install edb-as16-server

[root@edb-pem ~]# dnf install edb-as16-server-sslutils   //using for EDB Postgres Advanced Server

[root@edb-pem ~]# dnf install edb-pem

Change password of enterprisedb user

[enterprisedb@edb-pem ~]$ psql edb

edb=# \conninfo 

You are connected to database "edb" as user "enterprisedb" via socket in "/tmp" at port "5444".

edb=# \password 

Enter new password for user "enterprisedb": 

Configure authentication on the backend database

Modifying the pg_hba.conf file to allow pem to connect to database.

    

Initial configuration

[root@edb-pem ~]# /usr/edb/pem/bin/configure-pem-server.sh

...

 -----------------------------------------------------

 EDB Postgres Enterprise Manager

 -----------------------------------------------------

 Install type: 1:Web Services and Database, 2:Web Services 3: Database  1

 Enter local database server installation path (i.e. /usr/edb/as12 , or /usr/pgsql-12, etc.)  /usr/edb/as16

 Enter database super user name  enterprisedb

 Enter database server port number  5444

...

Please enter CIDR formatted network address range that agents will connect to the server from, to be added to the server's pg_hba.conf file. For example, 192.168.1.0/24  0.0.0.0/0

Enter database systemd unit file or init script name (i.e. edb-as-12 or postgresql-12, etc.)  edb-as-16

Please specify agent certificate path (Script will attempt to create this directory, if it does not exists)  /root/.pem/
Please specify PEM web server (openssl) certificate subject, template: /C=CountryCode/ST=StateName/L=CityName/O=CompanyName/OU=CompanySectionName/CN=CommonNameOrHostname/emailAddress=EmailAddress  /C=VN/ST=HoChiMinh/L=HCM/O=CHERRY/OU=VHUD/CN=edb-pem/emailAddress=khochiutronglong@gmail.com

...

...

...

-->  [Info] -->  [Info] Executing systemctl start httpd

-->  [Info] -->  [Info] Configured the webservice for EDB Postgres Enterprise Manager (PEM) Server on port '8443'.

-->  [Info] -->  [Info] PEM server can be accessed at https://127.0.0.1:8443/pem at your browser

Ref:

• EDB Docs - Postgres Enterprise Manager v9 - Installing Postgres Enterprise Manager server (enterprisedb.com)
• Step-by-step guide on installing Postgres Enterprise Manager for windows. - DEV Community

EDB - pgBackRest - Part III: RESTORE

 Theory

• The restore command automatically defaults to selecting the latest backup from the first repository where backups exist. The order in which the repositories are checked is dictated by the pgbackrest.conf (e.g. repo1 will be checked before repo2). To select from a specific repository, the --repo option can be passed (e.g. --repo=1). The --set option can be passed if a backup other than the latest is desired.
• Restoration Scenarios:
• Restoring the backup on a different host
• Restoring a single database from the Postgres cluster
• Restoring the backup to a specific point in time
• Restoring only the delta changes
• Restoring the backup on a different host to start as a streaming standby

1. Verify backup information

[enterprisedb@edb-nhatrang ~]$ pgbackrest info --stanza=employees

2. Restoring the backup on a different host (IP : 192.168.56.77)

• Setup Passwordless SSH Connection between Backup Server (192.168.56.79) and Database Server (192.168.56.77)
• Backup Server 
• ssh-keygen -t rsa
• ssh-copy-id enterprisedb@192.168.56.77

• Database Server
• ssh-keygen -t rsa
• ssh-copy-id enterprisedb@192.168.56.79

• Database Server - Setup pgBackRest Repository

        [root@edb-quynhon ~]# vi /etc/pgbackrest.conf 

        [global]

        repo1-host=192.168.56.79

        repo1-host-user=enterprisedb

        log-level-console=info

        log-level-file=debug

        [employees]

        pg1-path=/u01/edb/as16/data

        pg1-database=edb

        pg1-port=5444

        pg-version-force=16

2.1 Database Server - Restore the Lastest Backup 

[enterprisedb@edb-quynhon ~]$ pgbackrest --log-level-console=info --stanza=employees --process-max=2 restore

2024-06-25 15:47:07.509 P00   INFO: restore command begin 2.52: --exec-id=9675-76a8160f --log-level-console=info --log-level-file=debug --pg1-path=/u01/edb/as16/data --pg-version-force=16 --process-max=2 --repo1-host=192.168.56.79 --repo1-host-user=enterprisedb --stanza=employees

2024-06-25 15:47:08.481 P00   INFO: repo1: restore backup set 20240624-221733F_20240624-221842I, recovery will start at 2024-06-24 22:18:42

2024-06-25 15:47:17.598 P00   INFO: write updated /u01/edb/as16/data/postgresql.auto.conf

2024-06-25 15:47:17.625 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)

2024-06-25 15:47:17.628 P00   INFO: restore size = 740.5MB, file total = 2201

2024-06-25 15:47:17.628 P00   INFO: restore command end: completed successfully (10124ms)

==> The latest backup from the first repository will be restored

EDB - pgBackRest - Part II: Configuration & Backup

Theory

• A stanza is the configuration for a PostgreSQL database cluster that defines where it is located, how it will be backed up, archiving options, etc. Most db servers will only have one PostgreSQL database cluster and therefore one stanza, whereas backup servers will have a stanza for every database cluster that needs to be backed up.
• The stanza-delete command removes data in the repository associated with a stanza. Use this command with caution — it will permanently remove all backups and archives from the pgBackRest repository for the specified stanza.

A. CONFIGURATION

1. Create backup location

[root@edb-nhatrang /]# mkdir -p /backup/pgbackrest

[root@edb-nhatrang /]# chown -R enterprisedb: /backup/

2. Database Cluster Configuration

• Enable archive mode ON
• Set archive_command = ‘pgbackrest --stanza=employees archive-push %p’
• Get data_directory = ‘/u01/edb/as16/data’

edb=# show archive_mode ;

 archive_mode 

--------------

 on

(1 row)

edb=# show archive_command ;

                archive_command                

-----------------------------------------------

 pgbackrest --stanza=employees archive-push %p

(1 row)

edb=# show data_directory ;

   data_directory   

--------------------

 /u01/edb/as16/data

(1 row)

edb=# show wal_level ;

 wal_level 

-----------

 replica

(1 row)

edb=# show max_wal_senders ;

 max_wal_senders 

-----------------

 10

(1 row)

edb=# show log_filename ;

           log_filename           

----------------------------------

 enterprisedb-%Y-%m-%d_%H%M%S.log

(1 row)

3. Pgbackrest Repository Configuration

[root@edb-nhatrang ~]# vi /etc/pgbackrest.conf 

[global]

repo1-path=/backup/pgbackrest

repo1-retention-full=2

archive-async=y

log-level-console=info

log-level-file=debug

start-fast=y

[employees]

pg1-path=/u01/edb/as16/data

pg1-database=edb

pg1-port=5444

pg-version-force=16