Skip to content

Setup PostgreSQL in Ubuntu

August 9, 2008

This is a quick setup guide on how to setup PostgreSQL in Ubuntu 8.04 “Hardy Heron”. This is far from a complete PostgreSQL guide, but It should be enough to get you started.

Install PostgreSQL

Install the postgresql-8.3 package from Synaptic or apt-get:

$ sudo apt-get install postgresql-8.3

You might also want to install the pgadmin3 tool to help you manage your database:

$ sudo apt-get install pgadmin3

Reset the password of the postgres user

The default superuser, called ‘postgres’, does not have a password by default. We need to add one:

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

Where ‘password’ is your password. After this we need to modify the password of the postgres UNIX user:

$ sudo passwd -d postgres
$ sudo su postgres -c passwd

You will be asked for a new password, enter the same password with the one you specify in the ALTER USER statement above.

Allow local users to access the server

The default PostgreSQL installation in Ubuntu requires that a PostgreSQL user must also be a unix user. I don’t like it this since it makes it hard for us to create a new PostgreSQL user. I want a PostgreSQL user to be different than a UNIX user. To do this, we need to change the configuration in the pg_hba.conf file:

$ sudo gedit /etc/postgresql/8.3/main/pg_hba.conf

Replace out the line (comment it out by adding a ‘#’ in front of it):

local all all ident sameuser

With this line:

local all all md5

After that you will need to restart the server:

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

Now you’re all set. All you need to do is to create a new user, and a database and start coding.

Some important commands to remember:

  1. Creating a new PostgreSQL user
    $ sudo -u postgres createuser -d -R -P <new username>
  2. Create a new database owned by <username>
    $ sudo -u postgres createdb -O <usename> <database name>
  3. Executing an SQL file
    $ psql -f <filename> <database name> <username>
  4. Removing a database
    $ dropdb -U <username> <database name>
  5. Removing a PostgreSQL user
    $ sudo -u postgres dropuser <usernamen>
23 Comments
  1. Mark permalink

    Thanks you helped me out. I just have one question about the local all all md5.

    I am trying to use PGadmin3 from my home machine and log into a database on my friend’s machine. I don’t have a static ip address at home, so what can I put in the pg_hba.conf file to allow me to log in.

    Thanks

  2. @Mark: I’m glad to can help you.

    Maybe you can try: host all all 0.0.0.0/0 md5
    Be aware: This setting allows anyone in the net, as long as he knows the username/password, to connect to your server.

  3. Thanks dude. I now have psql running. :D

  4. Dinesh Dharme permalink

    Thanks for providing such a handy commands.

  5. marc permalink

    nicely done! worked like a charm. postgres up and running on my netbook/Ubuntu 9.04 (jaunty)

  6. Wow, I found this place on Google searching for something totally unrelated- now I’m going to have to read all the old posts! So long spare time this morning, but this was a truly spectacular find :D

  7. Thad Fleming permalink

    Thanks for the guide, however the install seems to be missing something. When trying to log in as postgres the following error shows:

    thad@Lobo1:~$ sudo su postgres -c psql template1
    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”?
    Any help would be appreciated as I will be using postgresql 8.4 to use Gnumed for my office.

  8. Well I was just searching on Google for some videos and songs of my favorite singers and just came across your blog, generally I just visit blogs and retrieve my required information but this time the useful information that you posted in this post compelled me to reply here and appreciate your good work. I just bookmarked your blog :).

  9. xxx permalink

    Thanks. Exactly what I was looking for!

  10. Hallo Dude , i love w/ ur blog. LOL Please come to my blog

  11. I found the instructions here out of date (since 8.4 included in Ubuntu repo) and confusing (which of these commands need executing within the Postgres shell?).

    If you find the same problems try this URL for official PostgreSQL on Ubuntu advice.

    Try https://help.ubuntu.com/community/PostgreSQL

  12. ashok permalink

    thank you dear.

  13. Reblogged this on My private-public notes and commented:
    Vary handy post about basics of PostgreSQL on Ubuntu

  14. Howdy I am so excited I found your blog, I really found you by mistake, while
    I was browsing on Askjeeve for something else, Anyhow I am here now and would just like to say thanks for
    a incredible post and a all round exciting blog (I also love the theme/design), I don’t have time to read it all at the moment but I have book-marked it and also added your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the great job.

  15. I all the time used to study post in news papers
    but now as I am a user of web therefore from now I am using net for articles or reviews, thanks to web.

  16. Johng325 permalink

    As soon as I found this internet site I went on reddit to share some of the love with them. eddddkfakcgb

  17. Nader fajowy post, badawcze wpisy polecam wszystkim lekturę

  18. Szczególnie skuteczny tekst, zalecam ludziom

  19. Interesujące spojrzenie na historię, każdy winien rozczytać i zaznajomić się z tematem.

  20. It seems like awesome post, on the other hand it just just one side from the medal. Awesome reading in any case, I generally appreciated superior brain teaser and solid amount of awesome information.

Trackbacks & Pingbacks

  1. Yiyang’s Weblog » Setup PostgreSQL in Ubuntu
  2. PostgreSQL with PHP in Ubuntu: Step-by-Step How To! « Shafiul Azam's Weblog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: