Monthly Archives: August 2013

Working with PostgreSQL

Installation

PostgreSQL installation command on Fedora:

# yum -y install postgresql-server phpPgAdmin

It assumes that you have already Apache web server installed (package httpd)

Setup

PostgreSQL database needs to be initialized first.

# postgresql-setup initdb
# su postgres		
bash-4.2$ createuser --superuser -P pgadmin
bash-4.2$ createdb --encoding=utf-8 --owner=pgadmin pgadmin
# systemctl start postgresql.service
# systemctl enable postgresql.service

Also it is necessary to enable logging into phpPgAdmin:

# vim /etc/phpPgAdmin/config.inc.php
$conf['extra_login_security'] = false;

To change the PostgreSQL password:

$ sudo -u postgres psql
password pgadmin
q

Setting up PostgreSQL database can be tricky. Just remember that after installation there is a user called postgres, who is a user in the operating system. The most comfortable way is to login as superuser and then as postgres user. After that you are able to create a database superuser, for example pgadmin. It’s necessary to create a database for him. This is not enough, you have to disable default authentication methods in /var/lib/pgsql/data/pg_hba.conf and change them to md5. After that and restarting the service I was able to login into phpPgAdmin as a pgadmin user.

# su postgres
bash-4.2$ vim /var/lib/pgsql/data/pg_hba.conf
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
bash-4.2$ exit
# systemctl restart postgresql.service

Creating users and their databases

The easiest way is to login to phpPgAdmin as pgadmin user. First you need to create a role, which seems to be the same as user in phpPgAdmin terminology. Fill in the role’s name, password, password confirmation and don’t forget to check at least “Can login?” check box. Then create a database for this user, for simplicity it is good habit to use the same name as the name of the user (role). The latest thing is to grant privileges for the selected user on already created database.

Running SQL commands from CLI

This example will focus on a simple Bash script, which is able to reset (drop and create) a PostgreSQL database. The script may look like this:

#!/bin/bash
# PostgreSQL 9.1 pg_stat_activity.procpid, 9.2 has just pg_stat_activity.pid
psql -U pgadmin -c "update pg_database set datallowconn = 'false' where datname = '$DATABASE'; SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE datname = '$DATABASE';"
psql -U pgadmin -c "drop database $DATABASE;"
psql -U pgadmin -c "create database $DATABASE owner $DATABASE encoding 'UTF8';"

Script assumes that you have already set up a super user pgadmin. Still there are few things to be done to run the script. The first thing is to edit the file pg_hba.conf and change authentication method to md5.

# vim /var/lib/pgsql/data/pg_hba.conf
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Now you should be able to run the script, but for each command you would have to fill in the password. To remove this necessity, you can save the password in your home directory.

$ vim ~/.pgpass
localhost:5432:*:pgadmin:password

Please note that this is not safe to save the password as a plain text, but I believe that for a local testing instance of PostgreSQL database it is a satisfactory solution.

 

Caveats

Hints

  • This utility will remove orphaned large objects from the database, its diagnosis mode is:
    /usr/pgsql-9.3/bin/vacuumlo -n -v database_name