Pgbackrest connection to Patroni cluster with HAProxy (VIP)

[Farik013]

Please provide the following information when submitting an issue (feature requests or general comments can skip this):

1. pgBackRest version: 2.50

2. PostgreSQL version:
   PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit

3. Operating system/version - if you have more than one server (for example, a database server, a repository host server, one or more standbys), please specify each:
   Oracle Linux Server 8.9

4. Did you install pgBackRest from source or from a package?
   package

5. Please attach the following as applicable:

   • pgbackrest.conf file(s)

 [global]
#repo1-path=/var/lib/pgbackrest
repo1-host-user=postgres
repo1-host-type=ssh
log-level-console=debug
log-level-file=detail
compress=n
start-fast=y
stop-auto=y

[test_db]
pg1-host=10.11.50.73
pg1-host-user=postgres
pg1-path=/opt/postgres/data/
pg1-port=5432
repo1-retention-full=2
repo1-path=/var/lib/pgbackrest/test_server
backup-standby=n
archive-async=y
process-max=2
compress=n

[cpg_test]
repo1-path=/var/lib/pgbackrest/cpg_test
pg1-host=10.10.50.90
pg1-host-user=pgbackrest
pg1-path=/opt/postgres/data/
pg1-port=5000
pg2-host=10.10.50.90
pg2-host-user=pgbackrest
pg2-path=/opt/postgres/data/
pg2-port=5001
backup-standby=y
archive-async=y
process-max=2
repo1-retention-full=2

- `postgresql.conf` settings applicable to pgBackRest (`archive_command`, `archive_mode`, `listen_addresses`, `max_wal_senders`, `wal_level`, `port`)

parameters:
    archive_command: pgbackrest --stanza=testdb archive-push %p
    archive_mode: true
    autovacuum_analyze_scale_factor: 0.05
    autovacuum_vacuum_scale_factor: 0.05
    checkpoint_completion_target: 0.9
    checkpoint_timeout: 15 min
    effective_cache_size: 11 GB
    effective_io_concurrency: 100
    huge_pages: false
    maintenance_work_mem: 320 MB
    max_connections: 1000;
    max_parallel_maintenance_workers: 4
    max_parallel_workers: 8
    max_parallel_workers_per_gather: 4
    max_wal_senders: 10
    max_wal_size: 1024 MB
    max_worker_processes: 8
    min_wal_size: 512 MB
    parallel_leader_participation: true
    pg_partman_bgw.dbname: cpg_order, cpg_finop_management
    pg_partman_bgw.interval: 3600
    random_page_cost: 1.25
    shared_buffers: 4096 MB
    shared_preload_libraries: pg_partman_bgw
    work_mem: 32 MB

- errors in the postgresql log file before or during the time you experienced the issue

 WARN: unable to check pg1: [DbConnectError] raised from remote-0 ssh protocol on '10.10.50.90': unable to connect to 'dbname='postgres' port=5000': connection to server on socket "/var/run/postgresql/.s.PGSQL.5000" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
WARN: unable to check pg2: [DbConnectError] raised from remote-0 ssh protocol on '10.10.50.90': unable to connect to 'dbname='postgres' port=5001': connection to server on socket "/var/run/postgresql/.s.PGSQL.5001" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
ERROR: [056]: unable to find primary cluster - cannot proceed
       HINT: are all available clusters in recovery?`

    - log file in `/var/log/pgbackrest` for the commands run (e.g. `/var/log/pgbackrest/mystanza_backup.log`)
`-------------------PROCESS START-------------------
2024-03-18 08:41:49.679 P00   INFO: stanza-create command begin 2.50: --exec-id=1598083-c6b0e543 --log-level-console=info --log-level-file=detail --pg1-host=10.10.50.90 --pg2-host=10.10.50.90 --pg1-host-user=pgbackrest --pg2-host-user=pgbackrest --pg1-path=/opt/postgres/data/ --pg2-path=/opt/postgres/data/ --pg1-port=5000 --pg2-port=5001 --repo1-host-type=ssh --repo1-path=/var/lib/pgbackrest/cpg_test --stanza=cpg_test
2024-03-18 08:41:50.055 P00   WARN: unable to check pg1: [OptionRequiredError] remote-0 process on '10.10.50.90' terminated unexpectedly [37]: ERROR: [037]: stanza-create command requires option: pg2-path
                                           HINT: does this stanza exist?
2024-03-18 08:41:50.292 P00   WARN: unable to check pg2: [OptionRequiredError] remote-0 process on '10.10.50.90' terminated unexpectedly [37]: ERROR: [037]: stanza-create command requires option: pg2-path
                                           HINT: does this stanza exist?
2024-03-18 08:41:50.292 P00  ERROR: [056]: unable to find primary cluster - cannot proceed
                                    HINT: are all available clusters in recovery?
2024-03-18 08:41:50.292 P00   INFO: stanza-create command end: aborted with exception [056]

-------------------PROCESS START-------------------
2024-03-18 08:43:24.648 P00   INFO: stanza-create command begin 2.50: --exec-id=1598299-79fdea8e --log-level-console=info --log-level-file=detail --pg1-host=10.10.50.90 --pg2-host=10.10.50.90 --pg1-host-user=pgbackrest --pg2-host-user=pgbackrest --pg1-path=/opt/postgres/data/ --pg2-path=/opt/postgres/data/ --pg1-port=5000 --pg2-port=5001 --repo1-host-type=ssh --repo1-path=/var/lib/pgbackrest/cpg_test --stanza=cpg_test
2024-03-18 08:43:25.016 P00   WARN: unable to check pg1: [DbConnectError] raised from remote-0 ssh protocol on '10.10.50.90': unable to connect to 'dbname='postgres' port=5000': connection to server on socket "/var/run/postgresql/.s.PGSQL.5000" failed: No such file or directory
                                        Is the server running locally and accepting connections on that socket?
2024-03-18 08:43:25.256 P00   WARN: unable to check pg2: [DbConnectError] raised from remote-0 ssh protocol on '10.10.50.90': unable to connect to 'dbname='postgres' port=5001': connection to server on socket "/var/run/postgresql/.s.PGSQL.5001" failed: No such file or directory
                                        Is the server running locally and accepting connections on that socket?
2024-03-18 08:43:25.256 P00  ERROR: [056]: unable to find primary cluster - cannot proceed
                                    HINT: are all available clusters in recovery?
2024-03-18 08:43:25.256 P00   INFO: stanza-create command end: aborted with exception [056]

-------------------PROCESS START-------------------
2024-03-18 08:48:16.410 P00   INFO: stanza-create command begin 2.50: --exec-id=1609201-6b589f0a --log-level-console=info --log-level-file=detail --pg1-host=10.10.50.90 --pg2-host=10.10.50.90 --pg1-host-user=pgbackrest --pg2-host-user=pgbackrest --pg1-path=/opt/postgres/data/ --pg2-path=/opt/postgres/data/ --pg1-port=5000 --pg2-port=5001 --repo1-host-type=ssh --repo1-path=/var/lib/pgbackrest/cpg_test --stanza=cpg_test
2024-03-18 08:48:16.781 P00   WARN: unable to check pg1: [DbConnectError] raised from remote-0 ssh protocol on '10.10.50.90': unable to connect to 'dbname='postgres' port=5000': connection to server on socket "/var/run/postgresql/.s.PGSQL.5000" failed: No such file or directory
                                        Is the server running locally and accepting connections on that socket?
2024-03-18 08:48:17.024 P00   WARN: unable to check pg2: [DbConnectError] raised from remote-0 ssh protocol on '10.10.50.90': unable to connect to 'dbname='postgres' port=5001': connection to server on socket "/var/run/postgresql/.s.PGSQL.5001" failed: No such file or directory
                                        Is the server running locally and accepting connections on that socket?
2024-03-18 08:48:17.024 P00  ERROR: [056]: unable to find primary cluster - cannot proceed
                                    HINT: are all available clusters in recovery?
2024-03-18 08:48:17.024 P00   INFO: stanza-create command end: aborted with exception [056]

7. Describe the issue:

I have Patroni cluster with:

1 VIP
3 nodes HAProxy
3 nodes PostgreSQL Patroni
3 nodes etcd

And dedicated pgbackrest server, from which I want to connect to Patroni cluster and take backups. But I don't want to connect to physical servers, because I've configured backup standby, and so after switchover or failovers, I must to change pgbackrest.conf file each time. Is it acceptable to connect through HAProxy VIP to Patroni cluster from pgbackrest.conf?

[dwsteele]

But I don't want to connect to physical servers, because I've configured backup standby, and so after switchover or failovers, I must to change pgbackrest.conf file each time.

No, you don't need to change pgbackrest.conf. pgbackrest will automatically find the primary and standby even when they change IPs.

[Farik013]

But I can't connect to HAProxy VIP adress. Errors provided
My question is that can I connect pgbackrest via VIP, as I connect to the database in Patroni cluster with a haproxy, or do I need to connect directly to the database?

[pgstef]

But I can't connect to HAProxy VIP adress. Errors provided My question is that can I connect pgbackrest via VIP, as I connect to the database in Patroni cluster with a haproxy, or do I need to connect directly to the database?

Hi,

As answered by David, you should use pg host options indexing to define the ips/names of your PG hosts in the pgBackRest configuration.

pgBackRest doesn't only use a DB connection, but also needs to run a pgbackrest local process on the PG host to establish the communication and pull data files (+ establish a local socket db connection).

That you are using Patroni and/or VIP doesn't matter from a pgBackRest point-of-view. You should just treat it as a PG replication cluster and use pg1-host, pg2-host, pg3-host,...