-----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@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Jehan-Guillaume (ioguix) de Rorthais > Sent: Tuesday, October 26, 2010 4:22 PM > To: Martin Povolny > Cc: pgsql-admin@xxxxxxxxxxxxxx > Subject: Re: 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@xxxxxxxxxxxxxx >> [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] *On Behalf Of *Martin Povolny >> *Sent:* Tuesday, 26 October 2010 10:12 PM >> *To:* pgsql-admin@xxxxxxxxxxxxxx >> *Subject:* 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@xxxxxxxxx > > -----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----- -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
