Following up from my old guide to installing PostgreSQL (for Ubuntu 7.10), I thought i’d better do an update for the latest releases…
This quick walk-through are my notes for installing 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 the current release of Ubuntu Linux, (8.04 - Hardy Heron) and PostgreSQL 8.3, but it should also be applicable to older versions (of Ubuntu and PostgreSQL) and other Debian based distros.
Right for the basic installation, at the command-line, enter the following commands (or search for the listed packages in synaptic if you prefer that way of working):
$ sudo apt-get install postgresql postgresql-client postgresql-contrib
$ sudo apt-get install pgadmin3
This installs the database server/client, some extra utility scripts and the pgAdmin GUI application for working with the database.
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):
$ 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
Now enter the same password that you used previously.
Then, from here on in we can use both pgAdmin and command-line access (as the postgres user) to run the database server. But before you jump into pgAdmin we should 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
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:
$ sudo gedit /etc/postgresql/8.3/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, we must define who can access the server. This is all done using the pg_hba.conf file.1
$ sudo gedit /etc/postgresql/8.3/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
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:
$ sudo /etc/init.d/postgresql-8.3 restart
And all should be working.
-
The following advice can also be given to you (plus you don’t even need to figure out IP addresses and subnet masks) from the latest versions of pgAdmin (1.6.x). But it doesn’t hurt to know how things work. ↩
Pingback: Cómo instalar PostgreSQL en Ubuntu 9.04 Jaunty Jackalope | lobo_tuerto
I did all instruction. After I run pgadmin3 and I see no server in GUI. Do I miss something?
Hi ariza, this is normal. You need to configure the connections to the database yourself using the user details that you have set up.
A+!
Worked perfectly.
Very useful guide. I haven’t used Linux in awhile and this got me started with my PostgresSQL install very nicely! Job well done.
I forgot to mention that i am using ubuntu 8.10 and the postgresql is 8.3
Good! it works like a magic. compact and complete article~
Can’t thank you enough! Couple of minor changes required, but works a charm.
Pingback: Install PostgreSQL di Ubuntu 8.4.2 Hardy Heron » Nanung Nur Zula
great thanks, it worked
Pingback: Postgres could not load private key file | Eager to Code, Enjoy to Debug ~ Embark into Each Stage with Your Heart
Hi.
I feel like the odd man out here. I haven’t been able to get this to work. Could be because this is the first time I came within spitting distance of postgresql. I am trying to install postgresql 8.4.3.1 in Ubuntu Hardy (Desktop).
The two apt-gets appears to have worked.
The next step:
sudo su postgres -c psql template1
gave me this error:
psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket “/var/run/postgresql/.s.PGSQL.5432”?
I can’t find the /etc/postgresql/8.4/main/postgresql.conf, either. I think that should have been created already, right?
Hope you guys can help.
Thanks
wd
Hi @wd,
Sorry but I don’t have an install of Ubuntu back on the 8.04 release (Hardy) in order to try and replicate and debug. Is this a backport package? (I’m assuming it is as this is one of the more recent releases of postgres you’re trying to install).
From the problems you’ve described it sounds like the package installations (apt-get) have failed in some way as the config file(s) are missing and the server isn’t running. What you could do is try to completely remove the packages and then re-install (posting the console output here if you like then we can go from there):
then…
Let me know how you get on.
Pingback: Pentaho Installation on Linux – Postgres « FBA Knowledgebase
Hi
My team developed an extension of PostgreSQL and I want to install it on ubuntu. So I need to install using the options stated here - http://www.postgresql.org/docs/8.3/interactive/install-short.html
In ubuntu, the root user is not there and the “/configure” script does not work. I get “bash: ./configure: /bin/sh^M: bad interpreter: No such file or directory
Please help me.
Hi, sorry for the slow response.
To run things as root in Ubuntu, use ‘sudo’ in front of any command - if you’re an administrator the command will then run as root.
As for the error message you’re getting, without more details about your system it’s a bit of a guess, but do you have the ‘build-essential’ package installed? You can sometimes get messages like this if you don’t have a required compiler installed.
On an aside - not that it’s bad, but why are you trying to build postgres from source? It’s very easy to install postgres using the built in package manager (and it will integrate better with the rest of your system). Unless you have a specific requirement for the most bleeding edge version, i’d use the ubuntu specific packages - it’ll save you time and potential headaches.
Thanks for giving the info about how to install this software via ubuntu. By the way, the software is great.