[GENERAL] pg_dump streaming fails - PostgreSQL 9.2

[drum.lu...@gmail.com]

Hi there!

I'm running this command: *(Trying to copy a full DB (2TB) from a
hot-standby server to a master (test) server)*

ssh postgres@myslaveserver "/usr/pgsql-9.2/bin/pg_dump
--exclude-table-data='junk.*' -—format=custom master_db" |
/usr/pgsql-9.2/bin/pg_restore --dbname=master_db_temp --exit-on-error
—-verbose

Then, after 3 GB I got this error:

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;

I've tried the pg_dump command even with: "--no-unlogged-table-data"
option, but it was unsuccessful (
http://www.postgresql.org/message-id/e1tutdo-0001hb...@wrigleys.postgresql.org
)

DB size: 2 TB

How can I solve the problem? What's going on? Thanks

Re: [GENERAL] pg_dump streaming fails - PostgreSQL 9.2

[Joshua D. Drake]

On 01/28/2016 05:23 PM, drum.lu...@gmail.com wrote:

Hi there!

I'm running this command: *(Trying to copy a full DB (2TB) from a
hot-standby server to a master (test) server)*

|ssh postgres@myslaveserver "/usr/pgsql-9.2/bin/pg_dump
--exclude-table-data='junk.*' -—format=custom
master_db"|/usr/pgsql-9.2/bin/pg_restore --dbname=master_db_temp
--exit-on-error —-verbose|

Then, after 3 GB I got this error:

|pg_dump:Dumping the contents
oftable"invoices"failed:PQgetResult()failed.pg_dump:Error message
fromserver:ERROR:canceling statement due toconflict withrecovery
DETAIL:Userwas holding a relation lock fortoo 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)TOstdout;|

I've tried the pg_dump command even with: "--no-unlogged-table-data"
option, but it was unsuccessful

(http://www.postgresql.org/message-id/e1tutdo-0001hb...@wrigleys.postgresql.org)

DB size: 2 TB

How can I solve the problem? What's going on? Thanks



Back up everything from the master.

JD



--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


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