QNAP/Linux Tool - Setting PostgreSQL and PHP Environment

To make your PHP easily connect to PostgreSQL installed in QNAP, you can use ipkg (install Optware IPKG) command to install PHP-PostgreSQL interface (php-pgsql).
[~] # ipkg install php-pgsqlInstalling php-pgsql (5.2.17-2) to root...Downloading http://ipkg.nslu2-linux.org/feeds/optware/cs08q1armel/cross/unstable/php-pgsql_5.2.17-2_arm.ipk
Installing php (5.2.17-2) to root...

The file config.inc.php is Central phpPgAdmin configuration for running PHP programs that accessing PostgreSQL databases. Add 'localhost' to the .conf file.

[/share/HDA_DATA/.qpkg/PostgreSQL/web/conf] # nano config.inc.php

// Display name for the server on the login screen $conf['servers'][0]['desc'] = 'PostgreSQL'; // Hostname or IP address for server. Use '' for UNIX domain socket. // use 'localhost' for TCP/IP connection on this computer $conf['servers'][0]['host'] = 'localhost'; // Database port on server (5432 is the PostgreSQL default) $conf['servers'][0]['port'] = 5432;









Find pg_hba.conf file in the PostgreSQL installation directory.
[~] # cd /share/HDA_DATA/.qpkg/PostgreSQL/postgresDB/ [/share/HDA_DATA/.qpkg/PostgreSQL/postgresDB] # ls PG_VERSION* pg_clog/ pg_multixact/ pg_snapshots/ pg_tblspc/ postgresql.conf* base/ pg_hba.conf* pg_notify/ pg_stat_tmp/ pg_twophase/ postmaster.opts global/ pg_ident.conf* pg_serial/ pg_subtrans/ pg_xlog/ postmaster.pid










Or use "find" command to search the file from home (/).

[/] # find -name pg_hba.conf ./share/HDA_DATA/.qpkg/Optware/var/pgsql/data/pg_hba.conf ./share/HDA_DATA/.qpkg/PostgreSQL/postgresDB/pg_hba.conf ./share/HDA_DATA/.qpkg/SignageStation/postgresql/signagedb/pg_hba.conf ./share/HDA_DATA/.qpkg/SignageStation/sudo/etc/pg_hba.conf

Now, let's try to build a database on pgsql. PostgreSQL's command line executer for running SQL queries is "psql". As we can see, if you run psql directly, some libraries might not be accessible. Therefore, PATH must be correctly set before run psql.

psql runtime needs libraries (directory) defined in the path

Remember that all installed QPKG settings are stored in: /etc/config/qpkg.conf. Let's retrieve the absolute path of PostgreSQL's installations.

[PostgreSQL] Name = PostgreSQL Class = null Status = complete Version = 9.2.1 Author = QNAP Systems, Inc. QPKG_File = PostgreSQL.qpkg Date = 2014-03-26 Shell = /share/HDA_DATA/.qpkg/PostgreSQL/PostgreSQL.sh Install_Path = /share/HDA_DATA/.qpkg/PostgreSQL WebUI = /phpPgAdmin/ Enable = TRUE














In previous article "QNAP NAS - My First App HelloWorld!", we have learned how to use getcfg to extraction desired values. Let's edit a executable script file for setting psql environment. The needed libraries are located in $PSQL/lib. 

CONF=/etc/config/qpkg.conf  
PSQL=$(/sbin/getcfg PostgreSQL Install_Path -f $CONF)

echo "PSQL=$PSQL" 
cd $PSQL 
export PATH=$PATH:$PSQL/lib 
set | grep "PATH" 

[/home] # nano pgs to edit a new script file.

When you login QTS with SSH, the default path is "/root", in which files within the /root are boot-time generated and stored in RAM. Therefore, we create pgs in the home directory (/home) since it's storage is hard disk. After change mode for execution (chmod 755 pgs), psql can be executed successfully. We use postgres account (i.e. the sa of MS SQL Server) "-U postgres" to connect with PostgreSQL. However, PHP/Web Apps have to use created accounts for connections. The default used database is the system database like master DB of SQL Server. Remember that the SQL command must be ended with ";". The namespace is <db.table> so that select * from pg_catalog.pg_user means the db "pg_catalog" and the table "pg_user". The role can be omitted so that we get the same result using select * from pg_user;.

[~] # cd /home [/home] # . pgs #use the same shell for execution, i.e. root permission. PSQL=/share/HDA_DATA/.qpkg/PostgreSQL PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/bin/X11:/usr/local/sbin:/opt/bin:/opt/sbin:/share/HDA_DATA/.qpkg/PostgreSQL/lib _=PATH [/share/HDA_DATA/.qpkg/PostgreSQL] # which psql #psql was copied to /opt/bin/psql #PATH of $PSQL/bin is not needed to be set. [/share/HDA_DATA/.qpkg/PostgreSQL] # psql -U postgres # -U "trust connection" Welcome to psql 8.2.13 (server 9.2.1), 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 WARNING: You are connected to a server with major version 9.2, but your psql client is major version 8.2. Some backslash commands, such as \d, might not work properly. postgres=# select * from pg_catalog.pg_user postgres-# ; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+---------+----------+----------+----------- postgres | 10 | t | t | t | t | ******** | |

































(1 row)
postgres=# select * from pg_user; usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil | useco
nfig ----------+----------+-------------+----------+-----------+---------+----------+----------+----------- postgres |       10 | t           | t        | t         | t       | ******** |          | 
(1 row)










postgres=# \q [/share/HDA_DATA/.qpkg/PostgreSQL] #