Jason’s Ranting & Raving

Those who don’t read have no advantage over those who can’t.

Ubuntu: Howto Setup a Local PostgreSQL and pgAdmin

Posted by jaystile on February 13, 2009

I’m setting up a project for some prototyping web development I’ve been doing. I’m looking at configuring Ubuntu 8.04 Hardy Heron to use Postgresql 8.3. I’ve decided to document my steps (so I don’t forget them).

First off, I’m borrowing heavily from this site: www.ubuntugeek.com which configured Postgresql 8.2 for Ubuntu 7.10. Because I’ve found that Ubuntu, while very helpful in making installations easier, usually does not follow the default installation instructions. Of course you could always RTFM: PostgreSQL Documentation

Download the Postgres 8.3
I used Synaptec Package Manager tool to download the packages ‘postgresql’, ‘postresql-client’, and ‘pgadmin3’. I’m sure you could use the ‘apt-get’ instead.

Configure Postgres Login
Now we need to reset the password for the ‘postgres’ admin account for the server. If you’re going to expose this to the outside world replace ‘password’ with a strong password, like ‘I3!ABaie#9’.

sudo su postgres -c "psql template1"
template1=# ALTER USER postgres WITH PASSWORD ‘password’;
template1=# \q

For version 8.4 I had to use the following code to get pdAdminIII to work:

sudo -u postgres psql postgres
psql (8.4.4)
Type "help" for help.

postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q

That alters the password for within the database, now we need to do the same for the unix user ‘postgres’. Make sure you use the password you entered inside of psql.

sudo passwd -d postgres
sudo su postgres -c passwd

From here on in we can use both pgAdmin and command-line access (as the postgres user) to run the database server. Postgres 8.3 is configured to use local access only by default. If you want to open up PostgreSQL follow the link mentioned at the beginning of this article.

Restart the PostgreSQL server.

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

I was having a hard time logging with pgadmin3. It turns out I was forgetting the ‘;’ semicolon in the ALTER USER statement.

Now What?
Now, that you have a database why don’t you do something with it? Like write a java application.


2 Responses to “Ubuntu: Howto Setup a Local PostgreSQL and pgAdmin”

  1. […] Posted by jaystile on April 1, 2009 I wanted to access my local postgresql database with JDBC and JPA in the J2SE environment. Refer to my previous post about setting up Postgres 8.3 on Ubuntu 8.04. […]

  2. larry m said

    my hero

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: