I'm running Postgres 9.0 on a relatively big (10TB) and old Database. I must move that database to new server hardware, so I copied the whole
$PGDATA directory via
rsync of a LVM Snapshot and used archive/restore Commands to get all the changes after the copy process. I switched to the new server using the trigger_file.
Everything looked good so far, but after a few hours I'm having some tables where data is not showing up in
SELECTs (I think that was instant after copying, but have no evidence).
VACUUM on the table - no data there. But when I do a
VACUUM FULL on the table, the data appears.
I couldn't find anything comparable in the postgres documentation except the Transaction Id wraparound. The autovacuum was disabled for 1/2 year at the old server.
A week ago I activated it and autovacuum did its job as I saw.
On the old database server, the data from my test table is is returned by
Did anyone encounter such a problem? I'm looking for a query that can show me which of the tables needs such a
Why cannot I copy the data directory at the file system level is also hard to understand for me at the moment. Did I miss anything from the documentation that needs to be done after using the Trigger File?
I don't think it's relevant, but the version at the old server was 9.0.18 and at the new one 9.0.23.