Re: pg_dump fundenental question

On Wed, 6 Jul 2016, 1:39 a.m. J. Cassidy, <sean(at)jdcassidy(dot)eu> wrote:

> Francisco,
>
> appreciate the tips/hints.
>
> My input (source) DB is 1TB in size, using the options as stated in my
> original email (i.e. no compression it would seem) the output file size is
> "only" 324GB.
>

It would be because of indexes do not take any space in backup, since they
are just an SQL statement. Some space might be saved because of bloats in
your db (which are not copied over in the sql backup).

I presume all of the formatting/indices have been ommited. As I said
> before, I can browse the backup file with less/heat/cat/tail etc.
>

Ofcourse you can but consider using custom or directory format (what is
your version? It is a good practice to state that along with your query)
and use -j to specify multiple threads to copy the data. Use -Z for
compression level.

pg_dump -d prod_db -Fd -j6 -f /mybackups/20160706-prod-db -Z9

If ever you want to browse the backup or look a specific table from the
backup, pg_restore with -f will be helpful e.g

pg_restore -f emp-from-backup.sql -Fd /mybackups/20160706-prod-db

> Regards,
>
> Henk
>
>
>
> On Tue, Jul 5, 2016 at 4:54 PM, J. Cassidy <sean(at)jdcassidy(dot)eu> wrote:
> > I have hopefully an "easy" question.
> > If I issue the pg_dump command with no switches or options i.e.
> > /usr/local/pgsql/bin/pg_dump -v dbabcd > /data3TB/Backup/dbabcd
> > Is their any "default" compression involved or not? Does pg_dump talk to
> > zlib during the dump process given that I have not specified any
> compression
> > on the > command line? (see above).
>
> IIRC no options means you are requesting an SQL-script. Those are not
> compressed, just pipe them through your favorite compressor. ( In a
> later message you stated you were in Linux and had a 324Gb file, and
> could head/tail it, have you done so? ).
>
> > Your considered opinions would be much appreciated.
>
> OTOH, with those big backup I would recommend using custom format (
> -Fc ), it's much more flexible ( andyou can have the sql script but
> asking pg_restore to generate it if you need it, but not the other way
> round ).
>
>
> Francisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>

--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com