search

vitabaks / postgresql_cluster

PostgreSQL High-Availability Cluster (based on Patroni). Automating with Ansible.
https://postgresql-cluster.org
MIT License
1.69k stars 411 forks source link

Support for multiple PgBouncer processes using so_reuseport #487

Closed vitabaks closed 11 months ago

vitabaks commented 1 year ago

issue: https://github.com/vitabaks/postgresql_cluster/issues/234

PgBouncer is single-threaded and uses one CPU core per instance. This option is a way to get PgBouncer to use multiple CPU cores.

New variable: pgbouncer_processes (default: 1)

Consider sponsoring the project via GitHub or Patreon

vitabaks commented 1 year ago

Test

pgbouncer_processes: 4


TASK [pgbouncer : Install pgbouncer package] ***********************************
changed: [10.172.0.22]
changed: [10.172.0.21]
changed: [10.172.0.20]

TASK [pgbouncer : Ensure config directory "/etc/pgbouncer" exist] **************
changed: [10.172.0.21]
changed: [10.172.0.20]
changed: [10.172.0.22]

TASK [pgbouncer : Check if pgbouncer systemd service file exists] **************
ok: [10.172.0.22]
ok: [10.172.0.20]
ok: [10.172.0.21]

TASK [pgbouncer : Stop and disable standard init script] ***********************
changed: [10.172.0.21]
changed: [10.172.0.22]
changed: [10.172.0.20]

TASK [pgbouncer : Configure pgbouncer systemd service file] ********************
changed: [10.172.0.20] => (item=pgbouncer)
changed: [10.172.0.21] => (item=pgbouncer)
changed: [10.172.0.22] => (item=pgbouncer)
changed: [10.172.0.21] => (item=pgbouncer-2)
changed: [10.172.0.20] => (item=pgbouncer-2)
changed: [10.172.0.22] => (item=pgbouncer-2)
changed: [10.172.0.21] => (item=pgbouncer-3)
changed: [10.172.0.22] => (item=pgbouncer-3)
changed: [10.172.0.20] => (item=pgbouncer-3)
changed: [10.172.0.21] => (item=pgbouncer-4)
changed: [10.172.0.22] => (item=pgbouncer-4)
changed: [10.172.0.20] => (item=pgbouncer-4)

TASK [pgbouncer : Ensure pgbouncer service is enabled] *************************
changed: [10.172.0.21] => (item=pgbouncer)
changed: [10.172.0.22] => (item=pgbouncer)
changed: [10.172.0.20] => (item=pgbouncer)
changed: [10.172.0.21] => (item=pgbouncer-2)
changed: [10.172.0.20] => (item=pgbouncer-2)
changed: [10.172.0.22] => (item=pgbouncer-2)
changed: [10.172.0.21] => (item=pgbouncer-3)
changed: [10.172.0.20] => (item=pgbouncer-3)
changed: [10.172.0.22] => (item=pgbouncer-3)
changed: [10.172.0.21] => (item=pgbouncer-4)
changed: [10.172.0.20] => (item=pgbouncer-4)
changed: [10.172.0.22] => (item=pgbouncer-4)

TASK [pgbouncer : Enable log rotation with logrotate] **************************
changed: [10.172.0.22] => (item=pgbouncer)
changed: [10.172.0.21] => (item=pgbouncer)
changed: [10.172.0.20] => (item=pgbouncer)
changed: [10.172.0.22] => (item=pgbouncer-2)
changed: [10.172.0.21] => (item=pgbouncer-2)
changed: [10.172.0.20] => (item=pgbouncer-2)
changed: [10.172.0.20] => (item=pgbouncer-3)
changed: [10.172.0.21] => (item=pgbouncer-3)
changed: [10.172.0.22] => (item=pgbouncer-3)
changed: [10.172.0.20] => (item=pgbouncer-4)
changed: [10.172.0.21] => (item=pgbouncer-4)
changed: [10.172.0.22] => (item=pgbouncer-4)

TASK [pgbouncer : Configure pgbouncer.ini] *************************************
changed: [10.172.0.20] => (item=pgbouncer)
changed: [10.172.0.22] => (item=pgbouncer)
changed: [10.172.0.21] => (item=pgbouncer)
changed: [10.172.0.20] => (item=pgbouncer-2)
changed: [10.172.0.22] => (item=pgbouncer-2)
changed: [10.172.0.21] => (item=pgbouncer-2)
changed: [10.172.0.20] => (item=pgbouncer-3)
changed: [10.172.0.21] => (item=pgbouncer-3)
changed: [10.172.0.22] => (item=pgbouncer-3)
changed: [10.172.0.22] => (item=pgbouncer-4)
changed: [10.172.0.20] => (item=pgbouncer-4)
changed: [10.172.0.21] => (item=pgbouncer-4)

TASK [pgpass : Configure a password file (/var/lib/postgresql/.pgpass)] ********
changed: [10.172.0.22]
changed: [10.172.0.21]
changed: [10.172.0.20]

RUNNING HANDLER [pgbouncer : Restart pgbouncer service] ************************
changed: [10.172.0.20] => (item=pgbouncer)
changed: [10.172.0.21] => (item=pgbouncer)
changed: [10.172.0.22] => (item=pgbouncer)
changed: [10.172.0.22] => (item=pgbouncer-2)
changed: [10.172.0.21] => (item=pgbouncer-2)
changed: [10.172.0.20] => (item=pgbouncer-2)
changed: [10.172.0.21] => (item=pgbouncer-3)
changed: [10.172.0.20] => (item=pgbouncer-3)
changed: [10.172.0.22] => (item=pgbouncer-3)
changed: [10.172.0.22] => (item=pgbouncer-4)
changed: [10.172.0.20] => (item=pgbouncer-4)
changed: [10.172.0.21] => (item=pgbouncer-4)

RUNNING HANDLER [pgbouncer : Wait for port "6432" to become open on the host] ***
ok: [10.172.0.20]
ok: [10.172.0.21]
ok: [10.172.0.22]

result:

postgres@pgnode01:~$ ls -l /etc/pgbouncer/
total 16
-rw-r----- 1 postgres postgres 873 Oct 17 15:30 pgbouncer-2.ini
-rw-r----- 1 postgres postgres 873 Oct 17 15:30 pgbouncer-3.ini
-rw-r----- 1 postgres postgres 873 Oct 17 15:30 pgbouncer-4.ini
-rw-r----- 1 postgres postgres 867 Oct 17 15:30 pgbouncer.ini
-rw-r----- 1 postgres postgres   0 Oct 17 13:33 userlist.txt

postgres@pgnode01:~$ sudo ls -l /etc/systemd/system/ | grep pgbouncer
-rw-r--r-- 1 postgres postgres  550 Oct 17 15:30 pgbouncer-2.service
-rw-r--r-- 1 postgres postgres  550 Oct 17 15:30 pgbouncer-3.service
-rw-r--r-- 1 postgres postgres  550 Oct 17 15:30 pgbouncer-4.service
-rw-r--r-- 1 postgres postgres  544 Oct 17 15:30 pgbouncer.service

postgres@pgnode01:~$ sudo systemctl status pgbouncer
● pgbouncer.service - pgBouncer connection pooling for PostgreSQL
     Loaded: loaded (/etc/systemd/system/pgbouncer.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2023-10-17 15:30:58 UTC; 15min ago
   Main PID: 16424 (pgbouncer)
      Tasks: 2 (limit: 77073)
     Memory: 2.7M
        CPU: 130ms
     CGroup: /docker/3847fc857805533fb8b89010c3a7fdcb109c0811c4feeeb9acd9aa61f3b9e726/system.slice/pgbouncer.service
             └─16424 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini

Oct 17 15:30:58 pgnode01 systemd[1]: Starting pgBouncer connection pooling for PostgreSQL...
Oct 17 15:30:58 pgnode01 systemd[1]: pgbouncer.service: Can't open PID file /run/pgbouncer/pgbouncer.pid (yet?) after start: Operation not permitted
Oct 17 15:30:58 pgnode01 systemd[1]: Started pgBouncer connection pooling for PostgreSQL.
postgres@pgnode01:~$ 
postgres@pgnode01:~$ sudo systemctl status pgbouncer-2
● pgbouncer-2.service - pgBouncer connection pooling for PostgreSQL
     Loaded: loaded (/etc/systemd/system/pgbouncer-2.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2023-10-17 15:30:59 UTC; 15min ago
   Main PID: 16492 (pgbouncer)
      Tasks: 2 (limit: 77073)
     Memory: 2.6M
        CPU: 133ms
     CGroup: /docker/3847fc857805533fb8b89010c3a7fdcb109c0811c4feeeb9acd9aa61f3b9e726/system.slice/pgbouncer-2.service
             └─16492 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer-2.ini

Oct 17 15:30:59 pgnode01 systemd[1]: Starting pgBouncer connection pooling for PostgreSQL...
Oct 17 15:30:59 pgnode01 systemd[1]: pgbouncer-2.service: Can't open PID file /run/pgbouncer/pgbouncer-2.pid (yet?) after start: Operation not permitted
Oct 17 15:30:59 pgnode01 systemd[1]: Started pgBouncer connection pooling for PostgreSQL.
postgres@pgnode01:~$ sudo systemctl status pgbouncer-3
● pgbouncer-3.service - pgBouncer connection pooling for PostgreSQL
     Loaded: loaded (/etc/systemd/system/pgbouncer-3.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2023-10-17 15:31:00 UTC; 15min ago
   Main PID: 16559 (pgbouncer)
      Tasks: 2 (limit: 77073)
     Memory: 2.6M
        CPU: 134ms
     CGroup: /docker/3847fc857805533fb8b89010c3a7fdcb109c0811c4feeeb9acd9aa61f3b9e726/system.slice/pgbouncer-3.service
             └─16559 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer-3.ini

Oct 17 15:31:00 pgnode01 systemd[1]: Starting pgBouncer connection pooling for PostgreSQL...
Oct 17 15:31:00 pgnode01 systemd[1]: pgbouncer-3.service: Can't open PID file /run/pgbouncer/pgbouncer-3.pid (yet?) after start: Operation not permitted
Oct 17 15:31:00 pgnode01 systemd[1]: Started pgBouncer connection pooling for PostgreSQL.
postgres@pgnode01:~$ sudo systemctl status pgbouncer-4
● pgbouncer-4.service - pgBouncer connection pooling for PostgreSQL
     Loaded: loaded (/etc/systemd/system/pgbouncer-4.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2023-10-17 15:31:01 UTC; 15min ago
   Main PID: 16627 (pgbouncer)
      Tasks: 2 (limit: 77073)
     Memory: 2.6M
        CPU: 127ms
     CGroup: /docker/3847fc857805533fb8b89010c3a7fdcb109c0811c4feeeb9acd9aa61f3b9e726/system.slice/pgbouncer-4.service
             └─16627 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer-4.ini

Oct 17 15:31:01 pgnode01 systemd[1]: Starting pgBouncer connection pooling for PostgreSQL...
Oct 17 15:31:01 pgnode01 systemd[1]: pgbouncer-4.service: Can't open PID file /run/pgbouncer/pgbouncer-4.pid (yet?) after start: Operation not permitted
Oct 17 15:31:01 pgnode01 systemd[1]: Started pgBouncer connection pooling for PostgreSQL.

postgres@pgnode01:~$ ls -l /var/log/pgbouncer/
total 16
-rw-r--r-- 1 postgres postgres 3776 Oct 17 15:46 pgbouncer-2.log
-rw-r--r-- 1 postgres postgres 3902 Oct 17 15:47 pgbouncer-3.log
-rw-r--r-- 1 postgres postgres 4043 Oct 17 15:47 pgbouncer-4.log
-rw-r--r-- 1 postgres postgres 3772 Oct 17 15:46 pgbouncer.log

postgres@pgnode01:~$ PGPASSWORD=postgres-pass psql -h /run/pgbouncer/pgbouncer-4 -p 6432 -U postgres -d postgres
psql (15.4 (Ubuntu 15.4-2.pgdg22.04+1))
Type "help" for help.

postgres=# \q
postgres@pgnode01:~$ PGPASSWORD=postgres-pass psql -h /run/pgbouncer/pgbouncer-3 -p 6432 -U postgres -d postgres
psql (15.4 (Ubuntu 15.4-2.pgdg22.04+1))
Type "help" for help.

postgres=# \q
postgres@pgnode01:~$ PGPASSWORD=postgres-pass psql -h /run/pgbouncer/pgbouncer-2 -p 6432 -U postgres -d postgres
psql (15.4 (Ubuntu 15.4-2.pgdg22.04+1))
Type "help" for help.

postgres=# \q
postgres@pgnode01:~$ PGPASSWORD=postgres-pass psql -h /run/pgbouncer/pgbouncer -p 6432 -U postgres -d postgres
psql (15.4 (Ubuntu 15.4-2.pgdg22.04+1))
Type "help" for help.

postgres=# \q

postgres@pgnode01:~$ ps auxf | grep pgbouncer
postgres   29634  0.0  0.0   6608  2356 pts/0    S+   15:51   0:00          \_ grep pgbouncer
postgres   16424  0.0  0.0  22240  8264 ?        Sl   15:30   0:00 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
postgres   16492  0.0  0.0  22240  8188 ?        Sl   15:30   0:00 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer-2.ini
postgres   16559  0.0  0.0  22240  7936 ?        Sl   15:31   0:00 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer-3.ini
postgres   16627  0.0  0.0  22240  8188 ?        Sl   15:31   0:00 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer-4.ini

passed

vitabaks commented 11 months ago

Test 2: pg_upgrade and multiple pgbouncers

Make sure that pause works for each pgbouncer process.

Pgbouncer pools pause check command:

for i in {1..1000}; do psql -h /var/run/pgbouncer -p 6432 -U postgres -c "select now(), version()"; sleep 1; done
for i in {1..1000}; do psql -h /var/run/pgbouncer-2 -p 6432 -U postgres -c "select now(), version()"; sleep 1; done
for i in {1..1000}; do psql -h /var/run/pgbouncer-3 -p 6432 -U postgres -c "select now(), version()"; sleep 1; done
for i in {1..1000}; do psql -h /var/run/pgbouncer-4 -p 6432 -U postgres -c "select now(), version()"; sleep 1; done

Ansible log:

...
PLAY [(5/7) UPGRADE: Upgrade PostgreSQL] ***************************************

TASK [Include OS-specific variables] *******************************************
ok: [10.172.2.20]
ok: [10.172.2.21]
ok: [10.172.2.22]

TASK [Include upgrade variables] ***********************************************
ok: [10.172.2.20]
ok: [10.172.2.21]
ok: [10.172.2.22]

TASK [Enable maintenance mode] *************************************************

TASK [upgrade : Pause Patroni cluster (enable maintenance mode)] ***************
changed: [10.172.2.20]

TASK [upgrade : Stop confd service] ********************************************
changed: [10.172.2.20]
changed: [10.172.2.21]
changed: [10.172.2.22]

TASK [upgrade : Update haproxy conf file (disable http-checks)] ****************
changed: [10.172.2.20]
changed: [10.172.2.21]
changed: [10.172.2.22]

TASK [upgrade : Reload haproxy service] ****************************************
changed: [10.172.2.20]
changed: [10.172.2.21]
changed: [10.172.2.22]

TASK [upgrade : Stop Patroni service] ******************************************
changed: [10.172.2.22]
changed: [10.172.2.21]
changed: [10.172.2.20]
FAILED - RETRYING: [10.172.2.20]: Wait until the Patroni cluster is stopped (30 retries left).
FAILED - RETRYING: [10.172.2.20]: Wait until the Patroni cluster is stopped (29 retries left).
FAILED - RETRYING: [10.172.2.20]: Wait until the Patroni cluster is stopped (28 retries left).

TASK [upgrade : Wait until the Patroni cluster is stopped] *********************
ok: [10.172.2.20]

TASK [Stop Services] ***********************************************************

TASK [upgrade : Execute CHECKPOINT before stopping PostgreSQL] *****************
changed: [10.172.2.20]
changed: [10.172.2.21]
changed: [10.172.2.22]

TASK [upgrade : Wait for the CHECKPOINT to complete] ***************************
changed: [10.172.2.21]
changed: [10.172.2.20]
changed: [10.172.2.22]

TASK [upgrade : Wait until replication lag is less than 10.00 MB] **************
ok: [10.172.2.20]

TASK [upgrade : Debug pgb_unix_socket_dirs] ************************************
ok: [10.172.2.20] => {
    "pgb_unix_socket_dirs": "/var/run/pgbouncer /var/run/pgbouncer-2 /var/run/pgbouncer-3 /var/run/pgbouncer-4"
}

TASK [upgrade : PAUSE PgBouncer pools] *****************************************
changed: [10.172.2.20]

TASK [upgrade : Stop PostgreSQL on the Leader] *********************************
changed: [10.172.2.20]

TASK [upgrade : Stop PostgreSQL on the Replica] ********************************
changed: [10.172.2.22]
changed: [10.172.2.21]

TASK [upgrade : Check if PostgreSQL 14 is stopped] *****************************
ok: [10.172.2.20]
ok: [10.172.2.21]
ok: [10.172.2.22]

TASK [upgrade : Check if PostgreSQL 16 is stopped] *****************************
ok: [10.172.2.20]
ok: [10.172.2.22]
ok: [10.172.2.21]

TASK [Check 'Latest checkpoint location'] **************************************

TASK [upgrade : Get 'Latest checkpoint location' on the Leader] ****************
ok: [10.172.2.20]

TASK [upgrade : Get 'Latest checkpoint location' on the Replicas] **************
ok: [10.172.2.22]
ok: [10.172.2.21]

TASK [upgrade : Print 'Latest checkpoint location' for the Leader] *************
ok: [10.172.2.20] => {
    "msg": "Leader's latest checkpoint location: 0/6000028"
}

TASK [upgrade : Print 'Latest checkpoint location' for the Replica] ************
ok: [10.172.2.21] => {
    "msg": "Replica: 10.172.2.21, latest checkpoint location: 0/6000028"
}
ok: [10.172.2.22] => {
    "msg": "Replica: 10.172.2.22, latest checkpoint location: 0/6000028"
}

TASK [upgrade : Check if all 'Latest checkpoint location' values match] ********
ok: [10.172.2.21]
ok: [10.172.2.22]

TASK [upgrade : SUCCESS: 'Latest checkpoint location' values match on all cluster nodes] ***
ok: [10.172.2.20] => {
    "msg": "'Latest checkpoint location' is the same on the leader and its standbys"
}

TASK [Upgrade Primary] *********************************************************

TASK [upgrade : Upgrade the PostgreSQL to version 16 on the Primary (using pg_upgrade --link)] ***
changed: [10.172.2.20]

TASK [upgrade : Print the result of the pg_upgrade] ****************************
ok: [10.172.2.20] => {
    "msg": [
        [
            "Performing Consistency Checks",
            "-----------------------------",
            "Checking cluster versions                                     ok",
            "Checking database user is the install user                    ok",
            "Checking database connection settings                         ok",
            "Checking for prepared transactions                            ok",
            "Checking for system-defined composite types in user tables    ok",
            "Checking for reg* data types in user tables                   ok",
            "Checking for contrib/isn with bigint-passing mismatch         ok",
            "Checking for incompatible \"aclitem\" data type in user tables  ok",
            "Creating dump of global objects                               ok",
            "Creating dump of database schemas                             ok",
            "Checking for presence of required libraries                   ok",
            "Checking database user is the install user                    ok",
            "Checking for prepared transactions                            ok",
            "Checking for new cluster tablespace directories               ok",
            "",
            "If pg_upgrade fails after this point, you must re-initdb the",
            "new cluster before continuing.",
            "",
            "Performing Upgrade",
            "------------------",
            "Setting locale and encoding for new cluster                   ok",
            "Analyzing all rows in the new cluster                         ok",
            "Freezing all rows in the new cluster                          ok",
            "Deleting files from new pg_xact                               ok",
            "Copying old pg_xact to new server                             ok",
            "Setting oldest XID for new cluster                            ok",
            "Setting next transaction ID and epoch for new cluster         ok",
            "Deleting files from new pg_multixact/offsets                  ok",
            "Copying old pg_multixact/offsets to new server                ok",
            "Deleting files from new pg_multixact/members                  ok",
            "Copying old pg_multixact/members to new server                ok",
            "Setting next multixact ID and offset for new cluster          ok",
            "Resetting WAL archives                                        ok",
            "Setting frozenxid and minmxid counters in new cluster         ok",
            "Restoring global objects in the new cluster                   ok",
            "Restoring database schemas in the new cluster                 ok",
            "Adding \".old\" suffix to old global/pg_control                 ok",
            "",
            "If you want to start the old cluster, you will need to remove",
            "the \".old\" suffix from /pgdata/14/main/global/pg_control.old.",
            "Because \"link\" mode was used, the old cluster cannot be safely",
            "started once the new cluster has been started.",
            "Linking user relation files                                   ok",
            "Setting next OID for new cluster                              ok",
            "Sync data directory to disk                                   ok",
            "Creating script to delete old cluster                         ok",
            "Checking for extension updates                                ok",
            "",
            "Upgrade Complete",
            "----------------",
            "Optimizer statistics are not transferred by pg_upgrade.",
            "Once you start the new server, consider running:",
            "    /usr/lib/postgresql/16/bin/vacuumdb -U postgres --all --analyze-in-stages",
            "Running this script will delete the old cluster's data files:",
            "    ./delete_old_cluster.sh"
        ]
    ]
}

TASK [Upgrade Secondary] *******************************************************

TASK [upgrade : Make sure that the new data directory "/pgdata/16/main" are empty on the Replica] ***
changed: [10.172.2.22] => (item=absent)
changed: [10.172.2.21] => (item=absent)
changed: [10.172.2.22] => (item=directory)
changed: [10.172.2.21] => (item=directory)

TASK [upgrade : Upgrade the PostgreSQL on the Replica (using rsync --hard-links)] ***
changed: [10.172.2.20]
FAILED - RETRYING: [10.172.2.20]: Wait for the rsync to complete. (1800 retries left).

TASK [upgrade : Wait for the rsync to complete.] *******************************
changed: [10.172.2.20]

TASK [Create WAL dir symlink] **************************************************

TASK [upgrade : Make sure /pgdata/16/main/pg_wal is not symlink] ***************
ok: [10.172.2.22]
ok: [10.172.2.21]
ok: [10.172.2.20]

TASK [upgrade : Make sure the custom WAL directory "/pgwal/16/pg_wal" exists and is empty] ***
ok: [10.172.2.20] => (item=absent)
ok: [10.172.2.21] => (item=absent)
ok: [10.172.2.22] => (item=absent)
changed: [10.172.2.21] => (item=directory)
changed: [10.172.2.20] => (item=directory)
changed: [10.172.2.22] => (item=directory)

TASK [upgrade : Synchronize /pgdata/16/main/pg_wal to /pgwal/16/pg_wal] ********
changed: [10.172.2.22]
changed: [10.172.2.21]
changed: [10.172.2.20]

TASK [upgrade : Rename pg_wal to pg_wal_old] ***********************************
changed: [10.172.2.20]
changed: [10.172.2.21]
changed: [10.172.2.22]

TASK [upgrade : Create symlink /pgdata/16/main/pg_wal -> /pgwal/16/pg_wal] *****
changed: [10.172.2.20]
changed: [10.172.2.22]
changed: [10.172.2.21]

TASK [upgrade : Remove pg_wal_old directory] ***********************************
changed: [10.172.2.20]
changed: [10.172.2.21]
changed: [10.172.2.22]

TASK [Remove old cluster from DCS] *********************************************

TASK [upgrade : Remove existing cluster "postgres-cluster" from DCS] ***********
changed: [10.172.2.20]

TASK [Start Services] **********************************************************

TASK [upgrade : Start Patroni service on the Cluster Leader] *******************
changed: [10.172.2.20]

TASK [upgrade : Wait for Patroni port "8008" to become open on the host] *******
ok: [10.172.2.20]

TASK [upgrade : Check Patroni is healthy on the Leader] ************************
ok: [10.172.2.20]

TASK [upgrade : Perform RESUME PgBouncer pools on the Leader] ******************
included: /workspace/postgresql_cluster/roles/upgrade/tasks/pgbouncer_resume.yml for 10.172.2.20

TASK [upgrade : RESUME PgBouncer pools] ****************************************
changed: [10.172.2.20]

TASK [upgrade : Start Patroni service on the Cluster Replica] ******************
changed: [10.172.2.22]
changed: [10.172.2.21]

TASK [upgrade : Wait for Patroni port "8008" to become open on the host] *******
ok: [10.172.2.22]
ok: [10.172.2.21]

TASK [upgrade : Check Patroni is healthy on the Replica] ***********************
ok: [10.172.2.22]
ok: [10.172.2.21]

TASK [upgrade : Perform RESUME PgBouncer pools on the Replica] *****************
included: /workspace/postgresql_cluster/roles/upgrade/tasks/pgbouncer_resume.yml for 10.172.2.21, 10.172.2.22

TASK [upgrade : RESUME PgBouncer pools] ****************************************
changed: [10.172.2.21]
changed: [10.172.2.22]

TASK [upgrade : Check PostgreSQL is started and accepting connections] *********
ok: [10.172.2.21]
ok: [10.172.2.20]
ok: [10.172.2.22]

TASK [Disable maintenance mode] ************************************************

TASK [upgrade : Update haproxy conf file (enable http-checks)] *****************
changed: [10.172.2.20]
changed: [10.172.2.21]
changed: [10.172.2.22]

TASK [upgrade : Reload haproxy service] ****************************************
changed: [10.172.2.20]
changed: [10.172.2.21]
changed: [10.172.2.22]

TASK [upgrade : Start confd service] *******************************************
changed: [10.172.2.21]
changed: [10.172.2.20]
changed: [10.172.2.22]

PLAY [(6/7) POST-UPGRADE: Perform Post-Checks and Update extensions] ***********

TASK [Include OS-specific variables] *******************************************
ok: [10.172.2.20]
ok: [10.172.2.21]
ok: [10.172.2.22]

TASK [Include upgrade variables] ***********************************************
ok: [10.172.2.20]
ok: [10.172.2.21]
ok: [10.172.2.22]

TASK [Running Post-Checks] *****************************************************

TASK [upgrade : Make sure that physical replication is active] *****************
ok: [10.172.2.20]

TASK [upgrade : Create a table "test_replication" with 10000 rows on the Primary] ***
changed: [10.172.2.20]

TASK [upgrade : Wait until the PostgreSQL replica is synchronized] *************
ok: [10.172.2.22]
ok: [10.172.2.21]

TASK [upgrade : Drop a table "test_replication"] *******************************
changed: [10.172.2.20]

TASK [upgrade : Print the result of checking the number of records] ************
ok: [10.172.2.21] => {
    "msg": [
        "The PostgreSQL Replication is OK for replica pgnode02",
        "The number of records in the test_replication table the same as the Primary (10000 rows)"
    ]
}
ok: [10.172.2.22] => {
    "msg": [
        "The PostgreSQL Replication is OK for replica pgnode03",
        "The number of records in the test_replication table the same as the Primary (10000 rows)"
    ]
}

TASK [Update extensions] *******************************************************

TASK [upgrade : Get a list of databases] ***************************************
ok: [10.172.2.20]

TASK [upgrade : Update extensions in each database] ****************************
included: /workspace/postgresql_cluster/roles/upgrade/tasks/update_extensions.yml for 10.172.2.20 => (item=template1)
included: /workspace/postgresql_cluster/roles/upgrade/tasks/update_extensions.yml for 10.172.2.20 => (item=postgres)

TASK [upgrade : Get list of installed PostgreSQL extensions (database: template1)] ***
ok: [10.172.2.20]

TASK [upgrade : Get list of old PostgreSQL extensions (database: template1)] ***
ok: [10.172.2.20]

TASK [upgrade : The extensions are up-to-date (database: template1)] ***********
ok: [10.172.2.20] => {
    "msg": [
        "The extension versions are up-to-date for the database template1",
        "No update is required."
    ]
}

TASK [upgrade : Get list of installed PostgreSQL extensions (database: postgres)] ***
ok: [10.172.2.20]

TASK [upgrade : Get list of old PostgreSQL extensions (database: postgres)] ****
ok: [10.172.2.20]

TASK [upgrade : The extensions are up-to-date (database: postgres)] ************
ok: [10.172.2.20] => {
    "msg": [
        "The extension versions are up-to-date for the database postgres",
        "No update is required."
    ]
}

PLAY [(7/7) POST-UPGRADE: Analyze a PostgreSQL database (update optimizer statistics) and Post-Upgrade tasks] ***

TASK [Include OS-specific variables] *******************************************
ok: [10.172.2.20]
ok: [10.172.2.21]
ok: [10.172.2.22]

TASK [Include upgrade variables] ***********************************************
ok: [10.172.2.20]
ok: [10.172.2.21]
ok: [10.172.2.22]

TASK [Analyze database] ********************************************************

TASK [upgrade : Start pg_terminator script: Monitor locks and terminate the 'ANALYZE' blockers] ***
changed: [10.172.2.20]

TASK [upgrade : Run vacuumdb to analyze the PostgreSQL databases] **************
changed: [10.172.2.20]

TASK [upgrade : Collecting statistics in progress. Wait for the analyze to complete.] ***
changed: [10.172.2.20]

TASK [upgrade : Stop pg_terminator script] *************************************
changed: [10.172.2.20]

TASK [Running Post-Upgrade tasks] **********************************************

TASK [upgrade : Ensure the current data directory is the new data directory] ***
ok: [10.172.2.21]
ok: [10.172.2.20]
ok: [10.172.2.22]

TASK [upgrade : Delete the old PostgreSQL data directory (perform pg_dropcluster)] ***
changed: [10.172.2.20]
changed: [10.172.2.21]
changed: [10.172.2.22]

TASK [upgrade : Delete the old PostgreSQL WAL directory] ***********************
ok: [10.172.2.20]
ok: [10.172.2.21]
ok: [10.172.2.22]

TASK [upgrade : Remove old PostgreSQL packages] ********************************
changed: [10.172.2.21] => (item=postgresql-14)
changed: [10.172.2.20] => (item=postgresql-14)
changed: [10.172.2.22] => (item=postgresql-14)
changed: [10.172.2.21] => (item=postgresql-client-14)
changed: [10.172.2.20] => (item=postgresql-client-14)
changed: [10.172.2.22] => (item=postgresql-client-14)
ok: [10.172.2.20] => (item=postgresql-contrib-14)
ok: [10.172.2.21] => (item=postgresql-contrib-14)
ok: [10.172.2.22] => (item=postgresql-contrib-14)
ok: [10.172.2.21] => (item=postgresql-server-dev-14)
ok: [10.172.2.20] => (item=postgresql-server-dev-14)
ok: [10.172.2.22] => (item=postgresql-server-dev-14)

TASK [upgrade : Check the Patroni cluster state] *******************************
ok: [10.172.2.20]

TASK [upgrade : Check the current PostgreSQL version] **************************
ok: [10.172.2.20]

TASK [upgrade : List the Patroni cluster members] ******************************
ok: [10.172.2.20] => {
    "msg": [
        "+ Cluster: postgres-cluster -------+-----------+----+-----------+",
        "| Member   | Host        | Role    | State     | TL | Lag in MB |",
        "+----------+-------------+---------+-----------+----+-----------+",
        "| pgnode01 | 10.172.2.20 | Leader  | running   |  2 |           |",
        "| pgnode02 | 10.172.2.21 | Replica | streaming |  2 |         0 |",
        "| pgnode03 | 10.172.2.22 | Replica | streaming |  2 |         0 |",
        "+----------+-------------+---------+-----------+----+-----------+"
    ]
}

TASK [upgrade : Upgrade completed] *********************************************
ok: [10.172.2.20] => {
    "msg": [
        "PostgreSQL upgrade completed.",
        "Current version: 16.0 (Ubuntu 16.0-1.pgdg22.04+1)"
    ]
}

PLAY RECAP *********************************************************************
10.172.2.20                : ok=146  changed=47   unreachable=0    failed=0    skipped=110  rescued=0    ignored=0
10.172.2.21                : ok=86   changed=26   unreachable=0    failed=0    skipped=128  rescued=0    ignored=0
10.172.2.22                : ok=86   changed=26   unreachable=0    failed=0    skipped=128  rescued=0    ignored=0

Result:

/var/run/pgbouncer

              now              |                                                              version                                                              
-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------
 2023-11-07 10:45:37.756086+00 | PostgreSQL 14.9 (Ubuntu 14.9-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

              now              |                                                              version                                                              
-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------
 2023-11-07 10:45:38.793145+00 | PostgreSQL 14.9 (Ubuntu 14.9-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

              now              |                                                              version                                                              
-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------
 2023-11-07 10:46:06.008329+00 | PostgreSQL 16.0 (Ubuntu 16.0-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

/var/run/pgbouncer-2

              now              |                                                              version                                                              
-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------
 2023-11-07 10:45:38.587937+00 | PostgreSQL 14.9 (Ubuntu 14.9-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

              now              |                                                              version                                                              
-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------
 2023-11-07 10:46:06.007366+00 | PostgreSQL 16.0 (Ubuntu 16.0-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

/var/run/pgbouncer-3


              now              |                                                              version                                                              
-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------
 2023-11-07 10:45:38.926362+00 | PostgreSQL 14.9 (Ubuntu 14.9-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

              now              |                                                              version                                                              
-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------
 2023-11-07 10:46:06.010875+00 | PostgreSQL 16.0 (Ubuntu 16.0-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

/var/run/pgbouncer-4


              now              |                                                              version                                                              
-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------
 2023-11-07 10:45:39.002152+00 | PostgreSQL 14.9 (Ubuntu 14.9-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

              now              |                                                              version                                                              
-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------
 2023-11-07 10:46:06.007178+00 | PostgreSQL 16.0 (Ubuntu 16.0-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

passed