search

EnterpriseDB / barman

Barman - Backup and Recovery Manager for PostgreSQL
https://www.pgbarman.org/
GNU General Public License v3.0
2.07k stars 191 forks source link

Attempting to use streaming_conninfo raises an error ERROR: cannot execute SQL commands in WAL sender for physical replication #970

Closed ofigenov closed 1 month ago

ofigenov commented 2 months ago

I have a cluster Postgres 13 of 2 nodes (host1 is now replica, host2 is now leader) that I run under patroni, now I'm trying to teach barman to redirect if a failover happens. Everything works until I try to use the setting wal_streaming_conninfo = host=host1,host2 port=5432 user=barman dbname=replication replication=true target_session_attrs=primary I try specify dbname=postgres, removed replication=true - It still doesn't work. And on a similar test cluster (the only difference I see there is I have version 14) I had it working before and it's working right now. barman check server returns PostgreSQL: OK superuser or standard user with backup privileges: OK PostgreSQL streaming: OK wal_level: OK PostgreSQL server is standby: OK Primary server is not a standby: OK Primary and standby have same system ID: OK has monitoring privileges (WAL streaming): OK PostgreSQL streaming (WAL streaming): FAILED (connection to server at "host1" (ip), port 5432 failed: ERROR: cannot execute SQL commands in WAL sender for physical replication) wal_level (WAL streaming): OK systemid coherence (WAL streaming): OK replication slot (WAL streaming): FAILED (slot 'barman' not active: is 'receive-wal' running?) directories: OK retention policy settings: OK backup maximum age: OK (interval provided: 2 days, latest backup age: 10 hours, 44 minutes, 2 seconds) backup minimum size: OK (247.6 GiB) wal maximum age: OK (no last_wal_maximum_age provided) wal size: OK (4.4 GiB) compression settings: OK failed backups: OK (there are 0 failed backups) minimum redundancy requirements: OK (have 7 backups, expected at least 3) pg_basebackup: OK pg_basebackup compatible: OK pg_basebackup supports tablespaces mapping: OK systemid coherence: OK pg_receivexlog: OK pg_receivexlog compatible: OK receive-wal running: FAILED (See the Barman log file for more details) archiver errors: OK

if I comments wal_streaming_conninfo, then it work. Maybe you have some ideas? p.s. Why Barman try sent sql command in replication mode? barman --version 3.10.0

ofigenov commented 2 months ago

I solved the problem, the reason was that Postgres 13 doesn't support target_session_attrs=primary

martinmarques commented 2 months ago

I think we should fix this. Let me have a look at it and get back

martinmarques commented 2 months ago

I mean, if this has to do with the version of postgres and an option in barman, that option should verify the version of the DB server before running any kind of query, or performing unsupported tasks

ofigenov commented 2 months ago

I mean, if this has to do with the version of postgres and an option in barman, that option should verify the version of the DB server before running any kind of query, or performing unsupported tasks

It was actually my mistake, because I missed the point that version 13 did not yet support all the variants of target_session_attrs that appeared in version 14, so there's nothing to modify. But I'll leave a comment in case anyone else has a similar problem and google it.

martinmarques commented 1 month ago

Looking at this closer, I feel that we'd have to check every single value of every single parameter. Seems like an overkill and users should know what values they can use with each DB server.