CODEWITHSUNDEEP
postgresqlpostgresql-9.1
Arunkmp
Arunkmp

Reputation: 33

postgres copy database to another server reduces database size

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'));

Upvotes: 2

Views: 602

Answers (2)

Calvin Cheng
Calvin Cheng

Reputation: 36506

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.

Upvotes: 4

Craig Ringer
Craig Ringer

Reputation: 324455

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.

Upvotes: 3

Related Questions