Skip to content

Database Replica Node Installation

Audience: System Administrators

Content Summary: The Database Replica node consists of a PostgreSQL hot standby replica node and extension. This page outlines the installation of the required packages and the initialization of a Database Replica Node. You can perform these steps to configure replication for a Query Engine node, a Metadata database node, or a combined database node.

Primary Database Node Setup

In order to configure replication, you must first configure the primary database. The following will create a replication user, configure PostgreSQL for streaming replication, and configure pg_hba to allow the replication user to connect.

The following environment variables will be used throughout the setup of the primary database.

  • IMMUTA_REPLICATION_PASSWORD: The password for the Immuta replication user. This should have been generated during Secure Password Generation.

Export these as environment variables before beginning the setup.

export IMMUTA_REPLICATION_PASSWORD=<Generated Immuta Replication Password>

Initialize the database using the instructions from the combined database setup guide. After initializing and starting the database, run the following to create the replication user and configure the primary PostgreSQL database for replication.

Note: The following command must be run as immutaqe.

/opt/immuta/postgres-11/bin/psql -U postgres -p 5432 -c "CREATE USER immuta_replication REPLICATION ENCRYPTED PASSWORD '${IMMUTA_REPLICATION_PASSWORD}';"

Note: The following commands must be run as root.

echo "wal_level = 'replica'" >> "${PGDATA}/postgresql.conf"
echo "hot_standby = 'on'" >> "${PGDATA}/postgresql.conf"
echo "max_wal_senders = '10'" >> "${PGDATA}/postgresql.conf"
echo "wal_keep_segments = '8'" >> "${PGDATA}/postgresql.conf"
echo "wal_log_hints = 'on'" >> "${PGDATA}/postgresql.conf"
echo "host replication immuta_replication 0.0.0.0/0 md5" >> "${PGDATA}/pg_hba.conf"

Restart PostgreSQL for the change to take effect.

Note: The following command must be run as root.

service immuta-query-engine enable
service immuta-query-engine restart

Database Replica Node Package Installation

Install the Immuta security extension. It has dependencies that will also install PostgreSQL. Immuta provided RPM's for PostgreSQL must be used, and PostgreSQL must not be installed on the system.

Note: The following command must be run as root.

yum install immuta-query-engine

Database Replica Node Environment Setup

The following environment variables will be used throughout the installation of the Database Replica.

  • PRIMARY_DB_HOST: This will contain the hostname to the primary database.
  • IMMUTA_REPLICATION_PASSWORD: The password for the Immuta replication user. This should have been generated during Secure Password Generation.
  • PATH_TO_PRIVATE_KEY: Path to the private key pgbouncer will use for TLS.
  • PATH_TO_CERT: Path to the certificate pgbouncer will use for TLS.

Export these as environment variables before beginning the setup.

export PRIMARY_DB_HOST=<hostname to primary db>
export IMMUTA_REPLICATION_PASSWORD=<Generated Immuta Replication Password>
export PATH_TO_PRIVATE_KEY=<path to private key>
export PATH_TO_CERT=<path to cert>

Database Replica Setup and Initialization

Execute the following to backup the primary database node. All files from the PGDATA directory on the primary will be backed up to the PGDATA directory on the replica at this point. This will also create a recovery.conf file in the PGDATA directory that will configure this postgres instance as a replica of the primary.

Note: The following command must be run as immutaqe.

/opt/immuta/postgres-11/bin/pg_basebackup -D ${PGDATA} -R --wal-method=stream \
    --dbname="host=${PRIMARY_DB_HOST} port=5432 user=immuta_replication password=${IMMUTA_REPLICATION_PASSWORD}"

Finally, enable and start the immuta-query-engine service.

Note: The following commands must be run as root.

chkconfig immuta-query-engine on
service immuta-query-engine start

Repeat on each Database Replica node

Repeat the Database Replica installation on each Database Replica node.