Creating PostgreSQL database and table spaces for a Content Platform Engine object store

Create a database and table spaces for a Content Platform Engine object store on PostgreSQL. Each additional object store requires an additional table space and a unique table space user.

About this task

  • Do not share the database that is used for the GCD with object stores or IBM® Content Navigator configuration data.
  • The database name needs to be unique. The table space name must be at most 18 characters long.
  • Set max_prepared_transactions to a value that accommodates 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 IBM FileNet® P8 domain, see topic Tuning a PostgreSQL database for Content Platform Engine.
  • Encoding (Character set) required - UTF8.
  • Locale settings (Collation) must include support for UTF8.
  • FileNet Content Manager recommends usage of a dedicated table space for each database for better performance and easier administration.

If you want to add a workflow system to an object store that does not already have one, the workflow system can use the table spaces that are used by the object store. Alternatively, you can create new table spaces for the workflow system, according to the rules on table space types and minimum sizes that are documented here.

If you are creating a table space for a new object store on an existing system, define the new table space with the same table space type and storage method that is used for existing object store table spaces.

The following table shows the recommended table space names, types, and minimum sizes:
Table 1. Recommended table space names, types, and minimum sizes
table spaces Minimum Size (MB) Description
DATA_TS 768 This is the default name of the data table space that is used by Content Platform Engine.
Icon representing the worksheet
Record this value in your customized Installation and Upgrade Worksheet. To find this property, use the Autofilter drop-down arrow in Column F, Property or Parameter (in user interface), to select Data table space name.
index_ts

(optional)

512 This is the default name of the optional default index table space that is used by Content Platform Engine. If you do not create an index table space, the data table space will be used for indexes.
Icon representing the worksheet
Record this value in your customized Installation and Upgrade Worksheet. To find this property, use the Autofilter drop-down arrow in Column F, Property or Parameter (in user interface), to select Index table space name.
lob_ts

(optional )

512 This is the default name of the optional default LOB table space that is used by Content Platform Engine. If you do not create a LOB table space, the data table space will be used for LOB data.
Icon representing the worksheet
Record this value in your customized Installation and Upgrade Worksheet. To find this property, use the Autofilter drop-down arrow in Column F, Property or Parameter (in user interface), to select LOB table space name.
user temporary ts 80 This is the default user temporary table space, which is required for Content Platform Engine use.
system temporary ts 80 This is the default system temporary table space, which is required for Content Platform Engine use.

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: The Content Platform Engine workflow system only accepts lowercase identifiers (like database and tablespace name) for PostgreSQL support, so make sure you prepare the database with only lowercase identifiers.
Tip: To create a database, you must be a superuser or have the special CREATEDB privilege.
-- create user <username>
CREATE ROLE <username> WITH INHERIT LOGIN ENCRYPTED PASSWORD '<your_password>';
-- please modify location follow your requirement
create tablespace os1db_tbs owner <username> location '/pgsqldata/os1db';
grant create on tablespace os1db_tbs to <username>;
-- create database os1db
create database os1db owner <username> tablespace os1db_tbs template template0 encoding UTF8 ;
revoke connect on database os1db from public;
grant all privileges on database os1db to <username>;
grant connect, temp, create on database os1db to <username>;
-- create a schema for os1db and set the default
-- connect to the respective database before executing the below commands
\connect os1db;
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <username>;
GRANT USAGE, CREATE ON SCHEMA <username> TO <username>; 
SET ROLE <username>;
ALTER DATABASE os1db SET search_path TO <username>; 
If you create a schema to use when creating an object store instance within the created database, you must authorize the database connection user to utilize the schema and grant the needed permissions. For example:
CREATE SCHEMA os1 AUTHORIZATION authadmin;
GRANT USAGE, CREATE ON SCHEMA os1 TO authadmin;