loading table of contents...

3.3.5. PostgreSQL Database

PostgreSQL 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 in the sql.properties file: 

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.

[Caution]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]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 -Fc if 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 dropAll will 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.

Prev Up Next
 Home