pg_dump - ERROR - PostgreSQL 9.2
Hi all,
*masterdb01 -> slave01 -> slave02 -> slave03*
*testmaster01 (Full copy from masterdb01)*
I'm trying to refresh my main DB, by running this command on my test server:
ssh postgres@slave02 "/usr/pgsql-9.2/bin/pg_dump
--exclude-table-data='junk.*' --format=custom master_db_name" |
/usr/pgsql-9.2/bin/pg_restore --dbname=master_db_name_temp --exit-on-error
But, I got the following error after few seconds:
pg_dump: Dumping the contents of table "invoices" failed: PQgetResult()
failed.
pg_dump: Error message from server: ERROR: canceling statement due to
conflict with recovery
DETAIL: User was holding a relation lock for too long.
pg_dump: The command was: COPY dm.invoices (invoice_id, format_version,
ts_issue, ts_ack, customer_id, code, tag, account_data, customer_data,
invoice_data, invoice_items_data) TO stdout;
If I run the pg_dump from the MASTER DB, it runs ok. But as the DB is 2 TB,
I just can't let it coping from Production. It has to be from a slave
server (I've also tried to copy from another slave. I get the same error).
What am I doing wrong? How can I solve the problem?
* I've sent the same e-mail to pgsql-admin
Thanks;
Lucas
[SOLVED]
Just to let you guys know.. I've solved this issue changing
the max_standby_streaming_delay on my slave to "-1".
Cheers
Lucas Possamai
- kinghost.co.nz
<http://forum.kinghost.co.nz/memberlist.php?mode=viewprofile&u=2&sid=e999f8370385657a65d41d5ff60b0b38>
- DigitalOcean <https://m.do.co/c/a0d31c597a03>
On 2 February 2016 at 13:21, drum.lucas@gmail.com <drum.lucas@gmail.com>
wrote:
Show quoted text
Hi all,
*masterdb01 -> slave01 -> slave02 -> slave03*
*testmaster01 (Full copy from masterdb01)*
I'm trying to refresh my main DB, by running this command on my test
server:ssh postgres@slave02 "/usr/pgsql-9.2/bin/pg_dump
--exclude-table-data='junk.*' --format=custom master_db_name" |
/usr/pgsql-9.2/bin/pg_restore --dbname=master_db_name_temp --exit-on-errorBut, I got the following error after few seconds:
pg_dump: Dumping the contents of table "invoices" failed: PQgetResult()
failed.
pg_dump: Error message from server: ERROR: canceling statement due to
conflict with recovery
DETAIL: User was holding a relation lock for too long.
pg_dump: The command was: COPY dm.invoices (invoice_id, format_version,
ts_issue, ts_ack, customer_id, code, tag, account_data, customer_data,
invoice_data, invoice_items_data) TO stdout;If I run the pg_dump from the MASTER DB, it runs ok. But as the DB is 2
TB, I just can't let it coping from Production. It has to be from a slave
server (I've also tried to copy from another slave. I get the same error).What am I doing wrong? How can I solve the problem?
* I've sent the same e-mail to pgsql-admin
Thanks;
Lucas