Thread: DB Backup from WAL Slave
Hello, we have a db master and a slave. master conf: wal_level = replica max_wal_senders = 5 wal_keep_segments = 100 archive_mode = on archive_command = 'rsync -a %p -e "ssh -i /var/lib/postgresql/.ssh/id_rsa" postgres@slave:/var/lib/postgresql/9.6/wals/master/%f </dev/null' slave conf: # replication hot_standby = on # pg_dump: Error message from server: ERROR: canceling statement due to conflict with recovery # DETAIL: User was holding a relation lock for too long. # https://www.postgresql.org/message-id/CADp-Sm6if-z0NbsxrJwPBQDQNt6AQJpbbagHbWyGrN7zyYZdCA%40mail.gmail.com max_standby_streaming_delay = 300000 The slave is not used as read-only But as docu say (https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html) wal_level = archive is mapped to replica How can I do an backup with pg_dumpall from slave? Best Regards,
On 24 July 2018 14:44:45 CEST, basti <mailinglist@unix-solution.de> wrote: >Hello, > >we have a db master and a slave. - > >How can I do an backup with pg_dumpall from slave? Set hot_standby_feedback to on. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company
Thanks a lot for this tip. I don't know is this is the right param. Becouse of: "... , but can cause database bloat on the primary for some workloads." This the an other way to backup the database and work around pg_dump: Error message from server: ERROR: canceling statement due to conflict with recovery # DETAIL: User was holding a relation lock for too long. this error? I have try bg_asepackup but this close with: pg_basebackup: could not connect to server: FATAL: number of requested standby connections exceeds max_wal_senders (currently 0) Best regards, On 24.07.2018 15:33, Andreas Kretschmer wrote: > On 24 July 2018 14:44:45 CEST, basti <mailinglist@unix-solution.de> wrote: >> Hello, >> >> we have a db master and a slave. > > - >> >> How can I do an backup with pg_dumpall from slave? > > Set hot_standby_feedback to on. > > > Regards, Andreas > >
Hi Basti,
When you are dumping a big table using hot_standby_feedback=on, you are holding the master from cleaning up old records (as in: it was running on master).
If too much time elapses while the lock is held (30 secs by default), then the statement is cancelled.At the expense of potentially accumulating more WAL segments on the primary, you might want to look into max_standby_archive_delay/max_standby_streaming_delay parameters to have more control over query cancellation
regards, fabio pardi
On 25/07/18 15:14, basti wrote:
Thanks a lot for this tip. I don't know is this is the right param. Becouse of: "... , but can cause database bloat on the primary for some workloads." This the an other way to backup the database and work around pg_dump: Error message from server: ERROR: canceling statement due to conflict with recovery # DETAIL: User was holding a relation lock for too long. this error? I have try bg_asepackup but this close with: pg_basebackup: could not connect to server: FATAL: number of requested standby connections exceeds max_wal_senders (currently 0) Best regards, On 24.07.2018 15:33, Andreas Kretschmer wrote:On 24 July 2018 14:44:45 CEST, basti <mailinglist@unix-solution.de> wrote:Hello, we have a db master and a slave.-How can I do an backup with pg_dumpall from slave?Set hot_standby_feedback to on. Regards, Andreas