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.