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 |
Use scripts to create roles, directories, tablespaces and permissions
The following script can be used to add tablespaces using pgAdmin or psql.
- 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;
- 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
- 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';
-
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;