[20.8.0] failure during pg_upgrade from PostgreSQL 12 to 13 because of removed sql_next_time & valid_db_resource_type functions

[tgurr]

Expected behavior

See the upgrade succeed.

Actual behavior

$ /usr/x86_64-pc-linux-gnu/libexec/postgresql-13/pg_upgrade -b /usr/x86_64-pc-linux-gnu/libexec/postgresql-12 -B /usr/x86_64-pc-linux-gnu/libexec/postgresql-13 -d /var/lib/postgresql/12/data -D /var/lib/postgresql/13/data
Führe Konsistenzprüfungen durch
-------------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

Wenn pg_upgrade ab diesem Punkt fehlschlägt, dann müssen Sie den
neuen Cluster neu mit initdb initialisieren, bevor fortgesetzt
werden kann.

Führe Upgrade durch
-------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
  gvmd                                                      
*failure*

Prüfen Sie die letzten Zeilen von »pg_upgrade_dump_16401.log« für den
wahrscheinlichen Grund für das Scheitern.
Fehlgeschlagen, Programm wird beendet

pg_upgrade_dump_16401.log

command: "/usr/x86_64-pc-linux-gnu/libexec/postgresql-13/pg_dump" --host /var/lib/postgresql --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="pg_upgrade_dump_16401.custom" 'dbname=gvmd' >> "pg_upgrade_dump_16401.log" 2>&1

command: "/usr/x86_64-pc-linux-gnu/libexec/postgresql-13/pg_restore" --host /var/lib/postgresql --port 50432 --username postgres --create --exit-on-error --verbose --dbname template1 "pg_upgrade_dump_16401.custom" >> "pg_upgrade_dump_16401.log" 2>&1
pg_restore: verbinde mit der Datenbank zur Wiederherstellung
pg_restore: erstelle DATABASE »gvmd«
pg_restore: verbinde mit neuer Datenbank »gvmd«
pg_restore: verbinde mit Datenbank »gvmd« als Benutzer »postgres«
pg_restore: erstelle DATABASE PROPERTIES »gvmd«
pg_restore: verbinde mit neuer Datenbank »gvmd«
pg_restore: verbinde mit Datenbank »gvmd« als Benutzer »postgres«
pg_restore: erstelle pg_largeobject »pg_largeobject«
pg_restore: erstelle SCHEMA »cert«
pg_restore: erstelle SCHEMA »scap«
pg_restore: erstelle EXTENSION »pgcrypto«
pg_restore: erstelle COMMENT »EXTENSION "pgcrypto"«
pg_restore: erstelle EXTENSION »uuid-ossp«
pg_restore: erstelle COMMENT »EXTENSION "uuid-ossp"«
pg_restore: erstelle FUNCTION »cert.cert_delete_bund_adv()«
pg_restore: erstelle FUNCTION »cert.cert_delete_cve()«
pg_restore: erstelle FUNCTION »public.armor("bytea")«
pg_restore: erstelle FUNCTION »public.armor("bytea", "text"[], "text"[])«
pg_restore: erstelle FUNCTION »public.certificate_iso_time(bigint)«
pg_restore: erstelle FUNCTION »public.common_cve("text", "text")«
pg_restore: erstelle FUNCTION »public.cpe_title("text")«
pg_restore: erstelle FUNCTION »public.create_index("text", "text", "text")«
pg_restore: erstelle FUNCTION »public.create_index("text", "text", "text", "text")«
pg_restore: erstelle FUNCTION »public.credential_value(integer, integer, "text")«
pg_restore: erstelle FUNCTION »public.crypt("text", "text")«
pg_restore: erstelle FUNCTION »public.current_severity(real, "text")«
pg_restore: erstelle FUNCTION »public.days_from_now(bigint)«
pg_restore: erstelle FUNCTION »public.dearmor("text")«
pg_restore: erstelle FUNCTION »public.decrypt("bytea", "bytea", "text")«
pg_restore: erstelle FUNCTION »public.decrypt_iv("bytea", "bytea", "bytea", "text")«
pg_restore: erstelle FUNCTION »public.digest("bytea", "text")«
pg_restore: erstelle FUNCTION »public.digest("text", "text")«
pg_restore: erstelle FUNCTION »public.dynamic_severity()«
pg_restore: erstelle FUNCTION »public.encrypt("bytea", "bytea", "text")«
pg_restore: erstelle FUNCTION »public.encrypt_iv("bytea", "bytea", "bytea", "text")«
pg_restore: erstelle FUNCTION »public.gen_random_bytes(integer)«
pg_restore: erstelle FUNCTION »public.gen_random_uuid()«
pg_restore: erstelle FUNCTION »public.gen_salt("text")«
pg_restore: erstelle FUNCTION »public.gen_salt("text", integer)«
pg_restore: erstelle FUNCTION »public.group_concat_pair("text", "text", "text")«
pg_restore: erstelle FUNCTION »public.hmac("bytea", "bytea", "text")«
pg_restore: erstelle FUNCTION »public.hmac("text", "text", "text")«
pg_restore: erstelle FUNCTION »public.hosts_contains("text", "text")«
pg_restore: erstelle FUNCTION »public.iso_time(bigint)«
pg_restore: erstelle FUNCTION »public.iso_time(bigint, "text")«
pg_restore: erstelle FUNCTION »public.level_max_severity("text", "text")«
pg_restore: erstelle FUNCTION »public.level_min_severity("text", "text")«
pg_restore: erstelle FUNCTION »public.lower(integer)«
pg_restore: erstelle FUNCTION »public.m_now()«
pg_restore: erstelle FUNCTION »public.make_uuid()«
pg_restore: erstelle FUNCTION »public.max_hosts("text", "text")«
pg_restore: erstelle FUNCTION »public.next_time(integer, integer, integer, integer)«
pg_restore: in Phase PROCESSING TOC:
pg_restore: in Inhaltsverzeichniseintrag 471; 1255 16425 FUNCTION next_time(integer, integer, integer, integer) dba
pg_restore: Fehler: could not execute query: FEHLER:  konnte Funktion »sql_next_time« nicht in Datei »/usr/x86_64-pc-linux-gnu/lib/libgvm-pg-server.so« finden
Die Anweisung war: CREATE FUNCTION "public"."next_time"(integer, integer, integer, integer) RETURNS integer
    LANGUAGE "c"
    AS '/usr/x86_64-pc-linux-gnu/lib/libgvm-pg-server', 'sql_next_time';

Steps to reproduce

1. Install PostgreSQL 13
2. Recompile gvmd against PostgreSQL 13
3. Run pg_upgrade: /usr/x86_64-pc-linux-gnu/libexec/postgresql-13/pg_upgrade -b /usr/x86_64-pc-linux-gnu/libexec/postgresql-12 -B /usr/x86_64-pc-linux-gnu/libexec/postgresql-13 -d /var/lib/postgresql/12/data -D /var/lib/postgresql/13/data

GVM versions

gsa: 20.08.0

gvm: 20.08.0 - Manager DB revision 233

openvas-scanner: 20.8.0

gvm-libs: 20.8.0

Environment

Operating system: Linux

Installation method / source: source-based distribution

[tgurr]

Looks like the function has been removed in GVM 20.8.0 via https://github.com/greenbone/gvmd/commit/87523bf9991d50f079de49bd138658285912faf2 but has not been removed from the database on the upgrade path to GVM 20.8.0 resulting in this issue.

Am I safe to just manually delete the three functions from my database?

[tgurr]

I went on an tried it, ran into the next similiar error

[...]
pg_restore: erstelle FUNCTION »public.valid_db_resource_type("text")«
pg_restore: in Phase PROCESSING TOC:
pg_restore: in Inhaltsverzeichniseintrag 552; 1255 16480 FUNCTION valid_db_resource_type("text") dba
pg_restore: Fehler: could not execute query: FEHLER:  konnte Funktion »sql_valid_db_resource_type« nicht in Datei »/usr/x86_64-pc-linux-gnu/lib/libgvm-pg-server.so« finden
Die Anweisung war: CREATE FUNCTION "public"."valid_db_resource_type"("text") RETURNS boolean
    LANGUAGE "c"
    AS '/usr/x86_64-pc-linux-gnu/lib/libgvm-pg-server', 'sql_valid_db_resource_type';

which has been removed with https://github.com/greenbone/gvmd/commit/659b29313c445c82af358c09a2342848b5c41d8d

You should really improve the upgrade path and take such things that have been removed during development into consideration when running gvmd --migrate on GVM version upgrades.

[cfi-gb]

I don't think this is the issue. I have noticed something similar while upgrading vom PostgreSQL 12 to 13 and it was caused by having the postgresql-12 development files still installed. After removing it (cmake had picked out the postgresql-12 development files instead of the postgresql-13 ones) had to fix cmake to correctly build libgvm-pg-server against PostgreSQL-13:

https://community.greenbone.net/t/hint-postgresql-13-issues-when-building-gvmd/7167

To sum up i think you're basically using an outdated libgvm-pg-server.so

[tgurr]

I don't think this is the issue

Well I don't have any problems with building gvmd with postgres 13, but with running pg_upgrade. We already pass -DPostgreSQL_TYPE_INCLUDE_DIR= in our build process and don't rely on cmake for this so we already build with the correct headers. After removing the 4 functions mentioned above the pg_upgrade went through smooth without any additional steps.

[cfi-gb]

Could be a different issue then the one i had faced. But note that the problem on my side was also seen while running pg_upgrade, and this only happened during the upgrade of PostgreSQL 12 to 13 with a wrong / not updated libgvm-pg-server.

[tgurr]

Ah sorry got what you mean, since you relied on cmake it didn't actually pick up postgres 13 but still kept building against 12 instead. But not the problem in this case.

[cfi-gb]

Any chances that you're building from the 20.08.0 tag? I guess it might be possible that this was already fixed in the (not released yet 20.08.1) with https://github.com/greenbone/gvmd/pull/1251

At least functions like "valid_db_resource_type" and "next_time" are mentioned there.

[tgurr]

@cfi-gb indeed! Thank you very very much for that hint! If I only knew before or if there would be a patch release from time to time for such serious issues. I'll add that patch to our downstream packages asap, but this is the problem, we usually don't have time to dig through the commits of each projects so patch releases we could just bump to would be very welcome.

[tgurr]

Still there seems to be something fishy as I was already on 20.08.0 and didn't try to dump/restore an old gvmd-9.0 dump. To my understanding gvmd --migrate should take care of those when updating to a new GVM version to have a clean database. The commit just adds dummy functions and seems to delay the actual problem. Please correct me if I'm wrong and my gvmd --migrate run on updating to 20.08.0 back then was buggy and should have removed the functions.

[cfi-gb]

Unfortunately i'm not familiar with that stuff but maybe @timopollmeier or @mattmundell can say something about that.

Also cc @bjoernricks for the patch releases as i don't have any insights into the release process of GVM.

[cfi-gb]

Just for the records, the issue i had faced / described in https://github.com/greenbone/gvmd/issues/1339#issuecomment-718718098 was indeed something different. Just had upgraded a second installation giving the following below which is caused by an outdated libgvm-pg-server / the mentioned cmake issue not finding PostgreSQL 13:

ERROR:  could not load library "/opt/gvm/lib/libgvm-pg-server.so": /opt/gvm/lib/libgvm-pg-server.so: undefined symbol: elog_finish
ERROR:  function public.hosts_contains(text, text) does not exist
ERROR:  could not load library "/opt/gvm/lib/libgvm-pg-server.so": /opt/gvm/lib/libgvm-pg-server.so: undefined symbol: elog_finish
ERROR:  function public.max_hosts(text, text) does not exist
ERROR:  could not load library "/opt/gvm/lib/libgvm-pg-server.so": /opt/gvm/lib/libgvm-pg-server.so: undefined symbol: elog_finish
ERROR:  function public.next_time_ical(text, text) does not exist
ERROR:  could not load library "/opt/gvm/lib/libgvm-pg-server.so": /opt/gvm/lib/libgvm-pg-server.so: undefined symbol: elog_finish
ERROR:  function public.next_time_ical(text, text, integer) does not exist
ERROR:  could not load library "/opt/gvm/lib/libgvm-pg-server.so": /opt/gvm/lib/libgvm-pg-server.so: undefined symbol: elog_finish
ERROR:  function public.regexp(text, text) does not exist
ERROR:  could not load library "/opt/gvm/lib/libgvm-pg-server.so": /opt/gvm/lib/libgvm-pg-server.so: undefined symbol: elog_finish
ERROR:  function public.severity_matches_ov(double precision, double precision) does not exist
ERROR:  function public.regexp(text, text) does not exist
ERROR:  operator does not exist: text public.?~# text
ERROR:  function public.hosts_contains(text, text) does not exist
LINE 41: ...IS NULL) OR ("overrides"."hosts" = ''::"text") OR "public"."...
                                                              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
ERROR:  relation "public.result_overrides" does not exist
ERROR:  relation "public.result_overrides" does not exist
LINE 10:                    FROM "public"."result_overrides"
                                 ^
ERROR:  relation "public.result_new_severities" does not exist

[y0urself]

Closing as this is related to an old version.