Step 4 - Installing the PGD software ==================================== 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-`` , ``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` .

.. Note 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 :ref:`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 :ref:`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 :ref:`Installing PGD for EDB Postgres Extended Server ` and :ref:`Installing PGD for Postgresql ` . Worked example -------------- Install the packages ^^^^^^^^^^^^^^^^^^^^ The first step is to install the packages. Each Postgres package has an ``edb-bdr5-`` 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 :ref:`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 :ref:`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 :ref:`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