I am trying to do an upgrade from Postgres 9.6 to 12, I have done this in the past but in this instance I am having difficulty.

When running the pg_upgrade command followed by the parameters:

/usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-9.6/bin --new-bindir /usr/pgsql-12/bin --old-datadir /var/lib/pgsql/9.6/data --new-datadir /var/lib/pgsql/12/data --link

On doing a check prior nothing comes up as an issue, when i remove the check flag and run I get the following:

command: "/usr/pgsql-12/bin/pg_dump" --host /var/lib/pgsql --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="pg_upgrade_dump_2554867.custom" 'dbname=titan' >> "pg_upgrade_dump_2554867.log" 2>&1
pg_dump: error: query failed: ERROR:  relation "staging.stg_fct_game_stats" does not exist
pg_dump: error: query was: LOCK TABLE "staging"."stg_fct_game_stats" IN ACCESS SHARE MODE

What is odd about this is when I look at the tables in this schema after setting my search_path. I run \dt and cannot see this table in that schema.

I then run:

SELECT n.nspname AS schemaname, c.relname, c.relkind
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  relname like '%stg_fct_game_stats';

This returns a result:

schemaname  relname relkind
staging stg_fct_game_stats  r

If I remove the like and have it as = 'stg_fct_game_stats' it would not appear.

I suspect there is some corruption but I cannot even drop the table as it cannot see this table?

Any ideas, I am happy to remove the table but errors:

 DROP TABLE staging.stg_fct_game_stats;
ERROR:  table "stg_fct_game_stats" does not exist

Any help is much appreciated.

  • select relname, encode(relname::bytea, 'hex') from pg_class where relname like '%stg_fct_game_stats'; please share the result of this query.
    – Melkij
    Nov 22 at 13:09
  • @Melkij returns nothing....
    – rdbmsNoob
    Nov 22 at 14:25
  • @Melkij If I remove the relname, encode(relname::bytea, 'hex') and just do a select * on the pg_class table it returns that relname.
    – rdbmsNoob
    Nov 22 at 14:30

Possibly the index on (relname, relnamespace) in pg_catalog.pg_class is in a corrupt state. That would explain why a sequential scan (with the LIKE clause) finds an entry that an index-based lookup can't find.

Generally the advice in that situation is to REINDEX pg_catalog.pg_class;

If the problem is limited to that particular table, that might be sufficient, at least to bring the database to a state where it can be dumped.

Make sure to not change the locales of your operating system without reindexing the text columns in your databases (check out https://wiki.postgresql.org/wiki/Locale_data_changes for why). In recent versions of Postgres, the columns of type name in pg_catalog are not affected by this, but I'm not sure for version 9.6

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.