Common PostgreSQL problem

I see this problem pop up in the #postgresql IRC channel so often I felt it was necessary to blog about it. This problem trips up so many new users it might even be worth changing the default error message to indicate what is going on. The error message happens when the user tries to run psql for the first time:

psql: FATAL: database "root" does not exist

Where " root" is the current Unix username of the operator. By default PostgreSQL attempts to log you into a database that is the same as your username. However, it does not setup this database for you because it would be silly to setup 500 databases for all of the Unix users on your system, if only two of them are going to be using PostgreSQL.

When setting up PostgreSQL for the first time you need to do the following:

1. su ( or otherwise ) become your root user
2. su ( or otherwise ) become your PostgreSQL user, typically 'postgres'
3. Create your first database

The ultimate goal here is to become your PostgreSQL user, typically this involves becoming root and then switching to user postgres. Upon setup this is the only user that is allowed to create users and databases.

Your "first" database can be created in one of two ways:

1. Run the command 'psql template1' followed by a 'CREATE DATABASE' SQL call
2. Run the command 'createdb '

While you're still the postgres user it is probably best to also create a user with 'createuser ' or a 'CREATE USER' SQL call. See this section of the PostgreSQL documentation for more information on creating users and roles. You'll also want to read up on managing databases.

NOTE: The programs createdb and createuser may not be, by default, in your PATH so it may be necessary to use locate or type in the full path to your PostgreSQL bin/ directory.

Hope this helps!