Home » Database

PostgreSQL

Tips and Tricks on MySQL

Django postgresql connect_timeout via environment variable

Mon, 02/06/2012 - 23:03 — sandip

Recently, I've had to move a postgresql database onto a separate server and split it out from the django application server.

On doing so, we saw intermittent "OperationalError: could not connect to server: Connection timed out".

This was quite obvious that the "connect_timeout" had to be increased to resolve the issue due to the latency introduced by the network. However, psycopg2 database adapter was being used which did not support the "connect_timeout" option to be passed via django.

We were able to work around the issue setting the environmental variable "PGCONNECT_TIMEOUT" so libpq would pick up the connection parameter.

Put the below in "django.wsgi":

os.environ['PGCONNECT_TIMEOUT'] = '30'

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

Optimize PostgreSQL Database Size

Wed, 12/09/2009 - 11:55 — sandip

Recently, I noticed that the postgresql database partition had been filled up. The general advice is to export data, drop and recreate database and import the data back in, since this would save a lot of time and also reduce the database size comparatively.

Reference: optimize postgresql database size.

Not too sure, if I wanted to go the dump and restore route, so decided to vacuum and reindex instead.

To my surprise, I did regain about 70% of space running the full vacuum and reindexing the database.

  • Full vacuum reduced the database size from 8GB to 4GB.
    vacuumdb -afzv | tee /tmp/vacuumdb.log
  • Reindex then reduced further to 2.5GB.
    reindexdb -a | tee /tmp/reindexdb.log
  • Total of 5.5GB space reduced with a full vacuum and reindex of the database.
  • Check the vacuumdb.log for database optimization notices.
  • It is also advised to reindex prior to vacuuming to further save on vacuum time.

PostgreSQL 8.3 install on CentOS

Wed, 04/01/2009 - 23:37 — sandip

# wget http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-centos-8.3-6.noarch.rpm
# rpm -Uvh pgdg-centos-8.3-6.noarch.rpmr /># yum install postgresql-server
# service postgresql initdb
# service postgresql start

PostgreSQL QuickStart/Reference Commands...

Sat, 04/15/2006 - 19:47 — sandip

Since I don't use PostgreSQL as often as MySQL, I tend to forget simple commands for administering the database. Hopefully these notes will help as reference when working with PostgreSQL:

Comment