I'm using pg_bulkload to load large amounts of CSV data into a postgres
database hourly.
This database is replicated to a second node.
Whenever a bulk load happens the indexes on the updated tables on the secondary
node corrupt and are unusable until a reindex is run on the primary node. I
get the error below on node 2:
ERROR: index "tablename" contains unexpected zero page at block 0
SQL state: XX002
Hint: Please REINDEX it.
I'm assuming that this is because of the way pg_bulkload builds the index on
the primary, and possibly has something to do with the way pg_bulkload
overwrites rows in the event of a constraint violation, but at the same time if
something works on the primary shouldn't the replicated node be able to process
the WAL log?
I've tried this on 9.1.6 and 9.1.5 on RHEL 6.3 with pg_bulkload build
3.1.1-1.pg91.rhel6 and it happens every time.
Does anyone have any experience in this area or advice they could give? If you
can point out something stupid I'm doing that would be very welcome :)
Thanks,
James