Problems with upgrade from 9.6 to 10 or 11 (column pg_stat_replication.sent_location does not exist) - Mailing list pgsql-admin

Hello,

while trying an upgrade from 9.6 to 10 (or 11, same issue) we  got errors about missing columns

in the pg_stat_replication view.

…

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 205; 1259 32960 VIEW pg_stat_replication ggdbpadm

pg_restore: [archiver (db)] could not execute query: ERROR:  column pg_stat_replication.sent_location does not exist

LINE 24:     "pg_stat_replication"."sent_location",

…

Is that somehow a (probably known) but? I have no really explanation for this.

Complete log output below or in the attachements.

Thanks and best regards

Dirk

LISTING:

-----------------------------------------------

command: "/DBA/postgresql/10.latest/bin/pg_dump" --host /DBA/nest/postgresql/DBPIOWS05/logs --port 50432 --username ggdbpadm --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="pg_upgrade_dump_1.custom" 'dbname=template1' >> "pg_upgrade_dump_1.log" 2>&1

command: "/DBA/postgresql/10.latest/bin/pg_restore" --host /DBA/nest/postgresql/DBPIOWS05/logs --port 50432 --username ggdbpadm --exit-on-error --verbose --dbname 'dbname=template1' "pg_upgrade_dump_1.custom" >> "pg_upgrade_dump_1.log" 2>&1

pg_restore: connecting to database for restore

pg_restore: creating pg_largeobject "pg_largeobject"

pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata"

pg_restore: creating COMMENT "DATABASE "template1""

pg_restore: creating SCHEMA "postgres_exporter"

pg_restore: creating SCHEMA "public"

pg_restore: creating COMMENT "SCHEMA "public""

pg_restore: creating VIEW "postgres_exporter.pg_stat_activity"

pg_restore: creating VIEW "postgres_exporter.pg_stat_replication"

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 205; 1259 32960 VIEW pg_stat_replication ggdbpadm

pg_restore: [archiver (db)] could not execute query: ERROR:  column pg_stat_replication.sent_location does not exist

LINE 24:     "pg_stat_replication"."sent_location",

             ^

    Command was:

-- For binary upgrade, must preserve pg_type oid

SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('32962'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid

SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('32961'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_class oids

SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('32960'::pg_catalog.oid);

CREATE VIEW "postgres_exporter"."pg_stat_replication" AS

SELECT "pg_stat_replication"."pid",

    "pg_stat_replication"."usesysid",

    "pg_stat_replication"."usename",

    "pg_stat_replication"."application_name",

    "pg_stat_replication"."client_addr",

    "pg_stat_replication"."client_hostname",

    "pg_stat_replication"."client_port",

    "pg_stat_replication"."backend_start",

    "pg_stat_replication"."backend_xmin",

    "pg_stat_replication"."state",

    "pg_stat_replication"."sent_location",

    "pg_stat_replication"."write_location",

    "pg_stat_replication"."flush_location",

    "pg_stat_replication"."replay_location",

    "pg_stat_replication"."sync_priority",

    "pg_stat_replication"."sync_state"

   FROM "pg_stat_replication";

command: "/DBA/postgresql/11.latest/bin/pg_dump" --host /DBA/nest/postgresql/DBPIOWS05/logs --port 50432 --username ggdbpadm --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="pg_upgrade_dump_1.custom" 'dbname=template1' >> "pg_upgrade_dump_1.log" 2>&1

command: "/DBA/postgresql/11.latest/bin/pg_restore" --host /DBA/nest/postgresql/DBPIOWS05/logs --port 50432 --username ggdbpadm --clean --create --exit-on-error --verbose --dbname postgres "pg_upgrade_dump_1.custom" >> "pg_upgrade_dump_1.log" 2>&1

pg_restore: connecting to database for restore

pg_restore: dropping DATABASE PROPERTIES template1

pg_restore: dropping DATABASE template1

pg_restore: creating DATABASE "template1"

pg_restore: connecting to new database "template1"

pg_restore: connecting to database "template1" as user "ggdbpadm"

pg_restore: creating COMMENT "DATABASE "template1""

pg_restore: creating DATABASE PROPERTIES "template1"

pg_restore: connecting to new database "template1"

pg_restore: connecting to database "template1" as user "ggdbpadm"

pg_restore: creating pg_largeobject "pg_largeobject"

pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata"

pg_restore: creating SCHEMA "postgres_exporter"

pg_restore: creating VIEW "postgres_exporter.pg_stat_activity"

pg_restore: creating VIEW "postgres_exporter.pg_stat_replication"

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 205; 1259 32960 VIEW pg_stat_replication ggdbpadm

pg_restore: [archiver (db)] could not execute query: ERROR:  column pg_stat_replication.sent_location does not exist

LINE 24:     "pg_stat_replication"."sent_location",

             ^

    Command was:

-- For binary upgrade, must preserve pg_type oid

SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('32962'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid

SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('32961'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_class oids

SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('32960'::pg_catalog.oid);

CREATE VIEW "postgres_exporter"."pg_stat_replication" AS

SELECT "pg_stat_replication"."pid",

    "pg_stat_replication"."usesysid",

    "pg_stat_replication"."usename",

    "pg_stat_replication"."application_name",

    "pg_stat_replication"."client_addr",

    "pg_stat_replication"."client_hostname",

    "pg_stat_replication"."client_port",

    "pg_stat_replication"."backend_start",

    "pg_stat_replication"."backend_xmin",

    "pg_stat_replication"."state",

    "pg_stat_replication"."sent_location",

    "pg_stat_replication"."write_location",

    "pg_stat_replication"."flush_location",

    "pg_stat_replication"."replay_location",

    "pg_stat_replication"."sync_priority",

    "pg_stat_replication"."sync_state"

   FROM "pg_stat_replication";