pgsql.pm: DBI->connect parameter dbname=postgres needs to be added

[kruntti]

$dbh = DBI->connect(

"DBI:Pg:host=$host;port=$port",

        "DBI:Pg:host=$host;port=$port;dbname=postgres",
        $user,
        $pass,
        { PrintError => $print_error, }

with out it DBI tries to connect to "$user" Database. So connection is not established. Thanks for a great module.

[mikaku]

with out it DBI tries to connect to "$user" Database. So connection is not established.

I'm not an experienced PostgreSQL user, but I have this module running on a pair of servers and it works finely. Please, make sure the user you defined in the username option has enough permission to read the global stats.

Thanks for a great module.

Thanks, I appreciate your feed back.

[kruntti]

Hi, my user is a superuser with all possible rights.

postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- pm9 | Superuser, Create role, Create DB, Replication | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

this is my cfg:

list = mgPettica3 host = localhost port = 5432 username = pm9 password = password db_list = kpi_db,customerdata_db rigid = 0, 0, 0, 0, 0, 0 limit = 100, 100, 100, 100, 100, 100

I reverted back my patch and then I did some tests. I also enabled print_error. Logs after monitorix restart.

Fri Jan 29 19:00:23 2021 - SIGTERM caught. Fri Jan 29 19:00:23 2021 - Exiting. Fri Jan 29 19:00:24 2021 - Starting Monitorix version 3.13.1 (pid 40915). Fri Jan 29 19:00:24 2021 - Loaded main configuration file '/etc/monitorix/monitorix.conf'. Fri Jan 29 19:00:24 2021 - Initializing graphs. Fri Jan 29 19:00:24 2021 - fs::fs_init: Unable to detect the device name of 'swap', I/O stats won't be shown in graph. If this is really a mount point then consider using to map it manually to a device name. Fri Jan 29 19:00:24 2021 - Generating the 'index.html' file. Fri Jan 29 19:00:24 2021 - Started HTTP built-in server (pid 41128). Fri Jan 29 19:00:24 2021 - Ok, ready. DBI connect('host=localhost;port=5432','pm9',...) failed: FATAL: database "pm9" does not exist at /usr/lib/monitorix/pgsql.pm line 208. Fri Jan 29 19:01:00 2021 - pgsql::pgsql_update: Cannot connect to PostgreSQL pm9 [password] 'localhost:5432'. Fri Jan 29 19:01:00 2021 - ERROR: while updating /var/lib/monitorix/pgsql.rrd: /var/lib/monitorix/pgsql.rrd: expected timestamp not found in data source from N

Postgreslog: [root@mgPettica3 monitorix]# grep pm9 /var/lib/pgsql/data/log/postgresql-Fri.log 2021-01-29 19:01:00.546 EET [46781] FATAL: database "pm9" does not exist 2021-01-29 19:02:00.875 EET [49543] FATAL: database "pm9" does not exist

I guess this is pretty much same if this is done manually from the prompt:

[kruntti@mgPettica3 kru]$ psql -h localhost -p 5432 -U pm9 psql: FATAL: database "pm9" does not exist [kruntti@mgPettica3 kru]$ psql -h localhost -p 5432 -U pm9 kpi_db psql (10.7) Type "help" for help.

kpi_db=#

Could it be so that you have just initial DB setup. "Most Postgres servers have three databases defined by default: template0, template1 and postgres. template0 and template1 are skeleton databases that are or can be used by the CREATE DATABASE command. postgres is the default database you will connect to before you have created any other databases. Once you have created another database you will want to switch to it in order to create tables and insert data."

And when there are more databases like in my case. Then postgres is not given anymore as default. So it needs to be given as a parameter. I have four different servers and additional database amount per server 2->150.

br, Kimmo

pe 29. tammik. 2021 klo 16.56 Jordi Sanfeliu (notifications@github.com) kirjoitti:

with out it DBI tries to connect to "$user" Database. So connection is not established.

I'm not an experienced PostgreSQL user, but I have this module running on a pair of servers and it works finely. Please, make sure the user you defined in the username option has enough permission to read the global stats.

Thanks for a great module.

Thanks, I appreciate your feed back.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/mikaku/Monitorix/issues/310#issuecomment-769853346, or unsubscribe https://github.com/notifications/unsubscribe-auth/ASUXYIWUPJ6VZPII5NWC2SDS4LEAPANCNFSM4WY5B64A .

[mikaku]

And when there are more databases like in my case. Then postgres is not given anymore as default. So it needs to be given as a parameter.

Thanks for your detailed information.

If I understood correctly, I need to add the paramater dbname= in the DBI line, as you suggested initially. So my question is, what database should I put there? any database from the list in db_list option will suffice?

[kruntti]

Hi Jordi, I think that any DB from db_list will be technically fine. We are looping $pgsql->{list} and maybe first instance of $pgsql->{desc}->{$pg}->{db_list} is the best option.

Other option is to use fixed postgres that should be available in all cases when collecting common "PostgreSQL statistics" "DBI:Pg:host=$host;port=$port;dbname=postgres",

and then db_list for all " 'my_db' database statistics"

I'm monitoring just one server (localhost) in my setup. So I don't have a test case for this multiple Server monitoring.

br, Kimmo

pe 29. tammik. 2021 klo 20.03 Jordi Sanfeliu (notifications@github.com) kirjoitti:

And when there are more databases like in my case. Then postgres is not given anymore as default. So it needs to be given as a parameter.

Thanks for your detailed information.

If I understood correctly, I need to add the paramater dbname= in the DBI line, as you suggested initially. So my question is, what database should I put there? any database from the list in db_list option will suffice?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/mikaku/Monitorix/issues/310#issuecomment-769959443, or unsubscribe https://github.com/notifications/unsubscribe-auth/ASUXYISUVPKEICJ7AGEES4TS4LZ5XANCNFSM4WY5B64A .

[mikaku]

Hi Kimmo,

Other option is to use fixed postgres that should be available in all cases when collecting common "PostgreSQL statistics" "DBI:Pg:host=$host;port=$port;dbname=postgres",

As you suggested, I've added the parameter ;dbname=postgres in the PostgreSQL servers and it seems they are continuing working fine. So, I'll add this patch to the official tree.

Thank you very much for tour feed back!

[woodyfon]

Mon Feb 22 23:54:01 2021 - pgsql_update(): install_driver(Pg) failed: Can't locate DBD/Pg.pm in @INC (you may need to install the DBD::Pg module)
(@INC contains: /usr/bin/lib /usr/lib/monitorix /etc/perl /usr/local/lib/x86_64-linux-gnu/perl/5.20.2 /usr/local/share/perl/5.20.2
 /usr/lib/x86_64-linux-gnu/perl5/5.20 /usr/share/perl5 /usr/lib/x86_64-linux-gnu/perl/5.20 /usr/share/perl/5.20 /usr/local/lib/site_perl .)
 at (eval 34) line 3.
Perhaps the DBD::Pg perl module hasn't been fully installed,
or perhaps the capitalisation of 'Pg' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, Proxy, Sponge.
 at /usr/lib/monitorix/pgsql.pm line 205.

The DBI module itself does not have the ability to communicate with any specific DBMS: for that it is necessary to install the appropriate back-end module, which in the case of PostgreSQL is DBD::Pg.

On Debian-based systems (including Ubuntu) the package that provides this module is libdbd-pg-perl:

apt-get install libdbd-pg-perl

and on Redhat-based systems it is perl-DBD-Pg:

yum install perl-DBD-Pg

In both of these cases, installing the back-end DBD package should automatically install the front-end DBI package as a dependency. Within Perl the dependency relationship is reversed: it is the DBI module that must be loaded explicitly, it then loads any required DBD modules

@woodyfon First of all, this is, if any, an issue completely different to #310, so this is a bad way to hijacking another issue. So please, next time open a new issue for a new problem.

In any case, I don't understand why the same package for MySQL databases doesn't show such error and don't require the same attention. Is this because the package perl-DBD-MySQL comes as a default in all systems?

I openen new issue #314

[IzzySoft]

Should that be added as recommends to the SPEC then, @mikaku? For RPM that would probably be perl-dbd-pg I guess. Though it should probably be sufficient pointing out the need for this in the docs, or as comment in the sample confs.

[mikaku]

@woodyfon First of all, this is, if any, an issue completely different to #310, so this is a bad way to hijacking another issue. So please, next time open a new issue for a new problem.

In any case, I don't understand why the same package for MySQL databases doesn't show such error and don't require the same attention. Is this because the package perl-DBD-MySQL comes as a default in all systems?

[IzzySoft]

Educated guess: --no-recommends was used, and the Perl packages come as recommends with some other package usually (like the corresponding client). Whatever, to keep it short as we're indeed on a hijacked ship here: those packages are not really hard requirements as Monitorix can be used without. If you want to monitor specific components you'll however need them, hence at max recommend, and min maybe mention it in the docs, at optimum have Monitorix throw a fitting error ("exception handler") pointing out the corresponding "Perl DBD" packages is needed. As the name of the actual package differs between systems, I'd indeed put it like that: "cannot access the XX database. Did you install the corresponding Perl DBD package?"

And now I keep my fingers out of here. As Mikaku wrote, please open a separate issue if you want to pursue this, @woodyfon.

[mikaku]

Yes, well, we could fill the .spec file with lots of package requirements covering all the possible Perl packages that Monitorix can need if the user wants to enable all the graphs, but ... really, is this so waste of space needed if you plan to have a small number of graphs?

And now I keep my fingers out of here. As Mikaku wrote, please open a separate issue if you want to pursue this, @woodyfon.

He just did it (#314), he edited his last post instead of open another one. That's why missed it. :smiley:

[IzzySoft]

@mikaku I just moved my last comment over. Feel free to delete it here (together with this one and your currently last one :wink:) and let's continue there.