Enable Percona Distribution for PostgreSQL components

Some components require additional configuration before using them with Percona Distribution for PostgreSQL. This sections provides configuration instructions per extension.

Patroni

Patroni is the high availability solution for PostgreSQL. The High Availability in PostgreSQL with Patroni chapter provides details about the solution overview and architecture deployment.

While setting up a high availability PostgreSQL cluster with Patroni, you will need the following components:

• Patroni installed on every postresql node.

• Distributed Configuration Store (DCS). Patroni supports such DCSs as etcd, zookeeper, Kubernetes though etcd is the most popular one. It is available within Percona Distribution for PostgreSQL for all supported operating systems.

• HAProxy .

If you install the software fom packages, all required dependencies and service unit files are included. If you install the software from the tarballs, you must first enable etcd. See the steps in the etcd section in this document.

See the configuration guidelines for Patroni and etcd.

etcd

If you installed etcd from binary tarballs, you need to create the etcd.service file. This file allows systemd to start, stop, restart, and manage the etcd service. This includes handling dependencies, monitoring the service, and ensuring it runs as expected.

/etc/systemd/system/etcd.service

[Unit]
After=network.target
Description=etcd - highly-available key value store

[Service]
LimitNOFILE=65536
Restart=on-failure
Type=notify
ExecStart=/usr/bin/etcd --config-file /etc/etcd/etcd.conf.yaml
User=etcd

[Install]
WantedBy=multi-user.target

pgBadger

Enable the following options in postgresql.conf configuration file before starting the service:

log_min_duration_statement = 0
log_line_prefix = '%t [%p]: '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_error_verbosity = default

For details about each option, see pdBadger documentation .

pgaudit

Add the pgaudit to shared_preload_libraries in postgresql.conf. The recommended way is to use the ALTER SYSTEM command. Connect to psql and use the following command:

ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';

Start / restart the server to apply the configuration.

To configure pgaudit, you must have the privileges of a superuser. You can specify the settings in one of these ways:

• globally (in postgresql.conf or using ALTER SYSTEM … SET),
• at the database level (using ALTER DATABASE … SET),
• at the role level (using ALTER ROLE … SET). Note that settings are not inherited through normal role inheritance and SET ROLE will not alter a user’s pgAudit settings. This is a limitation of the roles system and not inherent to pgAudit.

Refer to the pgaudit documentation for details about available settings.

To enable pgaudit, connect to psql and run the CREATE EXTENSION command:

CREATE EXTENSION pgaudit;

pgaudit set-user

Add the set-user to shared_preload_libraries in postgresql.conf. The recommended way is to use the ALTER SYSTEM command. Connect to psql and use the following command:

ALTER SYSTEM SET shared_preload_libraries = 'set-user';

Start / restart the server to apply the configuration.

Install the extension into your database:

psql <database>
CREATE EXTENSION set_user;

You can fine-tune user behavior with the custom parameters supplied with the extension.

pgbouncer

pgbouncer requires the pgbouncer.ini configuration file to start. The default path is /etc/pgbouncer/pgbouncer.ini. When installing pgbouncer from a tarball, the path is percona-pgbouncer/etc/pgbouncer.ini.

Find detailed information about configuration file options in the pgbouncer documentation.

pgpool2

pgpool-II requires the configuration file to start. When you install pgpool from a package, the configuration file is automatically created for you at the path /etc/pgpool2/pgpool.conf on Debian and Ubuntu and /etc/pgpool-II/pgpool.conf on RHEL and derivatives.

When you installed pgpool from tarballs, you can use the sample configuration file <tarballsdir>/percona-pgpool-II/etc/pgpool2/pgpool.conf.sample:

$ cp <tarballsdir>/percona-pgpool-II/etc/pgpool2/pgpool.conf.sample <config-gile-path>/pgpool.conf

Specify the path to it when starting pgpool:

$ pgpool -f <config-gile-path>/pgpool.conf

pg_stat_monitor

Please refer to pg_stat_monitor for setup steps.

wal2json

After the installation, enable the following option in postgresql.conf configuration file before starting the service:

wal_level = logical

«««< HEAD

Start / restart the server to apply the changes.

pgvector

To get started, enable the extension for the database where you want to use it:

CREATE EXTENSION vector;

7845a94c… PG-1214 Documented install and enable pgvector steps

Next steps

Connect to PostgreSQL