Postgre SQL

Creating database

Advanced users can create their own table space

We are going to create a database for clover to use a 'user group' role which will own the database and a user role which we will add to the user group. This user role will be then used by the server to access the database.

Database name: clover_db

UserGroup: cloveretl

User: clover

Password: clover

  1. Optionally, you can create new tablespace

  2. Connect as postgres (default admin) to the default db postgres and execute the following commands:

    CREATE ROLE cloveretl NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN;
    CREATE ROLE clover NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN ENCRYPTED PASSWORD 'clover';
    GRANT cloveretl TO clover;
    CREATE DATABASE clover_db;
    GRANT ALL ON DATABASE clover_db TO cloveretl;
    REVOKE ALL ON DATABASE clover_db FROM public;

To separate database into its own tablespace create a tablespace before creating the database:

and use the following command to create the db:

CREATE DATABASE clover_db WITH OWNER cloveretl TABLESPACE tablespace_name;

https://www.postgresql.org/docs/9.2/static/sql-createtablespace.html

CloverETL setup

If you use a properties file for configuration, specify these parameters: jdbc.driverClassName, jdbc.url, jdbc.username, jdbc.password, jdbc.dialect. For example:

jdbc.driverClassName=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost/clover?charSet=UTF-8
jdbc.username=postgres
jdbc.password=
jdbc.dialect=org.hibernate.dialect.PostgreSQLDialect

Please don't forget to a add JDBC 4 compliant driver on the classpath. A JDBC driver which doesn't meet the JDBC 4 specifications won't work properly.

The JDBC driver for PostgreSQL can be downloaded from: https://jdbc.postgresql.org/download.html. In Apache Tomcat, you would place libraries into $CATALINA_HOME/libs directory.