search

greenbone / gvmd

Greenbone Vulnerability Manager - The database backend for the Greenbone Community Edition
GNU Affero General Public License v3.0
290 stars 157 forks source link

Multiple database issues with PostgreSQL 9.6 #173

Closed michael-markevich closed 3 years ago

michael-markevich commented 6 years ago

Expected behavior

No scary error messages.

Current behavior

Multiple messages in postgres log, including:

Steps to reproduce

  1. Standard installation of OpenVAS with Postgres 9.6 backend.

GVM versions

gsa: (gsad --version) Today's snapshot from Github.

gvm: (gvmd --version) Today's snapshot from Github.

openvas-scanner: (openvassd --version) Today's snapshot from Github.

gvm-libs: Today's snapshot from Github.

openvas-smb: None.

Environment

Operating system: Debian 9 Stretch.

Installation method / source: (packages, source installation) Build from source.

Logfiles

2018-08-27 21:09:28.714 UTC [20756] LOG:  database system was shut down at 2018-08-27 21:09:27 UTC
2018-08-27 21:09:28.716 UTC [20756] LOG:  MultiXact member wraparound protections are now enabled
2018-08-27 21:09:28.718 UTC [20755] LOG:  database system is ready to accept connections
2018-08-27 21:09:28.718 UTC [20760] LOG:  autovacuum launcher started
2018-08-27 21:09:29.192 UTC [20762] [unknown]@[unknown] LOG:  incomplete startup packet
2018-08-27 21:51:21.155 UTC [10544] root@gvmd ERROR:  relation "public.meta" does not exist at character 19
2018-08-27 21:51:21.155 UTC [10544] root@gvmd STATEMENT:  SELECT value FROM public.meta WHERE name = 'database_version';
2018-08-27 21:51:21.648 UTC [10544] root@gvmd WARNING:  column "type" has type "unknown"
2018-08-27 21:51:21.648 UTC [10544] root@gvmd DETAIL:  Proceeding with relation creation anyway.
2018-08-27 21:51:47.270 UTC [10581] root@gvmd ERROR:  duplicate key value violates unique constraint "nvt_preferences_name_key"
2018-08-27 21:51:47.270 UTC [10581] root@gvmd DETAIL:  Key (name)=(IT-Grundschutz M4.023: Sicherer Aufruf ausführbarer Dateien[checkbox]:Alle Dateien Auflisten) already exists.
2018-08-27 21:51:47.270 UTC [10581] root@gvmd STATEMENT:  INSERT into nvt_preferences (name, value) VALUES ('IT-Grundschutz M4.023: Sicherer Aufruf ausführbarer Dateien[checkbox]:Alle Dateien Auflisten', 'no');
2018-08-27 21:55:49.095 UTC [10573] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-27 21:55:49.095 UTC [10573] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-27 21:55:59.176 UTC [10573] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-27 21:55:59.176 UTC [10573] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-27 22:54:13.748 UTC [10578] root@gvmd ERROR:  relation "cert_bund_advs" does not exist at character 30
2018-08-27 22:54:13.748 UTC [10578] root@gvmd STATEMENT:  SELECT EXISTS (SELECT * FROM cert_bund_advs  WHERE creation_time        > coalesce (CAST ((SELECT value FROM meta                           WHERE name                                 = 'cert_check_time')                          AS INTEGER),                    0));
2018-08-28 08:07:57.250 UTC [19078] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 08:07:57.250 UTC [19078] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 08:10:31.964 UTC [19078] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 08:10:31.964 UTC [19078] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 08:54:14.005 UTC [19078] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 08:54:14.005 UTC [19078] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 10:09:39.311 UTC [20755] LOG:  received fast shutdown request
2018-08-28 10:09:39.311 UTC [20755] LOG:  aborting any active transactions
2018-08-28 10:09:39.311 UTC [20760] LOG:  autovacuum launcher shutting down
2018-08-28 10:09:39.315 UTC [20757] LOG:  shutting down
2018-08-28 10:09:39.336 UTC [20755] LOG:  database system is shut down
2018-08-28 10:10:18.422 UTC [670] LOG:  database system was shut down at 2018-08-28 10:09:39 UTC
2018-08-28 10:10:18.429 UTC [670] LOG:  MultiXact member wraparound protections are now enabled
2018-08-28 10:10:18.431 UTC [674] LOG:  autovacuum launcher started
2018-08-28 10:10:18.431 UTC [669] LOG:  database system is ready to accept connections
2018-08-28 10:10:18.867 UTC [712] [unknown]@[unknown] LOG:  incomplete startup packet
2018-08-28 10:17:37.364 UTC [1339] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 10:17:37.364 UTC [1339] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 10:23:03.679 UTC [1339] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 10:23:03.679 UTC [1339] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 10:32:00.030 UTC [1339] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 10:32:00.030 UTC [1339] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 10:44:18.703 UTC [1339] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 10:44:18.703 UTC [1339] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 12:08:43.193 UTC [669] LOG:  received fast shutdown request
2018-08-28 12:08:43.193 UTC [669] LOG:  aborting any active transactions
2018-08-28 12:08:43.194 UTC [674] LOG:  autovacuum launcher shutting down
2018-08-28 12:08:43.195 UTC [671] LOG:  shutting down
2018-08-28 12:08:43.243 UTC [669] LOG:  database system is shut down
2018-08-28 12:09:24.360 UTC [654] LOG:  database system was shut down at 2018-08-28 12:08:43 UTC
2018-08-28 12:09:24.366 UTC [654] LOG:  MultiXact member wraparound protections are now enabled
2018-08-28 12:09:24.368 UTC [658] LOG:  autovacuum launcher started
2018-08-28 12:09:24.368 UTC [653] LOG:  database system is ready to accept connections
2018-08-28 12:09:24.822 UTC [669] [unknown]@[unknown] LOG:  incomplete startup packet
2018-08-28 13:39:57.678 UTC [7667] postgres@postgres ERROR:  role "root" already exists
2018-08-28 13:39:57.678 UTC [7667] postgres@postgres STATEMENT:  CREATE ROLE root NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
2018-08-28 13:39:58.240 UTC [7684] postgres@tasks ERROR:  role "dba" already exists
2018-08-28 13:39:58.240 UTC [7684] postgres@tasks STATEMENT:  create role dba with superuser noinherit; grant dba to root; create extension "uuid-ossp";
2018-08-28 13:40:14.711 UTC [7738] postgres@postgres ERROR:  role "root" already exists
2018-08-28 13:40:14.711 UTC [7738] postgres@postgres STATEMENT:  CREATE ROLE root NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
2018-08-28 13:40:14.771 UTC [7743] postgres@postgres ERROR:  database "tasks" already exists
2018-08-28 13:40:14.771 UTC [7743] postgres@postgres STATEMENT:  CREATE DATABASE tasks OWNER root;
2018-08-28 13:40:14.848 UTC [7755] postgres@tasks ERROR:  role "dba" already exists
2018-08-28 13:40:14.848 UTC [7755] postgres@tasks STATEMENT:  create role dba with superuser noinherit; grant dba to root; create extension "uuid-ossp";
2018-08-28 15:04:05.061 UTC [10581] postgres@postgres ERROR:  database "tasks" does not exist
2018-08-28 15:04:05.061 UTC [10581] postgres@postgres STATEMENT:  DROP DATABASE tasks;
2018-08-28 15:04:43.411 UTC [10634] postgres@tasks ERROR:  role "dba" already exists
2018-08-28 15:04:43.411 UTC [10634] postgres@tasks STATEMENT:  create role dba with superuser noinherit; grant dba to root;
2018-08-28 15:11:05.622 UTC [11654] root@tasks ERROR:  relation "public.meta" does not exist at character 19
2018-08-28 15:11:05.622 UTC [11654] root@tasks STATEMENT:  SELECT value FROM public.meta WHERE name = 'database_version';
2018-08-28 15:33:20.854 UTC [12110] root@tasks ERROR:  canceling statement due to user request
2018-08-28 15:33:20.854 UTC [12110] root@tasks STATEMENT:  SELECT pg_advisory_xact_lock (1);
2018-08-28 15:33:28.875 UTC [12103] root@tasks ERROR:  duplicate key value violates unique constraint "nvt_preferences_name_key"
2018-08-28 15:33:28.875 UTC [12103] root@tasks DETAIL:  Key (name)=(Microsoft Windows: Configure registry policy processing (background processing)[radio]:Value) already exists.
2018-08-28 15:33:28.875 UTC [12103] root@tasks STATEMENT:  INSERT into nvt_preferences (name, value) VALUES ('Microsoft Windows: Configure registry policy processing (background processing)[radio]:Value', '0;1');
2018-08-28 15:35:16.168 UTC [12451] root@tasks ERROR:  canceling statement due to user request
2018-08-28 15:35:16.168 UTC [12451] root@tasks STATEMENT:  SELECT pg_advisory_xact_lock (1);
2018-08-28 15:50:56.584 UTC [653] LOG:  received fast shutdown request
2018-08-28 15:50:56.584 UTC [653] LOG:  aborting any active transactions
2018-08-28 15:50:56.584 UTC [658] LOG:  autovacuum launcher shutting down
2018-08-28 15:50:56.589 UTC [655] LOG:  shutting down
2018-08-28 15:50:56.644 UTC [653] LOG:  database system is shut down
2018-08-28 15:51:35.342 UTC [653] LOG:  database system was shut down at 2018-08-28 15:50:56 UTC
2018-08-28 15:51:35.375 UTC [653] LOG:  MultiXact member wraparound protections are now enabled
2018-08-28 15:51:35.378 UTC [657] LOG:  autovacuum launcher started
2018-08-28 15:51:35.378 UTC [652] LOG:  database system is ready to accept connections
2018-08-28 15:51:35.763 UTC [667] [unknown]@[unknown] LOG:  incomplete startup packet
2018-08-28 16:28:47.004 UTC [1807] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 16:28:47.004 UTC [1807] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 16:36:39.134 UTC [1807] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 16:36:39.134 UTC [1807] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 16:44:38.367 UTC [652] LOG:  received fast shutdown request
2018-08-28 16:44:38.367 UTC [652] LOG:  aborting any active transactions
2018-08-28 16:44:38.367 UTC [657] LOG:  autovacuum launcher shutting down
2018-08-28 16:44:38.369 UTC [654] LOG:  shutting down
2018-08-28 16:44:38.428 UTC [652] LOG:  database system is shut down
2018-08-28 16:45:19.419 UTC [655] LOG:  database system was shut down at 2018-08-28 16:44:38 UTC
2018-08-28 16:45:19.426 UTC [655] LOG:  MultiXact member wraparound protections are now enabled
2018-08-28 16:45:19.428 UTC [659] LOG:  autovacuum launcher started
2018-08-28 16:45:19.428 UTC [654] LOG:  database system is ready to accept connections
2018-08-28 16:45:19.879 UTC [669] [unknown]@[unknown] LOG:  incomplete startup packet
2018-08-28 17:12:14.541 UTC [8528] root@gvmd ERROR:  duplicate key value violates unique constraint "nvt_preferences_name_key"
2018-08-28 17:12:14.541 UTC [8528] root@gvmd DETAIL:  Key (name)=(IT-Grundschutz M5.009: Protokollierung am Server[checkbox]:Alle Logfile-Einträge Auflisten) already exists.
2018-08-28 17:12:14.541 UTC [8528] root@gvmd STATEMENT:  INSERT into nvt_preferences (name, value) VALUES ('IT-Grundschutz M5.009: Protokollierung am Server[checkbox]:Alle Logfile-Einträge Auflisten', 'no');
2018-08-28 17:22:34.433 UTC [13935] root@gvmd LOG:  could not receive data from client: Connection reset by peer
2018-08-28 17:22:34.433 UTC [13935] root@gvmd LOG:  unexpected EOF on client connection with an open transaction
2018-08-28 17:25:17.049 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:25:17.049 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:25:29.062 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:25:29.062 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:25:41.057 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:25:41.057 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:27:04.094 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:27:04.094 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:27:16.055 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:27:16.055 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:28:16.060 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:28:16.060 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:32:24.158 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:32:24.158 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:33:00.552 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:33:00.552 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:36:22.143 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:36:22.143 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:39:52.444 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:39:52.444 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:41:31.391 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:41:31.391 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:42:22.444 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:42:22.444 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:42:42.520 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:42:42.520 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:47:31.428 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:47:31.428 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:48:00.375 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:48:00.375 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 17:52:57.343 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 17:52:57.343 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
2018-08-28 18:02:42.287 UTC [13988] root@gvmd ERROR:  could not obtain lock on relation "reports"
2018-08-28 18:02:42.287 UTC [13988] root@gvmd STATEMENT:  LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;
gittygoo commented 5 years ago

Getting the same as the OP and also the following on gvm10 with postgres 2019-10-22 16:30:56.684 UTC [29384] gvmduser@gvmd ERROR: could not obtain lock on relation "reports" 2019-10-22 16:30:56.684 UTC [29384] gvmduser@gvmd STATEMENT: LOCK table reports IN ACCESS EXCLUSIVE MODE NOWAIT;

tfilip commented 3 years ago

Getting the same issue with gvm 20 on a docker install.

bjoernricks commented 3 years ago

Should be re-tested with current version. If it is still valid please re-open.