pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed large database: problems with pg_dump and pg_restore
9+ messages / 7 participants
[nested] [flat]
* large database: problems with pg_dump and pg_restore
@ 2010-10-26 11:41 Martin Povolny <martin.povolny@solnet.cz>
0 siblings, 3 replies; 9+ messages in thread
From: Martin Povolny @ 2010-10-26 11:41 UTC (permalink / raw)
To: pgsql-admin
Hallo,
I have some quite grave problems with dumping and restoring large databases (>4GB of
dump).
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
I got over this issue by using the 'custom' format.
Unfortunately later on I was only able to restore 3 of the 5 databases -- any of the
2 dumps that would get over 4GB in the 'tar' format would fail.
/var/tmp# ls -l dumps/
total 16294020
-rw-r--r-- 1 root root 742611968 2010-10-16 20:36 archiv1.dump
-rw-r--r-- 1 root root 317352448 2010-10-16 20:37 archiv2.dump
-rw-r--r-- 1 root root 1137477632 2010-10-16 20:41 archiv3.dump
-rw-r--r-- 1 root root 3712833536 2010-10-16 20:57 archiv4.dump
-rw-r--r-- 1 root root 8735429632 2010-10-16 21:35 archiv5.dump
-rw-r--r-- 1 root root 1253325453 2010-10-16 22:59 bb.dump
archiv1-4 went OK, archiv5 -- the largest in 'tar' format -- failed and the 'bb.dump'
which is in the 'custom' format, failed too.
I got these messages:
for the archiv5 in the 'tar' format:
pg_restore: [tar archivář] nalezena poškozená tar hlavička v STEX
(předpokládáno 100, vypočteno 34044) pozice souboru 7750193152
sorry, it's in my native locale, but is says "found a corrupted tar header in STEX
(expected 100, calculated 34044) file position 7750193152
for the bb.dump in the 'custom' format:
pg_restore: [vlastní archivář] unexpected end of file
'vlastní archivář' is again in my locale, it should be in English "own archiver"
Later I tried to utilize the -I and -i switches of pg_restore to restore data that
are in the archive behing the table that was not restored. But got the same error
message.
The dump was created on postgresql-8.3 8.3.3-1~bpo40+1 from debian backports. I was
trying to restore on this version and later on using postgresql-8.4 8.4.5-1~bpo50+1
from debian backports, finally I tried 64bit version
of postgresql-8.4 8.4.5-0ubuntu10.10. No change, still the same error messages.
I welcome any help and/or hints on this issue as I need to dump and restore several
large databases.
Regards,
--
Mgr. Martin Povolný, soLNet, s.r.o.,
+420777714458, martin.povolny@solnet.cz
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: large database: problems with pg_dump and pg_restore
@ 2010-10-26 19:31 lst_hoe02@kwsoft.de
parent: Martin Povolny <martin.povolny@solnet.cz>
2 siblings, 0 replies; 9+ messages in thread
From: lst_hoe02@kwsoft.de @ 2010-10-26 19:31 UTC (permalink / raw)
To: pgsql-admin
Zitat von Martin Povolny <martin.povolny@solnet.cz>:
> Hallo,
>
> I have some quite grave problems with dumping and restoring large
> databases (>4GB of
> dump).
>
> 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
It looks like "tar" is limited to 4GB on your OS...
Try to use "split" to split up the dump to something which your
OS/Tools can reliable handle.
Regards
Andreas
Attachments:
smime.p7s (application/pkcs7-signature, 5.9K) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: large database: problems with pg_dump and pg_restore
@ 2010-10-26 21:51 Samuel Stearns <SStearns@internode.com.au>
parent: Martin Povolny <martin.povolny@solnet.cz>
2 siblings, 1 reply; 9+ messages in thread
From: Samuel Stearns @ 2010-10-26 21:51 UTC (permalink / raw)
To: Martin Povolny <martin.povolny@solnet.cz>; pgsql-admin
You can also try piping the dump through gzip and then restoring using cat:
pg_dumpall | gzip > db.out-`date +\%Y\%m\%d\%H`.gz
cat db.out-`date +\%Y\%m\%d\%H`.gz | gunzip | psql template1
Sam
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Martin Povolny
Sent: Tuesday, 26 October 2010 10:12 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] large database: problems with pg_dump and pg_restore
Hallo,
I have some quite grave problems with dumping and restoring large databases (>4GB of dump).
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
I got over this issue by using the 'custom' format.
Unfortunately later on I was only able to restore 3 of the 5 databases -- any of the 2 dumps that would get over 4GB in the 'tar' format would fail.
/var/tmp# ls -l dumps/
total 16294020
-rw-r--r-- 1 root root 742611968 2010-10-16 20:36 archiv1.dump
-rw-r--r-- 1 root root 317352448 2010-10-16 20:37 archiv2.dump
-rw-r--r-- 1 root root 1137477632 2010-10-16 20:41 archiv3.dump
-rw-r--r-- 1 root root 3712833536 2010- 10-16 20:57 archiv4.dump
-rw-r--r-- 1 root root 8735429632 2010-10-16 21:35 archiv5.dump
-rw-r--r-- 1 root root 1253325453 2010-10-16 22:59 bb.dump
archiv1-4 went OK, archiv5 -- the largest in 'tar' format -- failed and the 'bb.dump' which is in the 'custom' format, failed too.
I got these messages:
for the archiv5 in the 'tar' format:
pg_restore: [tar archivář] nalezena poškozená tar hlavička v STEX (předpokládáno 100, vypočteno 34044) pozice souboru 7750193152
sorry, it's in my native locale, but is says "found a corrupted tar header in STEX (expected 100, calculated 34044) file position 7750193152
for the bb.dump in the 'custom' format:
pg_restore: [vlastní archivář] unexpected end of file
'vlastní archiv ář' is again in my locale, it should be in English "own archiver"
Later I tried to utilize the -I and -i switches of pg_restore to restore data that are in the archive behing the table that was not restored. But got the same error message.
The dump was created on postgresql-8.3 8.3.3-1~bpo40+1 from debian backports. I was trying to restore on this version and later on using postgresql-8.4 8.4.5-1~bpo50+1 from debian backports, finally I tried 64bit version of postgresql-8.4 8.4.5-0ubuntu10.10. No change, still the same error messages.
I welcome any help and/or hints on this issue as I need to dump and restore several large databases.
Regards,
--
Mgr. Martin Povolný, soLNet, s.r.o.,
+420777714458, martin.povolny@solnet.cz
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: large database: problems with pg_dump and pg_restore
@ 2010-10-26 22:21 Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
parent: Samuel Stearns <SStearns@internode.com.au>
0 siblings, 1 reply; 9+ messages in thread
From: Jehan-Guillaume (ioguix) de Rorthais @ 2010-10-26 22:21 UTC (permalink / raw)
To: Martin Povolny <martin.povolny@solnet.cz>; +Cc: pgsql-admin
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Or even compress AND split it !
pg_dump -Fc dbname | split -b 1G - dump_dbname
and restore:
cat dump_dbname* | pg_restore -d dbname
or
cat dump_dbname* | pg_restore | psql dbname
Le 26/10/2010 23:51, Samuel Stearns a écrit :
> You can also try piping the dump through gzip and then restoring using cat:
>
>
>
> pg_dumpall | gzip > db.out-`date +\%Y\%m\%d\%H`.gz
>
>
>
> cat db.out-`date +\%Y\%m\%d\%H`.gz | gunzip | psql template1
>
>
>
> Sam
>
>
>
>
>
>
>
> *From:* pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] *On Behalf Of *Martin Povolny
> *Sent:* Tuesday, 26 October 2010 10:12 PM
> *To:* pgsql-admin@postgresql.org
> *Subject:* [ADMIN] large database: problems with pg_dump and pg_restore
>
>
>
> Hallo,
>
>
>
> I have some quite grave problems with dumping and restoring large
> databases (>4GB of dump).
>
> 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
>
>
>
> I got over this issue by using the 'custom' format.
>
>
>
> Unfortunately later on I was only able to restore 3 of the 5 databases
> -- any of the 2 dumps that would get over 4GB in the 'tar' format would
> fail.
>
>
>
> /var/tmp# ls -l dumps/
>
> total 16294020
>
> -rw-r--r-- 1 root root 742611968 2010-10-16 20:36 archiv1.dump
>
> -rw-r--r-- 1 root root 317352448 2010-10-16 20:37 archiv2.dump
>
> -rw-r--r-- 1 root root 1137477632 2010-10-16 20:41 archiv3.dump
>
> -rw-r--r-- 1 root root 3712833536 2010- 10-16 20:57 archiv4.dump
>
> -rw-r--r-- 1 root root 8735429632 2010-10-16 21:35 archiv5.dump
>
> -rw-r--r-- 1 root root 1253325453 2010-10-16 22:59 bb.dump
>
>
>
> archiv1-4 went OK, archiv5 -- the largest in 'tar' format -- failed and
> the 'bb.dump' which is in the 'custom' format, failed too.
>
>
>
> I got these messages:
>
>
>
> for the archiv5 in the 'tar' format:
>
>
>
> pg_restore: [tar archivář] nalezena poškozená tar hlavička v STEX
> (předpokládáno 100, vypočteno 34044) pozice souboru 7750193152
>
>
>
> sorry, it's in my native locale, but is says "found a corrupted tar
> header in STEX (expected 100, calculated 34044) file position 7750193152
>
>
>
> for the bb.dump in the 'custom' format:
>
>
>
> pg_restore: [vlastní archivář] unexpected end of file
>
>
>
> 'vlastní archiv ář' is again in my locale, it should be in English "own
> archiver"
>
>
>
> Later I tried to utilize the -I and -i switches of pg_restore to restore
> data that are in the archive behing the table that was not restored. But
> got the same error message.
>
>
>
> The dump was created on postgresql-8.3 8.3.3-1~bpo40+1 from debian
> backports. I was trying to restore on this version and later on using
> postgresql-8.4 8.4.5-1~bpo50+1 from debian backports, finally I tried
> 64bit version of postgresql-8.4 8.4.5-0ubuntu10.10. No change, still the
> same error messages.
>
>
>
> I welcome any help and/or hints on this issue as I need to dump and
> restore several large databases.
>
>
>
> Regards,
>
>
> --
> Mgr. Martin Povolný, soLNet, s.r.o.,
> +420777714458, martin.povolny@solnet.cz
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkzHVIAACgkQxWGfaAgowiL30ACglAXjKXTOZBsmrW5LFZzb8G83
XawAoIVc1UVkW4UQy5lK/jLNARxCb2QN
=AR/f
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: large database: problems with pg_dump and pg_restore
@ 2010-10-27 00:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Martin Povolny <martin.povolny@solnet.cz>
2 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2010-10-27 00:13 UTC (permalink / raw)
To: Martin Povolny <martin.povolny@solnet.cz>; +Cc: pgsql-admin
=?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.)
> 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"?
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: large database: problems with pg_dump and pg_restore
@ 2010-10-27 02:41 mark <dvlhntr@gmail.com>
parent: Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
0 siblings, 1 reply; 9+ messages in thread
From: mark @ 2010-10-27 02:41 UTC (permalink / raw)
To: 'Jehan-Guillaume \(ioguix\) de Rorthais' <ioguix@free.fr>; 'Martin Povolny' <martin.povolny@solnet.cz>; pgsql-admin
A long time ago, (8.1.11 IIRC)
We got much better speed not using the compression flag with pg_dump instead piping to gzip (or better yet something like pbzip2 or pigz, but I haven't used them).
I think there was a thread about this that had a test case and numbers.
IIRC it's because you will further bottleneck a core when using the compression flag. Using a pipe the compression can be done on another core (or cores if using pbzip2 or pigz) and throughput will be faster.
On the restore side hopefully people are now able to use parallel restore to improve things when reloading.
Just my thoughts,
~mark
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Jehan-Guillaume (ioguix) de Rorthais
Sent: Tuesday, October 26, 2010 4:22 PM
To: Martin Povolny
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] large database: problems with pg_dump and pg_restore
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Or even compress AND split it !
pg_dump -Fc dbname | split -b 1G - dump_dbname
and restore:
cat dump_dbname* | pg_restore -d dbname
or
cat dump_dbname* | pg_restore | psql dbname
Le 26/10/2010 23:51, Samuel Stearns a écrit :
> You can also try piping the dump through gzip and then restoring using cat:
>
>
>
> pg_dumpall | gzip > db.out-`date +\%Y\%m\%d\%H`.gz
>
>
>
> cat db.out-`date +\%Y\%m\%d\%H`.gz | gunzip | psql template1
>
>
>
> Sam
>
>
>
>
>
>
>
> *From:* pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] *On Behalf Of *Martin Povolny
> *Sent:* Tuesday, 26 October 2010 10:12 PM
> *To:* pgsql-admin@postgresql.org
> *Subject:* [ADMIN] large database: problems with pg_dump and pg_restore
>
>
>
> Hallo,
>
>
>
> I have some quite grave problems with dumping and restoring large
> databases (>4GB of dump).
>
> 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
>
>
>
> I got over this issue by using the 'custom' format.
>
>
>
> Unfortunately later on I was only able to restore 3 of the 5 databases
> -- any of the 2 dumps that would get over 4GB in the 'tar' format would
> fail.
>
>
>
> /var/tmp# ls -l dumps/
>
> total 16294020
>
> -rw-r--r-- 1 root root 742611968 2010-10-16 20:36 archiv1.dump
>
> -rw-r--r-- 1 root root 317352448 2010-10-16 20:37 archiv2.dump
>
> -rw-r--r-- 1 root root 1137477632 2010-10-16 20:41 archiv3.dump
>
> -rw-r--r-- 1 root root 3712833536 2010- 10-16 20:57 archiv4.dump
>
> -rw-r--r-- 1 root root 8735429632 2010-10-16 21:35 archiv5.dump
>
> -rw-r--r-- 1 root root 1253325453 2010-10-16 22:59 bb.dump
>
>
>
> archiv1-4 went OK, archiv5 -- the largest in 'tar' format -- failed and
> the 'bb.dump' which is in the 'custom' format, failed too.
>
>
>
> I got these messages:
>
>
>
> for the archiv5 in the 'tar' format:
>
>
>
> pg_restore: [tar archivář] nalezena poškozená tar hlavička v STEX
> (předpokládáno 100, vypočteno 34044) pozice souboru 7750193152
>
>
>
> sorry, it's in my native locale, but is says "found a corrupted tar
> header in STEX (expected 100, calculated 34044) file position 7750193152
>
>
>
> for the bb.dump in the 'custom' format:
>
>
>
> pg_restore: [vlastní archivář] unexpected end of file
>
>
>
> 'vlastní archiv ář' is again in my locale, it should be in English "own
> archiver"
>
>
>
> Later I tried to utilize the -I and -i switches of pg_restore to restore
> data that are in the archive behing the table that was not restored. But
> got the same error message.
>
>
>
> The dump was created on postgresql-8.3 8.3.3-1~bpo40+1 from debian
> backports. I was trying to restore on this version and later on using
> postgresql-8.4 8.4.5-1~bpo50+1 from debian backports, finally I tried
> 64bit version of postgresql-8.4 8.4.5-0ubuntu10.10. No change, still the
> same error messages.
>
>
>
> I welcome any help and/or hints on this issue as I need to dump and
> restore several large databases.
>
>
>
> Regards,
>
>
> --
> Mgr. Martin Povolný, soLNet, s.r.o.,
> +420777714458, martin.povolny@solnet.cz
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkzHVIAACgkQxWGfaAgowiL30ACglAXjKXTOZBsmrW5LFZzb8G83
XawAoIVc1UVkW4UQy5lK/jLNARxCb2QN
=AR/f
-----END PGP SIGNATURE-----
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [ADMIN] large database: problems with pg_dump and pg_restore
@ 2010-10-27 09:00 Martin Povolny <martin.povolny@solnet.cz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Martin Povolny @ 2010-10-27 09:00 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: large database: problems with pg_dump and pg_restore
@ 2010-10-27 09:43 Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
parent: mark <dvlhntr@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jehan-Guillaume (ioguix) de Rorthais @ 2010-10-27 09:43 UTC (permalink / raw)
To: mark <dvlhntr@gmail.com>; +Cc: Martin Povolny <martin.povolny@solnet.cz>; pgsql-admin
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Le 27/10/2010 04:41, mark a écrit :
> A long time ago, (8.1.11 IIRC)
>
> We got much better speed not using the compression flag with pg_dump instead piping to gzip (or better yet something like pbzip2 or pigz, but I haven't used them).
>
> I think there was a thread about this that had a test case and numbers.
>
> IIRC it's because you will further bottleneck a core when using the compression flag. Using a pipe the compression can be done on another core (or cores if using pbzip2 or pigz) and throughput will be faster.
Exact.
And it actually depend on some other use cases. If you need speed, your
solution makes totally sens indeed.
If you can spend some more time using -Fc, then you'll be able to play
with -l and -L switches of pg_restore to optionally decide what should
be restored or not (with more control than -n, -t, etc, think about
excluding slony stuffs as instance)
> On the restore side hopefully people are now able to use parallel restore to improve things when reloading.
+1
> Just my thoughts,
>
>
>
> ~mark
>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Jehan-Guillaume (ioguix) de Rorthais
> Sent: Tuesday, October 26, 2010 4:22 PM
> To: Martin Povolny
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] large database: problems with pg_dump and pg_restore
>
> Or even compress AND split it !
> pg_dump -Fc dbname | split -b 1G - dump_dbname
>
> and restore:
> cat dump_dbname* | pg_restore -d dbname
>
> or
> cat dump_dbname* | pg_restore | psql dbname
>
> Le 26/10/2010 23:51, Samuel Stearns a écrit :
>> You can also try piping the dump through gzip and then restoring using cat:
>
>
>
>> pg_dumpall | gzip > db.out-`date +\%Y\%m\%d\%H`.gz
>
>
>
>> cat db.out-`date +\%Y\%m\%d\%H`.gz | gunzip | psql template1
>
>
>
>> Sam
>
>
>
>
>
>
>
>> *From:* pgsql-admin-owner@postgresql.org
>> [mailto:pgsql-admin-owner@postgresql.org] *On Behalf Of *Martin Povolny
>> *Sent:* Tuesday, 26 October 2010 10:12 PM
>> *To:* pgsql-admin@postgresql.org
>> *Subject:* [ADMIN] large database: problems with pg_dump and pg_restore
>
>
>
>> Hallo,
>
>
>
>> I have some quite grave problems with dumping and restoring large
>> databases (>4GB of dump).
>
>> 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
>
>
>
>> I got over this issue by using the 'custom' format.
>
>
>
>> Unfortunately later on I was only able to restore 3 of the 5 databases
>> -- any of the 2 dumps that would get over 4GB in the 'tar' format would
>> fail.
>
>
>
>> /var/tmp# ls -l dumps/
>
>> total 16294020
>
>> -rw-r--r-- 1 root root 742611968 2010-10-16 20:36 archiv1.dump
>
>> -rw-r--r-- 1 root root 317352448 2010-10-16 20:37 archiv2.dump
>
>> -rw-r--r-- 1 root root 1137477632 2010-10-16 20:41 archiv3.dump
>
>> -rw-r--r-- 1 root root 3712833536 2010- 10-16 20:57 archiv4.dump
>
>> -rw-r--r-- 1 root root 8735429632 2010-10-16 21:35 archiv5.dump
>
>> -rw-r--r-- 1 root root 1253325453 2010-10-16 22:59 bb.dump
>
>
>
>> archiv1-4 went OK, archiv5 -- the largest in 'tar' format -- failed and
>> the 'bb.dump' which is in the 'custom' format, failed too.
>
>
>
>> I got these messages:
>
>
>
>> for the archiv5 in the 'tar' format:
>
>
>
>> pg_restore: [tar archiváY] nalezena poakozená tar hlavi ka v STEX
>> (pYedpokládáno 100, vypo teno 34044) pozice souboru 7750193152
>
>
>
>> sorry, it's in my native locale, but is says "found a corrupted tar
>> header in STEX (expected 100, calculated 34044) file position 7750193152
>
>
>
>> for the bb.dump in the 'custom' format:
>
>
>
>> pg_restore: [vlastní archiváY] unexpected end of file
>
>
>
>> 'vlastní archiv áY' is again in my locale, it should be in English "own
>> archiver"
>
>
>
>> Later I tried to utilize the -I and -i switches of pg_restore to restore
>> data that are in the archive behing the table that was not restored. But
>> got the same error message.
>
>
>
>> The dump was created on postgresql-8.3 8.3.3-1~bpo40+1 from debian
>> backports. I was trying to restore on this version and later on using
>> postgresql-8.4 8.4.5-1~bpo50+1 from debian backports, finally I tried
>> 64bit version of postgresql-8.4 8.4.5-0ubuntu10.10. No change, still the
>> same error messages.
>
>
>
>> I welcome any help and/or hints on this issue as I need to dump and
>> restore several large databases.
>
>
>
>> Regards,
>
>
>> --
>> Mgr. Martin Povolný, soLNet, s.r.o.,
>> +420777714458, martin.povolny@solnet.cz
>
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkzH9DgACgkQxWGfaAgowiJ+yQCfdayVaQFLiI9+/r/eb3YtlQnE
2qEAn1PRqUPaiYPS7xgvscBntFmMeTtT
=PPuW
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: large database: problems with pg_dump and pg_restore
@ 2010-10-31 18:45 Dimitri Fontaine <dimitri@2ndQuadrant.fr>
parent: Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
0 siblings, 0 replies; 9+ messages in thread
From: Dimitri Fontaine @ 2010-10-31 18:45 UTC (permalink / raw)
To: Jehan-Guillaume \(ioguix\) de Rorthais <ioguix@free.fr>; +Cc: mark <dvlhntr@gmail.com>; Martin Povolny <martin.povolny@solnet.cz>; pgsql-admin
"Jehan-Guillaume (ioguix) de Rorthais" <ioguix@free.fr> writes:
> If you can spend some more time using -Fc, then you'll be able to play
> with -l and -L switches of pg_restore to optionally decide what should
> be restored or not (with more control than -n, -t, etc, think about
> excluding slony stuffs as instance)
See also pg_staging that implements some commands for pg_restore
filtering by schema. You can use that feature on its own.
http://github.com/dimitri/pg_staging
http://tapoueh.org/articles/blog/_pg_staging's_bird_view.html
http://tapoueh.org/pgstaging.html
Ok, for now check the docs, later I'll try to blog about some features
in pg_staging that you can use on their own, and maybe some more about
getting up to speed with pg_staging, after all.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-10-31 18:45 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-26 11:41 large database: problems with pg_dump and pg_restore Martin Povolny <martin.povolny@solnet.cz>
2010-10-26 19:31 ` lst_hoe02@kwsoft.de
2010-10-26 21:51 ` Samuel Stearns <SStearns@internode.com.au>
2010-10-26 22:21 ` Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
2010-10-27 02:41 ` mark <dvlhntr@gmail.com>
2010-10-27 09:43 ` Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
2010-10-31 18:45 ` Dimitri Fontaine <dimitri@2ndQuadrant.fr>
2010-10-27 00:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-10-27 09:00 ` Martin Povolny <martin.povolny@solnet.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox