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;"