Search Postgresql Archives

Re: Standby pg_dump Conflict with Recovery

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Oct 15, 2015, at 6:16 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 10/15/2015 03:03 PM, Louis Battuello wrote:
Hello All,

I’ve got a confusing issue with dumping data from a standby PostgreSQL
9.4.5 database.

At night, on a nearly completely idle server, I run a pg_dump of a
database that contains numerous small tables and one 3GB table. The
dump consistently fails when reaching the 3GB table with this message:

pg_dump: Dumping the contents of table “<table>" failed: PQgetResult()
failed.
pg_dump: Error message from server: ERROR:  canceling statement due to
conflict with recovery
DETAIL:  User query might have needed to see row versions that must be
removed.
pg_dump: The command was: COPY <table> (...) TO stdout;

I have replication slots enabled on the primary (“repmgr_slot_3" for the
standby pg_dump source), and I’m using hot_standby_feedback. After
getting the failure a couple times, I temporarily set
max_standby_archive_delay and max_standby_streaming_delay to -1 to allow
infinite delay on the standby,  just to see if I could get the dump to
complete. I still encountered the above error.

How did you set and temporarily enable the settings

I changed the settings in the postgresql.conf file, restarted the standby server, checked that there wasn't any activity on the primary or the standby, and ran the pg_dump on the standby again - which failed. I watched the xmin value on the primary pg_replication_slots, which held steady until the dump failed.

Then, I changed the delay settings back to the defaults and restarted the standby so I wouldn’t affect the replication during the next business day.





postgres=# select * from pg_replication_slots ;
   slot_name   | plugin | slot_type | datoid | database | active
|  xmin   | catalog_xmin | restart_lsn
---------------+--------+-----------+--------+----------+--------+---------+--------------+-------------
 repmgr_slot_2 |        | physical  |        |          | t      |
    |              | A/C6502880
 repmgr_slot_3 |        | physical  |        |          | t      |
1356283 |              | A/C6502880
(2 rows)

Is there some other configuration setting I’m forgetting?

Thanks,
Louis



-- 
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux