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.
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. ![]() |
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.![]() |
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.![]() |
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.
-- 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>;
CREATE SCHEMA os1 AUTHORIZATION authadmin;
GRANT USAGE, CREATE ON SCHEMA os1 TO authadmin;