Creating data files for the Reviewer workspace in PostgreSQL

Available with Data Reviewer license.

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
REV_BDATA	Business table
REV_BINDEX	Business table index
REV_ADATA	Adds table (versioned)
REV_AINDEX	Adds table index
REV_DDATA	Deletes table (versioned)
REV_DINDEX	Deletes table index

Note:

Copying and pasting the examples may cause syntax errors.

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 'rev'.
DROP ROLE rev;
CREATE ROLE rev LOGIN ENCRYPTED PASSWORD 'revadmin' NOINHERIT CREATEDB;

--Group role rev editor
DROP ROLE role_rev_rev_editor;
CREATE ROLE role_rev_rev_editor NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

--Group role rev viewer
DROP ROLE role_rev_rev_viewer;
CREATE ROLE role_rev_rev_viewer NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

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

--User to view data called 'pmviewer'.
DROP ROLE pmviewer;
CREATE ROLE pmviewer LOGIN ENCRYPTED PASSWORD 'pmviewer' INHERIT;
GRANT role_rev_rev_viewer TO pmviewer;

2. Create directories

The following script can be used to create directories.

cd D:/postgresqldata/rev
D:
mkdir rev_sde_dict
mkdir rev_sde_dict_index
mkdir rev_sde_log
mkdir rev_sde_log_index
mkdir rev_Adata
mkdir rev_Aindex
mkdir rev_Ddata
mkdir rev_Dindex
mkdir rev_Bdata
mkdir rev_Bindex

3. Create tablespaces

The following script can be used to create tablespaces.

--sde dictionary
DROP TABLESPACE rev_sde_dict;
CREATE TABLESPACE rev_sde_dict OWNER postgres LOCATION
'D:/postgresqldata/rev/rev_sde_dict';
DROP TABLESPACE rev_sde_dict_index;
CREATE TABLESPACE rev_sde_dict_index OWNER postgres LOCATION
'D:/postgresqldata/rev/rev_sde_dict_index';
--sde log files
DROP TABLESPACE rev_sde_log;
CREATE TABLESPACE rev_sde_log OWNER postgres LOCATION
'D:/postgresqldata/rev/rev_sde_log';
DROP TABLESPACE rev_sde_log_index;
CREATE TABLESPACE rev_sde_log_index OWNER postgres LOCATION
'D:/postgresqldata/rev/rev_sde_log_index';
--delta tables
DROP TABLESPACE rev_Adata;
CREATE TABLESPACE rev_Adata OWNER postgres LOCATION 'D:/postgresqldata/rev/rev_Adata';
DROP TABLESPACE rev_Aindex;
CREATE TABLESPACE rev_Aindex OWNER postgres LOCATION
'D:/postgresqldata/rev/rev_Aindex';
DROP TABLESPACE rev_Ddata;
CREATE TABLESPACE rev_Ddata OWNER postgres LOCATION 'D:/postgresqldata/rev/rev_Ddata';
DROP TABLESPACE rev_Dindex;
CREATE TABLESPACE rev_Dindex OWNER postgres LOCATION
'D:/postgresqldata/rev/rev_Dindex';
--vector data
DROP TABLESPACE rev_Bdata;
CREATE TABLESPACE rev_Bdata OWNER postgres LOCATION 'D:/postgresqldata/rev/rev_Bdata';
DROP TABLESPACE rev_Bindex;
CREATE TABLESPACE rev_Bindex OWNER postgres LOCATION
'D:/postgresqldata/rev/rev_Bindex';

4. Grant permission to tablespaces

The following script can be used to grant permissions.

GRANT CREATE ON TABLESPACE rev_sde_dict TO sde;
GRANT CREATE ON TABLESPACE rev_sde_dict_index TO sde;
GRANT CREATE ON TABLESPACE rev_Adata TO rev;
GRANT CREATE ON TABLESPACE rev_Aindex TO rev;
GRANT CREATE ON TABLESPACE rev_Ddata TO rev;
GRANT CREATE ON TABLESPACE rev_Dindex TO rev;
GRANT CREATE ON TABLESPACE rev_Bdata TO rev;
GRANT CREATE ON TABLESPACE rev_Bindex TO rev;

GRANT CREATE ON TABLESPACE rev_sde_log TO sde;
GRANT CREATE ON TABLESPACE rev_sde_log_index TO sde;

GRANT CREATE ON TABLESPACE rev_sde_log TO rev;
GRANT CREATE ON TABLESPACE rev_sde_log_index TO rev;

GRANT CREATE ON TABLESPACE rev_sde_log TO pmeditor;
GRANT CREATE ON TABLESPACE rev_sde_log_index TO pmeditor;

GRANT CREATE ON TABLESPACE rev_sde_log TO pmviewer;
GRANT CREATE ON TABLESPACE rev_sde_log_index TO pmviewer;