1.3. Creating a Database

The first test to see whether you can access the database server is to try to create a database. Running Postgres-XC servers (coordinators and datanodes) 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 Postgres-XC 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 Postgres-XC user account for you. (Postgres-XC user accounts are distinct from operating system user accounts.) If you are the administrator, see Chapter 19 for help creating accounts. You will need to become the operating system user under which Postgres-XC was installed (usually postgres) to create the first user account. It could also be that you were assigned a Postgres-XC 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 Postgres-XC 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:

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

Not every user has authorization to create new databases. If Postgres-XC 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 Postgres-XC yourself then you should log in for the purposes of this tutorial under the user account that you started the server as. Or you may have error message like:

createdb: database creation failed: ERROR:  source database "template1" is being accessed by other users
DETAIL:  There are 1 other session(s) using the database.

This means that at least one of the coordinator pooler still holds a connection to template1 database to one of the datanodes. To release them, you should do the following as the database superuser.

$ psql
...
koichi=# CLEAN CONNECTION TO ALL FOR template1;
CLEAN CONNECTION
koichi=#

[1]

You can also create databases with other names. Postgres-XC 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 can be found in createdb and dropdb respectively.