Creating data files for the product library workspace in PostgreSQL

Available with Production Mapping 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	TABLESPACE
PRODLIB_BDATA	Business table
PRODLIB_BINDEX	Business table index
PRODLIB_ADATA	Adds table (versioned)
PRODLIB_AINDEX	Adds table index
PRODLIB_DDATA	Deletes table (versioned)
PRODLIB_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 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 'prodlib'.
   DROP ROLE prodlib;
   CREATE ROLE prodlib LOGIN ENCRYPTED PASSWORD 'prodlibadmin' NOINHERIT CREATEDB;
   
   -- Group role ckb_users
   DROP ROLE ckb_users;
   CREATE ROLE ckb_users NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
   
   --User to edit called 'prodlibuser'.  
   DROP ROLE prodlibuser;
   CREATE ROLE prodlibuser LOGIN ENCRYPTED PASSWORD 'prodlibuser ' INHERIT;
   
   GRANT ckb_users to prodlibuser;
   

2. Create directories.

   The following script can be used to create directories.

   cd D:/postgresqldata/prodlib
   D:
   mkdir prodlib_sde_dict
   mkdir prodlib_sde_dict_index
   mkdir prodlib_sde_log
   mkdir prodlib_sde_log_index
   mkdir prodlib_Adata
   mkdir prodlib_Aindex
   mkdir prodlib_Ddata
   mkdir prodlib_Dindex
   mkdir prodlib_Bdata
   mkdir prodlib_Bindex
   

3. Create tablespaces.

   The following script can be used to create tablespaces.

   -- sde dictionary
   DROP TABLESPACE prodlib_sde_dict;
   CREATE TABLESPACE prodlib_sde_dict OWNER postgres LOCATION 'D:/postgresqldata/prodlib/prodlib_sde_dict';
   DROP TABLESPACE prodlib_sde_dict_index;
   CREATE TABLESPACE prodlib_sde_dict_index OWNER postgres LOCATION 'D:/postgresqldata/prodlib/prodlib_sde_dict_index';
   --sde log files
   DROP TABLESPACE prodlib_sde_log;
   CREATE TABLESPACE prodlib_sde_log OWNER postgres LOCATION 'D:/postgresqldata/prodlib/prodlib_sde_log';
   DROP TABLESPACE prodlib_sde_log_index;
   CREATE TABLESPACE prodlib_sde_log_index OWNER postgres LOCATION 'D:/postgresqldata/prodlib/prodlib_sde_log_index';
   --delta tables
   DROP TABLESPACE prodlib_Adata;
   CREATE TABLESPACE prodlib_Adata OWNER postgres LOCATION 'D:/postgresqldata/prodlib/prodlib_Adata';
   DROP TABLESPACE prodlib_Aindex;
   CREATE TABLESPACE prodlib_Aindex OWNER postgres LOCATION 'D:/postgresqldata/prodlib/prodlib_Aindex';
   DROP TABLESPACE prodlib_Ddata;
   CREATE TABLESPACE prodlib_Ddata OWNER postgres LOCATION 'D:/postgresqldata/prodlib/prodlib_Ddata';
   DROP TABLESPACE prodlib_Dindex;
   CREATE TABLESPACE prodlib_Dindex OWNER postgres LOCATION 'D:/postgresqldata/prodlib/prodlib_Dindex';
   --vector data
   DROP TABLESPACE prodlib_Bdata;
   CREATE TABLESPACE prodlib_Bdata OWNER postgres LOCATION 'D:/postgresqldata/prodlib/prodlib_Bdata';
   DROP TABLESPACE prodlib_Bindex;
   CREATE TABLESPACE prodlib_Bindex OWNER postgres LOCATION 'D:/postgresqldata/prodlib/prodlib_Bindex';
   

4. Grant permission to tablespaces.

   The following script can be used to grant permissions.

   GRANT CREATE ON TABLESPACE prodlib_sde_dict TO sde;
   GRANT CREATE ON TABLESPACE prodlib_sde_dict_index TO sde;
   
   GRANT CREATE ON TABLESPACE prodlib_Adata TO prodlib;
   GRANT CREATE ON TABLESPACE prodlib_Aindex TO prodlib;
   GRANT CREATE ON TABLESPACE prodlib_Ddata TO prodlib;
   GRANT CREATE ON TABLESPACE prodlib_Dindex TO prodlib;
   GRANT CREATE ON TABLESPACE prodlib_Bdata TO prodlib;
   GRANT CREATE ON TABLESPACE prodlib_Bindex TO prodlib;
   
   GRANT CREATE ON TABLESPACE prodlib_sde_log TO sde;
   GRANT CREATE ON TABLESPACE prodlib_sde_log_index TO sde;
   
   GRANT CREATE ON TABLESPACE prodlib_sde_log TO prodlib;
   GRANT CREATE ON TABLESPACE prodlib_sde_log_index TO prodlib;
   
   GRANT CREATE ON TABLESPACE prodlib_sde_log TO prodlibuser;
   GRANT CREATE ON TABLESPACE prodlib_sde_log_index TO prodlibuser;