Step 4 - Installing the PGD software v5

Suggest edits

Installing the PGD software

With the repositories configured, you can now install the Postgres Distributed software. You must perform these steps on each host before proceeding to the next step.

  • Install the packages.
    • Install the PGD packages, which include a server-specific BDR package and generic PGD Proxy and CLI packages. (edb-bdr5-<postgresversion>, edb-pgd5-proxy, and edb-pgd5-cli)
  • Ensure the Postgres database server has been initialized and started.
    • Use systemctl status to check that the service is running.
    • If the service isn't running, initialize the database and start the service.
  • Configure the BDR extension.
    • Add the BDR extension ($libdir/bdr) at the start of the shared_preload_libraries setting in postgresql.conf.
    • Set the wal_level GUC variable to logical in postgresql.conf.
    • Turn on commit timestamp tracking by setting track_commit_timestamp to 'on' in postgresql.conf.
    • Increase the maximum worker processes to 16 or higher by setting max_worker_processes to '16' in postgresql.conf.

      The max_worker_processes value

      The max_worker_processes value is derived from the topology of the cluster, the number of peers, number of databases, and other factors. To calculate the needed value, see Postgres configuration/settings. The value of 16 was calculated for the size of cluster being deployed in this example. It must be increased for larger clusters.

    • Set a password on the EnterprisedDB/Postgres user.
    • Add rules to pg_hba.conf to allow nodes to connect to each other.
      • Ensure that these lines are present in pg_hba.conf:
      host all all all md5
host replication all all md5
    • Add a .pgpass file to allow nodes to authenticate each other.
      • Configure a user with sufficient privileges to log in to the other nodes.
      • See The Password File in the Postgres documentation for more on the .pgpass file.
  • Restart the server.
    • Verify the restarted server is running with the modified settings and the BDR extension is available.
  • Create the replicated database.
    • Log in to the server's default database (edb for EDB Postgres Advanced Server, postgres for PGE and community Postgres).
    • Use CREATE DATABASE bdrdb to create the default PGD replicated database.
    • Log out and then log back in to bdrdb.
    • Use CREATE EXTENSION bdr to enable the BDR extension and PGD to run on that database.

The worked example that follows shows the steps for EDB Postgres Advanced Server in detail.

If you're installing PGD with EDB Postgres Extended Server or community Postgres, the steps are similar, but details such as package names and paths are different. These differences are summarized in Installing PGD for EDB Postgres Extended Server and Installing PGD for Postgresql.

Worked example

Install the packages

The first step is to install the packages. Each Postgres package has an edb-bdr5-<postgresversion> package to go with it. For example, if you're installing EDB Postgres Advanced Server (epas) version 16, you'd install edb-bdr5-epas16.

There are two other packages to also install:

  • edb-pgd5-proxy for PGD Proxy
  • edb-pgd5-cli for the PGD command line tool

To install all of these packages on a RHEL or RHEL compatible Linux, run:

sudo dnf -y install edb-bdr5-epas16 edb-pgd5-proxy edb-pgd5-cli

Ensure the database is initialized and started

If the server wasn't initialized and started by the database's package initialization (or you're repeating the process), you need to initialize and start the server.

To see if the server is running, you can check the service. The service name for EDB Advanced Server is edb-as-16, so run:

sudo systemctl status edb-as-16

If the server isn't running, the response is:

○ edb-as-16.service - EDB Postgres Advanced Server 16
     Loaded: loaded (/usr/lib/systemd/system/edb-as-16.service; disabled; preset: disabled)
     Active: inactive (dead)

Active: inactive (dead) tells you that you need to initialize and start the server.

You need to know the path to the setup script for your particular Postgres flavor.

For EDB Postgres Advanced Server, you can find this script in /usr/edb/as16/bin as edb-as-16-setup. Run this command with the initdb parameter and pass an option to set the database to use UTF-8:

sudo PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/edb/as16/bin/edb-as-16-setup initdb

Once the database is initialized, start it so that you can continue configuring the BDR extension:

sudo systemctl start edb-as-16

Configure the BDR extension

Installing EDB Postgres Advanced Server creates a system user enterprisedb with admin capabilities when connected to the database. You'll use this user to configure the BDR extension.

Preload the BDR library

You need to preload the BDR library with other libraries. EDB Postgres Advanced Server has a number of libraries already preloaded, so you have to prefix the existing list with the BDR library.

echo -e "shared_preload_libraries = '\$libdir/bdr,\$libdir/dbms_pipe,\$libdir/edb_gen,\$libdir/dbms_aq'" | sudo -u enterprisedb tee -a /var/lib/edb/as16/data/postgresql.conf >/dev/null
Tip

This command format (echo ... | sudo ... tee -a ...) appends the echoed string to the end of the postgresql.conf file, which is owned by another user.

Set the wal_level

The BDR extension needs to set the server to perform logical replication. Do this by setting wal_level to logical:

echo -e "wal_level = 'logical'" | sudo -u enterprisedb tee -a /var/lib/edb/as16/data/postgresql.conf >/dev/null

Enable commit timestamp tracking

The BDR extension also needs the commit timestamp tracking enabled:

echo -e "track_commit_timestamp = 'on'" | sudo -u enterprisedb tee -a /var/lib/edb/as16/data/postgresql.conf >/dev/null

Increase max_worker_processes

To communicate between multiple nodes, Postgres Distributed nodes run more worker processes than usual. The default limit (8) is too low even for a small cluster.

The max_worker_processes value is derived from the topology of the cluster, the number of peers, number of databases, and other factors. To calculate the needed value, see Postgres configuration/settings.

This example, with a 3-node cluster, uses the value of 16.

Increase the maximum number of worker processes to 16:

echo -e "max_worker_processes = '16'" | sudo -u enterprisedb tee -a /var/lib/edb/as16/data/postgresql.conf >/dev/null

This value must be increased for larger clusters.

Add a password to the Postgres enterprisedb user

To allow connections between nodes, a password needs to be set on the Postgres enterprisedb user. This example uses the password secret. Select a different password for your deployments. You will need this password to Enable authentication between nodes.

sudo -u enterprisedb psql edb -c "ALTER USER enterprisedb WITH PASSWORD 'secret'"

Enable inter-node authentication in pg_hba.conf

Out of the box, Postgres allows local authentication and connections with the database but not external network connections. To enable this, edit pg_hba.conf and add appropriate rules, including rules for the replication users. To simplify the process, use this command:

echo -e "host all all all md5\nhost replication all all md5" | sudo tee -a /var/lib/edb/as16/data/pg_hba.conf

The command appends the following to pg_hba.conf:

host all all all md5
host replication all all md5

These commands enable the nodes to replicate.

Enable authentication between nodes

As part of the process of connecting nodes for replication, PGD logs into other nodes. It performs that login as the user that Postgres is running under. For EDB Postgres Advanced server, this is the enterprisedb user. That user needs credentials to log into the other nodes. Supply these credentials using the .pgpass file, which needs to reside in the user's home directory. The home directory for enterprisedb is /var/lib/edb.

Run this command to create the file:

echo -e "*:*:*:enterprisedb:secret" | sudo -u enterprisedb tee /var/lib/edb/.pgpass; sudo chmod 0600 /var/lib/edb/.pgpass

You can read more about the .pgpass file in The Password File in the PostgreSQL documentation.

Restart the server

After all these configuration changes, we recommend that you restart the server with:

sudo systemctl restart edb-as-16

Check the extension has been installed

At this point, it's worth checking whether the extension is actually available and the configuration was correctly loaded. You can query the pg_available_extensions table for the BDR extension like this:

sudo -u enterprisedb psql edb -c "select * from pg_available_extensions where name like 'bdr'"

This command returns an entry for the extension and its version:

 name | default_version | installed_version |                  comment
------+-----------------+-------------------+-------------------------------------------
 bdr  | 5.3.0           |                   | Bi-Directional Replication for PostgreSQL

You can also confirm the other server settings using this command:

sudo -u enterprisedb psql edb -c "show all" | grep -e wal_level -e track_commit_timestamp -e max_worker_processes

Create the replicated database

The server is now prepared for PGD. You need to next create a database named bdrdb and install the BDR extension when logged into it:

sudo -u enterprisedb psql edb -c "CREATE DATABASE bdrdb"
sudo -u enterprisedb psql bdrdb -c "CREATE EXTENSION bdr"

Finally, test the connection by logging in to the server.

sudo -u enterprisedb psql bdrdb

You're connected to the server. Execute the command "\dx" to list extensions installed:

bdrdb=# \dx
                                List of installed extensions
       Name       | Version |   Schema   |                   Description
------------------+---------+------------+--------------------------------------------------
 bdr              | 5.3.0   | pg_catalog | Bi-Directional Replication for PostgreSQL
 edb_dblink_libpq | 1.0     | pg_catalog | EnterpriseDB Foreign Data Wrapper for PostgreSQL
 edb_dblink_oci   | 1.0     | pg_catalog | EnterpriseDB Foreign Data Wrapper for Oracle
 edbspl           | 1.0     | pg_catalog | EDB-SPL procedural language
 plpgsql          | 1.0     | pg_catalog | PL/pgSQL procedural language
(5 rows)

Notice that the BDR extension is listed in the table, showing that it's installed.

Summaries

Installing PGD for EDB Postgres Advanced Server

For your convenience, here's a summary of the commands used in this example.

sudo dnf -y install edb-bdr5-epas16 edb-pgd5-proxy edb-pgd5-cli
sudo PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/edb/as16/bin/edb-as-16-setup initdb
sudo systemctl start edb-as-16
echo -e "shared_preload_libraries = '\$libdir/bdr,\$libdir/dbms_pipe,\$libdir/edb_gen,\$libdir/dbms_aq'" | sudo -u enterprisedb tee -a /var/lib/edb/as16/data/postgresql.conf >/dev/null
echo -e "wal_level = 'logical'" | sudo -u enterprisedb tee -a /var/lib/edb/as16/data/postgresql.conf >/dev/null
echo -e "track_commit_timestamp = 'on'" | sudo -u enterprisedb tee -a /var/lib/edb/as16/data/postgresql.conf >/dev/null
echo -e "max_worker_processes = '16'" | sudo -u enterprisedb tee -a /var/lib/edb/as16/data/postgresql.conf >/dev/null
sudo -u enterprisedb psql edb -c "ALTER USER enterprisedb WITH PASSWORD 'secret'"
echo -e "host all all all md5\nhost replication all all md5" | sudo tee -a /var/lib/edb/as16/data/pg_hba.conf >/dev/null
echo -e "*:*:*:enterprisedb:secret" | sudo -u enterprisedb tee /var/lib/edb/.pgpass >/dev/null; sudo chmod 0600 /var/lib/edb/.pgpass
sudo systemctl restart edb-as-16
sudo -u enterprisedb psql edb -c "CREATE DATABASE bdrdb"
sudo -u enterprisedb psql bdrdb -c "CREATE EXTENSION bdr"
sudo -u enterprisedb psql bdrdb

Installing PGD for EDB Postgres Extended Server

Installing PGD with EDB Postgres Extended Server has a number of differences from the EDB Postgres Advanced Server installation:

  • The BDR package to install is named edb-bdrV-pgextendedNN (where V is the PGD version and NN is the PGE version number).
  • Call a different setup utility: /usr/edb/pgeNN/bin/edb-pge-NN-setup.
  • The service name is edb-pge-NN.
  • The system user is postgres (not enterprisedb).
  • The home directory for the postgres user is /var/lib/pgqsl.
  • There are no preexisting libraries to add to shared_preload_libraries.

Summary: Installing PGD for EDB Postgres Extended Server 16

sudo dnf -y install edb-bdr5-pgextended16 edb-pgd5-proxy edb-pgd5-cli
sudo PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/edb/pge16/bin/edb-pge-16-setup ekend initdb
sudo systemctl start edb-pge-16
echo -e "shared_preload_libraries = '\$libdir/bdr'" | sudo -u postgres tee -a /var/lib/edb-pge/16/data/postgresql.conf >/dev/null
echo -e "wal_level = 'logical'" | sudo -u postgres tee -a /var/lib/edb-pge/16/data/postgresql.conf >/dev/null
echo -e "track_commit_timestamp = 'on'" | sudo -u postgres tee -a /var/lib/edb-pge/16/data/postgresql.conf >/dev/null
echo -e "max_worker_processes = '16'" | sudo -u postgres tee -a /var/lib/edb-pge/16/data/postgresql.conf >/dev/null
sudo -u postgres psql postgres -c "ALTER USER postgres WITH PASSWORD 'secret'"
echo -e "host all all all md5\nhost replication all all md5" | sudo tee -a /var/lib/edb-pge/16/data/pg_hba.conf >/dev/null
echo -e "*:*:*:postgres:secret" | sudo -u postgres tee /var/lib/pgsql/.pgpass >/dev/null; sudo chmod 0600 /var/lib/pgsql/.pgpass
sudo systemctl restart edb-pge-16
sudo -u postgres psql postgres -c "CREATE DATABASE bdrdb"
sudo -u postgres psql bdrdb -c "CREATE EXTENSION bdr"
sudo -u postgres psql bdrdb

Installing PGD for Postgresql

Installing PGD with PostgresSQL has a number of differences from the EDB Postgres Advanced Server installation:

  • The BDR package to install is named edb-bdrV-pgNN (where V is the PGD version and NN is the PostgreSQL version number).
  • Call a different setup utility: /usr/pgsql-NN/bin/postgresql-NN-setup.
  • The service name is postgresql-NN.
  • The system user is postgres (not enterprisedb).
  • The home directory for the postgres user is /var/lib/pgqsl.
  • There are no preexisting libraries to add to shared_preload_libraries.

Summary: Installing PGD for Postgresql 16

sudo dnf -y install edb-bdr5-pg16 edb-pgd5-proxy edb-pgd5-cli
sudo PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl start postgresql-16
echo -e "shared_preload_libraries = '\$libdir/bdr'" | sudo -u postgres tee -a /var/lib/pgsql/16/data/postgresql.conf >/dev/null
echo -e "wal_level = 'logical'" | sudo -u postgres tee -a /var/lib/pgsql/16/data/postgresql.conf >/dev/null
echo -e "track_commit_timestamp = 'on'" | sudo -u postgres tee -a /var/lib/pgsql/16/data/postgresql.conf >/dev/null
echo -e "max_worker_processes = '16'" | sudo -u postgres tee -a /var/lib/pgsql/16/data/postgresql.conf >/dev/null
sudo -u postgres psql postgres -c "ALTER USER postgres WITH PASSWORD 'secret'"
echo -e "host all all all md5\nhost replication all all md5" | sudo tee -a /var/lib/pgsql/16/data/pg_hba.conf >/dev/null
echo -e "*:*:*:postgres:secret" | sudo -u postgres tee /var/lib/pgsql/.pgpass; sudo chmod 0600 /var/lib/pgsql/.pgpass
sudo systemctl restart postgresql-16
sudo -u postgres psql postgres -c "CREATE DATABASE bdrdb"
sudo -u postgres psql bdrdb -c "CREATE EXTENSION bdr"
sudo -u postgres psql bdrdb
← Prev

Step 3 - Configuring PGD repositories

↑ Up

Deploying PGD manually

Next →

Step 5 - Creating the PGD cluster