13
May/08
49

Install PostgreSQL on Ubuntu 8.04

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.


  1. 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. 

Spread the Word
  • Twitter
  • del.icio.us
  • Digg
  • Facebook
  • Google Bookmarks
  • Reddit
  • NewsVine
  • Slashdot
  • connotea
  • HackerNews
  • Print this article!
  • E-mail this story to a friend!
Comments (42) Trackbacks (7)
  1. stg
    4:15 am on May 14th, 2008

    very useful, worked like a charm, ty

  2. amr
    10:11 am on May 20th, 2008

    thank you

  3. Mario R Eraso
    9:28 pm on May 23rd, 2008

    Thanks a lot, very helpful and quick.

  4. Hernan
    11:58 pm on May 23rd, 2008

    that’s a great job! Everything worked fine!

  5. Dylan
    7:02 am on June 2nd, 2008

    Hi, thanks for the guide here. It’s worked perfectly for me (much to my newbie-suprise).

    I’m now wanting to install phppgadmin. I’ve referred to your previous guide for this, but it’s using an older version of Ubuntu.

    Will the guide still work fine for Hardy?

  6. Daz
    8:36 am on June 2nd, 2008
    I’m now wanting to install phppgadmin. I’ve referred to your previous guide for this, but it’s using an older version of Ubuntu. Will the guide still work fine for Hardy?

    Hi, glad everything worked well for you. As for phppgadmin, yep the install for Hardy is exactly the same as the previous guide and shouldn’t give you any problems.

  7. Daniel
    1:44 pm on June 10th, 2008

    Fantastic and quick. Thanks!

  8. Anton
    8:19 pm on June 22nd, 2008

    Thanks, short and sweet.

  9. Ivotron
    1:58 am on June 26th, 2008

    Hi,

    Strange thing happens, I can login using pgAdmin but not through psql. Any advice? I love the command-line

  10. Adult Ühler
    11:45 am on July 2nd, 2008

    Thanks for the guide. Worked perfectly for me too.

  11. anjar
    10:17 pm on July 6th, 2008
    Strange thing happens, I can login using pgAdmin but not through psql. Any advice? I love the command-line

    edit this line in /etc/postgresql/8.3/main/pg_hba.conf

    # Database administrative login by UNIX sockets
    local   all         postgres                          ident sameuser

    change ident sameuser to md5

    restart your postgresql

    then try login to postgreql with psql -U ‘your username’ -W then input your password

    that method works for me

  12. mkyong
    12:31 am on July 14th, 2008

    Hi thanks for the guide of install PostgreSQL in Ubuntu. This is quite big different with Install PostgreSQL in Fedora. Please visit my website if you want to see how the different between install PostgreSQL in fedora and ubuntu

  13. susheel
    1:16 am on July 23rd, 2008

    For some one who is new to ubuntu and postgresql you made my life easy. Thanks is just a small tribute. world need more people like you.

  14. Willie Kuo
    6:32 pm on July 31st, 2008

    Thank you a log. It’s very useful.

  15. Willie Kuo
    6:35 pm on July 31st, 2008

    I’m sorry for the typo “log” (correct word: lot).

  16. bedjo
    2:23 am on August 1st, 2008

    thx a lot. its very useful. I have tried it in ubuntu 7.10 and installed successfully. I use posgresql v8.2. keep the good work!

  17. nanung
    3:10 am on August 5th, 2008

    top top top

  18. Yatzek
    5:43 am on August 24th, 2008

    Trying to install postgreSQL 8.2 on ubuntu 7.10

    when typing:

    $ sudo su postgres -c psql template1

    I get:

    psql: FATAL: database “postgres” does not exist

    what is wrong ??

  19. Neil Robins
    3:24 am on August 25th, 2008

    Hi,

    Typing “sudo su postgres -c psql template1” brings up the prompt “postres=#”, not the expected “template1=#”. What am I doing wrong?

    Thanks,

  20. Tommy Gibbons
    1:44 pm on September 15th, 2008

    Hi Daz, Many thanks for taking the time and effort to put this together with clarity and ease of understanding.

  21. Random
    12:45 pm on September 16th, 2008

    Simple and clear.

    My time is not that precious, but thanks to you I saved a bunch of it…

  22. Mirek
    10:39 am on September 19th, 2008

    On my system, restarting PostgreSQL with instruction:

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

    did not worked. Instead I had to reboot entire system and it is working fine now. Thank you for your tutorial.

    :)

  23. frans van der star
    11:00 pm on September 22nd, 2008

    Fist my appreciations in the way you helping people!!!

    I use postgresql in combination with webmin.

    I use postgresql 8.2 because according to the information I get from “Googling” edition 8.3 seems not good working with sql-ledger-software, which I am using now.

    When I try to get in Posgresql database-server I get following error: SQL select * from pg_database order by datname failed : missing or erroneous pg_hba.conf file.

    Here under I give you the pg_hba.conf file, which I have modified according to your instructions. Last line in config I did not add because I am using local host; I hope I do the right thing with this?

    # PostgreSQL Client Authentication Configuration File
    # ===================================================
    #
    # Refer to the "Client Authentication" section in the
    # PostgreSQL documentation for a complete description
    # of this file.  A short synopsis follows.
    #
    # This file controls: which hosts are allowed to connect, how clients
    # are authenticated, which PostgreSQL user names they can use, which
    # databases they can access.  Records take one of these forms:
    #
    # local      DATABASE  USER  METHOD  [OPTION]
    # host       DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
    # hostssl    DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
    # hostnossl  DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
    #
    # (The uppercase items must be replaced by actual values.)
    #
    # The first field is the connection type: "local" is a Unix-domain socket,
    # "host" is either a plain or SSL-encrypted TCP/IP socket, "hostssl" is an
    # SSL-encrypted TCP/IP socket, and "hostnossl" is a plain TCP/IP socket.
    #
    # DATABASE can be "all", "sameuser", "samerole", a database name, or
    # a comma-separated list thereof.
    #
    # USER can be "all", a user name, a group name prefixed with "+", or
    # a comma-separated list thereof.  In both the DATABASE and USER fields
    # you can also write a file name prefixed with "@" to include names from
    # a separate file.
    #
    # CIDR-ADDRESS specifies the set of hosts the record matches.
    # It is made up of an IP address and a CIDR mask that is an integer
    # (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that specifies
    # the number of significant bits in the mask.  Alternatively, you can write
    # an IP address and netmask in separate columns to specify the set of hosts.
    #
    # METHOD can be "trust", "reject", "md5", "crypt", "password",
    # "krb5", "ident", "pam" or "ldap".  Note that "password" sends passwords
    # in clear text; "md5" is preferred since it sends encrypted passwords.
    #
    # Database administrative login by UNIX sockets
    local all postgres ident sameuser
    
    # OPTION is the ident map or the name of the PAM service, depending on METHOD.
    #
    # Database and user names containing spaces, commas, quotes and other special
    # characters must be quoted. Quoting one of the keywords "all", "sameuser" or
    # "samerole" makes the name lose its special character, and just match a
    # database or username with that name.
    #
    # This file is read on server startup and when the postmaster receives
    # a SIGHUP signal.  If you edit the file on a running system, you have
    # to SIGHUP the postmaster for the changes to take effect.  You can use
    # "pg_ctl reload" to do that.
    
    # Put your actually configuration here
    
    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                          md5
    # 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
    

    I hope you can help me out of this.

    I changed above former one in MD5 and add linux users line

    Again thanks for your specious time for this.

    Greetings Frans van der Star Now: Malaysia after two weeks the Netherlands

  24. frans van der star
    11:14 pm on September 22nd, 2008

    Good day,

    Sorry but this file maybe usefull to you also:

    I try to do password for template1 but I have the idea, password not changed.

    Here is the result:

    $ sudo su postgres -c psql template1
    Welcome to psql 8.2.7, the PostgreSQL interactive terminal.
    
    Type:  \copyright for distribution terms
           \h for help with SQL commands
           \? for help with psql commands
           \g or terminate with semicolon to execute query
           \q to quit
    
    postgres=# template1=# ALTER USER postgres WITH PASSWORD 'frans148'
    postgres-# \q
  25. Daz
    3:00 pm on September 26th, 2008

    Hi,

    Sorry for the slow reply.

    It looks like the semi-colon (;) is missing from the end of the ‘ALTER USER’ command - without this it will not run. Try adding a semi-colon to the end of the command and running it again, does this help?

    Daz

  26. Johan de kok
    3:26 am on October 4th, 2008

    Hi mine doesn’t work well. I can ping with my windows system to my linux server but i cant acces the database with pgadmin3. I get the error that the server doens’t listen. I have un-installed ubuntu many times and try’d this tut again and again and get the same error the hole time. Do i need to forward some ports in my router or so?

    hope anybody have some tips for me

    greets johan de kok

  27. izzy dead?
    4:57 am on October 22nd, 2008

    Thanks a bunch for the info, it’s been really helpful. Now, I have encountered two minor problems so far:

    1) The postgres server doesn’t automatically load when I restart Linux. I have to run the command “/etc/init.d/postgresql-8.3 start” every time and it’s kind or annoying.

    2) When I use the command “sudo su postgres -c psql template1” I get sent to the postgres database. If I try using another user, i.e., if I type the command “sudo su username -c psql dbname”, I get the following error message: “psql: FATAL: database “username” does not exist”.

    Any help with either of these issues would be appreciated, especially number 1).

    Have a day, izzy dead?

  28. Wim
    5:40 am on October 28th, 2008

    Works fine for me. Thank you!

  29. phi
    9:43 am on October 29th, 2008

    Thanks for this explantation. It worked like a charm - im happy with linux again :-)

  30. prasad
    11:58 pm on November 5th, 2008

    I have successfully install postgreSQL But to use that in c language I require header files regarding postgre like postgre.h so how to add that in include directory

  31. Samuel
    5:29 am on November 11th, 2008

    Thank you very much, it was very useful.

  32. emecas
    10:54 am on December 4th, 2008

    Great…Very Useful Thank you

  33. Gilberto Albino
    8:11 pm on February 8th, 2009

    Thanks a lot! This is the only working tutorial I’ve found :D

  34. Tudor
    11:29 am on March 3rd, 2009

    works like a charm! good tutorial

  35. ganbolor
    6:35 pm on May 8th, 2009

    thx, good tutorial, I can start postgreSQL.

  36. Gostixel
    10:04 am on May 16th, 2009

    Вот так,согласен с предыдущими блоггерами ^..^ :-)

  37. hddrecovery
    4:02 am on June 10th, 2009

    I have been fighting with Postresql for over a week. This fix is now safely bookmarked. Thanks for such a clear and straight forward tute.

  38. Thorisoforn
    8:59 am on June 14th, 2009

    Да,несогласен с предыдущими неудачниками Споки :-)

  39. Robert
    7:36 pm on June 16th, 2009

    Thank you - text simple but very helpful. Regards!

  40. Ben
    7:20 pm on June 22nd, 2009

    Hi, i get a message like ..

    reading package lists … done building dependency tree reading state information… done E: Couldn’t find package pgadmin3

    with postgres it is the same … internet is working … :( any advice ??

  41. JD
    8:27 pm on June 24th, 2009

    For those of you who cannot get the restart to work, it’s because you are not doing the restart as root.

    Type “sudo -i” and hit enter. Type “/etc/init.d/postgresql-8.3 restart” and hit enter.

    This worked for Ubuntu Hardy - thanks for the guide.

  42. Daz
    9:34 pm on June 30th, 2009

    @Ben Sounds like it could be a problem with apt talking to the servers - is it still the same now?

Leave a comment