Re: [ADMIN] dbsize pg_dump

2006-04-26 Thread Jim C. Nasby 
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  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [ADMIN] dbsize pg_dump

2006-04-25 Thread mcelroy, tim 
Title: dbsize  pg_dump









Please
disregard this question. Im using
pg_dump F c which compresses the data a it backs it up.



Tim





-Original
Message-
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]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

Re: [ADMIN] dbsize pg_dump

2006-04-25 Thread Jason Minion 
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
[EMAIL PROTECTED]




From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq