News FeedThis is a repost and update of an old blog entry on one of my personal sites. I'm planning on cleaning out the site sometime soon, so I thought this would make a good new home for the tutorial...
This quick walk-through will help you to install the PostgreSQL database server and the PgAdmin administration application on Ubuntu Linux, and also set up the server so it allows access to other PC's on your network.
Before we move on, this guide was tested on (and intended for) the current release of Ubuntu Linux, (6.10 - Edgy Eft) and PostgreSQL 8.2, but it should also be applicable to older versions (of Ubuntu and PostgreSQL) and other Debian based distros. (Update: This also works fine for PostgreSQL 8.2 on the newer version of Ubuntu 7.04 - Feisty, you just shouldn't need the backports mentioned below).
First off, PostgreSQL 8.2 isn't in the main repositories in Edgy, you'll need to have backports enabled to get hold of the latest packages. Once you've done that, let's move on.
Right, now let's install the database server. At the command-line, enter the following (Or you can do all this in Synaptic - just search for and install the packages listed in the commands):
daz@na-box:~$ sudo apt-get install pgadmin3 pgadmin3-data
This installs the database server, and the pgAdmin administration application (If you don't really get on with the pgAdmin GUI, there is an alternative in the form of phpPgAdmin - a web-based administration interface - a guide to setting this up can be found here).
Now we need to reset the password for the 'postgres' admin account for the server, so we 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):
template1=# ALTER USER postgres WITH PASSWORD 'password';
template1=# \q
Then, from here on in we can use pgAdmin to run the database server. To get a menu entry for pgAdmin do the following...
Then paste the following into the file:
Name=pgAdmin III
Encoding=UTF-8
Exec=pgadmin3
Terminal=false
Comment[en_GB]=PostgreSQL Administrator III
Icon=/usr/share/pixmaps/pgadmin3.xpm
Type=Application
Categories=GNOME;Application;Database;System;
Name[en_GB]=pgAdmin III
Then save the file and exit gedit. You should find the launcher in the System Tools section of the Applications menu. Alternatively, you could just type 'pgadmin3' at the shell. The wizards to connect to the database should be pretty simple to figure out.
Finally, we need to open up the server so that we can access and use it remotely - unless you only want to access the database on the local machine. To do this, first, we need to edit the postgresql.conf file:
Now, to edit a couple of lines in the 'Connections and Authentication' section...
Change the line:
to
and also change the line:
to
Then save the file and close gedit.
Now for the final step, we must define who can access the server. This is all done using the pg_hba.conf file.
Comment out, or delete the current contents of the file, then add this text to the bottom of the file:
# 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
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!
If you wish to use the command-line tool psql with the 'postgres' account, this is not possible unless you create a full Unix user account called postgres, however, you can still log into 'psql' without doing this... Here's both ways of doing this:
Method 1 - Generating the 'postgres' account
Type the following at the command-line:
This clears the password for the Unix account 'postgres'. Now type the following and give it a password (preferably the same as you set in the above tutorial):
Now all you have to do to log into psql as the 'postgres' user is type the following:
postgres@na-box:/home/daz$ psql
Method 2 - The Quick Way...
When you want to access psql as the 'postgres' user, type the following: