postgres copy database to another server reduces database size

Question

Installed postgres 9.1 in both the machine.

Initially the DB size is 7052 MB then i used the following command for copy to another server.

pg_dump -C dbname | bzip2 | ssh  remoteuser@remotehost "bunzip2 | psql dbname"

After successfully copies, In destination machine i check size it shows 6653 MB.

then i checked for table count its same.

Has there been data loss? Is there missing data?

Note: Two machines have same hardware and software configuration.

i used:

SELECT pg_size_pretty(pg_database_size('dbname'));

Calvin Cheng · Accepted Answer · 2014-02-25 01:06:11Z

4

One of the PostgreSQL's most sophisticated features is so called Multi-Version Concurrency Control (MVCC), a standard technique for avoiding conflicts between reads and writes of the same object in database. MVCC guarantees that each transaction sees a consistent view of the database by reading non-current data for objects modified by concurrent transactions. Thanks to MVCC, PostgreSQL has great scalability, a robust hot backup tool and many other nice features comparable to the most advanced commercial databases.

Unfortunately, there is one downside to MVCC, the databases tend to grow over time and sometimes it can be a problem. In recent versions of PostgreSQL there is a separate server process called the autovacuum daemon (pg_autovacuum), whose purpose is to keep the database size reasonable. It does that by trying to recover reusable chunks of the database files. Still, there are many scenarios that will force the database to grow, even if the amount of the useful data in it doesn't really change. That happens typically if you have lots of UPDATE and/or DELETE statements in the applications that are using the database.

When you do a COPY, you recover extraneous space and so your copied DB appears smaller.

answered Feb 25, 2014 at 1:06

Calvin Cheng

36.7k40 gold badges121 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

In addition to MVCC, a newly built index is smaller than one that gets progressively built.

Craig Ringer · Accepted Answer · 2014-02-25 01:05:13Z

3

That looks normal. Databases are often smaller after restore, because a newly created b-tree index is more compact than one that's been progressively built by inserts. Additionally, UPDATEs and DELETEs leave empty space in the tables.

So you have nothing to worry about. You'll find that if you diff an SQL dump from the old DB and a dump taken from the just-restored DB, they'll be the same except for comments.

answered Feb 25, 2014 at 1:05

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Collectives™ on Stack Overflow

postgres copy database to another server reduces database size

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related