On Wed, 6 Jul 2016, 1:39 a.m. J. Cassidy, <sean@xxxxxxxxxxxx> 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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
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
