Monday, July 4, 2011

Installing PostgreSQL 9.0 on Ubuntu (11.04) using PPA

The version of default PostgreSQL in the latest Ubuntu (11.04) is still 8.4.8.
So I decided to install 9.0 version when I found there is a PPA (Personal Package Archives) for it. (I have no previous version of PostgreSQL in this Ubuntu PC.)

1. Resources.
PostgreSQL PPA: https://launchpad.net/~pitti/+archive/postgresql

2. Add PPA repository to my Ubuntu.
$ sudo add-apt-repository ppa:pitti/postgresql

3. Install PostgreSQL 9.0
$ sudo apt-get update
$ sudo apt-get install postgresql

4. Check the installation [updated 2011-07-24]
$ locate postgresql  # check the locations of postgresql files
...
/etc/init.d/postgresql                      # server daemon
/etc/logrotate.d/postgresql-common
/etc/postgresql/9.0
/etc/postgresql/9.0/main                    # directory for config files
/etc/postgresql/9.0/main/environment
/etc/postgresql/9.0/main/pg_ctl.conf
/etc/postgresql/9.0/main/pg_hba.conf
/etc/postgresql/9.0/main/pg_ident.conf
/etc/postgresql/9.0/main/postgresql.conf
/etc/postgresql/9.0/main/start.conf
...
/usr/lib/postgresql                          # server apps
/usr/lib/postgresql/9.0
/usr/lib/postgresql/9.0/bin
/usr/lib/postgresql/9.0/lib
...
/var/lib/postgresql                          # home directory for root user postgres
/var/lib/postgresql/9.0
/var/lib/postgresql/9.0/main                 # I will create error log directory (pg_log) here
/var/lib/update-rc.d/postgresql
/var/log/postgresql
/var/log/postgresql/postgresql-9.0-main.log
/var/log/postgresql/postgresql-9.0-main.log.1
...

$ psql -V             # check psql version
psql (PostgreSQL) 9.0.4

$ finger postgres     # check if user postgres is created
Login: postgres          Name: PostgreSQL administrator
Directory: /var/lib/postgresql       Shell: /bin/bash
Never logged in.
No mail.
No Plan.

$ su postgres          # switch user to postgres
$ psql                 # launch psql as postgres and check the server version
psql (9.0.4)

postgres=# select version();
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.0.4 on i686-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit


5. Setup Root User 'posrgres'
The installer has created a unix user postgres without a password. So first I give a (unix) password to this special user. The user postgress is a root user (database administrator) of PostgreSQL Server but without a (PostgreSQL) password. So I give it one here as well.
$ sudo passwd postgres # give the postgres user a (unix) password
$ su postgres           # switch to the user postgres
$ psql                   # launch psql to give postgres PostgreSQL passord
postgres=# alter user postgres with password 'secret';  # new password for postgres 
ALTER ROLE
postgres=# \q            # quit psql
$ exit                   # exit from user 'postgres'
exit


6.Configure PostgreSQL Server
I want to make some changes to server config.

Make backup copies of config files.
$ su postgres    # switch to the user postgres
$ cd /etc/postgresql/9.0/main
$ ls -la
...
-rw-r----- 1 postgres postgres  4108 2011-07-02 19:54 pg_hba.conf
-rw-r--r-- 1 postgres postgres 18064 2011-07-02 19:54 postgresql.conf
...

postgres@ubuntu-pc:/etc/postgresql/9.0/main$ cp pg_hba.conf pg_hba.conf.bak.original 
postgres@ubuntu-pc:/etc/postgresql/9.0/main$ cp postgresql.conf postgresql.conf.bak.original 

postgres@ubuntu-pc:/etc/postgresql/9.0/main$ ls -la
...
-rw-r----- 1 postgres postgres  4108 2011-07-02 19:54 pg_hba.conf
-rw-r----- 1 postgres postgres  4108 2011-07-03 18:33 pg_hba.conf.bak.original
-rw-r--r-- 1 postgres postgres 18064 2011-07-02 19:54 postgresql.conf
-rw-r--r-- 1 postgres postgres 18064 2011-07-03 18:33 postgresql.conf.bak.original
...

Make changes to pg_hba.config (authetification methods).
host    all         all       127.0.0.1/32       trust          # md5 -> trust

Make changes to postgresql.conf for error log.
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
log_destination = 'stderr'  # 2011.07.04 - enabled
logging_collector = on   # 2011.07.04 - enabled and turned on
log_directory = 'pg_log'  # 2011.07.04 - enabled. I will create this folder (see below).
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # 2011.07.04 - enabled
log_truncate_on_rotation = off  # 2011.07.04 - enabled and keep it off
log_rotation_age = 1d   # 2011.07.04 - enabled
log_rotation_size = 10MB  # 2011.07.04 - enabled

Create a log directory as specified in the config above.[updated on 2011-07-24]
$ su postgres         # switch to postgres user
$ cd ~/9.0/main/      # /var/postgresql/9.0/main
$ mkdir pg_log        # create a new log directory as specified in the config file above
$ ls -F               # confirm the new directory 'pg_log'
PG_VERSION  pg_log/ ...
$ exit

7. Restart the server [new]
I have to restart the server to use new configurations.
$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.0 database server  
$ sudo /etc/init.d/postgresql status
Running clusters: 9.0/main 

8. Check the new log. [new]
Now I should have a new log generated.
$ sudo ls /var/lib/postgresql/9.0/main/pg_log
postgresql-2011-07-23_135126.log

9. Finally Install pgAdmin III (GUI tool for PostgreSQL) [new]
Since the same PPA repository (ppa:pitti/postgresql) also contains the latest pgAdmin III, I can install it with a simple apt-get command.
$ sudo apt-get install pgadmin3   # install the latest pgAdminIII
$ pgadmin3                        # launch it
It is version 1.12.2.

22 comments:

  1. This is a thing of beauty. You just saved me a ton of time. Thank you!

    ReplyDelete
  2. I used this to install and configure 8.4 as well. Thank you for the clear concise language.

    ReplyDelete
  3. First time I tried to configure it all, I lost about 3 hours to find out how to do that properly. Your lead through all the stages is so clear and makes things extremely easy. Thanks a lot, well done!

    ReplyDelete
  4. At step $su postgres it is asking me for password. I am eventually ending up in su: Authentication error.

    Any suggestions.

    ReplyDelete
  5. > At step $su postgres it is asking me for password...
    Initially, the user postgres does not have password. So you can give any (unix) password (Step 5).

    ReplyDelete
  6. Thanks a lot. It was really easy. Anything about postGIS?

    ReplyDelete
  7. we followed the above steps and installed successfully.But now we want uninstall postgres.Can u help us to uninstall PostgreSql completely?.

    ReplyDelete
  8. Hugely useful, thanks for posting this.

    ReplyDelete
  9. Really useful guide! Thanks!

    ReplyDelete
  10. Xcellent article

    I used for my CrunchBang Linux 10 openbox

    and works perfectly

    ReplyDelete
  11. i Follow each and every thing Except pg_log Section it didn't show me desire out put. please help in this regards..

    postgres@MIS:/etc/postgresql/9.1/main$ mkdir pg_log
    postgres@MIS:/etc/postgresql/9.1/main$ ls -f
    pg_hba.conf.bak.original environment pg_ident.conf start.conf postgresql.conf postgresql.conf.bak.original pg_log pg_hba.conf . pg_ctl.conf ..
    postgres@MIS:/etc/postgresql/9.1/main$ ls -F
    environment pg_ctl.conf pg_hba.conf pg_hba.conf.bak.original pg_ident.conf pg_log/ postgresql.conf postgresql.conf.bak.original start.conf
    postgres@MIS:/etc/postgresql/9.1/main$ PG_VERSION pg_log/
    PG_VERSION: command not found
    razakhalid@MIS:~$ sudo ls /var/lib/postgresql/9.1/main/pg_log
    ls: cannot access /var/lib/postgresql/9.1/main/pg_log: No such file or directory

    ReplyDelete
  12. Raza,

    You created pg_log directly in a wrong place.
    It should be under postgres user's home directory.

    ReplyDelete
  13. Perfect with Ubuntu 11.04!
    Thanks!

    ReplyDelete
  14. I can only agree with all of the positive comments above. Great document!

    ReplyDelete
  15. Top job, my man!

    ReplyDelete
  16. also if you are using a virtual machine to install the server or you have a different client machine don't forget to change the .conf file to accept connections on all '*' or the specified IP address.

    ReplyDelete
  17. When I take the very first step .. sudo add-apt-repository ppa:pitti/postgresql
    it says ..

    Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --secret-keyring /etc/apt/secring.gpg --trustdb-name /etc/apt/trustdb.gpg --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --keyserver hkp://keyserver.ubuntu.com:80/ --recv FB322597BBC86D52FEE950E299B656EA8683D8A2
    gpg: requesting key 8683D8A2 from hkp server keyserver.ubuntu.com
    ?: keyserver.ubuntu.com: Host not found
    gpgkeys: HTTP fetch error 7: couldn't connect: No such file or directory
    gpg: no valid OpenPGP data found.
    gpg: Total number processed: 0

    Can someone resolve me this error ?

    ReplyDelete
  18. Thanks! Really cool and clear How-to! A small addition for Ubuntu-Users: su is disabled in the basic configuration. You can use sudo -i instead to get a root-shell.

    ReplyDelete
  19. after type $ sudo add-apt-repository ppa:pitti/postgresql result like buttom,

    why the result is unchanged: 1

    Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --secret-keyring /etc/apt/secring.gpg --trustdb-name /etc/apt/trustdb.gpg --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --keyserver hkp://keyserver.ubuntu.com:80/ --recv FB322597BBC86D52FEE950E299B656EA8683D8A2
    gpg: requesting key 8683D8A2 from hkp server keyserver.ubuntu.com
    gpg: key 8683D8A2: "Launchpad PPA for Martin Pitt" not changed
    gpg: Total number processed: 1
    gpg: unchanged: 1

    ReplyDelete
  20. Not so far I have found new cool tool to work with PostgreSQL on ubuntu — Valentina Studio. Its free edition can do things more than many commercial tools!!

    I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview

    ReplyDelete