search

sipcapture / homer-app

HOMER 7.x Front-End and API Server
http://sipcapture.io
GNU Affero General Public License v3.0
207 stars 85 forks source link

How to reduce DB log errors? #357

Closed systemcrash closed 4 years ago

systemcrash commented 4 years ago

I want to reduce the amount of log-spam in the DB logs. Is there a better way of executing the CREATE DATABASE queries? Postgres doesn't have the WHERE NOT EXISTS clause - but it's still possible to check if tables are already present.

I'm not sure where these originate from - heplify-server, homer-app, or some other script. 

2020-05-29 04:54:15.076 UTC [1] LOG:  received smart shutdown request
2020-05-29 04:54:15.342 UTC [1] LOG:  background worker "logical replication launcher" (PID 26) exited with exit code 1
2020-05-29 04:54:15.393 UTC [21] LOG:  shutting down
2020-05-29 04:54:15.480 UTC [1] LOG:  database system is shut down

PostgreSQL Database directory appears to contain a database; Skipping initialization

2020-05-29 04:54:29.571 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-05-29 04:54:29.572 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2020-05-29 04:54:29.583 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-05-29 04:54:29.640 UTC [20] LOG:  database system was shut down at 2020-05-29 04:54:15 UTC
2020-05-29 04:54:29.650 UTC [1] LOG:  database system is ready to accept connections
2020-05-29 04:54:31.468 UTC [40] ERROR:  database "homer_data" already exists
2020-05-29 04:54:31.468 UTC [40] STATEMENT:  CREATE DATABASE homer_data
2020-05-29 04:54:31.469 UTC [40] ERROR:  role "homer_user" already exists
2020-05-29 04:54:31.469 UTC [40] STATEMENT:  CREATE USER homer_user WITH PASSWORD 'homer_password';
2020-05-29 04:54:31.469 UTC [40] ERROR:  role "postgres" does not exist
2020-05-29 04:54:31.469 UTC [40] STATEMENT:  GRANT postgres to homer_user;
2020-06-01 14:27:09.533 UTC [2504] ERROR:  database "homer_config" already exists
2020-06-01 14:27:09.533 UTC [2504] STATEMENT:  CREATE DATABASE homer_config OWNER root
2020-06-01 14:27:09.880 UTC [2505] ERROR:  database "homer_data" already exists
2020-06-01 14:27:09.880 UTC [2505] STATEMENT:  CREATE DATABASE homer_data OWNER root
2020-06-01 14:27:30.389 UTC [2624] ERROR:  database "homer_data" already exists
2020-06-01 14:27:30.389 UTC [2624] STATEMENT:  CREATE DATABASE homer_data
2020-06-01 14:27:30.390 UTC [2624] ERROR:  role "homer_user" already exists
2020-06-01 14:27:30.390 UTC [2624] STATEMENT:  CREATE USER homer_user WITH PASSWORD 'homer_password';
2020-06-01 14:27:30.390 UTC [2624] ERROR:  role "postgres" does not exist
2020-06-01 14:27:30.390 UTC [2624] STATEMENT:  GRANT postgres to homer_user;
2020-06-02 02:51:30.167 UTC [1] LOG:  received smart shutdown request
2020-06-02 02:51:30.225 UTC [1] LOG:  background worker "logical replication launcher" (PID 26) exited with exit code 1
2020-06-02 02:51:30.303 UTC [21] LOG:  shutting down
2020-06-02 02:51:30.433 UTC [1] LOG:  database system is shut down

PostgreSQL Database directory appears to contain a database; Skipping initialization

2020-06-02 02:51:44.924 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-06-02 02:51:44.924 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2020-06-02 02:51:44.925 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-06-02 02:51:44.954 UTC [20] LOG:  database system was shut down at 2020-06-02 02:51:30 UTC
2020-06-02 02:51:44.967 UTC [1] LOG:  database system is ready to accept connections
2020-06-02 02:51:45.105 UTC [27] ERROR:  database "homer_data" already exists
2020-06-02 02:51:45.105 UTC [27] STATEMENT:  CREATE DATABASE homer_data OWNER root
2020-06-02 02:51:47.191 UTC [44] ERROR:  database "homer_data" already exists
2020-06-02 02:51:47.191 UTC [44] STATEMENT:  CREATE DATABASE homer_data
2020-06-02 02:51:47.198 UTC [44] ERROR:  role "homer_user" already exists
2020-06-02 02:51:47.198 UTC [44] STATEMENT:  CREATE USER homer_user WITH PASSWORD 'homer_password';
2020-06-02 02:51:47.200 UTC [44] ERROR:  role "postgres" does not exist
2020-06-02 02:51:47.200 UTC [44] STATEMENT:  GRANT postgres to homer_user;

Some suggestions:

https://stackoverflow.com/questions/18389124/simulate-create-database-if-not-exists-for-postgresql

https://dba.stackexchange.com/questions/45143/check-if-postgresql-database-exists-case-insensitive-way

adubovikov commented 4 years ago

this is related to heplify-server, please open the issue here.