PostgreSQL 8.4 on Ubuntu 9.10 (Karmic)

Getting PostgreSQL installed on Ubuntu is pretty straight forward as standard .deb packages already exist for the db in Ubuntu’s repository system. However beyond getting the package installed there are a couple of configuration steps that are required to complete the basic configuration of the PostgreSQL Db System.

  1. Install the DB and associated packages
  2. Reset the default postgres db password
  3. Reset the default postgres system password
  4. Backup/Edit the postgresql.conf file
  5. Backup/Edit the pg_hba.conf file
  6. Restart the Db
  7. Create a new db user and new database

Install the DB and associated packages
The database and any prerequisite packages:

aptitude update
aptitude install postgresql postgresql-doc pgadmin3

Reset the default postgres db password
Reset the default password for DATABASE ACCOUNT postgres:

sudo su postgres -c psql template1
postgres=# ALTER USER postgres WITH PASSWORD ‘YourNewPasswordForTheDb’;
postgres=# \q

Reset the default postgres system password
Reset the default password for SYSTEM ACCOUNT postgres:

sudo passwd -d postgres
sudo su postgres -c passwd
When prompted enter YourNewPasswordForTheDb as the password for the SYSTEM account postgress

Backup/Edit the postgresql.conf file
cd /etc/postgresql/8.4/main
cp postgresql.conf postgresql.conf.orig (back up this file — just in case)
vi postgresql.conf (edit this file)

Find the line that reads:
#listen_addresses = ‘localhost’
Change to:
listen_addresses = ‘*’

Find the line that reads:
#password_encryption = on
Change to:
password_encryption = on

Backup/Edit the pg_hba.conf file
cp pg_hba.conf pg_hba.conf.orig (back up this file — just in case)
vi pg_hba.conf (edit this file)

Find the line that reads:
local    all    all    ident
Change to:
local   all    all    md5

Confirm that there is a line that reads:
host    all    all    127.0.0.1/32    md5

Confirm that there is a line that reads:
host    all    all    ::1/128        md5

Add the line:
host     all    all    192.168.1.0/24    md5
(Adjust the above line you have a different ip scheme on your LAN)


Restart the Db

/etc/init.d/postgresql-8.4 restart


Create a new db user and new database

sudo -u postgres createuser -D -A -P newdbuser
sudo -u postgres createdb -O newdbuser newdb

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s