Creating the database in PostgreSQL for the Workflow Manager (Classic) workspace

Available with Workflow Manager license.

Creating the database

Use the following script example to set up the database in PostgreSQL for the Workflow Manager (Classic) workspace:

DROP DATABASE wmx;

CREATE DATABASE wmx WITH OWNER = postgres ENCODING = 'UTF8'
TABLESPACE=wmx_sde_dict;

ALTER DATABASE wmx SET search_path="$user", public, sde;
GRANT ALL ON DATABASE wmx TO public;
GRANT ALL ON DATABASE wmx TO postgres;

Use the following script example to install PostGIS geometry:

\connect wmx
CREATE EXTENSION postgis;

Creating schemas in the Workflow Manager (Classic) database

Use the following script example to create schemas in the Workflow Manager (Classic) database:

\connect wmx

--'sde' schema.
CREATE SCHEMA sde AUTHORIZATION sde;
GRANT ALL ON SCHEMA sde TO sde;
GRANT ALL ON SCHEMA sde TO public;

--'wmx' schema.
CREATE SCHEMA wmx authorization wmx;
GRANT USAGE ON SCHEMA wmx to public;

--'pmeditor' schema.
CREATE SCHEMA pmeditor authorization pmeditor;
GRANT USAGE ON SCHEMA pmeditor to public;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA wmx TO role_wmx_wmx_editor;

--'pmviewer' schema.
CREATE SCHEMA pmviewer autorization pmviewer;
GRANT USAGE ON SCHEMA pmviewer to public;

GRANT SELECT ON ALL TABLES IN SCHEMA wmx TO role_wmx_wmx_viewer;

Use the following script example to grant privileges for each user that will create data using the PostGIS geometry storage:

GRANT SELECT, INSERT, UPDATE, DELETE ON public.geometry_columns to wmx;
GRANT SELECT ON public.spatial_ref_sys to wmx;

For more information on creating a geodatabase, see Create an enterprise geodatabase.

Moving indices to wmx_sde_dict_index

Use the following script example to put the indices in order:

select schemaname, tablespace, indexname, tablespace;
from pg_indexes
  where schemaname = 'sde'
  order by schemaname, tablename, indexname, tablespace;

Use the following script example to move the indices:

select 'alter text'|| schemaname||'.'||indexname||'set tablespace wmx_sde_dict_index;'
as SQLTXT
from pg_indexes
  where schemaname = 'sde'
  order by schemaname, tablename, indexname, tablespace;