Postgresql Installation on Linux
To install the PostgreSQL database server and the PgAdmin administration application on Ubuntu Linux
We are using the Ubuntu Linux, (8.10) and PostgreSQL 8.2.
At the command-line, enter the following commands
$ sudo apt-get install postgresql postgresql-client postgresql-contrib
$ sudo apt-get install pgadmin3
This will install the database server/client
Reset the password for the ‘postgres’ admin account for the server, so we can use this for all of the system administration tasks
$ sudo su postgres -c psql template1
template1=# ALTER USER postgres WITH PASSWORD 'password';
template1=# \q
That alters the password for within the database, now we need to do the same for the unix user ‘postgres’:
$ sudo passwd -d postgres
$ sudo su postgres -c passwd
Enter the same password used previously.
We can use both pgAdmin and command-line access to run the database server.
Set-up the PostgreSQL admin pack that enables better logging and monitoring within pgAdmin
$ sudo su postgres -c psql < /usr/share/postgresql/8.2/contrib/adminpack.sql
Open up the server so that we can access and use it remotely Edit the postgresql.conf file:
$ sudo gedit /etc/postgresql/8.2/main/postgresql.conf
Edit a couple of lines in the ‘Connections and Authentication’ section also
#listen_addresses = 'localhost'
to
listen_addresses = '*'
#password_encryption = on
to
password_encryption = on
The Last step, we must define who can access the server. This is all done using the pg_hba.conf file
$ sudo gedit /etc/postgresql/8.2/main/pg_hba.conf
Comment out, or delete the current contents of the file, then add this text to the bottom of the file:
# 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
#
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all all [ip address] [subnet mask] md5
In the last line, add in your subnet mask (i.e. 255.255.255.0) and the IP address of the machine that you would like to access your server . However, if you would like to enable access to a range of IP addresses, just substitute the last number for a zero and all machines within that range will be allowed access
Restart the server:
$ sudo /etc/init.d/postgresql-8.2 restart