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