Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] database size changed after restoring using pg_restore

[GENERAL] database size changed after restoring using pg_restore

From
Dylan Luong
Date:

Hi

I am upgrading some databases from a PostgreSQL 9.2 server to a new PostgreSQL 9.6 server.

 

I used pg_dump and pg_restore for the upgrade.

 

This is the command I used to dump the database. I ran the command from the 9.6 server.

pg_dump -h 9.2server -j 16 --format=directory -f /pg_backup/backup/dump/mydb-20171108.dmp -U postgres -W mydb

 

This is the command I used to restore the database on the 9.6server.

pg_restore -j 16 -C -d postgres /pg_backup/backup/dump/mydb-20171108.dmp

 

Everything appears ok, the dump and restore completed without and errors.

 

But when I listed the size of the database (postgres=# \l+) between the 9.2 and the upgraded 9.6, they were different.

 

on 9.2 it was 3776 MB

on 9.6 it was 1570 MB

 

I also did a few more databases using the same steps and they all appeared to be smaller. Is that normal?

 

Regards

Dylan

Re: [GENERAL] database size changed after restoring usingpg_restore

From
Andreas Joseph Krogh
Date:
På onsdag 08. november 2017 kl. 23:59:40, skrev Dylan Luong <Dylan.Luong@unisa.edu.au>:

Hi

I am upgrading some databases from a PostgreSQL 9.2 server to a new PostgreSQL 9.6 server.

 

I used pg_dump and pg_restore for the upgrade.

 

This is the command I used to dump the database. I ran the command from the 9.6 server.

pg_dump -h 9.2server -j 16 --format=directory -f /pg_backup/backup/dump/mydb-20171108.dmp -U postgres -W mydb

 

This is the command I used to restore the database on the 9.6server.

pg_restore -j 16 -C -d postgres /pg_backup/backup/dump/mydb-20171108.dmp

 

Everything appears ok, the dump and restore completed without and errors.

 

But when I listed the size of the database (postgres=# \l+) between the 9.2 and the upgraded 9.6, they were different.

 

on 9.2 it was 3776 MB

on 9.6 it was 1570 MB

 

I also did a few more databases using the same steps and they all appeared to be smaller. Is that normal?

Yep.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 

Re: [GENERAL] database size changed after restoring using pg_restore

From
John R Pierce
Date:
On 11/8/2017 2:59 PM, Dylan Luong wrote:

Hi

I am upgrading some databases from a PostgreSQL 9.2 server to a new PostgreSQL 9.6 server.

I used pg_dump and pg_restore for the upgrade.  .....

But when I listed the size of the database (postgres=# \l+) between the 9.2 and the upgraded 9.6, they were different.

on 9.2 it was 3776 MB
on 9.6 it was 1570 MB I also did a few more databases using the same steps and they all appeared to be smaller. Is that normal?


yes.  a freshly restored database will be all contiguous with no embedded free tuples left over from operations.   databases that get updates (or inserts/deletes) tend to bloat.


-- 
john r pierce, recycling bits in santa cruz