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
Last activity: Mar 20, 2025, 04:04 PM