I'm at the stage to rehearse data migration from MSAccess to PG. The PG db
actually consolidates 4 MSAccess dbs, with all the possible data validations
and integrity in place. I have some queries over the Access tables which
then write the data to the ODBC-linked tables in the PG db. Data coming from
Access cannot be fully trusted, so it must be loaded with regular inserts,
in order for PG to perform all the validations.
The problem is speed. If I run the query over the Access db only to view the
records, it takes a couple of seconds. When it also writes to the PG db, it
takes a couple of hours. The following is the output of a vmstat 60 2 on the
PG server (PG7.2.1 on RH7.3) while appending records:
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us sy
id
1 1 1 19496 10096 176188 617472 0 0 7 3 1 1 4 1
4
4 0 0 19496 10036 176196 617532 0 0 0 661 244 6560 78 20
2
Please comment. Is the PG server the bottleneck? Is there a faster way to
perform this task?
Thank you all.