PostgreSQL external database cluster

On-premises database cluster

OPSWAT recommends setting up an external PosgreSQL cluster in the streaming replication mode as a primary and a hot standby pair.

To setup a primary and standby PostgreSQL instance, perform the following steps:

Primary server

1. Install PostgreSQL on the primary server

2. Enable remote connection for Email Gateway Security instances

   1. Edit C:\Program Files\PostgreSQL\14\data\postgresql.conf
   2. Configure listen_addresses to listen on addresses accessible for Email Gateway Security hosts. For details see https://www.postgresql.org/docs/14/runtime-config-connection.html.
      1. Example: listen_addresses = '*'

3. Enable remote authentication for Email Gateway Security instances

   1. Edit C:\Program Files\PostgreSQL\14\data\pg_hba.conf
   2. Add entry to allow authentication from other hosts. For details see https://www.postgresql.org/docs/14/auth-pg-hba-conf.html.
      1. Example: host all postgres 172.0.0.0/8 scram-sha-256

4. Enable PostgreSQL port (default 5432) through the firewall

5. In PostgreSQL create a user for replication

   1. Example: CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicator';

   2. Configure postgreSQL for streaming replication

      1. Edit C:\Program Files\PostgreSQL\14\data\postgresql.conf

         1. Set wal_level = replica .

         2. Set archive_mode = on .

         3. Set hot_standby = on .

         4. Set archive_command to a reasonable value.

            1. For details see https://www.postgresql.org/docs/14/runtime-config-wal.html.

         5. Set wal_keep_size to a reasonable value.

         6. Set max_wal_senders to a reasonable value.

            1. For details see https://www.postgresql.org/docs/14/runtime-config-replication.html.

         7. Example:

            1. wal_level = replica
            2. max_wal_senders = 10
            3. wal_keep_size = 10000
            4. archive_mode = on
            5. hot_standby = on
            6. archive_command = 'copy "%p" "c:\\temp\archivedir\\%f"'

      2. Create the directory for the WAL archives

         1. Example: mkdir C:\temp\archivedir

      3. Enable remote authentication for replicas

         1. Edit C:\Program Files\PostgreSQL\14\data\pg_hba.conf
         2. Add entry to allow authentication from other hosts. Use the PostgreSQL user that was created in step 1.e.i. For details see https://www.postgresql.org/docs/14/auth-pg-hba-conf.html.
            1. Example: host all replicator 172.0.0.0/8 scram-sha-256

Standby server

1. Install PostgreSQL on the standby server and stop the PostgreSQL service

2. Empty the C:\Program Files\PostgreSQL\14\data directory

   1. Example:
      1. cd C:\Program Files\PostgreSQL\14\data
      2. del *.*

3. Take a base backup on the primary server and apply it on the standby. Use the PostgreSQL user and password that was created in step 1.e.i.

   1. Example: "C:\Program Files\PostgreSQL\14\bin\pg_basebackup.exe" -D "c:\Program Files\PostgreSQL\14\data" -h win11-a -p 5432 -Xs -R -P -U replicator --password

4. Create the C:\Program Files\PostgreSQL\14\data\standby.signal file (should be created by the previous step)

5. Start the PostgreSQL service

6. Verify that replication works for example with adding records on the primary instance that should show up on the standby.

For details see https://www.enterprisedb.com/blog/how-set-streaming-replication-keep-your-postgresql-database-performant-and-date.

Database as a service

PostgreSQL is offered by major SaaS providers. PostgreSQL as a service can be used as the database cluster for Email Gateway Security as long as the prerequisites are fulfilled. For details see Software prerequisites.