Thread: parallel dump fails to dump large tables
I am having problem using parallel pg_dump feature in postgres release 9.4. The size of the table is large(54GB). The dump fails with the error: "pg_dump: [parallel archiver] a worker process died unexpectedly". After this error the pg_dump aborts. The error log file gets the following message:
2015-02-09 15:22:04 PST [8636]: [2-1] user=pdroot,db=iii,appname=pg_dump STATEMENT: COPY iiirecord.varfield (id, field_type_tag, marc_tag, marc_ind1, marc_ind2, field_content, field_group_id, occ_num, record_id) TO stdout;
2015-02-09 15:22:04 PST [8636]: [3-1] user=pdroot,db=iii,appname=pg_dump FATAL: connection to client lost
2015-02-09 15:22:04 PST [8636]: [4-1] user=pdroot,db=iii,appname=pg_dump STATEMENT: COPY iiirecord.varfield (id, field_type_tag, marc_tag, marc_ind1, marc_ind2, field_content, field_group_id, occ_num, record_id) TO stdout;
Is there any config parameter that I need to set to use parallel dump for large tables.
thanks
shasingh
On 14/02/2015 15:42, Shanker Singh wrote: > Hi, > I am having problem using parallel pg_dump feature in postgres release > 9.4. The size of the table is large(54GB). The dump fails with the > error: "pg_dump: [parallel archiver] a worker process died > unexpectedly". After this error the pg_dump aborts. The error log file > gets the following message: > > 2015-02-09 15:22:04 PST [8636]: [2-1] user=pdroot,db=iii,appname=pg_dump > STATEMENT: COPY iiirecord.varfield (id, field_type_tag, marc_tag, > marc_ind1, marc_ind2, field_content, field_group_id, occ_num, record_id) > TO stdout; > 2015-02-09 15:22:04 PST [8636]: [3-1] user=pdroot,db=iii,appname=pg_dump > FATAL: connection to client lost There's your problem - something went wrong with the network. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
"Raymond O'Donnell" <rod@iol.ie> writes:
> On 14/02/2015 15:42, Shanker Singh wrote:
>> Hi,
>> I am having problem using parallel pg_dump feature in postgres release
>> 9.4. The size of the table is large(54GB). The dump fails with the
>> error: "pg_dump: [parallel archiver] a worker process died
>> unexpectedly". After this error the pg_dump aborts. The error log file
>> gets the following message:
>>
>> 2015-02-09 15:22:04 PST [8636]: [2-1] user=pdroot,db=iii,appname=pg_dump
>> STATEMENT: COPY iiirecord.varfield (id, field_type_tag, marc_tag,
>> marc_ind1, marc_ind2, field_content, field_group_id, occ_num, record_id)
>> TO stdout;
>> 2015-02-09 15:22:04 PST [8636]: [3-1] user=pdroot,db=iii,appname=pg_dump
>> FATAL: connection to client lost
> There's your problem - something went wrong with the network.
I'm wondering about SSL renegotiation failures as a possible cause of
the disconnect --- that would explain why it only happens on large tables.
regards, tom lane
If I exclude the large tables(>30GB) in the parallel dump it does succeed and normal dump also succeeds. So I am not
sureif the network is at fault. Is there any other option that might help to make parallel dump usable for large
tables?
thanks
shanker
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 14, 2015 9:00 AM
To: rod@iol.ie
Cc: Shanker Singh; pgsql-general@postgresql.org
Subject: Re: [GENERAL] parallel dump fails to dump large tables
"Raymond O'Donnell" <rod@iol.ie> writes:
> On 14/02/2015 15:42, Shanker Singh wrote:
>> Hi,
>> I am having problem using parallel pg_dump feature in postgres
>> release 9.4. The size of the table is large(54GB). The dump fails
>> with the
>> error: "pg_dump: [parallel archiver] a worker process died
>> unexpectedly". After this error the pg_dump aborts. The error log
>> file gets the following message:
>>
>> 2015-02-09 15:22:04 PST [8636]: [2-1]
>> user=pdroot,db=iii,appname=pg_dump
>> STATEMENT: COPY iiirecord.varfield (id, field_type_tag, marc_tag,
>> marc_ind1, marc_ind2, field_content, field_group_id, occ_num,
>> record_id) TO stdout;
>> 2015-02-09 15:22:04 PST [8636]: [3-1]
>> user=pdroot,db=iii,appname=pg_dump
>> FATAL: connection to client lost
> There's your problem - something went wrong with the network.
I'm wondering about SSL renegotiation failures as a possible cause of the disconnect --- that would explain why it only
happenson large tables.
regards, tom lane
I tried turning off ssl renegotiation by setting "ssl_renegotiation_limit = 0" in postgresql.conf but it had no effect.
Theparallel dump still fails on large tables consistently.
Thanks
Shanker
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 14, 2015 9:00 AM
To: rod@iol.ie
Cc: Shanker Singh; pgsql-general@postgresql.org
Subject: Re: [GENERAL] parallel dump fails to dump large tables
"Raymond O'Donnell" <rod@iol.ie> writes:
> On 14/02/2015 15:42, Shanker Singh wrote:
>> Hi,
>> I am having problem using parallel pg_dump feature in postgres
>> release 9.4. The size of the table is large(54GB). The dump fails
>> with the
>> error: "pg_dump: [parallel archiver] a worker process died
>> unexpectedly". After this error the pg_dump aborts. The error log
>> file gets the following message:
>>
>> 2015-02-09 15:22:04 PST [8636]: [2-1]
>> user=pdroot,db=iii,appname=pg_dump
>> STATEMENT: COPY iiirecord.varfield (id, field_type_tag, marc_tag,
>> marc_ind1, marc_ind2, field_content, field_group_id, occ_num,
>> record_id) TO stdout;
>> 2015-02-09 15:22:04 PST [8636]: [3-1]
>> user=pdroot,db=iii,appname=pg_dump
>> FATAL: connection to client lost
> There's your problem - something went wrong with the network.
I'm wondering about SSL renegotiation failures as a possible cause of the disconnect --- that would explain why it only
happenson large tables.
regards, tom lane
I tried turning off ssl renegotiation by setting "ssl_renegotiation_limit = 0" in postgresql.conf but it had no effect. The parallel dump still fails on large tables consistently.
Thanks
Shanker
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 14, 2015 9:00 AM
To: rod@iol.ie
Cc: Shanker Singh; pgsql-general@postgresql.org
Subject: Re: [GENERAL] parallel dump fails to dump large tables"Raymond O'Donnell" <rod@iol.ie> writes:
> On 14/02/2015 15:42, Shanker Singh wrote:
>> Hi,
>> I am having problem using parallel pg_dump feature in postgres
>> release 9.4. The size of the table is large(54GB). The dump fails
>> with the
>> error: "pg_dump: [parallel archiver] a worker process died
>> unexpectedly". After this error the pg_dump aborts. The error log
>> file gets the following message:
>>
>> 2015-02-09 15:22:04 PST [8636]: [2-1]
>> user=pdroot,db=iii,appname=pg_dump
>> STATEMENT: COPY iiirecord.varfield (id, field_type_tag, marc_tag,
>> marc_ind1, marc_ind2, field_content, field_group_id, occ_num,
>> record_id) TO stdout;
>> 2015-02-09 15:22:04 PST [8636]: [3-1]
>> user=pdroot,db=iii,appname=pg_dump
>> FATAL: connection to client lost
> There's your problem - something went wrong with the network.
I'm wondering about SSL renegotiation failures as a possible cause of the disconnect --- that would explain why it only happens on large tables.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi,
There is no 2nd server involved. The pg_dump runs on the server where postgres server is running. The regular dump runs the same way and it completes
Successfully within 3 hours. It’s only parallel dump fails on the table with size about 48GB. We run the pg_dump through an script on the postgresql server with
Command similar to “nohup ./postages_dumpdb &”;
Thanks
shanker
From: Sterfield [mailto:sterfield@gmail.com]
Sent: Sunday, February 22, 2015 8:50 AM
To: Shanker Singh
Cc: Tom Lane; rod@iol.ie; pgsql-general@postgresql.org
Subject: Re: [GENERAL] parallel dump fails to dump large tables
2015-02-20 14:26 GMT-08:00 Shanker Singh <ssingh@iii.com>:
I tried turning off ssl renegotiation by setting "ssl_renegotiation_limit = 0" in postgresql.conf but it had no effect. The parallel dump still fails on large tables consistently.
Thanks
Shanker
HI,
Maybe you could try to setup an SSH connection between the two servers, with a keepalive option, and left it open for a long time (at least the duration of your backup), just to test if your ssh connection is still being cut after some time.
That way, you will be sure if the problem is related to SSH or related to Postgresql.
Thanks,
Guillaume
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 14, 2015 9:00 AM
To: rod@iol.ie
Cc: Shanker Singh; pgsql-general@postgresql.org
Subject: Re: [GENERAL] parallel dump fails to dump large tables"Raymond O'Donnell" <rod@iol.ie> writes:
> On 14/02/2015 15:42, Shanker Singh wrote:
>> Hi,
>> I am having problem using parallel pg_dump feature in postgres
>> release 9.4. The size of the table is large(54GB). The dump fails
>> with the
>> error: "pg_dump: [parallel archiver] a worker process died
>> unexpectedly". After this error the pg_dump aborts. The error log
>> file gets the following message:
>>
>> 2015-02-09 15:22:04 PST [8636]: [2-1]
>> user=pdroot,db=iii,appname=pg_dump
>> STATEMENT: COPY iiirecord.varfield (id, field_type_tag, marc_tag,
>> marc_ind1, marc_ind2, field_content, field_group_id, occ_num,
>> record_id) TO stdout;
>> 2015-02-09 15:22:04 PST [8636]: [3-1]
>> user=pdroot,db=iii,appname=pg_dump
>> FATAL: connection to client lost
> There's your problem - something went wrong with the network.
I'm wondering about SSL renegotiation failures as a possible cause of the disconnect --- that would explain why it only happens on large tables.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I did setup the keepalive option for ssh but still the pg_dump fails on the table of size 48GB. It was able to dump table of size 34Gb(dump file size 2Gb) but fails on table of size
48Gb(partial dump file size 3.9Gb). Is there any limit on the size of the dump file in parallel load?
Thanks
shanker
From: Sterfield [mailto:sterfield@gmail.com]
Sent: Sunday, February 22, 2015 8:50 AM
To: Shanker Singh
Cc: Tom Lane; rod@iol.ie; pgsql-general@postgresql.org
Subject: Re: [GENERAL] parallel dump fails to dump large tables
2015-02-20 14:26 GMT-08:00 Shanker Singh <ssingh@iii.com>:
I tried turning off ssl renegotiation by setting "ssl_renegotiation_limit = 0" in postgresql.conf but it had no effect. The parallel dump still fails on large tables consistently.
Thanks
Shanker
HI,
Maybe you could try to setup an SSH connection between the two servers, with a keepalive option, and left it open for a long time (at least the duration of your backup), just to test if your ssh connection is still being cut after some time.
That way, you will be sure if the problem is related to SSH or related to Postgresql.
Thanks,
Guillaume
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 14, 2015 9:00 AM
To: rod@iol.ie
Cc: Shanker Singh; pgsql-general@postgresql.org
Subject: Re: [GENERAL] parallel dump fails to dump large tables"Raymond O'Donnell" <rod@iol.ie> writes:
> On 14/02/2015 15:42, Shanker Singh wrote:
>> Hi,
>> I am having problem using parallel pg_dump feature in postgres
>> release 9.4. The size of the table is large(54GB). The dump fails
>> with the
>> error: "pg_dump: [parallel archiver] a worker process died
>> unexpectedly". After this error the pg_dump aborts. The error log
>> file gets the following message:
>>
>> 2015-02-09 15:22:04 PST [8636]: [2-1]
>> user=pdroot,db=iii,appname=pg_dump
>> STATEMENT: COPY iiirecord.varfield (id, field_type_tag, marc_tag,
>> marc_ind1, marc_ind2, field_content, field_group_id, occ_num,
>> record_id) TO stdout;
>> 2015-02-09 15:22:04 PST [8636]: [3-1]
>> user=pdroot,db=iii,appname=pg_dump
>> FATAL: connection to client lost
> There's your problem - something went wrong with the network.
I'm wondering about SSL renegotiation failures as a possible cause of the disconnect --- that would explain why it only happens on large tables.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I tried dumping the largest table that is having problem using –j1 flag in parallel dump. This time I got error on the console “File size limit exceeded” but the system allows
Unlimited file size. Also the pg_dump without –j flag goes through fine. Do you guys know what’s going on with parallel dump? The system is 64 bit centos(
2.6.32-504.8.1.el6.x86_64 #1 SMP Wed Jan 28 21:11:36 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux) with ext4 file system.
limit
cputime unlimited
filesize unlimited
datasize unlimited
stacksize 10240 kbytes
coredumpsize 0 kbytes
memoryuse unlimited
vmemoryuse unlimited
descriptors 25000
memorylocked 64 kbytes
maxproc 1024
From: Sterfield [mailto:sterfield@gmail.com]
Sent: Sunday, February 22, 2015 8:50 AM
To: Shanker Singh
Cc: Tom Lane; rod@iol.ie; pgsql-general@postgresql.org
Subject: Re: [GENERAL] parallel dump fails to dump large tables
2015-02-20 14:26 GMT-08:00 Shanker Singh <ssingh@iii.com>:
I tried turning off ssl renegotiation by setting "ssl_renegotiation_limit = 0" in postgresql.conf but it had no effect. The parallel dump still fails on large tables consistently.
Thanks
Shanker
HI,
Maybe you could try to setup an SSH connection between the two servers, with a keepalive option, and left it open for a long time (at least the duration of your backup), just to test if your ssh connection is still being cut after some time.
That way, you will be sure if the problem is related to SSH or related to Postgresql.
Thanks,
Guillaume
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 14, 2015 9:00 AM
To: rod@iol.ie
Cc: Shanker Singh; pgsql-general@postgresql.org
Subject: Re: [GENERAL] parallel dump fails to dump large tables"Raymond O'Donnell" <rod@iol.ie> writes:
> On 14/02/2015 15:42, Shanker Singh wrote:
>> Hi,
>> I am having problem using parallel pg_dump feature in postgres
>> release 9.4. The size of the table is large(54GB). The dump fails
>> with the
>> error: "pg_dump: [parallel archiver] a worker process died
>> unexpectedly". After this error the pg_dump aborts. The error log
>> file gets the following message:
>>
>> 2015-02-09 15:22:04 PST [8636]: [2-1]
>> user=pdroot,db=iii,appname=pg_dump
>> STATEMENT: COPY iiirecord.varfield (id, field_type_tag, marc_tag,
>> marc_ind1, marc_ind2, field_content, field_group_id, occ_num,
>> record_id) TO stdout;
>> 2015-02-09 15:22:04 PST [8636]: [3-1]
>> user=pdroot,db=iii,appname=pg_dump
>> FATAL: connection to client lost
> There's your problem - something went wrong with the network.
I'm wondering about SSL renegotiation failures as a possible cause of the disconnect --- that would explain why it only happens on large tables.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
There is no problem dumping large tables using parallel dump. My script had limit on the file size that was causing parallel dump to abort on large tables. Thanks everyone for their valuable suggestion.
Thanks
shanker
From: Shanker Singh
Sent: Monday, February 23, 2015 6:18 PM
To: Sterfield
Cc: Tom Lane; rod@iol.ie; pgsql-general@postgresql.org; Shanker Singh
Subject: RE: [GENERAL] parallel dump fails to dump large tables
I tried dumping the largest table that is having problem using –j1 flag in parallel dump. This time I got error on the console “File size limit exceeded” but the system allows
Unlimited file size. Also the pg_dump without –j flag goes through fine. Do you guys know what’s going on with parallel dump? The system is 64 bit centos(
2.6.32-504.8.1.el6.x86_64 #1 SMP Wed Jan 28 21:11:36 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux) with ext4 file system.
limit
cputime unlimited
filesize unlimited
datasize unlimited
stacksize 10240 kbytes
coredumpsize 0 kbytes
memoryuse unlimited
vmemoryuse unlimited
descriptors 25000
memorylocked 64 kbytes
maxproc 1024
From: Sterfield [mailto:sterfield@gmail.com]
Sent: Sunday, February 22, 2015 8:50 AM
To: Shanker Singh
Cc: Tom Lane; rod@iol.ie; pgsql-general@postgresql.org
Subject: Re: [GENERAL] parallel dump fails to dump large tables
2015-02-20 14:26 GMT-08:00 Shanker Singh <ssingh@iii.com>:
I tried turning off ssl renegotiation by setting "ssl_renegotiation_limit = 0" in postgresql.conf but it had no effect. The parallel dump still fails on large tables consistently.
Thanks
Shanker
HI,
Maybe you could try to setup an SSH connection between the two servers, with a keepalive option, and left it open for a long time (at least the duration of your backup), just to test if your ssh connection is still being cut after some time.
That way, you will be sure if the problem is related to SSH or related to Postgresql.
Thanks,
Guillaume
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 14, 2015 9:00 AM
To: rod@iol.ie
Cc: Shanker Singh; pgsql-general@postgresql.org
Subject: Re: [GENERAL] parallel dump fails to dump large tables"Raymond O'Donnell" <rod@iol.ie> writes:
> On 14/02/2015 15:42, Shanker Singh wrote:
>> Hi,
>> I am having problem using parallel pg_dump feature in postgres
>> release 9.4. The size of the table is large(54GB). The dump fails
>> with the
>> error: "pg_dump: [parallel archiver] a worker process died
>> unexpectedly". After this error the pg_dump aborts. The error log
>> file gets the following message:
>>
>> 2015-02-09 15:22:04 PST [8636]: [2-1]
>> user=pdroot,db=iii,appname=pg_dump
>> STATEMENT: COPY iiirecord.varfield (id, field_type_tag, marc_tag,
>> marc_ind1, marc_ind2, field_content, field_group_id, occ_num,
>> record_id) TO stdout;
>> 2015-02-09 15:22:04 PST [8636]: [3-1]
>> user=pdroot,db=iii,appname=pg_dump
>> FATAL: connection to client lost
> There's your problem - something went wrong with the network.
I'm wondering about SSL renegotiation failures as a possible cause of the disconnect --- that would explain why it only happens on large tables.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general