Postgres DB size problem

tuxslonik · #1 (**permalink**) 07-27-11, 09:44

I do have problem in Size of PostgreSQL DB.

The real size of the version DB is 6.05 GB(6195 MB)

Code:

mydb=# SELECT pg_size_pretty(pg_database_size('mydb'));
 pg_size_pretty
----------------
 6195 MB
(1 row)

After that I took the dump of the db in tar format,but the size of the db is 11GB.

Code:

[root@myserver]# du -hs mydb.out.tar
11G     mydb.out.tar

The actual size of DB and the tar size is differ by 5GB(appx).
I thought of it is System problem and I tried it in different system.There also the same problem.

Code:

mydb1=# SELECT pg_size_pretty(pg_database_size('mydb'));
 pg_size_pretty
----------------
 6195 MB
(1 row)

Code:

[root@server]# du -hs mydb.out.tar
11G     mydb.out.tar

I used pg_dump command to dump the db.

Code:

pg_dump  mydb|gzip >mydb.out.tar.gz

[root@myserver]# du -hs mydb.out.tar.gz
[root@myserver]# gunzip du -hs mydb.out.tar.gz

Can anyone please explain why it is and Please instruct to solve the problem.

someidiot · #2 (**permalink**) 08-03-11, 19:39

By default pg_dump creates a plain text file with SQL instructions to recreate your database. It will always be bigger than the database's binary data files.

If you don't need an SQL file, you can use pg_dump -Fc to create a binary file which is smaller but can only be imported via pg_restore.

This might be useful: Compare pg_dump and gzip compression | IAmA DBA AMA

Internet Services

Web Development

Digital Marketing

Consumer Tech