Sounds like you have some on insert triggers that are taking a while to
validate the data. You probably want to look for ways to speed up the
triggers, and reduce the amount of validation that has to be done for every
record if at all possible.
Wes
"Mihai Gheorghiu" <admin@planwithtan.com>@postgresql.org on 11/21/2002
12:45:26 PM
Sent by: pgsql-general-owner@postgresql.org
To: <pgsql-general@postgresql.org>
cc:
Subject: [GENERAL] Migrating data from MSAccess
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.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster