Home

postgres

munin-node and postgresql plugins setup

Sun, 02/05/2012 - 19:37 — sandip

While setting up munin to monitor postgresql, I was getting "[DBD::Pg not found, and cannot do psql yet]" when running `munin-node-configure --suggest | grep postgres`.

I confirmed that the rpm package "perl-DBI-1.52-2.el5" was indeed installed.

However, when I ran a test against the module, it failed with:

# perl -MDBD::Pg -e 1
Can't load '/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Pg/Pg.so' for module DBD::Pg: libpq.so.4: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.
at -e line 0
Compilation failed in require.
BEGIN failed--compilation aborted.

On checking the library, it returned with "libpq.so.4 => not found":

# ldd /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Pg/Pg.so
linux-vdso.so.1 =>  (0x00007fffb60bb000)
libpq.so.4 => not found
libc.so.6 => /lib64/libc.so.6 (0x00007fa36d2c2000)
/lib64/ld-linux-x86-64.so.2 (0x00007fa36d845000)

Indeed libpq.so.4 was missing since postgresql90-libs was installed which only includes "libpq.so.5".

To get libpq.so.4, "compat-postgresql-libs" package needed to be installed.

Once installed the perl module test passed and I was able to get the munin plugins linked using:

# munin-node-configure --shell | grep postgres | sh

PostgreSQL role based password authentication

Wed, 12/16/2009 - 14:30 — sandip

To manage/setup role based password authentication for postgresql database.

Modify "/var/lib/pgsql/data/pg_hba.conf" and include:

host    samerole         all         127.0.0.1/32         md5

Save the below script and run:

./db_setup.sh <DBNAME> <DBMAINUSER> <DBMAINUSERPASS> create

#!/bin/bash
# db_setup.sh

USAGE="Usage: $0 <DBNAME> <DBMAINUSER> <DBMAINUSERPASS> <create|drop>"
DBNAME=${1?"$USAGE";}
DBMAINUSER=${2?"$USAGE&quot;}
DBMAINUSERPASS=${3?"$USAGE"}

# Create new database + main user
#
create_db_and_mainuser() {
psql -U postgres template1 -f - <<EOT

CREATE ROLE ${DBNAME} NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN;
CREATE ROLE ${DBMAINUSER} NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '${DBMAINUSERPASS}';
GRANT ${DBNAME} TO ${DBMAINUSER};
CREATE DATABASE ${DBNAME} WITH OWNER=${DBMAINUSER};

EOT
}

# Remove database + main user
#
drop_db_and_mainuser() {
psql -U postgres template1 -f - <<EOT

-- TERMINATE CONNECTIONS OF ALL USERS CONNECTED TO <DBNAME>
DROP DATABASE ${DBNAME};
DROP ROLE ${DBMAINUSER};
DROP ROLE ${DBNAME};

EOT
}

# Main
case "$4" in
  create)
    create_db_and_mainuser
    ;;
  drop)
    drop_db_and_mainuser
    ;;
  *)
    echo $USAGE
    exit 1
    ;;
esac

exit 0

You should now be able to connect using:

psql -U <DBMAINUSER> -d <DBNAME>  -h 127.0.0.1

Comment