Spatial Wiki 5 - Setup PostgreSQL

Following on building our Spatial Wiki we've already installed PostgreSQL when we setup the Amazon EC2 server, but we need to do a few more things to make sure it's setup properly for our use.

Changing the postgres User Password

When PostgreSQL is installed the postgres user is automatically created, we just need to login to PostgreSQL and change the password.

Login to PostgreSQL with the postgres user:

sudo -u postgres psql postgres

And then from the PostgreSQL prompt do:

\password postgres

You will be prompted for the password. Exit postgres with \q when done.

Setup for Remote Access

We're going to want to setup PostgreSQL for remote access, for example so we can connect our QGIS desktop client directly to the database and display points from the spatial wiki.  The first part of the setup was allowing traffic on port 5432 that we took care of as part of the server setup.

The next steps are to configure PostgreSQL to allow connections from remote IPs and to allow TCP/IP sockets, a good overview of this process is here:

http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html

First to allow connections from any IP:

sudo vi /etc/postgresql/8.4/main/pg_hba.conf

Change the host line as shown below (0.0.0.0/0 means can connect from any IP)

# IPv4 local connections:
# -default- host    all         all         127.0.0.1/32          md5
host    all         all         0.0.0.0/0          md5

See also good notes on this topic here:

http://archives.postgresql.org/pgsql-general/2010-04/msg00865.php

Then allow TCP/IP sockets:

sudo vi /etc/postgresql/8.4/main/postgresql.conf

Uncomment/setup the listen_addresses line so it looks like this:

listen_addresses = '*'          # what IP address(es) to listen on;

Next restart PostgreSQL, make sure the firewalls are setup and you're good to go.  We'll also be testing the database setup later in this Blog when we setup the spatial database.

Restart PostgreSQL 8.4 on Ubuntu 10.04 with:

sudo /etc/init.d/postgresql-8.4 restart

Note that on your system the restart might be slightly different, for example:

sudo /etc/init.d/postgresql restart

Setup for PHP

Because we'll be writing some PHP code to interact with our spatial database in PostgreSQL we need to install the PHP libraries for PostgreSQL. The first line installs the pgsql extension for PHP, the second line restarts the Apache server.

sudo apt-get install php5-pgsql
sudo /etc/init.d/apache2 restart

For good articles on using PHP with PostgreSQL see:

http://www.techrepublic.com/blog/howdoi/how-do-i-use-php-with-postgresql/110

See the next step on Installing PostGIS.