pgbouncer user doesn't exist error when trying to deploy from command line #795

biglolka · 2024-10-22T11:32:59Z

I'm trying to deploy the cluster with the ansible script, but I keep getting the following error:

`fatal: [172.22.134.241]: FAILED! => {"changed": true, "cmd": ["/usr/lib/postgresql/16/bin/psql", "-p", "5432", "-U", "postgres", "-d", "postgres", "-tAXc", "CREATE FUNCTION user_search(uname TEXT) RETURNS TABLE (usename name, passwd text) AS $$ SELECT usename, passwd FROM pg_shadow WHERE usename=$1; $$ LANGUAGE sql SECURITY DEFINER; REVOKE ALL ON FUNCTION user_search(uname TEXT) FROM public; GRANT EXECUTE ON FUNCTION user_search(uname TEXT) TO pgbouncer"], "delta": "0:00:00.011840", "end": "2024-10-22 13:16:06.143323", "msg": "non-zero return code", "rc": 1, "start": "2024-10-22 13:16:06.131483", "stderr": "ERROR:  role \"pgbouncer\" does not exist", "stderr_lines": ["ERROR:  role \"pgbouncer\" does not exist"], "stdout": "CREATE FUNCTION\nREVOKE", "stdout_lines": ["CREATE FUNCTION", "REVOKE"]}`

Thanks in advance for your response!

The text was updated successfully, but these errors were encountered:

vitabaks · 2024-10-22T11:52:02Z

Hi @biglolka

Which version of postgresql_cluster are you using?

Make sure that you have a pgbouncer user defined in the postgresql_users variable as here https://github.com/vitabaks/postgresql_cluster/blob/2.0.0/automation/vars/main.yml#L186

biglolka · 2024-10-22T17:37:30Z

Hi @vitabaks,

Thanks for the heads-up, you diverted my attention to the right direction:
According to the comment the password for the user will be generated if not defined explicitly in the yaml:
https://github.com/vitabaks/postgresql_cluster/blob/23d021967cd38dd11e84692e07d381d1d90bb381/automation/vars/main.yml#L353
In fact that caused the problem, when setting the password in the yaml for "pgbouncer"user it works OK.

vitabaks · 2024-10-22T18:44:39Z

there should be no problem for automatic password generation if pgbouncer_auth_password is not set, what error did you get?

biglolka · 2024-10-23T07:14:54Z

If I don't set pgbouncer password (but postgres and replicator) in main.yaml I get this error at this stage:

If I don't set postgres user password in main.yaml it fails at this stage:

If I set postgres password in main.yaml but I don't set replicator password it's hanging here:

so for sure generating the passwords in case they are not explicitly set in the main.yaml doesn't work.

vitabaks · 2024-10-23T16:56:23Z

At the same time, did you have the postgresql_users variable filled in? Could you attach the full Ansible log?

if I understood correctly, the problem with the password for pgbouncer (lack of autogeneration) is possible if the password for pgbouncer_auth_password is not set, but at the same time set it for patroni_superuser_password.

I'll check it out.

biglolka · 2024-10-24T07:59:00Z

well I left the postgresql_users section as default, didn't touch it
patroni_superuser_and_patroni_replicator_passwords_are_set_but_no_password_for_pgbouncer.gz
patroni_superuser_password_is_set_but_no_password_for_patroni_replicator_and_pgbouncer.gz
without_patroni_superuser_patroni_replicator_and_pgbouncer_password_set.gz

mainframe · 2024-11-20T23:36:49Z

I can confirm that leaving the pgbouncer_auth_password value empty will produce previously mentioned "role does not exist" error with deploy_pgcluster.yml playbook (ie password autogeneration does not seem to work). That also applies to patroni_superuser_password and patroni_replication_password - if their values are left empty.

vitabaks · 2024-11-21T07:20:42Z

Are you sure you're using version 2.0?

mainframe · 2024-11-21T09:04:29Z

@vitabaks you are correct - I was using latest master branch with newer commits beyond 2.0.0 tag:
commit 67a21273856c740eb917312fda092e98ddc279ac (origin/master, old-origin/master, old-origin/HEAD, master) Author: Vitaliy Kukharik <[email protected]> Date: Tue Nov 19 15:34:19 2024 +0300

Btw - great work!

vitabaks · 2024-11-21T09:33:50Z

I’m not yet sure how to reproduce your issue, as I’ve performed numerous cluster deployments without encountering such a problem. Additionally, daily tests confirm that passwords are successfully generated without any errors.

If you could provide the full Ansible logs, it might help me identify the issue. Even better, sharing an archive of your postgresql_cluster directory would allow for a more thorough investigation.

I also recommend trying to deploy via the Console (UI).

mainframe · 2024-11-21T09:55:39Z

@vitabaks could you please clarify "defined" meaning in this warning "If not defined, a password will be generated automatically during deployment"? Ie should password be autogenerated when password is left empty (pgbouncer_auth_password: "") or if pgbouncer_auth_password variable is commented out in vars/main.yml (as "not defined")?

chobostar · 2024-11-28T16:00:24Z

Deploy fails when passwords are not defined - ubuntu/jammy64

$ ansible --version
ansible [core 2.16.13]
  config file = /home/user/git/pgsql/postgresql-cluster/automation/ansible.cfg
  configured module search path = ['/home/user/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/user/git/pgsql/postgresql-cluster/.venv/lib/python3.12/site-packages/ansible
  ansible collection location = /home/user/.ansible/collections:/usr/share/ansible/collections
  executable location = /home/user/git/pgsql/postgresql-cluster/.venv/bin/ansible
  python version = 3.12.3 (main, Nov  6 2024, 18:32:19) [GCC 13.2.0] (/home/user/git/pgsql/postgresql-cluster/.venv/bin/python3.12)
  jinja version = 3.1.4
  libyaml = True

on current master

vitabaks · 2024-11-29T21:31:06Z

Please attach your "automation" directory, I will try to solve the problem.

set1991 · 2025-01-26T14:56:28Z

I can confirm that leaving the pgbouncer_auth_password value empty will produce previously mentioned "role does not exist" thanks for help. If i set pgbouncer_auth_password it's work

vitabaks · 2025-01-26T16:18:01Z

Have you tried using the latest version 2.1.0?

set1991 · 2025-01-26T16:22:26Z

Have you tried using the latest version 2.1.0?

i'm using commit 225a328 (HEAD -> master, origin/master, origin/HEAD)

error without pgbouncer_auth_password

TASK [pgbouncer/config : Create 'user_search' function for pgbouncer 'auth_query' option] ****************************************************
fatal: [10.0.100.21]: FAILED! => {"changed": true, "cmd": ["/usr/lib/postgresql/17/bin/psql", "-p", "5432", "-U", "postgres", "-d", "postgres", "-tAXc", "CREATE FUNCTION user_search(uname TEXT) RETURNS TABLE (usename name, passwd text) AS $$ SELECT usename, passwd FROM pg_shadow WHERE usename=$1; $$ LANGUAGE sql SECURITY DEFINER; REVOKE ALL ON FUNCTION user_search(uname TEXT) FROM public; GRANT EXECUTE ON FUNCTION user_search(uname TEXT) TO pgbouncer"], "delta": "0:00:00.017670", "end": "2025-01-26 14:22:19.961438", "msg": "non-zero return code", "rc": 1, "start": "2025-01-26 14:22:19.943768", "stderr": "ERROR:  role \"pgbouncer\" does not exist", "stderr_lines": ["ERROR:  role \"pgbouncer\" does not exist"], "stdout": "CREATE FUNCTION\nREVOKE", "stdout_lines": ["CREATE FUNCTION", "REVOKE"]}

vitabaks · 2025-01-26T17:00:42Z

Please attach full ansible log.

set1991 · 2025-01-26T17:12:33Z

Please attach full ansible log.

aleksandcsuba@pg-cluster-host:~/autobase/automation$ ansible-playbook deploy_pgcluster.yml

PLAY [Deploy PostgreSQL HA Cluster (based on "Patroni")] *************************************************************************************

TASK [Gathering Facts] ***********************************************************************************************************************
ok: [localhost]

TASK [Include main variables] ****************************************************************************************************************
ok: [localhost]

PLAY [deploy_pgcluster.yml | Perform pre-checks] *********************************************************************************************

TASK [Gathering Facts] ***********************************************************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]
ok: [10.0.100.13]
ok: [10.0.100.19]
ok: [10.0.100.18]
ok: [10.0.100.20]

TASK [Include main variables] ****************************************************************************************************************
ok: [10.0.100.18]
ok: [10.0.100.20]
ok: [10.0.100.19]
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [Include system variables] **************************************************************************************************************
ok: [10.0.100.18]
ok: [10.0.100.19]
ok: [10.0.100.20]
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [Include OS-specific variables] *********************************************************************************************************
ok: [10.0.100.18]
ok: [10.0.100.19]
ok: [10.0.100.20]
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [System info] ***************************************************************************************************************************
ok: [10.0.100.18] => {
    "msg": {
        "architecture": "x86_64",
        "cpu": {
            "cores": "1",
            "count": "1",
            "model": "Intel(R) Xeon(R) CPU @ 2.20GHz"
        },
        "disk_space_total": "10.14 GB",
        "ip_address": "10.0.100.18",
        "kernel": "6.5.0-1022-gcp",
        "memory": "3.81 GB",
        "os": "Ubuntu 22.04",
        "product_name": "Google Compute Engine",
        "server_name": "pg-cluster-etcd2",
        "virtualization_type": "kvm"
    }
}
ok: [10.0.100.20] => {
    "msg": {
        "architecture": "x86_64",
        "cpu": {
            "cores": "1",
            "count": "1",
            "model": "Intel(R) Xeon(R) CPU @ 2.20GHz"
        },
        "disk_space_total": "10.14 GB",
        "ip_address": "10.0.100.20",
        "kernel": "6.5.0-1022-gcp",
        "memory": "3.81 GB",
        "os": "Ubuntu 22.04",
        "product_name": "Google Compute Engine",
        "server_name": "pg-cluster-etcd3",
        "virtualization_type": "kvm"
    }
}
ok: [10.0.100.19] => {
    "msg": {
        "architecture": "x86_64",
        "cpu": {
            "cores": "1",
            "count": "1",
            "model": "Intel(R) Xeon(R) CPU @ 2.20GHz"
        },
        "disk_space_total": "10.14 GB",
        "ip_address": "10.0.100.19",
        "kernel": "6.5.0-1022-gcp",
        "memory": "3.81 GB",
        "os": "Ubuntu 22.04",
        "product_name": "Google Compute Engine",
        "server_name": "pg-cluster-etcd1",
        "virtualization_type": "kvm"
    }
}
ok: [10.0.100.13] => {
    "msg": {
        "architecture": "x86_64",
        "cpu": {
            "cores": "1",
            "count": "1",
            "model": "Intel(R) Xeon(R) CPU @ 2.20GHz"
        },
        "disk_space_total": "10.14 GB",
        "ip_address": "10.0.100.13",
        "kernel": "6.5.0-1022-gcp",
        "memory": "3.81 GB",
        "os": "Ubuntu 22.04",
        "product_name": "Google Compute Engine",
        "server_name": "pgnode01",
        "virtualization_type": "kvm"
    }
}
ok: [10.0.100.14] => {
    "msg": {
        "architecture": "x86_64",
        "cpu": {
            "cores": "1",
            "count": "1",
            "model": "AMD EPYC 7B12"
        },
        "disk_space_total": "10.14 GB",
        "ip_address": "10.0.100.14",
        "kernel": "6.5.0-1022-gcp",
        "memory": "3.81 GB",
        "os": "Ubuntu 22.04",
        "product_name": "Google Compute Engine",
        "server_name": "pgnode02",
        "virtualization_type": "kvm"
    }
}
ok: [10.0.100.12] => {
    "msg": {
        "architecture": "x86_64",
        "cpu": {
            "cores": "1",
            "count": "1",
            "model": "Intel(R) Xeon(R) CPU @ 2.20GHz"
        },
        "disk_space_total": "10.14 GB",
        "ip_address": "10.0.100.12",
        "kernel": "6.5.0-1022-gcp",
        "memory": "3.81 GB",
        "os": "Ubuntu 22.04",
        "product_name": "Google Compute Engine",
        "server_name": "pgnode03",
        "virtualization_type": "kvm"
    }
}

TASK [Update apt cache] **********************************************************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]
ok: [10.0.100.13]
changed: [10.0.100.18]
changed: [10.0.100.20]
changed: [10.0.100.19]

TASK [Make sure the gnupg and apt-transport-https packages are present] **********************************************************************
changed: [10.0.100.14]
changed: [10.0.100.18]
changed: [10.0.100.12]
changed: [10.0.100.20]
changed: [10.0.100.19]
changed: [10.0.100.13]

TASK [Make sure that the iproute is installed] ***********************************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.18]
ok: [10.0.100.19]
ok: [10.0.100.20]
ok: [10.0.100.13]
ok: [10.0.100.12]

TASK [pre-checks : PostgreSQL | check that data directory "/var/lib/postgresql/17/main" is not initialized] **********************************
ok: [10.0.100.14]
ok: [10.0.100.12]
ok: [10.0.100.13]

TASK [pre-checks : HugePages | Get shared_buffers value from postgresql_parameters variable] *************************************************
ok: [10.0.100.13]
ok: [10.0.100.12]
ok: [10.0.100.14]

TASK [pre-checks : HugePages | Set variable: shared_buffers_gb] ******************************************************************************
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [hostname : Change hostname] ************************************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [hostname : Change hostname in /etc/hosts] **********************************************************************************************
changed: [10.0.100.14] => (item=None)
changed: [10.0.100.14]
changed: [10.0.100.12] => (item=None)
changed: [10.0.100.12]
changed: [10.0.100.13] => (item=None)
changed: [10.0.100.13]

TASK [tls_certificate/generate : Make sure that the python3-cryptography package is present] *************************************************
ok: [10.0.100.14]
ok: [10.0.100.18]
ok: [10.0.100.19]
ok: [10.0.100.20]
ok: [10.0.100.12]
ok: [10.0.100.13]

TASK [tls_certificate/generate : Clean up existing certificates (if any)] ********************************************************************
ok: [10.0.100.14] => (item=/etc/tls/server.key)
ok: [10.0.100.18] => (item=/etc/tls/server.key)
ok: [10.0.100.19] => (item=/etc/tls/server.key)
ok: [10.0.100.20] => (item=/etc/tls/server.key)
ok: [10.0.100.13] => (item=/etc/tls/server.key)
ok: [10.0.100.12] => (item=/etc/tls/server.key)
ok: [10.0.100.14] => (item=/etc/tls/server.crt)
ok: [10.0.100.19] => (item=/etc/tls/server.crt)
ok: [10.0.100.18] => (item=/etc/tls/server.crt)
ok: [10.0.100.12] => (item=/etc/tls/server.crt)
ok: [10.0.100.14] => (item=/etc/tls/ca.crt)
ok: [10.0.100.20] => (item=/etc/tls/server.crt)
ok: [10.0.100.13] => (item=/etc/tls/server.crt)
ok: [10.0.100.14] => (item=/etc/tls/ca.key)
ok: [10.0.100.19] => (item=/etc/tls/ca.crt)
ok: [10.0.100.18] => (item=/etc/tls/ca.crt)
ok: [10.0.100.12] => (item=/etc/tls/ca.crt)
ok: [10.0.100.20] => (item=/etc/tls/ca.crt)
ok: [10.0.100.13] => (item=/etc/tls/ca.crt)
ok: [10.0.100.14] => (item=/etc/etcd/tls/server.crt)
ok: [10.0.100.19] => (item=/etc/tls/ca.key)
ok: [10.0.100.18] => (item=/etc/tls/ca.key)
ok: [10.0.100.12] => (item=/etc/tls/ca.key)
ok: [10.0.100.20] => (item=/etc/tls/ca.key)
ok: [10.0.100.13] => (item=/etc/tls/ca.key)
ok: [10.0.100.14] => (item=/etc/etcd/tls/ca.crt)
ok: [10.0.100.19] => (item=/etc/etcd/tls/server.crt)
ok: [10.0.100.18] => (item=/etc/etcd/tls/server.crt)
ok: [10.0.100.14] => (item=/etc/etcd/tls/server.key)
ok: [10.0.100.12] => (item=/etc/etcd/tls/server.crt)
ok: [10.0.100.20] => (item=/etc/etcd/tls/server.crt)
ok: [10.0.100.13] => (item=/etc/etcd/tls/server.crt)
ok: [10.0.100.19] => (item=/etc/etcd/tls/ca.crt)
ok: [10.0.100.18] => (item=/etc/etcd/tls/ca.crt)
ok: [10.0.100.12] => (item=/etc/etcd/tls/ca.crt)
ok: [10.0.100.20] => (item=/etc/etcd/tls/ca.crt)
ok: [10.0.100.13] => (item=/etc/etcd/tls/ca.crt)
ok: [10.0.100.19] => (item=/etc/etcd/tls/server.key)
ok: [10.0.100.12] => (item=/etc/etcd/tls/server.key)
ok: [10.0.100.18] => (item=/etc/etcd/tls/server.key)
ok: [10.0.100.20] => (item=/etc/etcd/tls/server.key)
ok: [10.0.100.13] => (item=/etc/etcd/tls/server.key)

TASK [tls_certificate/generate : Generate subjectAltName entries for all hosts] **************************************************************
ok: [10.0.100.18 -> 10.0.100.13]

TASK [tls_certificate/generate : Display Certificate subjectAltName future value] ************************************************************
ok: [10.0.100.18 -> 10.0.100.13] => {
    "subject_alt_name": "DNS:pg-cluster-etcd2,DNS:pg-cluster-etcd1,DNS:pg-cluster-etcd3,DNS:pgnode01,DNS:pgnode02,DNS:pgnode03,DNS:pg-cluster-etcd2.europe-west1-b.c.prod-448817.internal,DNS:pg-cluster-etcd1.europe-west1-b.c.prod-448817.internal,DNS:pg-cluster-etcd3.europe-west1-b.c.prod-448817.internal,DNS:pg-cluster-master.europe-west1-b.c.prod-448817.internal,DNS:pg-cluster-slave1.europe-west1-b.c.prod-448817.internal,DNS:pg-cluster-slave2.europe-west1-b.c.prod-448817.internal,IP:10.0.100.18,IP:10.0.100.19,IP:10.0.100.20,IP:10.0.100.13,IP:10.0.100.14,IP:10.0.100.12,DNS:localhost,IP:127.0.0.1"
}

TASK [tls_certificate/generate : Ensure TLS directory exist] *********************************************************************************
changed: [10.0.100.18 -> 10.0.100.13]

TASK [tls_certificate/generate : Generate CA private key] ************************************************************************************
changed: [10.0.100.18 -> 10.0.100.13]

TASK [tls_certificate/generate : Create CSR for CA certificate] ******************************************************************************
changed: [10.0.100.18 -> 10.0.100.13]

TASK [tls_certificate/generate : Create self-signed CA certificate from CSR] *****************************************************************
changed: [10.0.100.18 -> 10.0.100.13]

TASK [tls_certificate/generate : Create server private key] **********************************************************************************
changed: [10.0.100.18 -> 10.0.100.13]

TASK [tls_certificate/generate : Create server CSR] ******************************************************************************************
changed: [10.0.100.18 -> 10.0.100.13]

TASK [tls_certificate/generate : Sign server certificate with the CA] ************************************************************************
changed: [10.0.100.18 -> 10.0.100.13]

TASK [tls_certificate/generate : Write server certificate] ***********************************************************************************
changed: [10.0.100.18 -> 10.0.100.13]

PLAY [etcd_cluster.yml | Deploy etcd Cluster] ************************************************************************************************

TASK [Gathering Facts] ***********************************************************************************************************************
ok: [10.0.100.18]
ok: [10.0.100.20]
ok: [10.0.100.19]

TASK [Include main variables] ****************************************************************************************************************
ok: [10.0.100.18]
ok: [10.0.100.19]
ok: [10.0.100.20]

TASK [Include system variables] **************************************************************************************************************
ok: [10.0.100.18]
ok: [10.0.100.19]
ok: [10.0.100.20]

TASK [Include OS-specific variables] *********************************************************************************************************
ok: [10.0.100.18]
ok: [10.0.100.19]
ok: [10.0.100.20]

TASK [Update apt cache] **********************************************************************************************************************
ok: [10.0.100.18]
ok: [10.0.100.19]
ok: [10.0.100.20]

TASK [Make sure the gnupg and apt-transport-https packages are present] **********************************************************************
ok: [10.0.100.18]
ok: [10.0.100.19]
ok: [10.0.100.20]

TASK [etcd : Make sure the unzip/tar packages are present] ***********************************************************************************
changed: [10.0.100.18]
changed: [10.0.100.20]
changed: [10.0.100.19]

TASK [etcd : Download "etcd" package] ********************************************************************************************************
changed: [10.0.100.18] => (item=https://github.com/etcd-io/etcd/releases/download/v3.5.17/etcd-v3.5.17-linux-amd64.tar.gz)
[WARNING]: Module remote_tmp /tmp/root/ansible did not exist and was created with a mode of 0700, this may cause issues when running as
another user. To avoid this, create the remote_tmp dir with the correct permissions manually
changed: [10.0.100.20] => (item=https://github.com/etcd-io/etcd/releases/download/v3.5.17/etcd-v3.5.17-linux-amd64.tar.gz)
changed: [10.0.100.19] => (item=https://github.com/etcd-io/etcd/releases/download/v3.5.17/etcd-v3.5.17-linux-amd64.tar.gz)

TASK [etcd : Extract "etcd" into /tmp] *******************************************************************************************************
changed: [10.0.100.18]
changed: [10.0.100.20]
changed: [10.0.100.19]

TASK [etcd : Copy "etcd" and "etcdctl" binary files to /usr/local/bin/] **********************************************************************
changed: [10.0.100.18] => (item=etcd)
changed: [10.0.100.19] => (item=etcd)
changed: [10.0.100.20] => (item=etcd)
changed: [10.0.100.18] => (item=etcdctl)
changed: [10.0.100.19] => (item=etcdctl)
changed: [10.0.100.20] => (item=etcdctl)

TASK [etcd : Add etcd user] ******************************************************************************************************************
changed: [10.0.100.18]
changed: [10.0.100.19]
changed: [10.0.100.20]

TASK [etcd : Create etcd conf directory] *****************************************************************************************************
changed: [10.0.100.18]
changed: [10.0.100.19]
changed: [10.0.100.20]

TASK [Fetch etcd TLS certificate, key and CA from the master node] ***************************************************************************
included: ../roles/tls_certificate/copy for 10.0.100.18, 10.0.100.19, 10.0.100.20

TASK [../roles/tls_certificate/copy : Fetch TLS certificate, key and CA from the master node into memory] ************************************
ok: [10.0.100.18 -> 10.0.100.13] => (item=/etc/tls/server.key)
ok: [10.0.100.18 -> 10.0.100.13] => (item=/etc/tls/server.crt)
ok: [10.0.100.18 -> 10.0.100.13] => (item=/etc/tls/ca.crt)

TASK [../roles/tls_certificate/copy : Create directory /etc/etcd/tls] ************************************************************************
changed: [10.0.100.18]
changed: [10.0.100.19]
changed: [10.0.100.20]

TASK [../roles/tls_certificate/copy : Copy PostgreSQL TLS certificate, key and CA to all nodes] **********************************************
changed: [10.0.100.18] => (item={'index': 1, 'path': '/etc/etcd/tls/server.crt', 'mode': '0644'})
changed: [10.0.100.19] => (item={'index': 1, 'path': '/etc/etcd/tls/server.crt', 'mode': '0644'})
changed: [10.0.100.20] => (item={'index': 1, 'path': '/etc/etcd/tls/server.crt', 'mode': '0644'})
changed: [10.0.100.18] => (item={'index': 2, 'path': '/etc/etcd/tls/ca.crt', 'mode': '0644'})
changed: [10.0.100.19] => (item={'index': 2, 'path': '/etc/etcd/tls/ca.crt', 'mode': '0644'})
changed: [10.0.100.20] => (item={'index': 2, 'path': '/etc/etcd/tls/ca.crt', 'mode': '0644'})
changed: [10.0.100.18] => (item={'index': 0, 'path': '/etc/etcd/tls/server.key', 'mode': '0400'})
changed: [10.0.100.19] => (item={'index': 0, 'path': '/etc/etcd/tls/server.key', 'mode': '0400'})
changed: [10.0.100.20] => (item={'index': 0, 'path': '/etc/etcd/tls/server.key', 'mode': '0400'})

TASK [etcd : Create etcd data directory] *****************************************************************************************************
changed: [10.0.100.18]
changed: [10.0.100.19]
changed: [10.0.100.20]

TASK [etcd : Generate conf file "/etc/etcd/etcd.conf"] ***************************************************************************************
changed: [10.0.100.18]
changed: [10.0.100.19]
changed: [10.0.100.20]

TASK [etcd : Copy systemd service file] ******************************************************************************************************
changed: [10.0.100.18]
changed: [10.0.100.19]
changed: [10.0.100.20]

TASK [etcd : Enable and start etcd service] **************************************************************************************************
changed: [10.0.100.18]
changed: [10.0.100.19]
changed: [10.0.100.20]

TASK [etcd : Wait for port 2379 to become open on the host] **********************************************************************************
ok: [10.0.100.18]
ok: [10.0.100.20]
ok: [10.0.100.19]

TASK [etcd : Wait until the etcd cluster is healthy] *****************************************************************************************
ok: [10.0.100.19]
ok: [10.0.100.18]
ok: [10.0.100.20]

TASK [etcd : cluster health] *****************************************************************************************************************
ok: [10.0.100.18] => {
    "msg": "https://10.0.100.18:2379 is healthy: successfully committed proposal: took = 34.829164ms\n"
}
ok: [10.0.100.19] => {
    "msg": "https://10.0.100.19:2379 is healthy: successfully committed proposal: took = 38.183032ms\n"
}
ok: [10.0.100.20] => {
    "msg": "https://10.0.100.20:2379 is healthy: successfully committed proposal: took = 39.511203ms\n"
}

PLAY [consul.yml | Configure Consul instances] ***********************************************************************************************
skipping: no hosts matched

PLAY [deploy_pgcluster.yml | Postgres Cluster Configuration] *********************************************************************************

TASK [Gathering Facts] ***********************************************************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]
ok: [10.0.100.13]

TASK [Include main variables] ****************************************************************************************************************
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [Include system variables] **************************************************************************************************************
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [Include OS-specific variables] *********************************************************************************************************
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [add-repository : Make sure the python3-debian package is present] **********************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]
ok: [10.0.100.13]

TASK [add-repository : Add repository] *******************************************************************************************************
changed: [10.0.100.14] => (item={'repo': 'deb https://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main', 'key': 'https://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc'})
changed: [10.0.100.12] => (item={'repo': 'deb https://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main', 'key': 'https://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc'})
changed: [10.0.100.13] => (item={'repo': 'deb https://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main', 'key': 'https://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc'})

TASK [add-repository : Update apt cache] *****************************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [packages : Update apt cache] ***********************************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [packages : Install system packages] ****************************************************************************************************
ok: [10.0.100.14] => (item=python3)
ok: [10.0.100.12] => (item=python3)
ok: [10.0.100.13] => (item=python3)
changed: [10.0.100.14] => (item=python3-dev)
changed: [10.0.100.12] => (item=python3-dev)
changed: [10.0.100.13] => (item=python3-dev)
changed: [10.0.100.14] => (item=python3-psycopg2)
ok: [10.0.100.14] => (item=python3-setuptools)
changed: [10.0.100.12] => (item=python3-psycopg2)
ok: [10.0.100.12] => (item=python3-setuptools)
changed: [10.0.100.13] => (item=python3-psycopg2)
ok: [10.0.100.13] => (item=python3-setuptools)
changed: [10.0.100.14] => (item=python3-pip)
ok: [10.0.100.14] => (item=curl)
ok: [10.0.100.14] => (item=less)
ok: [10.0.100.14] => (item=sudo)
ok: [10.0.100.14] => (item=vim)
ok: [10.0.100.14] => (item=gcc)
changed: [10.0.100.12] => (item=python3-pip)
changed: [10.0.100.13] => (item=python3-pip)
ok: [10.0.100.12] => (item=curl)
ok: [10.0.100.13] => (item=curl)
changed: [10.0.100.14] => (item=jq)
ok: [10.0.100.12] => (item=less)
ok: [10.0.100.14] => (item=iptables)
ok: [10.0.100.12] => (item=sudo)
ok: [10.0.100.13] => (item=less)
ok: [10.0.100.12] => (item=vim)
ok: [10.0.100.12] => (item=gcc)
ok: [10.0.100.13] => (item=sudo)
ok: [10.0.100.13] => (item=vim)
changed: [10.0.100.14] => (item=acl)
ok: [10.0.100.13] => (item=gcc)
changed: [10.0.100.12] => (item=jq)
ok: [10.0.100.12] => (item=iptables)
changed: [10.0.100.14] => (item=dnsutils)
changed: [10.0.100.14] => (item=moreutils)
changed: [10.0.100.13] => (item=jq)
changed: [10.0.100.12] => (item=acl)
ok: [10.0.100.13] => (item=iptables)
changed: [10.0.100.14] => (item=unzip)
ok: [10.0.100.14] => (item=tar)
ok: [10.0.100.14] => (item=zstd)
changed: [10.0.100.12] => (item=dnsutils)
changed: [10.0.100.13] => (item=acl)
changed: [10.0.100.12] => (item=moreutils)
changed: [10.0.100.13] => (item=dnsutils)
changed: [10.0.100.12] => (item=unzip)
ok: [10.0.100.12] => (item=tar)
ok: [10.0.100.12] => (item=zstd)
changed: [10.0.100.13] => (item=moreutils)
changed: [10.0.100.13] => (item=unzip)
ok: [10.0.100.13] => (item=tar)
ok: [10.0.100.13] => (item=zstd)

TASK [packages : PostgreSQL | ensure postgresql database-cluster manager package] ************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [packages : PostgreSQL | disable initializing of a default postgresql cluster] **********************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [packages : PostgreSQL | disable log rotation with logrotate for postgresql] ************************************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [packages : Install PostgreSQL packages] ************************************************************************************************
changed: [10.0.100.14] => (item=postgresql-17)
ok: [10.0.100.14] => (item=postgresql-client-17)
ok: [10.0.100.14] => (item=postgresql-contrib-17)
changed: [10.0.100.12] => (item=postgresql-17)
ok: [10.0.100.12] => (item=postgresql-client-17)
changed: [10.0.100.13] => (item=postgresql-17)
ok: [10.0.100.12] => (item=postgresql-contrib-17)
ok: [10.0.100.13] => (item=postgresql-client-17)
ok: [10.0.100.13] => (item=postgresql-contrib-17)
changed: [10.0.100.14] => (item=postgresql-server-dev-17)
changed: [10.0.100.12] => (item=postgresql-server-dev-17)
changed: [10.0.100.14] => (item=postgresql-17-dbgsym)
changed: [10.0.100.13] => (item=postgresql-server-dev-17)
changed: [10.0.100.12] => (item=postgresql-17-dbgsym)
changed: [10.0.100.13] => (item=postgresql-17-dbgsym)

TASK [sudo : Add user to /etc/sudoers.d/] ****************************************************************************************************
changed: [10.0.100.14] => (item={'name': 'postgres', 'nopasswd': 'yes', 'commands': 'ALL'})
changed: [10.0.100.13] => (item={'name': 'postgres', 'nopasswd': 'yes', 'commands': 'ALL'})
changed: [10.0.100.12] => (item={'name': 'postgres', 'nopasswd': 'yes', 'commands': 'ALL'})

TASK [swap : Ensure swap exists] *************************************************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.13]
ok: [10.0.100.12]

TASK [swap : Create swap file] ***************************************************************************************************************
changed: [10.0.100.12]
changed: [10.0.100.13]
changed: [10.0.100.14]

TASK [swap : Set permissions on swap file] ***************************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [swap : Make swap file if necessary] ****************************************************************************************************
changed: [10.0.100.12]
changed: [10.0.100.14]
changed: [10.0.100.13]

TASK [swap : Run swapon on the swap file] ****************************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [swap : Manage swap file entry in fstab] ************************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [sysctl : Build a sysctl_conf dynamic variable] *****************************************************************************************
ok: [10.0.100.13] => (item=balancers)
ok: [10.0.100.13] => (item=master)
ok: [10.0.100.14] => (item=balancers)
ok: [10.0.100.13] => (item=postgres_cluster)
ok: [10.0.100.14] => (item=postgres_cluster)
ok: [10.0.100.14] => (item=replica)
ok: [10.0.100.12] => (item=balancers)
ok: [10.0.100.12] => (item=postgres_cluster)
ok: [10.0.100.12] => (item=replica)

TASK [sysctl : Setting kernel parameters] ****************************************************************************************************
changed: [10.0.100.14] => (item={'name': 'net.ipv4.ip_nonlocal_bind', 'value': '1'})
changed: [10.0.100.12] => (item={'name': 'net.ipv4.ip_nonlocal_bind', 'value': '1'})
changed: [10.0.100.13] => (item={'name': 'net.ipv4.ip_nonlocal_bind', 'value': '1'})
changed: [10.0.100.14] => (item={'name': 'net.ipv4.ip_forward', 'value': '1'})
changed: [10.0.100.12] => (item={'name': 'net.ipv4.ip_forward', 'value': '1'})
changed: [10.0.100.13] => (item={'name': 'net.ipv4.ip_forward', 'value': '1'})
changed: [10.0.100.12] => (item={'name': 'net.ipv4.ip_local_port_range', 'value': '10000 65535'})
changed: [10.0.100.13] => (item={'name': 'net.ipv4.ip_local_port_range', 'value': '10000 65535'})
changed: [10.0.100.12] => (item={'name': 'net.core.netdev_max_backlog', 'value': '10000'})
changed: [10.0.100.12] => (item={'name': 'net.ipv4.tcp_max_syn_backlog', 'value': '8192'})
changed: [10.0.100.13] => (item={'name': 'net.core.netdev_max_backlog', 'value': '10000'})
changed: [10.0.100.12] => (item={'name': 'net.core.somaxconn', 'value': '65535'})
changed: [10.0.100.13] => (item={'name': 'net.ipv4.tcp_max_syn_backlog', 'value': '8192'})
changed: [10.0.100.14] => (item={'name': 'net.ipv4.ip_local_port_range', 'value': '10000 65535'})
changed: [10.0.100.12] => (item={'name': 'net.ipv4.tcp_tw_reuse', 'value': '1'})
changed: [10.0.100.14] => (item={'name': 'net.core.netdev_max_backlog', 'value': '10000'})
changed: [10.0.100.13] => (item={'name': 'net.core.somaxconn', 'value': '65535'})
changed: [10.0.100.14] => (item={'name': 'net.ipv4.tcp_max_syn_backlog', 'value': '8192'})
changed: [10.0.100.12] => (item={'name': 'vm.overcommit_memory', 'value': '2'})
changed: [10.0.100.13] => (item={'name': 'net.ipv4.tcp_tw_reuse', 'value': '1'})
changed: [10.0.100.14] => (item={'name': 'net.core.somaxconn', 'value': '65535'})
changed: [10.0.100.12] => (item={'name': 'vm.swappiness', 'value': '1'})
changed: [10.0.100.14] => (item={'name': 'net.ipv4.tcp_tw_reuse', 'value': '1'})
changed: [10.0.100.13] => (item={'name': 'vm.overcommit_memory', 'value': '2'})
changed: [10.0.100.12] => (item={'name': 'vm.min_free_kbytes', 'value': '102400'})
changed: [10.0.100.14] => (item={'name': 'vm.overcommit_memory', 'value': '2'})
changed: [10.0.100.13] => (item={'name': 'vm.swappiness', 'value': '1'})
changed: [10.0.100.14] => (item={'name': 'vm.swappiness', 'value': '1'})
changed: [10.0.100.12] => (item={'name': 'vm.dirty_expire_centisecs', 'value': '1000'})
changed: [10.0.100.13] => (item={'name': 'vm.min_free_kbytes', 'value': '102400'})
changed: [10.0.100.14] => (item={'name': 'vm.min_free_kbytes', 'value': '102400'})
changed: [10.0.100.12] => (item={'name': 'vm.dirty_background_bytes', 'value': '67108864'})
changed: [10.0.100.14] => (item={'name': 'vm.dirty_expire_centisecs', 'value': '1000'})
changed: [10.0.100.13] => (item={'name': 'vm.dirty_expire_centisecs', 'value': '1000'})
changed: [10.0.100.12] => (item={'name': 'vm.dirty_bytes', 'value': '536870912'})
changed: [10.0.100.14] => (item={'name': 'vm.dirty_background_bytes', 'value': '67108864'})
changed: [10.0.100.13] => (item={'name': 'vm.dirty_background_bytes', 'value': '67108864'})
changed: [10.0.100.14] => (item={'name': 'vm.dirty_bytes', 'value': '536870912'})
changed: [10.0.100.12] => (item={'name': 'vm.zone_reclaim_mode', 'value': '0'})
changed: [10.0.100.14] => (item={'name': 'vm.zone_reclaim_mode', 'value': '0'})
changed: [10.0.100.13] => (item={'name': 'vm.dirty_bytes', 'value': '536870912'})
changed: [10.0.100.12] => (item={'name': 'kernel.numa_balancing', 'value': '0'})
changed: [10.0.100.14] => (item={'name': 'kernel.numa_balancing', 'value': '0'})
changed: [10.0.100.13] => (item={'name': 'vm.zone_reclaim_mode', 'value': '0'})
changed: [10.0.100.12] => (item={'name': 'kernel.sched_autogroup_enabled', 'value': '0'})
changed: [10.0.100.14] => (item={'name': 'kernel.sched_autogroup_enabled', 'value': '0'})
changed: [10.0.100.13] => (item={'name': 'kernel.numa_balancing', 'value': '0'})
changed: [10.0.100.13] => (item={'name': 'kernel.sched_autogroup_enabled', 'value': '0'})

TASK [transparent_huge_pages : Create systemd service "disable-transparent-huge-pages.service"] **********************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

RUNNING HANDLER [transparent_huge_pages : Start disable-transparent-huge-pages service] ******************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [pam_limits : Linux PAM limits | add or modify nofile limits] ***************************************************************************
changed: [10.0.100.14] => (item={'limit_type': 'soft', 'limit_item': 'nofile', 'value': 65536})
changed: [10.0.100.12] => (item={'limit_type': 'soft', 'limit_item': 'nofile', 'value': 65536})
changed: [10.0.100.13] => (item={'limit_type': 'soft', 'limit_item': 'nofile', 'value': 65536})
changed: [10.0.100.14] => (item={'limit_type': 'hard', 'limit_item': 'nofile', 'value': 200000})
changed: [10.0.100.12] => (item={'limit_type': 'hard', 'limit_item': 'nofile', 'value': 200000})
changed: [10.0.100.13] => (item={'limit_type': 'hard', 'limit_item': 'nofile', 'value': 200000})

TASK [locales : Generate locales] ************************************************************************************************************
ok: [10.0.100.14] => (item={'language_country': 'en_US', 'encoding': 'UTF-8'})
ok: [10.0.100.12] => (item={'language_country': 'en_US', 'encoding': 'UTF-8'})
ok: [10.0.100.13] => (item={'language_country': 'en_US', 'encoding': 'UTF-8'})

TASK [locales : Set locale "en_US.utf-8" into /etc/default/locale] ***************************************************************************
changed: [10.0.100.14] => (item=LANG=en_US.utf-8)
changed: [10.0.100.13] => (item=LANG=en_US.utf-8)
changed: [10.0.100.12] => (item=LANG=en_US.utf-8)
changed: [10.0.100.14] => (item=LANGUAGE=en_US.utf-8)
changed: [10.0.100.13] => (item=LANGUAGE=en_US.utf-8)
changed: [10.0.100.12] => (item=LANGUAGE=en_US.utf-8)
changed: [10.0.100.14] => (item=LC_ALL=en_US.utf-8)
changed: [10.0.100.12] => (item=LC_ALL=en_US.utf-8)
changed: [10.0.100.13] => (item=LC_ALL=en_US.utf-8)

PLAY [balancers.yml | Configure HAProxy load balancers] **************************************************************************************

TASK [Gathering Facts] ***********************************************************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.13]
ok: [10.0.100.12]

TASK [Include main variables] ****************************************************************************************************************
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [Include system variables] **************************************************************************************************************
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [Include OS-specific variables] *********************************************************************************************************
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [Update apt cache] **********************************************************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]
ok: [10.0.100.13]

TASK [Make sure the gnupg and apt-transport-https packages are present] **********************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]
ok: [10.0.100.13]

TASK [hostname : Change hostname] ************************************************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.13]
ok: [10.0.100.12]

TASK [hostname : Change hostname in /etc/hosts] **********************************************************************************************
ok: [10.0.100.14] => (item=None)
ok: [10.0.100.14]
ok: [10.0.100.13] => (item=None)
ok: [10.0.100.13]
ok: [10.0.100.12] => (item=None)
ok: [10.0.100.12]

TASK [sysctl : Build a sysctl_conf dynamic variable] *****************************************************************************************
ok: [10.0.100.13] => (item=balancers)
ok: [10.0.100.12] => (item=balancers)
ok: [10.0.100.13] => (item=master)
ok: [10.0.100.12] => (item=postgres_cluster)
ok: [10.0.100.14] => (item=balancers)
ok: [10.0.100.13] => (item=postgres_cluster)
ok: [10.0.100.12] => (item=replica)
ok: [10.0.100.14] => (item=postgres_cluster)
ok: [10.0.100.14] => (item=replica)

TASK [sysctl : Setting kernel parameters] ****************************************************************************************************
ok: [10.0.100.14] => (item={'name': 'net.ipv4.ip_nonlocal_bind', 'value': '1'})
ok: [10.0.100.13] => (item={'name': 'net.ipv4.ip_nonlocal_bind', 'value': '1'})
ok: [10.0.100.12] => (item={'name': 'net.ipv4.ip_nonlocal_bind', 'value': '1'})
ok: [10.0.100.14] => (item={'name': 'net.ipv4.ip_forward', 'value': '1'})
ok: [10.0.100.13] => (item={'name': 'net.ipv4.ip_forward', 'value': '1'})
ok: [10.0.100.12] => (item={'name': 'net.ipv4.ip_forward', 'value': '1'})
ok: [10.0.100.14] => (item={'name': 'net.ipv4.ip_local_port_range', 'value': '10000 65535'})
ok: [10.0.100.13] => (item={'name': 'net.ipv4.ip_local_port_range', 'value': '10000 65535'})
ok: [10.0.100.12] => (item={'name': 'net.ipv4.ip_local_port_range', 'value': '10000 65535'})
ok: [10.0.100.14] => (item={'name': 'net.core.netdev_max_backlog', 'value': '10000'})
ok: [10.0.100.14] => (item={'name': 'net.ipv4.tcp_max_syn_backlog', 'value': '8192'})
ok: [10.0.100.13] => (item={'name': 'net.core.netdev_max_backlog', 'value': '10000'})
ok: [10.0.100.12] => (item={'name': 'net.core.netdev_max_backlog', 'value': '10000'})
ok: [10.0.100.14] => (item={'name': 'net.core.somaxconn', 'value': '65535'})
ok: [10.0.100.13] => (item={'name': 'net.ipv4.tcp_max_syn_backlog', 'value': '8192'})
ok: [10.0.100.12] => (item={'name': 'net.ipv4.tcp_max_syn_backlog', 'value': '8192'})
ok: [10.0.100.14] => (item={'name': 'net.ipv4.tcp_tw_reuse', 'value': '1'})
ok: [10.0.100.12] => (item={'name': 'net.core.somaxconn', 'value': '65535'})
ok: [10.0.100.13] => (item={'name': 'net.core.somaxconn', 'value': '65535'})
ok: [10.0.100.14] => (item={'name': 'vm.overcommit_memory', 'value': '2'})
ok: [10.0.100.14] => (item={'name': 'vm.swappiness', 'value': '1'})
ok: [10.0.100.12] => (item={'name': 'net.ipv4.tcp_tw_reuse', 'value': '1'})
ok: [10.0.100.13] => (item={'name': 'net.ipv4.tcp_tw_reuse', 'value': '1'})
ok: [10.0.100.14] => (item={'name': 'vm.min_free_kbytes', 'value': '102400'})
ok: [10.0.100.12] => (item={'name': 'vm.overcommit_memory', 'value': '2'})
ok: [10.0.100.13] => (item={'name': 'vm.overcommit_memory', 'value': '2'})
ok: [10.0.100.12] => (item={'name': 'vm.swappiness', 'value': '1'})
ok: [10.0.100.13] => (item={'name': 'vm.swappiness', 'value': '1'})
ok: [10.0.100.12] => (item={'name': 'vm.min_free_kbytes', 'value': '102400'})
ok: [10.0.100.13] => (item={'name': 'vm.min_free_kbytes', 'value': '102400'})
ok: [10.0.100.12] => (item={'name': 'vm.dirty_expire_centisecs', 'value': '1000'})
ok: [10.0.100.13] => (item={'name': 'vm.dirty_expire_centisecs', 'value': '1000'})
ok: [10.0.100.12] => (item={'name': 'vm.dirty_background_bytes', 'value': '67108864'})
ok: [10.0.100.13] => (item={'name': 'vm.dirty_background_bytes', 'value': '67108864'})
ok: [10.0.100.14] => (item={'name': 'vm.dirty_expire_centisecs', 'value': '1000'})
ok: [10.0.100.12] => (item={'name': 'vm.dirty_bytes', 'value': '536870912'})
ok: [10.0.100.13] => (item={'name': 'vm.dirty_bytes', 'value': '536870912'})
ok: [10.0.100.14] => (item={'name': 'vm.dirty_background_bytes', 'value': '67108864'})
ok: [10.0.100.12] => (item={'name': 'vm.zone_reclaim_mode', 'value': '0'})
ok: [10.0.100.14] => (item={'name': 'vm.dirty_bytes', 'value': '536870912'})
ok: [10.0.100.13] => (item={'name': 'vm.zone_reclaim_mode', 'value': '0'})
ok: [10.0.100.14] => (item={'name': 'vm.zone_reclaim_mode', 'value': '0'})
ok: [10.0.100.12] => (item={'name': 'kernel.numa_balancing', 'value': '0'})
ok: [10.0.100.13] => (item={'name': 'kernel.numa_balancing', 'value': '0'})
ok: [10.0.100.14] => (item={'name': 'kernel.numa_balancing', 'value': '0'})
ok: [10.0.100.12] => (item={'name': 'kernel.sched_autogroup_enabled', 'value': '0'})
ok: [10.0.100.13] => (item={'name': 'kernel.sched_autogroup_enabled', 'value': '0'})
ok: [10.0.100.14] => (item={'name': 'kernel.sched_autogroup_enabled', 'value': '0'})

TASK [haproxy : Install HAProxy package] *****************************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [haproxy : Make sure the kernel parameter "net.ipv4.ip_nonlocal_bind" are enabled] ******************************************************
ok: [10.0.100.14]
ok: [10.0.100.13]
ok: [10.0.100.12]

TASK [haproxy : Add haproxy group] ***********************************************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]
ok: [10.0.100.13]

TASK [haproxy : Add haproxy user] ************************************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [haproxy : Create directories] **********************************************************************************************************
changed: [10.0.100.14] => (item=/etc/haproxy)
changed: [10.0.100.13] => (item=/etc/haproxy)
changed: [10.0.100.12] => (item=/etc/haproxy)
ok: [10.0.100.14] => (item=/run/haproxy)
ok: [10.0.100.13] => (item=/run/haproxy)
ok: [10.0.100.12] => (item=/run/haproxy)
changed: [10.0.100.14] => (item=/var/lib/haproxy/dev)
changed: [10.0.100.12] => (item=/var/lib/haproxy/dev)
changed: [10.0.100.13] => (item=/var/lib/haproxy/dev)

TASK [haproxy : Generate conf file "/etc/haproxy/haproxy.cfg"] *******************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [haproxy : Generate systemd service file "/etc/systemd/system/haproxy.service"] *********************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [confd : Download and copy "confd" binary file to /usr/local/bin/] **********************************************************************
changed: [10.0.100.14] => (item=https://github.com/kelseyhightower/confd/releases/download/v0.16.0/confd-0.16.0-linux-amd64)
changed: [10.0.100.12] => (item=https://github.com/kelseyhightower/confd/releases/download/v0.16.0/confd-0.16.0-linux-amd64)
changed: [10.0.100.13] => (item=https://github.com/kelseyhightower/confd/releases/download/v0.16.0/confd-0.16.0-linux-amd64)

TASK [confd : Create conf directories] *******************************************************************************************************
changed: [10.0.100.14] => (item=/etc/confd/conf.d)
changed: [10.0.100.13] => (item=/etc/confd/conf.d)
changed: [10.0.100.12] => (item=/etc/confd/conf.d)
changed: [10.0.100.14] => (item=/etc/confd/templates)
changed: [10.0.100.13] => (item=/etc/confd/templates)
changed: [10.0.100.12] => (item=/etc/confd/templates)

TASK [confd : Generate conf file "/etc/confd/confd.toml"] ************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [confd : Generate conf file "/etc/confd/conf.d/haproxy.toml"] ***************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [confd : Generate template "/etc/confd/templates/haproxy.tmpl"] *************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [confd : Copy systemd service file] *****************************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

RUNNING HANDLER [haproxy : Restart haproxy service] ******************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

RUNNING HANDLER [haproxy : Check HAProxy is started and accepting connections] ***************************************************************
ok: [10.0.100.14]
ok: [10.0.100.13]
ok: [10.0.100.12]

RUNNING HANDLER [confd : Restart confd service] **********************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

PLAY [deploy_pgcluster.yml | Install and configure pgBackRest] *******************************************************************************

TASK [Gathering Facts] ***********************************************************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]
ok: [10.0.100.13]

TASK [Include main variables] ****************************************************************************************************************
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [Include OS-specific variables] *********************************************************************************************************
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

PLAY [deploy_pgcluster.yml | PostgreSQL Cluster Deployment] **********************************************************************************

TASK [Gathering Facts] ***********************************************************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]
ok: [10.0.100.13]

TASK [Include main variables] ****************************************************************************************************************
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [Include system variables] **************************************************************************************************************
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [Include OS-specific variables] *********************************************************************************************************
ok: [10.0.100.13]
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [tls_certificate/copy : Fetch TLS certificate, key and CA from the master node into memory] *********************************************
ok: [10.0.100.13] => (item=/etc/tls/server.key)
ok: [10.0.100.13] => (item=/etc/tls/server.crt)
ok: [10.0.100.13] => (item=/etc/tls/ca.crt)

TASK [tls_certificate/copy : Create directory /etc/tls] **************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [tls_certificate/copy : Copy PostgreSQL TLS certificate, key and CA to all nodes] *******************************************************
changed: [10.0.100.14] => (item={'index': 1, 'path': '/etc/tls/server.crt', 'mode': '0644'})
changed: [10.0.100.13] => (item={'index': 1, 'path': '/etc/tls/server.crt', 'mode': '0644'})
changed: [10.0.100.12] => (item={'index': 1, 'path': '/etc/tls/server.crt', 'mode': '0644'})
changed: [10.0.100.14] => (item={'index': 2, 'path': '/etc/tls/ca.crt', 'mode': '0644'})
changed: [10.0.100.13] => (item={'index': 2, 'path': '/etc/tls/ca.crt', 'mode': '0644'})
changed: [10.0.100.12] => (item={'index': 2, 'path': '/etc/tls/ca.crt', 'mode': '0644'})
changed: [10.0.100.14] => (item={'index': 0, 'path': '/etc/tls/server.key', 'mode': '0400'})
changed: [10.0.100.13] => (item={'index': 0, 'path': '/etc/tls/server.key', 'mode': '0400'})
changed: [10.0.100.12] => (item={'index': 0, 'path': '/etc/tls/server.key', 'mode': '0400'})

TASK [pgbouncer : Install pgbouncer package] *************************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [pgbouncer : Ensure config directory "/etc/pgbouncer" exist] ****************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [pgbouncer : Ensure log directory "/var/log/pgbouncer" exist] ***************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [pgbouncer : Check if pgbouncer systemd service file exists] ****************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.13]
ok: [10.0.100.12]

TASK [pgbouncer : Stop and disable standard init script] *************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [pgbouncer : Configure pgbouncer systemd service file] **********************************************************************************
changed: [10.0.100.14] => (item=pgbouncer)
changed: [10.0.100.13] => (item=pgbouncer)
changed: [10.0.100.12] => (item=pgbouncer)

TASK [pgbouncer : Ensure pgbouncer service is enabled] ***************************************************************************************
changed: [10.0.100.14] => (item=pgbouncer)
changed: [10.0.100.12] => (item=pgbouncer)
changed: [10.0.100.13] => (item=pgbouncer)

TASK [pgbouncer : Enable log rotation with logrotate] ****************************************************************************************
changed: [10.0.100.14] => (item=pgbouncer)
changed: [10.0.100.12] => (item=pgbouncer)
changed: [10.0.100.13] => (item=pgbouncer)

TASK [pgbouncer : Configure pgbouncer.ini] ***************************************************************************************************
changed: [10.0.100.14] => (item=pgbouncer)
changed: [10.0.100.13] => (item=pgbouncer)
changed: [10.0.100.12] => (item=pgbouncer)

TASK [pgpass : Configure a password file (/var/lib/postgresql/.pgpass)] **********************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

RUNNING HANDLER [Restart pgbouncer service] **************************************************************************************************
changed: [10.0.100.14] => (item=pgbouncer)
changed: [10.0.100.12] => (item=pgbouncer)
changed: [10.0.100.13] => (item=pgbouncer)

RUNNING HANDLER [Wait for port "6432" to become open on the host] ****************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.13]
ok: [10.0.100.12]

RUNNING HANDLER [Reload pgbouncer service] ***************************************************************************************************
changed: [10.0.100.14] => (item=pgbouncer)
changed: [10.0.100.13] => (item=pgbouncer)
changed: [10.0.100.12] => (item=pgbouncer)

TASK [patroni : Install patroni package] *****************************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [patroni : Create conf directory] *******************************************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [patroni : Generate conf file "/etc/patroni/patroni.yml"] *******************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [patroni : Copy systemd service file "/etc/systemd/system/patroni.service"] *************************************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [patroni : Prepare PostgreSQL | make sure the postgresql log directory "/var/log/postgresql" exists] ************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [patroni : Prepare PostgreSQL | make sure PostgreSQL data directory "/var/lib/postgresql/17/main" exists] *******************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [patroni : Prepare PostgreSQL | make sure PostgreSQL config directory exists] ***********************************************************
ok: [10.0.100.14]
ok: [10.0.100.13]
ok: [10.0.100.12]

TASK [patroni : Prepare PostgreSQL | make sure PostgreSQL config files exists] ***************************************************************
ok: [10.0.100.14]
ok: [10.0.100.13]
ok: [10.0.100.12]

TASK [patroni : Prepare PostgreSQL | generate default PostgreSQL config files] ***************************************************************
[WARNING]: Module remote_tmp /tmp/postgres/ansible did not exist and was created with a mode of 0700, this may cause issues when running as
another user. To avoid this, create the remote_tmp dir with the correct permissions manually
changed: [10.0.100.12]
changed: [10.0.100.14]
changed: [10.0.100.13]

TASK [patroni : Prepare PostgreSQL | make sure the data directory "/var/lib/postgresql/17/main" is empty] ************************************
changed: [10.0.100.14] => (item=absent)
changed: [10.0.100.13] => (item=absent)
changed: [10.0.100.12] => (item=absent)
changed: [10.0.100.14] => (item=directory)
changed: [10.0.100.13] => (item=directory)
changed: [10.0.100.12] => (item=directory)

TASK [patroni : Start patroni service on the Master server] **********************************************************************************
changed: [10.0.100.13]

TASK [patroni : Wait for port 8008 to become open on the host] *******************************************************************************
ok: [10.0.100.13]

TASK [patroni : Check PostgreSQL is started and accepting connections] ***********************************************************************
ok: [10.0.100.13]

TASK [patroni : Wait for the cluster to initialize (master is the leader with the lock)] *****************************************************
ok: [10.0.100.13]

TASK [patroni : Prepare PostgreSQL | generate pg_hba.conf] ***********************************************************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [patroni : Prepare PostgreSQL | reload for apply the pg_hba.conf] ***********************************************************************
ok: [10.0.100.14]
changed: [10.0.100.13]
ok: [10.0.100.12]

TASK [patroni : Start patroni service on Replica servers] ************************************************************************************
changed: [10.0.100.12]
changed: [10.0.100.14]

TASK [patroni : Wait for port 8008 to become open on the host] *******************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [patroni : Check that the patroni is healthy on the replica server] *********************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]

TASK [patroni : Turning off postgresql autostart from config "start.conf" (will be managed by patroni)] **************************************
changed: [10.0.100.14]
changed: [10.0.100.12]
changed: [10.0.100.13]

TASK [patroni : Disable "postgresql@17-main" service] ****************************************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]
ok: [10.0.100.13]

TASK [patroni : Add PATRONICTL_CONFIG_FILE environment variable into /etc/environment] *******************************************************
changed: [10.0.100.14]
changed: [10.0.100.13]
changed: [10.0.100.12]

TASK [pgbouncer/config : Ensure config directory "/etc/pgbouncer" exist] *********************************************************************
ok: [10.0.100.14]
ok: [10.0.100.12]
ok: [10.0.100.13]

TASK [pgbouncer/config : Update pgbouncer.ini] ***********************************************************************************************
ok: [10.0.100.14] => (item=pgbouncer)
ok: [10.0.100.13] => (item=pgbouncer)
ok: [10.0.100.12] => (item=pgbouncer)

TASK [pgbouncer/config : Check if 'user_search' function exists] *****************************************************************************
ok: [10.0.100.13]

TASK [pgbouncer/config : Create 'user_search' function for pgbouncer 'auth_query' option] ****************************************************
fatal: [10.0.100.13]: FAILED! => {"changed": true, "cmd": ["/usr/lib/postgresql/17/bin/psql", "-p", "5432", "-U", "postgres", "-d", "postgres", "-tAXc", "CREATE FUNCTION user_search(uname TEXT) RETURNS TABLE (usename name, passwd text) AS $$ SELECT usename, passwd FROM pg_shadow WHERE usename=$1; $$ LANGUAGE sql SECURITY DEFINER; REVOKE ALL ON FUNCTION user_search(uname TEXT) FROM public; GRANT EXECUTE ON FUNCTION user_search(uname TEXT) TO pgbouncer"], "delta": "0:00:00.020620", "end": "2025-01-26 13:53:33.757649", "msg": "non-zero return code", "rc": 1, "start": "2025-01-26 13:53:33.737029", "stderr": "ERROR:  role \"pgbouncer\" does not exist", "stderr_lines": ["ERROR:  role \"pgbouncer\" does not exist"], "stdout": "CREATE FUNCTION\nREVOKE", "stdout_lines": ["CREATE FUNCTION", "REVOKE"]}

PLAY RECAP ***********************************************************************************************************************************
10.0.100.12                : ok=110  changed=60   unreachable=0    failed=0    skipped=429  rescued=0    ignored=0   
10.0.100.13                : ok=113  changed=61   unreachable=0    failed=1    skipped=449  rescued=0    ignored=0   
10.0.100.14                : ok=110  changed=60   unreachable=0    failed=0    skipped=429  rescued=0    ignored=0   
10.0.100.18                : ok=43   changed=22   unreachable=0    failed=0    skipped=88   rescued=0    ignored=0   
10.0.100.19                : ok=32   changed=14   unreachable=0    failed=0    skipped=61   rescued=0    ignored=0   
10.0.100.20                : ok=32   changed=14   unreachable=0    failed=0    skipped=61   rescued=0    ignored=0   
localhost                  : ok=2    changed=0    unreachable=0    failed=0    skipped=238  rescued=0    ignored=0

vitabaks self-assigned this Oct 23, 2024

vitabaks added the priority: low label Jan 11, 2025

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

pgbouncer user doesn't exist error when trying to deploy from command line #795

pgbouncer user doesn't exist error when trying to deploy from command line #795

biglolka commented Oct 22, 2024 •

edited by vitabaks

Loading

vitabaks commented Oct 22, 2024

biglolka commented Oct 22, 2024 •

edited

Loading

vitabaks commented Oct 22, 2024

biglolka commented Oct 23, 2024

vitabaks commented Oct 23, 2024 •

edited

Loading

biglolka commented Oct 24, 2024 •

edited

Loading

mainframe commented Nov 20, 2024

vitabaks commented Nov 21, 2024

mainframe commented Nov 21, 2024

vitabaks commented Nov 21, 2024

mainframe commented Nov 21, 2024

chobostar commented Nov 28, 2024 •

edited

Loading

vitabaks commented Nov 29, 2024

set1991 commented Jan 26, 2025

vitabaks commented Jan 26, 2025

set1991 commented Jan 26, 2025 •

edited by vitabaks

Loading

vitabaks commented Jan 26, 2025

set1991 commented Jan 26, 2025 •

edited by vitabaks

Loading

pgbouncer user doesn't exist error when trying to deploy from command line #795

pgbouncer user doesn't exist error when trying to deploy from command line #795

Comments

biglolka commented Oct 22, 2024 • edited by vitabaks Loading

vitabaks commented Oct 22, 2024

biglolka commented Oct 22, 2024 • edited Loading

vitabaks commented Oct 22, 2024

biglolka commented Oct 23, 2024

vitabaks commented Oct 23, 2024 • edited Loading

biglolka commented Oct 24, 2024 • edited Loading

mainframe commented Nov 20, 2024

vitabaks commented Nov 21, 2024

mainframe commented Nov 21, 2024

vitabaks commented Nov 21, 2024

mainframe commented Nov 21, 2024

chobostar commented Nov 28, 2024 • edited Loading

vitabaks commented Nov 29, 2024

set1991 commented Jan 26, 2025

vitabaks commented Jan 26, 2025

set1991 commented Jan 26, 2025 • edited by vitabaks Loading

vitabaks commented Jan 26, 2025

set1991 commented Jan 26, 2025 • edited by vitabaks Loading

biglolka commented Oct 22, 2024 •

edited by vitabaks

Loading

biglolka commented Oct 22, 2024 •

edited

Loading

vitabaks commented Oct 23, 2024 •

edited

Loading

biglolka commented Oct 24, 2024 •

edited

Loading

chobostar commented Nov 28, 2024 •

edited

Loading

set1991 commented Jan 26, 2025 •

edited by vitabaks

Loading

set1991 commented Jan 26, 2025 •

edited by vitabaks

Loading