Fred Palma | Computer Scientist

These are my notes for installing the Postgresql 8.3 database server on Linux Ubuntu Server 8.04 with set up allowing access to other IP’ s.
These steps should be applicable to other Debian based distros.

At the command-line, enter the following commands (or search for the listed packages using synaptic):

These installs the database server/client, some extra utility scripts and the pgAdmin GUI application for working with the database.

$ sudo apt-get install postgresql postgresql-client postgresql-contrib

If you want the pgadmin:

$ sudo apt-get install pgadmin3

To reset the password for the ‘postgres’ admin account for the server.  Change the Password  you want to use for your administrator account:

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

Do the same for the unix user ‘postgres’. Enter the same password that you used previously:

$ sudo passwd -d postgres
$ sudo su postgres -c passwd

You’ re able to use command-line access and pgAdmin to run the database server.

To set-up the PostgreSQL admin pack that enables better logging and monitoring within pgAdmin. Run the following at the command-line:

$ sudo su postgres -c psql < /usr/share/postgresql/8.3/contrib/adminpack.sql

To open up the server enabling access and use it remotely, edit (vi, nano, gedit) the postgresql.conf file:

$ sudo vi /etc/postgresql/8.3/main/postgresql.conf

In Connection settings section:
# – Connection Settings -

listen_addresses = ‘*’

and for security:

password_encryption = on

Save the file and close the editor.

To define who can access the server, edit the pg_hba.conf file and in the last line, add in your subnet mask and the IP address of the machine that you want to enable access to your server.

$ sudo vi /etc/postgresql/8.3/main/pg_hba.conf

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database
# super user can access the database using some other method.
# Noninteractive
# access to all databases is required during automatic maintenance
# (autovacuum, daily cronjob, replication, and similar tasks).
#
# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# “local” is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

# Connections for all PCs on the subnet
#
# DATABASE USER IP-ADDRESS IP-MASK METHOD
host    all         all         [ip address]          [subnet mask]  md5

Restart the server:

$ sudo /etc/init.d/postgresql-8.3 restart

Tags: Database, Linux, Operational System // Add Comment »