Sample Header Ad - 728x90

Postgres Upgrading Errors due to table not existing

0 votes
1 answer
396 views
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.
Asked by rdbmsNoob (459 rep)
Nov 22, 2021, 12:48 PM
Last activity: Mar 20, 2025, 04:04 PM