Tuesday 29 September 2009

Setting up PostgresSQL on Ubuntu

Most of my database use has been with MySQL to date as I have LAMP installations for websites but I was looking for a DB to use with a new desktop application of ours. It seems that Postgres may be a better option. Below are the steps I needed to get a Postgres server running on my laptop.

1) Install the packages
# sudo apt-get install postgresql postgresql-client pgadmin3

This will install the latest versions, which for ubuntu 9.04 at this time is 8.3 if an older version installs then change the references to directories below as appropriate.

2) Change the admin passwords

# sudo -u postgres psql template1
This will start up the command line admin tool and select the template1 db which seems to be where postgre stores user credentials

# ALTER USER postgres WITH PASSWORD 'new password';
and quit by typing \q

3) Change the ubuntu postgres user password. This is a conveinence step as all the conf files are created with postgres user and group. You could add or edit them as root with sudo and then make sure they are all chown to postgres:postgres

# sudo passwd -d postgres
Will delete the current postgres password (needed otherwise you are asked for it in the next step)
# sudo su postgres -c passwd
Enter a new password. For simplicity use the same password as the admin user above, but can be anything so long as you remember it

Restart the server with
# sudo /etc/init.d/postgresql-8.3 restart

You can now use the pgAdmin tool to connect to the DB

No comments: