0

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).

I did ANALYZE and 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 SELECTs.

Did anyone encounter such a problem? I'm looking for a query that can show me which of the tables needs such a vacuum full.

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.

  • Figured out, that its related to the Index. When i SELECT * FROM tab WHERE source_key='21144110' i get no result (source_key is the first part of a 4 Column unique index and the index is used as explain says) With SELECT * FROM tab WHERE source_key LIKE '21144110%' im getting the right result (this query wont use the Index) – Schors Jun 14 '16 at 10:41
1

To answer myself.

It was somehow related to https://www.postgresql.org/message-id/flat/111D0E27-A8F3-4A84-A4E0-B0FB703863DF%40s24.com#111D0E27-A8F3-4A84-A4E0-B0FB703863DF@s24.com i think.

The OS changed from Debian7 to Debian8, and so the dynamically linked libc6 changed. Had to do a REINDEX on all my Tables with Indexed String Columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.