PostgreSQL is an open source SQL database available from http://www.postgresql.org.

For the certified PostgreSQL database you need the org.postgresql:postgresql driver. It is available at https://mvnrepository.com/artifact/org.postgresql/postgresql, and it is preconfigured in the database-drivers pom.xml.

You should delete all other dependencies in database-drivers. They do not harm, but you do not need them, and libraries always bear the risk of introducing security vulnerabilities.

Moreover, configure the following settings:

sql.store.driver=org.postgresql.Driver
sql.store.url=jdbc:postgresql://<host>:<port>/<databasename>
sql.store.user=<DB-UserName>
sql.store.password=<DB-UserPassword>

In a standard PostgreSQL installation the port is 5432. So replace <port> with 5432 and <host> with the name of your PostgreSQL computer. Replace <databasename> with the name of the PostgreSQL database you created using the createdb command.

For PostgreSQL on Azure, the login user name is different from the database user name, and must be configured with an additional property:

sql.store.login-user-name=<DB-UserName>@<Domain>

Caution

When creating the database, take care to select Unicode character encoding:

createdb -E UNICODE <my-databasename>

You need to create a schema for each database user, because CoreMedia CMS requires that the schema name equals the user name. Otherwise, all tables would be created in the public schema. You also have to create the schema before starting the Content Server for the first time. For example, the following commands can be used at the psql command line in order to create one database user for the Content Management Server and Workflow Server (named cm_mgmt in the example) and one database user for the Master Live Server (named cm_master in the example):

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE USER cm_mgmt PASSWORD 'secret';
CREATE SCHEMA cm_MGMT AUTHORIZATION cm_mgmt;
CREATE USER cm_master PASSWORD 'topsecret';
CREATE SCHEMA cm_master AUTHORIZATION cm_master;

It is your choice whether you use different schemas in one database, or separate databases. Both deployment variants are supported.

Note

PostgreSQL requires regular maintenance for proper operation. Please see the PostgreSQL manual for VACUUM and ANALYZE. You may also want to use pg-autovacuum.

Storing blobs in PostgreSQL largeObjects

On PostgreSQL, a different storage format inside the database is used for large blobs. The native PostgreSQL largeObject format supports streaming, and will not cause the server to run out of memory on huge blobs, in contrast to PostgreSQL's bytea type used before

By default, bytea is used for blobs up to 1 MB, largeObject is used for larger blobs. Note that largeObject storage has administrative implications: