The following bug has been logged on the website:
Bug reference: 7546
Logged by: Stuart Bishop
Email address: [email protected]
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.
I can excercise this problem with the following script:
#!/bin/sh
dbname="repl_test"
master_port=5432
slave_port=5434
rows=2000000
slow="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 = 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=0
while [ $synced -ne 1 ]
do sleep 5
synced=`psql -qtA -p $master_port -d $dbname -c "SELECT
(pg_current_xlog_location() = min(replay_location))::integer FROM
pg_stat_replication;"`
echo synced $synced synced
done
(pg_dump -p $slave_port --format=c $dbname | $slow > /dev/null) &
sleep 5;
psql -p $master_port -d $dbname -c "vacuum verbose BigStuff;"
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
