Pgbackrest connection to Patroni cluster with HAProxy (VIP)
0
votes
0
answers
211
views
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?
pgbackrest.conf:
[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
[pg_test]
repo1-path=/var/lib/pgbackrest/pg_test
pg1-host=VIP
pg1-host-user=pgbackrest
pg1-path=/opt/postgres/data/
pg1-port=5000
pg2-host=VIP
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
My patroni parameters:
parameters:
archive_command: pgbackrest --stanza=pg_test 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
random_page_cost: 1.25
shared_buffers: 4096 MB
work_mem: 32 MB
And this is error I'm getting when creating stanza:
-------------------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=VIP_adress --pg2-host=VIP_adress --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/pg_test --stanza=pg_test
2024-03-18 08:41:50.055 P00 WARN: unable to check pg1: [OptionRequiredError] remote-0 process on 'VIP_adress' terminated unexpectedly : ERROR: : 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 'VIP_adress' terminated unexpectedly : ERROR: : stanza-create command requires option: pg2-path
HINT: does this stanza exist?
2024-03-18 08:41:50.292 P00 ERROR: : 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
Asked by Farid Jafarzadeh
(1 rep)
Mar 18, 2024, 10:51 AM
Last activity: Mar 22, 2024, 06:58 AM
Last activity: Mar 22, 2024, 06:58 AM