Content Server Manual / Version 2101
Table Of ContentsPostgreSQL is an open source SQL database available from http://www.postgresql.org. For the certified PostgreSQL database you need the following JDBC driver:
Up-to-date PostgreSQL JDBC4 driver for your version of PostgreSQL
The JDBC driver is available from the URL http://jdbc.postgresql.org.
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:
If you use pgdump for a complete data base backup, you may use different formats than the standard plain text format due to size and speed. For example, use
-Fcif you have a lot of binary data. If you want to do a selective backup (schema or table), you should check the PostgreSQL documentation on http://www.postgresql.org for details.Access control for blobs in the DB is only possible on a per-database level, not per schema.
cm schemaaccess dropAllwill work in the sense that the data base appears empty to the content server process, but it will not delete the Blobs and therefore does not free the hard disk space.
