This page in other versions: 8.4 / 9.0 / 9.1 / 9.2 / 9.3  |  Development versions: devel  |  Unsupported versions: 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3

1.3. Creating a Database

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 this 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

This should produce as response:

CREATE DATABASE

If so, 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 the search path was not set correctly. 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 back in the installation instructions to correct the situation.

Another response could be this:

createdb: could not connect to database template1: 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.

If you do not have the privileges required to create a database, you will see the following:

createdb: database creation failed: ERROR:  permission denied to create database

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 characters 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 may be found in createdb and dropdb respectively.

Notes

[1]

As an explanation for why this works: PostgreSQL user names are separate from operating system user accounts. If 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


Nov. 3, 2005, 10:16 p.m.

Paraphrased from PostgresSql Essential reference page 228:

For Package Based install (eg debian apt-get):
1. install
2. verify there's a postgres user (cat /etc/passwd and look for postgres)
3. from root, su as postgres
4. create users > createuser web
5. or, createuser (your username) (yes to create db) (yes to create users)
6. now, as youself (username in 5) you can do all the things in this tutorial...
-Todd


July 16, 2007, 7:29 a.m.

Installation of postgresql-7.4.3 on cygwin 1.5.24

It's very important to add postgresql-7.4.3/lib into $PATH environment variable. I had been successfully running the same version of postgresql on cygwin. Then after a while, I reinstalled cygwin and tried to get postgresql-7.4.3 running again. But my createdb and psql command didn't return anything, even with "--help". I thought it has something to do with the new cygwin version. After trying several times and getting no results from documents and google, it became very frustrating. Then I decided to try one more time and copied my old .bashrc to current home directory. It turns out I my $PATH only included postgresql-7.4.3/bin, without postgresql-7.4.3/lib. And my old .bashrc contains the lib directory, and it worked.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group