Обсуждение: DB Dump Size
Hi all, I am curious as to why a pg dump of database "name" is 2.9gig. But is measured at 1.66gig by: SELECT pg_database_size(pg_database.datname) AS db_size FROM pg_database WHERE pg_database.datname='name' ; This dump was about 1 gig around 12 months ago. I am performing a monthly vacuum full on the database and a nightly vacuum all Thanks. Steve.
On Thu, Aug 14, 2008 at 12:06:53PM +1000, steve@outtalimits.com.au wrote: > Hi all, > > I am curious as to why a pg dump of database "name" is 2.9gig. But is > measured at 1.66gig by: > SELECT pg_database_size(pg_database.datname) AS db_size FROM pg_database > WHERE pg_database.datname='name' ; > > This dump was about 1 gig around 12 months ago. Which options do you use for pg_dump? And what version of PosgreSQL are you running? In general it's not that strange for an uncompressed dump to be larger than the database size, plain SQL dumps are much less space efficient than a DBMS can be when it stores the data on disk. But of course, there also indices to consider. Have you tried pg_dump -Fc? > I am performing a monthly vacuum full on the database and a nightly vacuum > all That should only impact the pg_database_size.
Вложения
G'day, I am just running a straight pg_dump with no options. With the -Fc option the DB size is reduced to about: 700MB Is the -Fc a compressed format? Are there any limitations or side effects to using this output, is it slower?? etc etc. Can this output be restored via the normal method of: psql dbname < sql.dump Thanks. Steve On Thu, 14 Aug 2008 10:30:07 +0200, Thomas Jacob <jacob@internet24.de> wrote: > On Thu, Aug 14, 2008 at 12:06:53PM +1000, steve@outtalimits.com.au wrote: >> Hi all, >> >> I am curious as to why a pg dump of database "name" is 2.9gig. But is >> measured at 1.66gig by: >> SELECT pg_database_size(pg_database.datname) AS db_size FROM pg_database >> WHERE pg_database.datname='name' ; >> >> This dump was about 1 gig around 12 months ago. > > Which options do you use for pg_dump? And what version of PosgreSQL are > you running? > > In general it's not that strange for an uncompressed > dump to be larger than the database size, plain SQL > dumps are much less space efficient than a DBMS can > be when it stores the data on disk. But of course, there > also indices to consider. > > Have you tried pg_dump -Fc? > >> I am performing a monthly vacuum full on the database and a nightly > vacuum >> all > > That should only impact the pg_database_size.
On Fri, Aug 15, 2008 at 09:26:18AM +1000, steve@outtalimits.com.au wrote: > G'day, > > I am just running a straight pg_dump with no options. > With the -Fc option the DB size is reduced to about: 700MB > > Is the -Fc a compressed format? Are there any limitations or side effects > to using this output, is it slower?? etc etc. Can this output be restored > via the normal method of: psql dbname < sql.dump Yes -Fc is a compressed format. From the man page of pg_dump: "custom: Output a custom archive suitable for input into pg_restore. This is the most flexible format in that it allows reordering of loading data as well as object definitions. This format is also compressed by default." Whether or not the dump takes less or more time probably depends on your spare CPU vs your spare IO capacity, in general I'd say it isn't slower, given today's CPU, but I haven't measured this. For restoring the DB you need to run the dump through pg_restore first, which gives you various dials to control what to restore and how, and then feed it into psql as usual. This gives you more flexibility than with the plain SQL dump. Also, you can recreate a plain SQL dump with pg_restore should you require one. One drawback could be that if you lose your DB and something also damages parts of your dumps, a compressed format might mean you can't restore any data at all, vs. at least some data with plain SQL dump. But that's not a very likely scenario, is it? ;-) Also, you can't always restore from an -Fc dump, if your pg_restore version is much older than the one that particular dump was created with. For instance, trying to restore an 8.3.3 dump with a 7.4.19 pg_restore I get: "pg_restore: [archiver] unsupported version (1.10) in file header" When I use a 8.1.11 pg_restore, it seems to work fine. But that's hardly a suprising result. Thomas
Вложения
Nice, that has cleared it up. I am on 8.1 also. On my test box, a standard dump took 6m 26sec & a -Fc dump took 11min 2sec. That's not a great difference, but the size difference is quite noticeable. Thanks for your help. Steve. On Fri, 15 Aug 2008 02:15:25 +0200, Thomas Jacob <jacob@internet24.de> wrote: > On Fri, Aug 15, 2008 at 09:26:18AM +1000, steve@outtalimits.com.au wrote: >> G'day, >> >> I am just running a straight pg_dump with no options. >> With the -Fc option the DB size is reduced to about: 700MB >> >> Is the -Fc a compressed format? Are there any limitations or side > effects >> to using this output, is it slower?? etc etc. Can this output be > restored >> via the normal method of: psql dbname < sql.dump > > Yes -Fc is a compressed format. From the man page of pg_dump: > > "custom: Output a custom archive suitable for input into pg_restore. > This is the most flexible format in that it allows reordering of loading > data as well as object definitions. This format is also compressed by > default." > > Whether or not the dump takes less or more time probably depends > on your spare CPU vs your spare IO capacity, in general I'd > say it isn't slower, given today's CPU, but I haven't measured > this. > > For restoring the DB you need to run the dump through pg_restore first, > which gives you various dials to control what to restore > and how, and then feed it into psql as usual. This gives you > more flexibility than with the plain SQL dump. Also, you > can recreate a plain SQL dump with pg_restore should > you require one. > > One drawback could be that if you lose your DB and something > also damages parts of your dumps, a compressed format might mean you can't > restore any data at all, vs. at least some data > with plain SQL dump. But that's not a very likely > scenario, is it? ;-) > > Also, you can't always restore from an -Fc dump, if > your pg_restore version is much older than the one that > particular dump was created with. For instance, trying > to restore an 8.3.3 dump with a 7.4.19 pg_restore I > get: > > "pg_restore: [archiver] unsupported version (1.10) in file header" > > When I use a 8.1.11 pg_restore, it seems to work fine. > But that's hardly a suprising result. > > Thomas
On Fri, Aug 15, 2008 at 11:09:02AM +1000, steve@outtalimits.com.au wrote: > Nice, that has cleared it up. > > I am on 8.1 also. > > On my test box, a standard dump took 6m 26sec & a -Fc dump took 11min 2sec. > That's not a great difference, but the size difference is quite noticeable. You might use --compress=6 or even --compress=1 to lower the impact of compression and try again. On the other hand, 11 minutes is not a big deal for dumping a whole DB... Tino. -- "What we nourish flourishes." - "Was wir nähren erblüht." www.craniosacralzentrum.de www.forteego.de
I am also curious as to why an SQL dump from the production server would come out to 2.8G but a dump of an exact replica on a test box would come out to 3.0G. What determines the size and makeup of an SQL dump? Cheers. Steve. On Fri, 15 Aug 2008 12:24:32 +0200, Tino Schwarze <postgresql@tisc.de> wrote: > On Fri, Aug 15, 2008 at 11:09:02AM +1000, steve@outtalimits.com.au wrote: >> Nice, that has cleared it up. >> >> I am on 8.1 also. >> >> On my test box, a standard dump took 6m 26sec & a -Fc dump took 11min > 2sec. >> That's not a great difference, but the size difference is quite > noticeable. > > You might use --compress=6 or even --compress=1 to lower the impact of > compression and try again. On the other hand, 11 minutes is not a big > deal for dumping a whole DB... > > Tino. > > -- > "What we nourish flourishes." - "Was wir nähren erblüht." > > www.craniosacralzentrum.de > www.forteego.de > >
> I am also curious as to why an SQL dump from the production > server would > come out to 2.8G but a dump of an exact replica on a test > box would come > out to 3.0G. What determines the size and makeup of an SQL > dump? Why not repeat the dump without any compression then compare them with a file comparison tool? Send instant messages to your online friends http://uk.messenger.yahoo.com