Windows – Why does pg_restore take so much longer on Ubuntu than Windows

postgresqlUbuntuwindows

I generated a dump file of 21 MB or so:

pg_dump --format=tar --verbose --file=database.backup mydatabase

When I import this file on windows doing:

pg_restore --dbname mydatabase --verbose database.backup

It takes 1 hour to be done.

Doing the same on Ubuntu 10.10 64bits boxes, takes about 7 hours!

Of course I'm talking about the same hardware specs (Dell Studio XPS). Same RAM, CPU, etc.

In both cases I'm using out of box configs for PostgreSQL 8.4.7.

Perhaps the distros configuration is different…
Perhaps some optimization that just windows distro is doing?

Extra info:
On Windows 7 -> NTFS. On Ubuntu 10.10 -> ext4

When I do

pg_dump --format=tar --verbose --file=workspace/work/dumps/loaded.backup mydb

I takes only 5 seconds! If I restore on an empty new db doing:

pg_restore --dbname mydb-2 --verbose workspace/work/dumps/loaded.backup

I takes only 10 seconds. (Problem solved?… almost)
It seems the db guys exported the original dump using different options. Perhaps –inserts option?

The big difference between Windows and Ubuntu using the original dump is still bothering my mind. Any thoughts on this?

Best Answer

Even one hour is very long for a small dump file of 21 MB. We are restoring databases of 2 GB compressed dump file in about 30 minutes but we might have better hardware ;-)

What you should read first:

http://www.postgresql.org/docs/8.4/static/populate.html

It is all about your problem. It tells you how to poulate a database fast.

Aditional tips:

  • First enable logging of all statements with duration time and see what's going on
  • Increase shared_buffers, default on ubuntu 10.10 is only 24 MB, see http://www.postgresql.org/docs/8.4/static/kernel-resources.html#SYSVIPC for configuring your linux system to accept higher values
  • use --format=custom or -Fc for dumping. It's the best choice
  • you can run pg_restore on multiple CPU with "-j" but i guess you have other problems that getting the last bits of performance

For further information:

Related Topic