Thread: BUG #7546: Backups on hot standby cancelled despite hot_standby=on
BUG #7546: Backups on hot standby cancelled despite hot_standby=on
From
stuart@stuartbishop.net
Date:
The following bug has been logged on the website: Bug reference: 7546 Logged by: Stuart Bishop Email address: stuart@stuartbishop.net PostgreSQL version: 9.1.5 Operating system: Ubuntu 12.10 Description: = I have a primary and a hot standby using streaming replication. The hot standby specifies 'hot_standby_feedback=3Don' with other replication settin= gs set to default. If a vacuum occurs on the primary while pg_dump is dumping a large table, the pg_dump is cancelled, usually with the following error: ERROR: canceling statement due to conflict with recovery DETAIL: User was holding shared buffer pin for too long. I can excercise this problem with the following script: #!/bin/sh dbname=3D"repl_test" master_port=3D5432 slave_port=3D5434 rows=3D2000000 slow=3D"pv --rate-limit 20k" createdb -p $master_port $dbname psql -p $master_port -d $dbname -f - <<EOM CREATE TABLE IF NOT EXISTS BigStuff ( a serial primary key, b integer, c text, d text) WITH (autovacuum_enabled =3D FALSE); INSERT INTO BigStuff (b, c, d) SELECT i,md5(i::text),reverse(md5(i::text)) FROM generate_series(1,${rows}) AS i; DELETE FROM BigStuff WHERE random() < 0.15; EOM synced=3D0 while [ $synced -ne 1 ] do sleep 5 synced=3D`psql -qtA -p $master_port -d $dbname -c "SELECT (pg_current_xlog_location() =3D min(replay_location))::integer FROM pg_stat_replication;"` echo synced $synced synced done (pg_dump -p $slave_port --format=3Dc $dbname | $slow > /dev/null) & sleep 5; psql -p $master_port -d $dbname -c "vacuum verbose BigStuff;"
On Mon, Sep 17, 2012 at 7:28 PM, <stuart@stuartbishop.net> wrote: > The following bug has been logged on the website: > > Bug reference: 7546 > Logged by: Stuart Bishop > Email address: stuart@stuartbishop.net > PostgreSQL version: 9.1.5 > Operating system: Ubuntu 12.10 > Description: > > I have a primary and a hot standby using streaming replication. The hot > standby specifies 'hot_standby_feedback=on' with other replication settings > set to default. > > If a vacuum occurs on the primary while pg_dump is dumping a large table, > the pg_dump is cancelled, usually with the following error: > > ERROR: canceling statement due to conflict with recovery > DETAIL: User was holding shared buffer pin for too long. Maybe I'm missing something, but ISTM that hot_standby_feedback doesn't eliminate the query cancels caused by buffer pin lock which you encountered. It eliminates only the query cancels caused by cleanup of rows. So you might need to set max_standby_streaming_delay to -1, to avoid query cancels. Regards, -- Fujii Masao