Creating data files for the Workflow Manager (Classic) workspace in PostgreSQL—ArcMap

Use scripts to create roles, directories, tablespaces and permissions

Standard GIS storage recommendations favor keeping index and log files separate from vector and tabular business tables. For performance reasons, it is better to position the business, feature, and spatial index tables separately and position tablespace data files based on their usage pattern. For a multiversioned, highly active editing geodatabase, database files of the VERSIONS tablespace may be separated and dispersed across available disks to avoid input/output contention.

The following table has the recommended tablespaces to be created for storing the workspace feature classes and tables.


TABLESPACE	CONFIGURATION_KEYWORD_PARAMETER
WMX_BDATA	Business table
WMX_BINDEX	Business table index
WMX_ADATA	Adds table (versioned)
WMX_AINDEX	Adds table index
WMX_DDATA	Deletes table (versioned)
WMX_DINDEX	Deletes table index

Use scripts to create roles, directories, tablespaces and permissions

The following script can be used to add tablespaces using pgAdmin or psql.

1. Create roles

The following script can be used to create roles.

\connect postgres postgres

--create sde role
DROP ROLE sde;
CREATE ROLE sde LOGIN ENCRYPTED PASSWORD 'sdeadmin' SUPERUSER NOINHERIT CREATEDB
CREATEROLE;

--User to own data called 'wmx'.
DROP ROLE wmx;
CREATE ROLE wmx LOGIN ENCRYPTED PASSWORD 'wmxadmin' NOINHERIT CREATE DB;

--Group role wmx_editor;
DROP ROLE role_wmx_wmx_editor;
CREATE ROLE role_wmx_wmx_editor NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

--Group role wmx viewer;
DROP ROLE role_wmx_wmx_viewer;
CREATE ROLE role_wmx_wmx_viewer NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

--User to edit called 'pmeditor'.
DROP ROLE pmeditor;
CREATE ROLE pmeditor LOGIN ENCRYPTED PASSWORD 'pmeditor' INHERIT;
GRANT role_wmx_wmx_editor TO pmeditor;

--User to edit called 'pmviewer'.
DROP ROLE pmviewer;
CREATE ROLE pmeditor LOGIN ENCRYPTED PASSWORD 'pmviewer' INHERIT;
GRANT role_wmx_wmx_editor TO pmviewer;

2. Create directories

The following script can be used to create directories.

cd D:/postgresqldata/wmx
D:
mkdir wmx_sde_dict
mkdir wmx_sde_dict_index
mkdir wmx_sde_log
mkdir wmx_sde_log_index
mkdir wmx_Adata
mkdir wmx_Aindex
mkdir wmx_Ddata
mkdir wmx_Dindex
mkdir wmx_Bdata
mkdir wmx_Bindex

3. Create tablespaces

The following script can be used to create tablespaces.

--sde dictionary
DROP TABLESPACE wmx_sde_dict;
CREATE TABLESPACE wmx_sde_dict OWNER postgres LOCATION
'D:/postgresqldata/wmx/wmx_sde_dict';
DROP TABLESPACE wmx_sde_dict_index;
CREATE TABLESPACE wmx_sde_dict_index OWNER postgres LOCATION
'D:/postgresqldata/wmx/wmx_sde_dict_index';
--sde log files
DROP TABLESPACE wmx_sde_log;
CREATE TABLESPACE wmx_sde_log OWNER postgres LOCATION
'D:/postgresqldata/wmx/wmx_sde_log';
DROP TABLESPACE wmx_sde_log_index;
CREATE TABLESPACE wmx_sde_log_index OWNER postgres LOCATION
'D:/postgresqldata/wmx/wmx_sde_log_index';
--delta tables
DROP TABLESPACE wmx_Adata;
CREATE TABLESPACE wmx_Adata OWNER postgres LOCATION
'D:/postgresqldata/wmx/wmx_Adata';
DROP TABLESPACE wmx_Aindex;
CREATE TABLESPACE wmx_Aindex OWNER postgres LOCATION
'D:/postgresqldata/wmx/wmx_Aindex';
DROP TABLESPACE wmx_Ddata;
CREATE TABLESPACE wmx_Ddata OWNER postgres LOCATION
'D:/postgresqldata/wmx/wmx_Ddata';
DROP TABLESPACE wmx_Dindex;
CREATE TABLESPACE wmx_Dindex OWNER postgres LOCATION
'D:/postgresqldata/wmx/wmx_Dindex';
--vector data
DROP TABLESPACE wmx_Bdata;
CREATE TABLESPACE wmx_Bdata OWNER postgres LOCATION
'D:/postgresqldata/wmx/wmx_Bdata';
DROP TABLESPACE wmx_Bindex;
CREATE TABLESPACE wmx_Bindex OWNER postgres LOCATION
'D:/postgresqldata/wmx/wmx_Bindex';

4. Grant permission to tablespaces

The following script can be used to grant permissions.