PostgreSQL Create a database

Let's see if you can access the database server. The first example is trying to create a database. A running PostgreSQL server can manage many databases. Typically we use a single database for each project and for each user.

The node administrator may have created a database for you to use. He should have told you the name of the database. If so, you can omit this step and skip to the next section.

To create a new database (called mydbin our example), you can use the following command:

$createdb mydb

It should generate a response such as the following:

CREATE DATABASE

If so, then this step succeeds, and you can ignore the rest of the section.

If you see a message like this:

Createdb:command not found

Then PostgreSQL is not installed: either it is not loaded at all, or the search path is not set correctly. Try calling the command with an absolute path:

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

This path may not be the same on your node. Please contact your administrator or see the installation instructions to get the correct location.

Another response might 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 does not start or does not start up where createdb is expected. Also, you should check the installation instructions or find an administrator.

Another response might be this:

Createdb:could not connect to database postgres:fatal:  role ' Joe ' does not exist

Mention your own login name here. These images can occur if the administrator has not created a PostgreSQL user account for you. PostgreSQL user accounts and operating system user accounts are different. If you are an administrator, see Chapter 18 for help creating user accounts. You need to become the user of the operating system that is installing PostgreSQL (typically postgres) to create the first user account. It is also possible to assign PostgreSQL usernames and operating system usernames differently, in which case you need to use the- u switch or use the pguser environment variable to declare the PostgreSQL user name.

If you have a database user account but do not have the necessary permissions to create a database, you will see something like this:

Createdb:database creation Failed:error:  Permission denied to create database

Not all users have been authorized to create a new database. If PostgreSQL refuses to create a database for you, you need to have the node administrator give you permission to create the database. Check with your node administrator when this happens. If you installed PostgreSQL yourself, then you should log in as the user who started the database server and then refer to the manual to complete the assignment of permissions. [1]

You can also create a database with a different name. PostgreSQL allows you to create any number of databases on a single node. The database name must start with a letter and be less than 63 characters in length. A handy way to do this is to create a database with the same name as your current user name. Many tools assume that it is the default database name, so you can save keystrokes. To create such a database, you only need to type:

$createdb

If you don't want to use your database anymore, you can delete it. For example, if you are the owner (creator) of the database mydb , you can delete it using the following command:

$dropdb mydb

For this command, the database name is not the default user name, and you must explicitly declare it. This action physically deletes all files associated with the database and is not recoverable, so be sure to do so before doing this.

More information about createdb and dropdb can be found in sections createdb and dropdb .

Attention

[1]

Why are you doing this? This is explained as follows: PostgreSQL user name is separate from the operating system user account. If you are connected to a database, you can specify which PostgreSQL user name to connect to, and if you do not specify, then the default is your current operating system account. If so, there will always be a PostgreSQL user account with the same name as the operating system user to start the server, and usually this user has permission to create the database. If you do not want to log in as the user, you can also declare a- u option anywhere to select a PostgreSQL user name to use when connecting.

PostgreSQL Create a database