Configuration the PostgreSQL database for metadata using PGPool on lab environment

Continuing from the last time(Configuration the 389 directory server for authentication and authorization for lab environment), this time, we will summarize how to build a database for storing metadata required by each application.

The architecture for continuous machine learning in lab environment

As the scale of the system increases, service stability becomes more and more important, and this can be addressed with the HA configuration. There are two ways to configure HA in PostgreSQL Database using PGBouncer and PGPool. Considering the performance and simplicity of installation, you should choose PGBouncer, but since PGBouncer does not provide HA, you need to configure the L4 Switch in front of PGBouncer. (For a detailed comparison, please refer to this article.) In general, the database for storing metadata values stability rather than performance, so here we introduce a method of configuring it using PGPool.

prerequisite

• PostgreSQL 13
• PGPool ≥ 4.2
• Redhat/CentOS/Rocky ≥ 8

PostgreSQL 13 HA Architecture

PostgreSQL HA configuration was configured by referring to PGPool-II + Watchdog Setup Example in Reference. The overall configuration is shown in the image below.

Note: The roles of Active, Standy, Primary, Standby are not fixed and may be changed by further operations.

OS environment and kernel parameter setting

It increases efficient memory usage and the number of open files. Each parameter depends on the resource of the server, so the information below is for reference only.

Kernel Parameter

• Path : /etc/sysctl.d/01-postgres.conf
• Contents:
  kernel.shmall = 1230591
  kernel.shmmax = 5040500736
  fs.file-max = 65536
  vm.swappiness = 0

Apply the changed kernel parameter using the sysctl command.

$ sysctl -p /etc/sysctl.d/01-postgres.conf

Security Parameter

• Path : /etc/security/limits.d/postgres.conf
• Contents:
  postgres soft nofile 65535
  postgres hard nofile 65535

The above parameter will apply after login.

PostgreSQL cluster information

Basic information of the PostgreSQL Cluster is as follows.

Hostname and IP address

+-----------+------------+--------------------+
| Hostname  | IP Address | Virtual IP Address |
+-----------+------------+--------------------+
| postgres1 | 10.0.0.164 |                    |
| postgres2 | 10.0.0.165 |         10.0.0.163 |
| postgres3 | 10.0.0.166 |                    |
+-----------+------------+--------------------+

PostgreSQL version and Configuration

+---------------------+----------------------+--------+
|        Item         |        Value         | Detail |
+---------------------+----------------------+--------+
| PostgreSQL          | 13                   |        |
| Port                | 5432                 |        |
| Postgres User Home  | /data1/pgsql         |        |
| $PGDATA             | /data1/pgsql/13/data |        |
| Archive mode        | On                   |        |
| Replication Slots   | Enable               |      - |
| Start automatically | Disable              |      - |
+---------------------+----------------------+--------+

Installation PostgreSQL

Connect the PostgreSQL yum repository to the OS and install the PostgreSQL server. After installing the PostgreSQL server package, configure the firewall.

$ dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
$ dnf -qy module disable postgresql
$ dnf install -y postgresql13-contrib postgresql13-server
$ firewall-cmd --add-service=postgresql --permanent
$ firewall-cmd --reload

Configuration PostgreSQL

Initialize the PostgreSQL database using initdb. Initialization must be done only on the primary server. The reason for initializing only the primary server will be explained later, but the other two units use replication to copy the settings.

$ export PGSETUP_INITDB_OPTIONS="-D /data1/pgsql/13/data"
$ /usr/pgsql-13/bin/postgresql-13-setup initdb

After initialization is complete, change the HA-related settings in the postgresql.conf file.

From now on, these are the tasks to configure after starting the PostgreSQL service. Among them, user creation and pg_hba setting will be performed first. The users to be created are repl and pgpool, the two users will be used for HA and Postgres will change the password. The pg_monitor role was granted to the pgpool user.

$ psql -c "SET password_encryption = 'scram-sha-256'; ALTER USER postgres WITH PASSWORD 'ChangeMe';"
$ psql -c "SET password_encryption = 'scram-sha-256'; DROP ROLE IF EXISTS repl; CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'ChangeMe';"
$ psql -c "SET password_encryption = 'scram-sha-256'; DROP ROLE IF EXISTS pgpool; CREATE ROLE pgpool WITH LOGIN PASSWORD 'ChangeMe';"
$ psql -c "GRANT pg_monitor TO pgpool;"

Also, access information must be set in pg_hba so that the created user can access PostgreSQL.

# pg_hba.conf
host    all             all             samenet                 scram-sha-256
host    replication     all             samenet                 scram-sha-256

When user creation and pg_hba configuration are completed, the configuration is read again so that the new configuration is reflected in the PostgreSQL service.

$ psql -c "SELECT pg_reload_conf();"

When the changed settings are applied, the settings are completed in the Primary Service. Now, if you set up replication for the Primary server to the Secondary servers, synchronization is automatically performed. Replication is possible with the pg_basebackup command. But before that, you need to configure some pg_basebackup. pg_basebackup basically connects to the primary server without a password and performs replication. Therefore, you need to configure .pgpass to help PostgreSQL use it without a password. The .pgpass configuration must be set on all servers, primary and secondary, and the configuration information is the same.

# ~postgres/.pgpass
postgres1:5432:replication:repl:ChangeMe
postgres1:5432:postgres:postgres:ChangeMe
postgres1:5432:postgres:pgpool:ChangeMe
postgres2:5432:replication:repl:ChangeMe
postgres2:5432:postgres:postgres:ChangeMe
postgres2:5432:postgres:pgpool:ChangeMe
postgres3:5432:replication:repl:ChangeMe
postgres3:5432:postgres:postgres:ChangeMe
postgres3:5432:postgres:pgpool:ChangeMe
# This is for Virtual IP setting
10.0.0.163:9999:postgres:pgpool:ChangeMe

After .pgpass configuration is completed, execute the pg_basebackup command to replicate the primary information to secondary. The pg_basebackup command must be executed on all servers except the primary server.

postgres2

$ pg_basebackup -h postgres1 -D /data1/pgsql/13/data -U repl -P -v -R -X stream -C -S postgres2

postgres3

$ pg_basebackup -h postgres1 -D /data1/pgsql/13/data -U repl -P -v -R -X stream -C -S postgres3

PGPool cluster information

Basic information of the PGPool Cluster is as follows.

+-------------------+----------------------------+
|       Item        |           Value            |
+-------------------+----------------------------+
| Pgpool-II Version | 4.2                        |
| Port              | 9999,9898,9000,9694(UDP)   |
| Config file       | /etc/pgpool-II/pgpool.conf |
| Running mode      | streaming replication mode |
| Watchdog          | On                         |
| Start             | automatically Enable       |
| Archive Directory | /data1/pgsql/13/archive    |
| Backup Directory  | /data1/pgsql/13/backup     |
+-------------------+----------------------------+

Installation PGPool

To install PGPool, you need to install the rpm package related to the pgpool repository. When repository configuration is complete, uninstall pgpool and pgpool extension.

$ dnf install -y https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-8-x86_64/pgpool-II-release-4.2-1.noarch.rpm
$ dnf install -y pgpool-II-pg13 pgpool-II-pg13-extensions

After the pgpool package installation is complete, register the port used by pgpool in the firewall. One of the watch dog’s ports to use pgpool’s VIP (Virtual IP) uses UDP, so register carefully.

$ firewall-cmd --permanent --zone=public --add-port=9999/tcp --add-port=9898/tcp --add-port=9000/tcp  --add-port=9694/udp
$ firewall-cmd --reload

When the package installation and firewall registration are completed, the environment configuration necessary to use pgpool is completed. Next, let’s proceed with setting only for pgpool. pgpool basically uses ssh to execute commands between clusters. So, let’s generate ssh key and clone the key so that remote commands can be executed without a password in ssh. Key generation and replication must be performed on all clusters.

$ ssh-keygen -q -t rsa -N '' -f ~postgres/.ssh/id_rsa_pgpool <<< y
$ ssh-copy-id -o StrictHostKeyChecking=no \
    -i ~postgres/.ssh/id_rsa_pgpool \
    -f postgres@postgres1
$ ssh-copy-id -o StrictHostKeyChecking=no \
    -i ~postgres/.ssh/id_rsa_pgpool \
    -f postgres@postgres2
$ ssh-copy-id -o StrictHostKeyChecking=no \
    -i ~postgres/.ssh/id_rsa_pgpool \
    -f postgres@postgres3

Configuration Pgpool

The pgpool-related settings exist in the /etc/pgpool-II directory. First, let’s give the node_id of pgpool. In general, node ids should be assigned in order.

• postgres1: 1
• postgres2: 2
• postgres3: 3

# postgres1
$ echo 1 > /etc/pgpool-II/pgpool_node_id
# postgres2
$ echo 2 > /etc/pgpool-II/pgpool_node_id
# postgres3
$ echo 3 > /etc/pgpool-II/pgpool_node_id

Now the pgpool configuration remains. The pgpool configuration is a bit complicated, so it is recommended to read the contents before setting and proceed in order.

Let’s proceed with the backend configuration in the pgpool.conf file. Configuration should be the same for all pgpool clusters.

When the basic pgpool configuration is completed, modify the shell contents for failover to fit the cluster. For the shell file, copy the file provided by default and use it.

$ cp /etc/pgpool-II/failover.sh{.sample,}
$ cp /etc/pgpool-II/follow_primary.sh{.sample,}

Modify information of follow_primary.sh

• ARCHIVEDIR: /var/lib/pgsql/archivedir → /data1/pgsql/13/archive
• REPLUSER: repl → repl
• PCP_USER: pgpool → pgpool

Like Postgres, PGPool also provides a .pcppass file to allow login without a password. Edit the file so that it can be accessed without logging in.

# ~postgres/.pcppass
localhost:9898:pgpool:ChangeMe
10.0.0.163:9898:pgpool:ChangeMe

Additionally, the pgpool password must be added to pcp.conf. The password must be encoded with md5 and added.

$ echo "pgpool:`pg_md5 {{ postgres_user_pass }}`" >> /etc/pgpool-II/pcp.conf

Now, let’s proceed with the setup for recovery. Recovery proceeds in three stages and consists of recovery_1st_stage, recovery_2nd_stage, and pgpool_remote_start. Let’s copy and configure the three files provided as samples.

$ cp /etc/pgpool-II/recovery_1st_stage{.sample,}
$ cp /etc/pgpool-II/recovery_2nd_stage{.sample,}
$ cp /etc/pgpool-II/pgpool_remote_start{.sample,}

after copy, change those parameters for revovery_1st_stage and recovey_2nd_stage.

recovery_1st_stage

+------------+---------------------------+-------------------------+
| Parameter  |           As-is           |          To-be          |
+------------+---------------------------+-------------------------+
| ARCHIVEDIR | /var/lib/pgsql/archivedir | /data1/pgsql/13/archive |
| REPLUSER   | repl                      | repl                    |
| passfile   | /var/lib/pgsql/.pgpass    | /data1/pgsql/.pgpass    |
+------------+---------------------------+-------------------------+

recovery_2nd_stage

+------------+---------------------------+-------------------------+
| Parameter  |           As-is           |          To-be          |
+------------+---------------------------+-------------------------+
| ARCHIVEDIR | /var/lib/pgsql/archivedir | /data1/pgsql/13/archive |
| REPLUSER   | repl                      | repl                    |
+------------+---------------------------+-------------------------+

Install the recovery extension in pgpool.

$ psql template1 -c "CREATE EXTENSION pgpool_recovery"

Next, let’s proceed with the watchdog setup. The watchdog checks the status of the pgpool and provides a function to transfer the VIP to another node if an error occurs. You can configure it in the pgpool.conf file.

In addition, proceed with the escalation setting. The escalation configuration file is also provided as a sample, so let’s copy and use the sample file.

$ cp -p /etc/pgpool-II/escalation.sh{.sample,}

• PGPOOLS: (server1 server2 server) → (postgres1,postgres2,postgres3)
• VIP: 192.168.137.150 → 10.0.0.163
• DEVICE: enp0s8 → Your NIC device

This completes all settings related to pgpool. Check all the settings once again and start the pgpool service if the process is successful.

$ systemctl start pgpool

This completes all settings related to pgpool. Check all the settings once again and start the pgpool service if the process is successful. If the pgpool status is displayed normally, stop the PostgreSQL secondary service and restart it with the service using pgpool.

# postgres2
$ /usr/pgsql-13/bin/pg_ctl -D /data1/pgsql/13/data -m immediate stop
$ pcp_recovery_node -h 10.0.0.163 -p 9898 -U pgpool -n 1 -w
# postgres3
$ /usr/pgsql-13/bin/pg_ctl -D /data1/pgsql/13/data -m immediate stop
$ pcp_recovery_node -h 10.0.0.163 -p 9898 -U pgpool -n 1 -w

Once all services have been restarted, use “show pool_nodes” to check the status. If the setup is completed normally, replication_state and replication_sync_state should be displayed as streaming and async.

Pgpool-II + Watchdog Setup Example

Conclusion

All settings were configured as ansible playbook and published on github. Before looking at the ansible playbook, please first look at the postgresql and pgpool documents and use the playbook.

GitHub - awslife/postgres-ha

I hope it will be helpful to those who are looking for related content. And if you have saved a lot of time with this content, please donate a cup of coffee. (Please help me to write while eating ice americano at a local cafe.)

And I am looking for a job. If you are interested, please comment.

https://buymeacoffee.com/7ov2xm5