DB Backup from WAL Slave

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,

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 statementis 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