Re: pg_dump - ERROR - PostgreSQL 9.2

[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(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com>
wrote:

> 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(at)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
>