Обсуждение: dbsize & pg_dump
Good afternoon,
Probably an easy question but why are the file sizes differ so much between these two tools?
For example:
A backup using pg_dump of our largest DB creates a file 384MB in size
Using the following SQL code utilizing dbsize I get the following:
FIX1=# SELECT D1.pg_size_pretty AS "FIX1_DB_SIZE"
FIX1-# FROM (SELECT pg_size_pretty(database_size('FIX1'))) D1;
FIX1_DB_SIZE
--------------
3832 MB
(1 row)
Is it safe to assume that pg_dump does a compression of the data?
Thanks
Tim
Please disregard this question. I’m using pg_dump –F c which compresses the data a it backs it up.
Tim
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of mcelroy, tim
Sent: Tuesday, April 25, 2006 4:40 PM
To: 'pgsql-admin@postgresql.org'
Subject: [ADMIN] dbsize & pg_dump
Good afternoon,
Probably an easy question but why are the file sizes differ so much between these two tools?
For example:
A backup using pg_dump of our largest DB creates a file 384MB in size
Using the following SQL code utilizing dbsize I get the following:
FIX1=# SELECT D1.pg_size_pretty AS "FIX1_DB_SIZE"
FIX1-# FROM (SELECT pg_size_pretty(database_size('FIX1'))) D1;
FIX1_DB_SIZE
--------------
3832 MB
(1 row)
Is it safe to assume that pg_dump does a compression of the data?
Thanks
Tim
Usually a dump is significantly smaller than a live database due to space taken up by indexes and discarded tuples from MVCC. If it's significantly smaller you may also want to take a look at your vacuuming procedure. But I'm not sure database_size() is. Jason Minion jason.minion@sigler.com ________________________________ From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of mcelroy, tim Sent: Tuesday, April 25, 2006 4:06 PM To: 'pgsql-admin@postgresql.org' Subject: Re: [ADMIN] dbsize & pg_dump Please disregard this question. I'm using pg_dump -F c which compresses the data a it backs it up. Tim -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of mcelroy, tim Sent: Tuesday, April 25, 2006 4:40 PM To: 'pgsql-admin@postgresql.org' Subject: [ADMIN] dbsize & pg_dump Good afternoon, Probably an easy question but why are the file sizes differ so much between these two tools? For example: A backup using pg_dump of our largest DB creates a file 384MB in size Using the following SQL code utilizing dbsize I get the following: FIX1=# SELECT D1.pg_size_pretty AS "FIX1_DB_SIZE" FIX1-# FROM (SELECT pg_size_pretty(database_size('FIX1'))) D1; FIX1_DB_SIZE -------------- 3832 MB (1 row) Is it safe to assume that pg_dump does a compression of the data? Thanks Tim
On Tue, Apr 25, 2006 at 04:47:53PM -0500, Jason Minion wrote: > Usually a dump is significantly smaller than a live database due to > space taken up by indexes and discarded tuples from MVCC. If it's > significantly smaller you may also want to take a look at your vacuuming > procedure. Between excluding the database overhead (mostly tuple headers), excluding indexes, and compression, getting a 10x reduction in database size isn't unexpected. Using pg_dumpall and bzip2 the databases on http://stats.distributed.net go from 41G down to 2G. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461