Hi,
I'm running on Redhat 7.2 with postgresql 7.3.2 and I have two schema in
the same database 'db' and 'db_dev'. Both contain a set of >20 tables for
a total of less than 50 Mb of data each (on the order of 50k rows in
total). Once in a while (often these days!), I need to synchronize the
dev version from the production 'db'. Currently, I do this by setting
constraints to deferred, deleting everything in db_dev, then issue a serie
of insert ... select ... to copy data from each table in db to the
equivalent table in db_dev.
This approach used to run in less than 30 seconds in MySQL, but in
PostgreSQL it currently takes around 30 minutes. The postmaster process
is running at 100% cpu all the time. I enclosed all the delete statement
in one transaction and all the insert statements in a second transaction.
All the time is taken at the commit of both transaction.
Is there a more straightforward way to synchronize a development
database to a production one? Is there anyway to increase the performance
of this delete/insert combination? I've got indexes and constraints on
most tables, could that be the problem? At some point in the future, I
will also need to make a copy of a whole schema ('db' into 'db_backup'),
what would be an efficient way to do that?
These are the parameters I've adjusted in the postgresql.conf:
max_connections = 16
shared_buffers = 3000
max_fsm_relations = 2000
max_fsm_pages = 20000
sort_mem = 20000
vacuum_mem = 20000
effective_cache_size = 15000
And this is the memory state of the machine:
slemieux@neptune> free
total used free shared buffers cached
Mem: 2059472 2042224 17248 24768 115712 1286572
-/+ buffers/cache: 639940 1419532
Swap: 2096440 490968 1605472
thanks,
--
Sebastien Lemieux
Bioinformatics, post-doc
Elitra-canada