Install PostgreSQL on Ubuntu

Today I just install postgresql on ubuntu feisty complete ... T_T. Sure I install PostgreSQL v8.2 by using the following command:


$sudo apt-get install postgresql-8.2


Then I install the following package - phppgadmin pgadmin3 pgadmin3-data using command:


$sudo apt-get install phppgadmin pgadmin3 pgadmin3-data


After this, I need to reset the password for the 'postgres' admin account for the server, so I can use this for all of the system administration tasks. Type the following at the command-line (substitute in the password you want to use for your administrator account):


$sudo su postgres -c psql template1
template1=# ALTER USER postgres WITH PASSWORD 'password_you_want';
template1=# \q


Then, from here on in we can use pgAdmin to run the database server.

Finally, I need to open up the server so that I can access and use it remotely - unless you only want to access the database on the local machine (The guidelines here are for opening up your server on a secure LAN - if you are not on a secure LAN you may want to look into adding SSL authentication before proceeding with these steps).

To do this, first, I need to edit the postgresql.conf file:


$sudo gedit /etc/postgresql/8.2/main/postgresql.conf


Now, to edit a couple of lines in the 'Connections and Authentication' section...

Change the line:


#listen_addresses = 'localhost'


to


listen_addresses = '*'


and also change the line:


#password_encryption = on


to


password_encryption = on


Then save the file and close gedit.

Now for the final step, I 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


Now add the following lines to the file:


# Allow any user on the local system to connect to any database under
# any user name using Unix-domain sockets (the default for local
# connections).
#
# Database administrative login by UNIX sockets
local all all trust

# 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


and 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 (i.e. 138.250.192.115). 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 (i.e. 138.250.192.0 would allow all machines with an IP address 138.250.192.x to use the database server).

That's it, now all you have to do is restart the server and all should be working!


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