[ubuntu-za] Database Problems

[Matthew French]

On 23 Jan 2010, at 9:03 PM, Ian Whitfield wrote:
> I think I need something more robust. Back to PostgreSQL I guess - just 
> need to get the damned thing running.
> 
> Why is this such a mission???

PostgreSQL setup should be quite straightforward. This tutorial seems quite accurate:
http://library.linode.com/databases/postgresql/install-postgresql-ubuntu-9.10-karmic

Some things to watch out for:

1. PostgreSQL doesn't recognise root as a superuser. So the moment you want to change anything related to the database, it is important to do it as the postgres user. (This is what the su - postgres command does.)

2. The default installation usually recognises local system users, but you still have to run createuser as postgres before the database will grant automatic access to you. 

3. You probably want to create a new database (createdb) with the same name as your username, as this makes it possible to run psql without any other flags. Even if your actual database is called something else. This is not required, but it makes life easier.

4. Remote access from another system is disabled by default. You need to change this, but *only* if you are going to connect to the database from another box, or sometimes if the database client you are using only uses TCP/IP to connect. 

5. To enable TCP/IP from another box you will need to add a line to pg_hba.conf like:
host    all         all         192.168.1.0/24       md5
Make sure localhost (127.0.0.1) access is also enabled.

6. Old versions of postgres required one to enable tcp, although this should not be necessary on newer versions. Make sure you aren't trying to follow installation instructions for older versions of postgres.

7. When changing pg_hba.conf, you will need to restart the database server:
# /etc/init.d/postgres restart
(Not always true, but better to make sure.)

If you still have issues, please let us know exactly what you have tried and what the error message or result was.

Oh, and one other thing: when working on Linux it is important to avoid using a Windows text editor and copying files back and forth. The Windows newline is different and this can really break things. Apologies if you already know this, but I find this mistake happens with alarming frequency and it can be an absolute nightmare to find this problem.

- Matthew