Friday, February 09, 2007

Setting up Postgres -The Last Mile

Every time I have to setup PostgreSQL, I always get stumped with how to setup my first database. There's this sort of chicken and egg problem you run into. You can't create the first database till you have a database. Which you can't create, till you have a database.

This problem is trivial to solve, but I always forget how it's done. So, I'm writing it down here once and for all. I expect to visit this page again sometime in the distant future. I plan to thank myself then.

Here's how I installed Postgres a few days ago...

  1. Install the RPMS:
        postgresql-8.1.4-1PGDG.i686.rpm
        postgresql-contrib-8.1.4-1PGDG.i686.rpm
        postgresql-docs-8.1.4-1PGDG.i686.rpm
        postgresql-libs-8.1.4-1PGDG.i686.rpm
        postgresql-server-8.1.4-1PGDG.i686.rpm
    
  2. Become the postgres user
  3. Run /usr/bin/initdb
  4. Connect to the Postgres server and create a user and create a database
        /usr/bin/psql postgres
        CREATE USER whoever SUPERUSER ENCRYPTED PASSWORD 'whatever';
        CREATE DATABASE foo;
    
  5. Become my regular user again

This point, you can now connect to Postgres using the user you created and get to the database you made as well.

If you are going to be connecting to Postgres remotely you'll want to edit /var/lib/pgsql/data/pg_hba.conf to control which hosts can access the server.

You'll notice in a fresh pg_hba.conf file that there's the keyword trust on the open connection lines. It's that setting that allows you to get in to create your first database without knowing a password.

2 comments:

  1. Hi Ben, I think the usual choice is to connect to database template1, which should exist in all postgres installations, without having to run anything.

    Dan Muresan
    Omnigia.com: Scheme and the web

    ReplyDelete
  2. Thanks Dan - that's another good tip.

    ReplyDelete