Archive for the ‘PosgreSQL’ Category

In previous article I've wrote on how to install postgresql on Debian Linux using the deb repository this was necessery to import some PostGres DBs, however this was not enough to run the posgresql php based website aimed as connection from Apache / PHP module to PostGre was failing after a bit of investigation and a check in phpinfo(); I've realized the module PHP module for postgres pgsql.so was missing, here is what I did in order to install it:

PHP sessions enable configuration

As it is common a common problem with PHP applications written to use PostGres is to loose sessions and by default PHP does not have configured sessions.save_path it is a very good practice to directly enable it in /etc/php5/apache2/php.ini open the file in text editor:

debian:~# vim /etc/php5/apache2/php.ini

Find the commented directive line:

;session.save_path = “/tmp”

and uncomment it, i.e.:

session.save_path = “/tmp”

Quit saving vim with the usual :wq!

The 3 modules provides pgsql.so for PHP and mod_auth_pgsql.so for Apache2, the 3rd packae phpgadmin provides a Web administration interface for installed PostgreSQL servers Databases, for those experienced with MySQL Database its the same as PHPMyAdmin.

Here is quick configuration for use of PostgreAdmin interface:

By default PHPPGADMIN installation process configure the Apache2 server'/etc/phppgadmin/apache.conf to use /etc/apache2/conf.d/phppgadmin

Here is the default my server package instaleld file content:

Alias /phppgadmin /usr/share/phppgadmin

<Directory /usr/share/phppgadmin>

DirectoryIndex index.php
AllowOverride None

order deny,allow
deny from all
allow from 127.0.0.0/255.0.0.0 ::1/128
# allow from all

It is generally a good practice to change the default Alias location of phppgadmin, so edit the file and change it to something like:

Alias /phppostgresgadmin /usr/share/phppgadmin

Then phpPgAdmin is available at http://servername.com/phppostgresadmin (only from localhost, however in my case I wanted to be able to access it also from other hosts so allowed PostgresGadmin from every hosts, to do so, I've commented in above config

# allow from 127.0.0.0/255.0.0.0 ::1/128

and uncommented #allow from all line, e.g.:

allow from all

Also another thing here is in your VirtualHost whenever you plan to access the PHPPGADMIN is to include in config ( in my case this is the file /etc/apache2/sites-enabled/000-default before (</VirtualHost> end line) following Alias:

Alias /phpposgreadmin /usr/share/phppgadmin

Then to access PostGreSQL PHP Admin interface in Firefox / Chrome open URL:

http://your-default-domain.com/phpposgreadmin

Configure access to a remote PostgreSQL Server

With PhpPgAdmin, you can manage many PostgreSQL servers locally (on the localhost) or on remote hosts.

First, you have to make sure that the distant PostgreSQL server can handle your request, that you can connect to it. You can do this by modifying the /etc/postgresql/9.5/main/filepg_hba.conf and adding a line like:

Then, you need to add your distant PostgreSQL server into the config file for PhpPgAdmin. This file is /etc/phppgadmin/config.inc.php the default postgresql port is 5432, however you might have configured it already to use some different port if you're not sure about the port number the postgresql is listening check it out:

Welcome to psql 9.5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1=#

template1=# CREATE USER MyNewUser WITH PASSWORD 'myPassword';

To add a new database to postgres from shell:

template1=# CREATE DATABASE NewDatabase;

template1=# GRANT ALL PRIVILEGES ON DATABASE NewDatabase to MyNewUser;

template1=# q

Last command instructs it to quit if you want to get more info about possible commands do type instead of q? for general help or for database / table commands only h
If you need to connect to NewDatabase just to test first it works in console before trying it from postgrepgadmin

If you're in position like me to be running an old version of Debian (Squeeze) and you need to install PostgreSQL you will notice that the Debian 6.0 standard repositories are no longer active and apt-get update && apt-get upgrade are returning errors, thus because this Debian release is already too old and even the LTS repositories are inactive it is impossible to install postgresql with the usual.

To get around the situation first thing I did was to try to add followin Debian repositories. to /etc/apt/sources.list

I guess though this worked for Debian Squeeze installing current versions such as Debian 8.0 Jessis and newer wouldn't be a prolem if you just change the debian version keyword witht he distribution for which you need the postgresql package

Here is all the consequential steps I took to make the PostgreSQL 9.5 running on my old and unsupported Debian 6.0 Squeeze

Create /etc/apt/sources.list.d/pgdg.list. The distributions are called codename-pgdg. In the example, replace wheezy with the actual distribution you are using: