Re: large database: problems with pg_dump and pg_restore

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(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Jehan-Guillaume (ioguix) de Rorthais
Sent: Tuesday, October 26, 2010 4:22 PM
To: Martin Povolny
Cc: pgsql-admin(at)postgresql(dot)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(at)postgresql(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)org] *On Behalf Of *Martin Povolny
> *Sent:* Tuesday, 26 October 2010 10:12 PM
> *To:* pgsql-admin(at)postgresql(dot)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(dot)povolny(at)solnet(dot)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-----