PostgreSQL: improving pg_dump, pg_restore performance
First check that you are getting reasonable IO performance from your disk setup. Then check that you PostgreSQL installation is appropriately tuned. In particular shared_buffers
should be set correctly, maintenance_work_mem
should be increased during the restore, full_page_writes
should be off during the restore, wal_buffers
should be increased to 16MB during the restore, checkpoint_segments
should be increased to something like 16 during the restore, you shouldn't have any unreasonable logging on (like logging every statement executed), auto_vacuum
should be disabled during the restore.
If you are on 8.4 also experiment with parallel restore, the --jobs option for pg_restore.
Improve pg dump&restore
PG_DUMP | always use format-directory and -j
options
time pg_dump -j 8 -Fd -f /tmp/newout.dir fsdcm_external
PG_RESTORE | always use tuning for postgres.conf and format-directory and -j
options
work_mem = 32MBshared_buffers = 4GBmaintenance_work_mem = 2GBfull_page_writes = offautovacuum = offwal_buffers = -1time pg_restore -j 8 --format=d -C -d postgres /tmp/newout.dir/
Two issues/ideas:
By specifying -Fc, the pg_dump output is already compressed. The compression is not maximal, so you may find some space savings by using "gzip -9", but I would wager it's not enough to warrant the extra time (and I/O) used compressing and uncompressing the -Fc version of the backup.
If you are using PostgreSQL 8.4.x you can potentially speed up the restore from a -Fc backup with the new pg_restore command-line option "-j n" where n=number of parallel connections to use for the restore. This will allow pg_restore to load more than one table's data or generate more than one index at the same time.