PostgreSQL database creation

Database users

There are two users involved in the creation of Jiglu databases:

• the owner user, who owns all the databases and their tables, and can be used to perform data definition operations;
• the database user, who is used to connect from the Jiglu server and perform data manipulation operations.

These are represented as internal variables in the SQL scripts and called respectively dbowner and dbuser. The value for dbuser is also used as the name of the database. If you are not running multiple instances of the Jiglu server on the same machine then it will be simplest to use the name jigluowner for the owner user and jiglu for the database user.

Creating the database

To create the owner user, the initial database and the connecting user use:

cd /opt/jiglu/db/postgresql
psql --set dbowner=owner-name \
    --set dbownerpwd=owner-password \
    --set dbuser=user-name \
    --set dbuserpwd=user-password \
    -f create_database.sql postgres postgres

You will be prompted for the postgres user password (i.e. the postgres super user) and the owner user password if these are not set up as environment variables.

Note that because of possible shell expansion issues, you should avoid a dollar sign in the passwords.

Deleting the database

If you make a mistake creating the database then to remove it use:

psql --set dbowner=owner-name
    --set dbuser=user-name
    -f drop_database.sql postgres postgres

You will be prompted for the postgres user password (i.e. the postgres super user). When it is removed the database is not recoverable.