The first test to see whether you can access the database
server is to try to create a database. A running PostgreSQL server can manage many databases.
Typically, a separate database is used for each project or for
each user.

Possibly, your site administrator has already created a
database for your use. He should have told you what the name of
your database is. In that case you can omit this step and skip
ahead to the next section.

To create a new database, in this example named mydb, you use the following command:

$createdb mydb

If this produces no response then this step was successful and
you can skip over the remainder of this section.

If you see a message similar to:

createdb: command not found

then PostgreSQL was not
installed properly. Either it was not installed at all or your
shell's search path was not set to include it. Try calling the
command with an absolute path instead:

$/usr/local/pgsql/bin/createdb mydb

The path at your site might be different. Contact your site
administrator or check the installation instructions to correct
the situation.

Another response could be this:

createdb: could not connect to database postgres: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

This means that the server was not started, or it was not
started where createdb expected it.
Again, check the installation instructions or consult the
administrator.

Another response could be this:

createdb: could not connect to database postgres: FATAL: role "joe" does not exist

where your own login name is mentioned. This will happen if
the administrator has not created a PostgreSQL user account for you.
(PostgreSQL user accounts are
distinct from operating system user accounts.) If you are the
administrator, see Chapter 20 for
help creating accounts. You will need to become the operating
system user under which PostgreSQL was installed (usually postgres) to create the first user account. It
could also be that you were assigned a PostgreSQL user name that is different from
your operating system user name; in that case you need to use the
-U switch or set the PGUSER environment variable to specify your
PostgreSQL user name.

If you have a user account but it does not have the privileges
required to create a database, you will see the following:

Not every user has authorization to create new databases. If
PostgreSQL refuses to create
databases for you then the site administrator needs to grant you
permission to create databases. Consult your site administrator
if this occurs. If you installed PostgreSQL yourself then you should log in
for the purposes of this tutorial under the user account that you
started the server as. [1]

You can also create databases with other names. PostgreSQL allows you to create any number
of databases at a given site. Database names must have an
alphabetic first character and are limited to 63 bytes in length.
A convenient choice is to create a database with the same name as
your current user name. Many tools assume that database name as
the default, so it can save you some typing. To create that
database, simply type:

$createdb

If you do not want to use your database anymore you can remove
it. For example, if you are the owner (creator) of the database
mydb, you can destroy it using the
following command:

$dropdb mydb

(For this command, the database name does not default to the
user account name. You always need to specify it.) This action
physically removes all files associated with the database and
cannot be undone, so this should only be done with a great deal
of forethought.

More about createdb and dropdb can be found in createdb
and dropdb respectively.

Notes

As an explanation for why this works: PostgreSQL user names are separate from
operating system user accounts. When you connect to a
database, you can choose what PostgreSQL user name to connect as; if
you don't, it will default to the same name as your current
operating system account. As it happens, there will always be
a PostgreSQL user account
that has the same name as the operating system user that
started the server, and it also happens that that user always
has permission to create databases. Instead of logging in as
that user you can also specify the -U
option everywhere to select a PostgreSQL user name to connect as.

Comments

Sept. 4, 2009, 7:04 p.m.

Please note that if you happen to be the system administrator (hopefully on your personal machine) on a Linux system, many Linux distributions create an user ID called "postgres" or something similar. You need to su into this account from the root account in order to be able to access the system wide installation of the postgresql server for the first time.I had to do a Google search to figure it out. So I thought I might document it here as well.

Dec. 9, 2009, 12:53 a.m.

I wasn't able to use the steps above to create the database without reading beyond this point. The syntax that I found worked for creating the db is this: CREATE DATABASE "mydb";. Note that I did not use the dollar sign. The double quotes are important to maintain the case, since SQL is case sensitive, or the case will be converted to lower. As usual, the semicolon is necessary to indicate the end of the SQL statement.

Jan. 4, 2010, 2:17 p.m.

To the person using the "create database .." syntax:

You are misinformed and perhaps misinforming too. You are referring to SQL syntax, where indeed the line "create database 'mydb'" will be interpreted as instructions to create a database. In order to use the SQL syntax however, some sort of SQL parsing client needs to be used. Like PHP, or part of a C program parsing SQL queries.

This page however explains creating databases from the operating system shell, where a specific binary called 'createdb' is used to access database creation promptly, without resorting to SQL language.

This is the important difference. Even though 'createdb' did not work for you, rest assured you CANNOT create databases typing 'CREATE DATABASE "mydb"' at Windows 'cmd' or UNIX sh prompt. You are mixing terms here. You yourself have mentioned 'SQL', while it is clear the article does not cover the use of SQL, but instead how to create Postgres databases from generic shell command line (as opposed to some sort of SQL-enabled shell).