Обсуждение: pg_dump issues
Hi All,
I have a database with only one schema with 5 tables
nspname | relname | size
---------+---------------------+---------
sch | job1 | 211 MB
sch | job2 | 5611 MB
sch | job3 | 6584 kB
sch | job4 | 89 MB
sch | job5 | 39 MB
Total sum of sizes of the table is less than 6 GB.
When I do a pg_dump of this database it is 20GB (I also did a pg_dump of only schema data and the size was still the same). I am using postgres 8.4.1 version.
Could any one explain why the pg_dump size of the file is 3 times the size of the tables?
Thanks
Deepak
Hi All,
I have a database with only one schema with 5 tables
nspname | relname | size
---------+---------------------+---------
sch | job1 | 211 MB
sch | job2 | 5611 MB
sch | job3 | 6584 kB
sch | job4 | 89 MB
sch | job5 | 39 MB
Total sum of sizes of the table is less than 6 GB.
When I do a pg_dump of this database it is 20GB (I also did a pg_dump of only schema data and the size was still the same). I am using postgres 8.4.1 version.
Could any one explain why the pg_dump size of the file is 3 times the size of the tables?
Thanks
Deepak
On Tue, Feb 2, 2010 at 4:07 PM, DM <dm.aeqa@gmail.com> wrote: > Hi All, > I have a database with only one schema with 5 tables > nspname | relname | size > ---------+---------------------+--------- > sch | job1 | 211 MB > sch | job2 | 5611 MB > sch | job3 | 6584 kB > sch | job4 | 89 MB > sch | job5 | 39 MB > Total sum of sizes of the table is less than 6 GB. > When I do a pg_dump of this database it is 20GB (I also did a pg_dump of > only schema data and the size was still the same). I am using postgres 8.4.1 > version. > Could any one explain why the pg_dump size of the file is 3 times the size > of the tables? Your database is likely full of easily compressed text, which postgresql automagically compresses inline when storing it and decompresses when it retrieves it. To see if this is the case, try zipping or gzipping or bzip2ing the dump file and see if it comes out to something less than or about 6Gigs.
DM a écrit : > Hi All, > > I have a database with only one schema with 5 tables > > nspname | relname | size > ---------+---------------------+--------- > sch | job1 | 211 MB > sch | job2 | 5611 MB > sch | job3 | 6584 kB > sch | job4 | 89 MB > sch | job5 | 39 MB > > Total sum of sizes of the table is less than 6 GB. > > When I do a pg_dump of this database it is 20GB (I also did a pg_dump of > only schema data and the size was still the same). I am using postgres > 8.4.1 version. > > Could any one explain why the pg_dump size of the file is 3 times the > size of the tables? Because it doesn't only contains the fields values but also the full set of commands to restore (remake) it identical (and it's text). Also, if you use UTF-8, some chars take more than one byte per each. -- Call on God, but row away from the rocks. -- Indian proverb
You were right, after zipping the dump file it came out to 6.9G
Thanks for your help.
thanks
Deepak
On Tue, Feb 2, 2010 at 3:20 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
Your database is likely full of easily compressed text, whichOn Tue, Feb 2, 2010 at 4:07 PM, DM <dm.aeqa@gmail.com> wrote:
> Hi All,
> I have a database with only one schema with 5 tables
> nspname | relname | size
> ---------+---------------------+---------
> sch | job1 | 211 MB
> sch | job2 | 5611 MB
> sch | job3 | 6584 kB
> sch | job4 | 89 MB
> sch | job5 | 39 MB
> Total sum of sizes of the table is less than 6 GB.
> When I do a pg_dump of this database it is 20GB (I also did a pg_dump of
> only schema data and the size was still the same). I am using postgres 8.4.1
> version.
> Could any one explain why the pg_dump size of the file is 3 times the size
> of the tables?
postgresql automagically compresses inline when storing it and
decompresses when it retrieves it. To see if this is the case, try
zipping or gzipping or bzip2ing the dump file and see if it comes out
to something less than or about 6Gigs.
On 02/02/2010 03:41 PM, DM wrote: > You were right, after zipping the dump file it came out to 6.9G Also check out the "custom" pg_dump format (pg_dump -Fc ...), which is compressed. There are caveats regarding portability across PG versions with the custom format, which you may want to research. -Reece