Setting up a PostgreSQL database for Content Platform Engine

PostgreSQL is an open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

Many vendors and software as a service offerings are available sources of a PostgreSQL instance. You can also download and install PostgreSQL at https://www.postgresql.org/download/. The FileNet Content Manager qualifies its services using the PostgreSQL from that download.

Create the PostgreSQL instance with the following considerations:
  • In general the FileNet Content Manager components require a growing amount of data storage over time. It is recommended to do some advance planning to ensure you can provide sizing settings to meet those needs.
  • Set max_prepared_transactions to a value that will accommodate your workload. PostgreSQL recommends that max_prepared_transactions to be at least as large as max_connections so that every session can have a prepared transaction pending. To estimate the number of database connections that might be concurrently active within a FileNet P8 domain, see the topic "Tuning a PostgreSQL database for IBM FileNet P8 components"
  • Encoding (Character set) required: UTF8
  • Locale settings (Collation) must include support for UTF8 .

As a best practice, create a database user to act as the owner of the database and to use in the data source definition for the connecting database user credentials. Grant the user full access to the database and revoke public access to ensure that only the owning user can access the database. Create tablespaces to store the data for the database and make the database connection user the owner of the tablespace. More than one tablespace might be needed to allow data to be separated to reduce I/O bottlenecks and increase performance. For example, distinct tablespaces can be created to store tables and indexes separately. Grant the database user create privileges for all the tablespaces.

When creating the databases for your FileNet P8 domain, it is recommended to create a database using template0 to ensure if contains only the standard objects predefined by your version of PostgreSQL. This approach is useful if you wish to avoid copying any installation-local objects that might have been added to template1. In particular, template0 should be used to allow the required encoding and locale settings.

For example, the following sample set of psql commands might be used to create a database to use with an object store. The required encoding of UTF8 is included but the locale settings are omitted. For the locale settings, the default values for these categories used are those determined when initdb was run.

Tip: PostgreSQL is case sensitive and only accepts identifiers as lowercase.
Tip: To create a database, you must be a superuser or have the special CREATEDB privilege.
create database os1db owner ceuser template template0 encoding UTF8 ;
revoke connect on database os1db from public;
grant all privileges on database os1db to ceuser;
grant connect, temp, create on database os1db to ceuser;

create tablespace os1db_tbs owner ceuser location '/pgsqldata';
grant create on tablespace os1db_tbs to ceuser; 

What to do next

To configure an SSL connection for your PostgreSQL database, see topic (V5.5.5 and later) Configuring an SSL connection for PostgreSQL.