PostgreSQL Installation On Oleg's Firmware
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 220.127.116.11, Linux kernel 2.4.20) running on Asus WL series routers.
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):
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';
test=# CREATE TABLE xyz (x INTEGER, y FLOAT(2), z VARCHAR(16));
test=# GRANT SELECT, INSERT ON xyz TO dummy;
test=# SET SESSION AUTHORIZATION dummy;
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
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
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:
$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";
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