Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id F00F2133616B for ; Wed, 27 Oct 2010 06:00:36 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 65328-02 for ; Wed, 27 Oct 2010 09:00:23 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ns.solnet.cz (ns.solnet.cz [193.165.198.50]) by mail.postgresql.org (Postfix) with ESMTP id B5C1B1337B56 for ; Wed, 27 Oct 2010 06:00:20 -0300 (ADT) Received: from mail by ns.solnet.cz with scanned-ok (Exim 4.69) id 1PB1rm-0005L2-IB for ; Wed, 27 Oct 2010 11:00:18 +0200 X-AntiVirus: scanned for viruses by soLNet AVirCheck 2.0.60 (http://www.solnet.cz/avircheck) Received: from localhost ([127.0.0.1] helo=solnet.cz) by ns.solnet.cz with esmtpa (Exim 4.69) id 1PB1rW-0005Bs-De; Wed, 27 Oct 2010 11:00:02 +0200 Date: Wed, 27 Oct 2010 11:00:00 +0200 From: =?utf-8?Q?Martin_Povolny?= To: tgl@sss.pgh.pa.us Cc: pgsql-admin@postgresql.org In-Reply-To: <2634.1288138390@sss.pgh.pa.us> References: <2634.1288138390@sss.pgh.pa.us> Subject: =?utf-8?Q?Re=3A_=5BADMIN=5D_large_database=3A_problems_with_pg=5Fdump_and_pg=5Frestore_?= Mime-Version: 1.0 Content-Type: multipart/alternative; boundary=mimepart_4cc7ea125f49f_1bd8..fdbe628d62618 User-Agent: InChange X-Priority: 0 Message-Id: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/194 X-Sequence-Number: 35935 --mimepart_4cc7ea125f49f_1bd8..fdbe628d62618 Content-Type: text/plain; charset=utf-8 27.10.2010 tgl@sss.pgh.pa.us napsal(a): > =?utf-8?Q?Martin_Povolny?= writes: >> I had 5 databases, 4 dumped ok, the 5th, the largest failed dumping: I >> was unable to >> make a dump in the default 'tar' format. I got this message: >> pg_dump: [tar archiver] archive member too large for tar format > > This is expected: tar format has a documented limit of 8GB per table. > (BTW, tar is not the "default" nor the recommended format, in part > because of that limitation. The custom format is preferred unless > you really *need* to manipulate the dump files with "tar" for some > reason.) Ok, I get it. Don't use the 'tar' format. I will not. As to hitting the limit of 8 GB per table -- I have one really large table. But if I dump the table separetely, I get: pg_dump --verbose --host localhost --username bb --create --format tar --file archiv5-process.dump --table process archiv5 -rw-r--r-- 1 root root 4879763968 2010-10-27 10:15 archiv5-process.dump in other words: I am sure I did not hit the 8GB per table limit. But I am over 4GB per table. The 'process' table is the largest and is also the one where restore fails in both cases (tar format and custom format). > >> for the bb.dump in the 'custom' format: >> pg_restore: [vlastní archivář] unexpected end of file > > Hm, that's weird. I can't think of any explanation other than the dump > file somehow getting corrupted. Do you get sane-looking output if you > run "pg_restore -l bb.dump"? Sure, I did pg_restore -l into a file and did not get any errors. Then I commented out the already restored files and then tried restoring tables behind the table 'process'. But I got the same error message :-( like this: $ /usr/lib/postgresql/8.4/bin/pg_restore -l bb.dump > bb.list # then edit bb.list, commenting out lines before and including table 'process', saving into bb.list-post-process $ /usr/lib/postgresql/8.4/bin/pg_restore --verbose --use-list bb.list-post-process bb.dump > bb-list-restore.sql pg_restore: restoring data for table "process_internet" pg_restore: [custom archiver] unexpected end of file pg_restore: *** aborted because of error As to splitting the dump as suggested earlier in this thread -- I am sure my system can work with files over 4 GB also I don't understand how spliting the output from pg_dump would prevent the pg_dump from failing. But I can try that too. Also I did not try the '-F plain' dump format. I have stopped using the plain format in the past because I was getting output as if I used --inserts atlhough I did not and I don't see any option for pg_dump, that would force the use of COPY for dumping data. But that is several versions of postgres back and I did not try this since that time. Many thanks for your time and tips! -- Mgr. Martin Povolný, soLNet, s.r.o., +420777714458, martin.povolny@solnet.cz --mimepart_4cc7ea125f49f_1bd8..fdbe628d62618 Content-Type: text/html; charset=utf-8 27.10.2010 tgl@sss.pgh.pa.us napsal(a):
> =?utf-8?Q?Martin_Povolny?= <martin.povolny@solnet.cz> writes:
>> I had 5 databases, 4 dumped ok, the 5th, the largest failed dumping: I
>> was unable to
>> make a dump in the default 'tar' format. I got this message:
>> pg_dump: [tar archiver] archive member too large for tar format
>
> This is expected: tar format has a documented limit of 8GB per table.
> (BTW, tar is not the "default" nor the recommended format, in part
> because of that limitation. The custom format is preferred unless
> you really *need* to manipulate the dump files with "tar" for some
> reason.)


Ok, I get it. Don't use the 'tar' format. I will not. 

As to hitting the limit of 8 GB per table  -- I have one really large table. 

But if I dump the table separetely, I get:

pg_dump --verbose --host localhost --username bb --create --format tar --file archiv5-process.dump --table process archiv5

-rw-r--r-- 1 root root 4879763968 2010-10-27 10:15 archiv5-process.dump

in other words: I am sure I did not hit the 8GB per table limit. But I am over 4GB per table.

The 'process' table is the largest and is also the one where restore fails in both cases (tar format and custom format).

>
>> for the bb.dump in the 'custom' format:
>> pg_restore: [vlastní archivář] unexpected end of file
>
> Hm, that's weird. I can't think of any explanation other than the dump
> file somehow getting corrupted. Do you get sane-looking output if you
> run "pg_restore -l bb.dump"?

Sure, I did pg_restore -l into a file and did not get any errors.

Then I commented out the already restored files and then tried restoring tables behind the table 'process'.

But I got the same error message :-(

like this:

$ /usr/lib/postgresql/8.4/bin/pg_restore -l bb.dump > bb.list

# then edit bb.list, commenting out lines before and including table 'process', saving into bb.list-post-process

$ /usr/lib/postgresql/8.4/bin/pg_restore --verbose --use-list bb.list-post-process bb.dump > bb-list-restore.sql
pg_restore: restoring data for table "process_internet"
pg_restore: [custom archiver] unexpected end of file
pg_restore: *** aborted because of error

As to splitting the dump as suggested earlier in this thread -- I am sure my system can work with files over 4 GB also I don't understand how spliting the output from pg_dump would prevent the pg_dump from failing. But I can try that too.

Also I did not try the '-F plain' dump format. 

I have stopped using the plain format in the past because I was getting output as if I used --inserts atlhough I did not and I don't see any option for pg_dump, that would force the use of COPY for dumping data. But that is several versions of postgres back and I did not try this since that time.

Many thanks for your time and tips!

--
Mgr. Martin Povolný, soLNet, s.r.o.,
+420777714458, martin.povolny@solnet.cz
--mimepart_4cc7ea125f49f_1bd8..fdbe628d62618--