Return to Main Page


PostgreSQL Installation On Oleg's Firmware


Introduction

PostgreSQL, an open source SQL server, represents a good alternative, though not as popular, to MySQL. Which server is better is a matter of a larger debate, but definitely for certain tasks PostgreSQL is a better choice than MySQL regarding both speed and flexibility. This article covers basic information on how to install and operate PostgreSQL version 8.2.13 on Oleg's Firmware (version 1.9.2.7, Linux kernel 2.4.20) running on Asus WL series routers.

Installation

Installing the server package is easy. Just run the following command:

ipkg install postgresql

During installation user and group postgres is create and in order to run the server process with postgres UID you will need to save /etc/group and /etc/passwd in flash memory otherwise you lose the changes on reboot (don't forget to list those files in /tmp/local/.files). By default directory /opt/var/pgsql is created where all basic files are placed. In order to launch the server without error you need to uncomment listen_addresses, unix_socket_directory and port options in postgresql.conf and change them to look like this (please check that unix_socket_directory has all permissions for user postgres):

listen_addresses='127.0.0.1'
port=5432
unix_socket_directory='/your/directory/for/lock/file'

Another alternative is to use Unix sockets for communication with the server. But TCP port option is simpler to start with. In general you need to restart the server for changes in postgresql.conf to take effect.

Initially the only user created for PostgreSQL is superuser postgres. So you will need to become the operating system user under which the server was installed (postgres) to work with the server, because by default the server matches those two names. That's why if you logged in as root (admin) you have to switch to postgres with su command line tool. If you miss su then you have to install coreutils package.

Before starting the server you need to initialize the data cluster that PostgreSQL will use for data storage. Installation creates the database cluster by default in the directory /opt/var/pgsql/data. If you want your database cluster in another place run the following command and don't forget to change after PGDATA variable in /opt/etc/init.d/S**postgresql:

su - postgres -c "initdb -D /new/postgresql/data"

Now you can launch PostgreSQL. Run /opt/etc/init.d/S**postgresql start. Type ps auxf command and see parent postmaster process with two children: writer and stats collector. Alternatively you can do this via command line:

su - postgres -c "postgres -D /opt/var/pgsql/data >/opt/var/pgsql/data/serverlog 2>&1 &"

Create a new database "test":

su - postgres -c "createdb -h 127.0.0.1 test"

For security reasons it is mandatory to assign a password to the superuser postgres:

psql -h 127.0.0.1 -U postgres -d template1 -c "ALTER USER postgres WITH ENCRYPTED PASSWORD 'mypass';"

Now you should switch on security control changing ps_hba.conf, since by default the server has the lowest level of password protection. Change the file as follows (further details are provided in the official manual). Don't forget to restart the server afterwards.

local  all  all                password
host   all  all  127.0.0.1/32  password
host   all  all  ::1/128       password

Accessing PostgreSQL From Command Line Client psql

The PostgreSQL package is bundled with a client utility called psql. If you plan to work with it interactively you better update (install) the readline package of at least 6.1-2 version. I was not successful in launching psql in interactive mode with libreadline.so library from readline-6.1-1. To launch psql in interactive mode type the following. Database commands presented below will create a table, a new user 'dummy' and assign specific privileges to that user. Basic SQL instructions are also tested.

psql -h 127.0.0.1 -d test -U postgres
Password for user postgres:
Welcome to psql 8.2.13, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test=# CREATE ROLE dummy WITH LOGIN ENCRYPTED PASSWORD 'mypass';
CREATE ROLE
test=# CREATE TABLE xyz (x INTEGER, y FLOAT(2), z VARCHAR(16));
CREATE TABLE
test=# GRANT SELECT, INSERT ON xyz TO dummy;
GRANT
test=# SET SESSION AUTHORIZATION dummy;
SET
test=> INSERT INTO xyz VALUES(1, 23.4, 'Marry');
INSERT 0 1
test=> INSERT INTO xyz VALUES(31, 150.41, 'Rick');
INSERT 0 1
test=> INSERT INTO xyz VALUES(2, 0.04, 'Ancelm');
INSERT 0 1
test=> SELECT z, x FROM xyz ORDER BY z;
   z    | x
--------+----
Ancelm  |  2
Marry   |  1
Rick    | 31
(3 rows)

test=> DELETE FROM xyz;
ERROR: permission denied for relation xyz

You are able also to issue database SQL instructions in non-interactive mode. For example:

psql -h 127.0.0.1 -U dummy -d test -c "SELECT z, x FROM xyz ORDER BY z;"

   z    | x
--------+----
Ancelm  |  2
Marry   |  1
Rick    | 31
(3 rows)

Accessing PostgreSQL From PHP Client

First of all install pgsql.so and pdo_pgsql.so shared libraries:

ipkg install php-pgsql

Current php-pgsql package (version 5.2.12-1) after installation creates php.d/pgsql.ini configuration file, so you don't have to change the extension section of your php.ini. If you run PHP as a FastCGI module (php-fcgi), you should to restart the web server in order for pgsql.so library to be accessible by the process. Write and execute the following code:

<?php

$dbh = pg_connect('host=127.0.0.1 port=5432 dbname=test user=dummy password=mypass');
$sth = pg_query($dbh, "SELECT * FROM xyz");

while($row = pg_fetch_row($sth))
   {
   echo implode("\t", $row), "\n";
   }
pg_free_result($sth);

pg_close($dbh);

exit(0);

?>

The output:

1   23.4    Marry
31  150.41  Rick
2   0.04    Ancelm

If you need further details please refer to specialized literature. Complete and extensive information on how to run PostgreSQL is described in the official manual.

June 19, 2010