Creating data files for the Production Mapping 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	CONFIGURATION_KEYWORD_PARAMETER
PM_BDATA	Business table
PM_BINDEX	Business table index
PM_ADATA	Adds table (versioned)
PM_AINDEX	Adds table index
PM_DDATA	Deletes table (versioned)
PM_DINDEX	Deletes table index
PM_ARCHIVE_BDATA	Archive Business table
PM_ARCHIVE_BINDEX	Archive Business table index
PM_XML_DOC	XML table
PM_XML_INDEX	XML index
PM_RASTER	Raster table
PM_RASTER_INDEX	Raster table index
PM_RASTER_BLK	Raster BLK table
PM_RASTER_BLK_INDEX	Raster BLK table index
PM_NET_BDATA	Network Business table
PM_NET_BINDEX	Network Business table index
PM_NET_ADATA	Network Adds table (versioned)
PM_NET_AINDEX	Network Adds table index
PM_NET_DDATA	Network Deletes table (versioned)
PM_NET_DINDEX	Network Deletes table index
PM_TOPO_BDATA	Topology Business table
PM_TOPO_BINDEX	Topology Business table index
PM_TOPO_ADATA	Topology Adds table (versioned)
PM_TOPO_AINDEX	Topology Adds table index
PM_TOPO_DDATA	Topology Deletes table (versioned)
PM_TOPO_DINDEX	Topology Deletes table index
PM_TERRAIN_BDATA	Terrain Business table
PM_ TERRAIN_BINDEX	Terrain Business table index
PM_ TERRAIN_ADATA	Terrain Adds table (versioned)
PM_ TERRAIN_AINDEX	Terrain Adds table index
PM_ TERRAIN_DDATA	Terrain Deletes table (versioned)
PM_ TERRAIN_DINDEX	Terrain Deletes table index

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

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 'pm'.
   DROP ROLE pm;
   CREATE ROLE pm LOGIN ENCRYPTED PASSWORD 'pmadmin' NOINHERIT CREATEDB;
   
   --Group role pm editor
   DROP ROLE role_pm_pm_editor;
   CREATE ROLE role_pm_pm_editor NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
   
   --Group role pm viewer
   DROP ROLE role_pm_pm_viewer;
   CREATE ROLE role_pm_pm_viewer NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
   
   --User to edit called 'pmeditor'.
   DROP ROLE pmeditor;
   CREATE ROLE pmeditor LOGIN ENCRYPTED PASSWORD 'pmeditor' INHERIT;
   GRANT role_pm_pm_editor TO pmeditor;
   
   --User to view data called 'pmviewer'.
   DROP ROLE pmviewer;
   CREATE ROLE pmviewer LOGIN ENCRYPTED PASSWORD 'pmviewer' INHERIT;
   GRANT role_pm_pm_viewer TO pmviewer;
   

2. Create directories.

   The following script can be used to create directories.

   cd D:/postgresqldata/pm
   D:
   mkdir pm_sde_dict
   mkdir pm_sde_dict_index
   mkdir pm_sde_log
   mkdir pm_sde_log_index
   mkdir pm_Adata
   mkdir pm_Aindex
   mkdir pm_Ddata
   mkdir pm_Dindex
   mkdir pm_Bdata
   mkdir pm_Bindex
   mkdir pm_Archive_Bdata
   mkdir pm_Archive_Bindex
   mkdir pm_Xml_doc
   mkdir pm_Xml_index
   mkdir pm_Raster
   mkdir pm_Raster_index
   mkdir pm_Raster_Blk
   mkdir pm_Raster_Blk_index
   mkdir pm_Net_Bdata
   mkdir pm_Net_Bindex
   mkdir pm_Net_Adata
   mkdir pm_Net_Aindex
   mkdir pm_Net_Ddata
   mkdir pm_Net_Dindex
   mkdir pm_Topo_Bdata
   mkdir pm_Topo_Bindex
   mkdir pm_Topo_Adata
   mkdir pm_Topo_Aindex
   mkdir pm_Topo_Ddata
   mkdir pm_Topo_Dindex
   mkdir pm_Terrain_Bdata
   mkdir pm_Terrain_Bindex
   mkdir pm_Terrain_Adata
   mkdir pm_Terrain_Aindex
   mkdir pm_Terrain_Ddata
   mkdir pm_Terrain_Dindex
   

3. Create tablespaces.

   The following script can be used to create tablespaces.

   --sde dictionary
   DROP TABLESPACE pm_sde_dict;
   CREATE TABLESPACE pm_sde_dict OWNER postgres LOCATION
   'D:/postgresqldata/pm/pm_sde_dict';
   DROP TABLESPACE pm_sde_dict_index;
   CREATE TABLESPACE pm_sde_dict_index OWNER postgres LOCATION
   'D:/postgresqldata/pm/pm_sde_dict_index';
   --sde log files
   DROP TABLESPACE pm_sde_log;
   CREATE TABLESPACE pm_sde_log OWNER postgres LOCATION
   'D:/postgresqldata/pm/pm_sde_log';
   DROP TABLESPACE pm_sde_log_index;
   CREATE TABLESPACE pm_sde_log_index OWNER postgres LOCATION
   'D:/postgresqldata/pm/pm_sde_log_index';
   --delta tables
   DROP TABLESPACE pm_Adata;
   CREATE TABLESPACE pm_Adata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Adata';
   DROP TABLESPACE pm_Aindex;
   CREATE TABLESPACE pm_Aindex OWNER postgres LOCATION
   'D:/postgresqldata/pm/pm_Aindex';
   DROP TABLESPACE pm_Ddata;
   CREATE TABLESPACE pm_Ddata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Ddata';
   DROP TABLESPACE pm_Dindex;
   CREATE TABLESPACE pm_Dindex OWNER postgres LOCATION
   'D:/postgresqldata/pm/pm_Dindex';
   --vector data
   DROP TABLESPACE pm_Bdata;
   CREATE TABLESPACE pm_Bdata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Bdata';
   DROP TABLESPACE pm_Bindex;
   CREATE TABLESPACE pm_Bindex OWNER postgres LOCATION
   'D:/postgresqldata/pm/pm_Bindex';
   --archive data
   DROP TABLESPACE pm_Archive_Bdata;
   CREATE TABLESPACE pm_Archive_Bdata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Archive_Bdata';
   DROP TABLESPACE pm_Archive_Bindex;
   CREATE TABLESPACE pm_Archive_Bindex OWNER postgres LOCATION
   'D:/postgresqldata/pm/pm_Archive_Bindex';
   --xml data
   DROP TABLESPACE pm_Xml_doc;
   CREATE TABLESPACE pm_Xml_doc OWNER postgres LOCATION
   'D:/postgresqldata/pm/pm_Xml_doc';
   DROP TABLESPACE pm_Xml_index;
   CREATE TABLESPACE pm_Xml_index OWNER postgres LOCATION
   'D:/postgresqldata/pm/pm_Xml_index';
   --raster data
   DROP TABLESPACE pm_Raster;
   CREATE TABLESPACE pm_Raster OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Raster';
   DROP TABLESPACE pm_Raster_index;
   CREATE TABLESPACE pm_Raster_index OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Raster_index';
   DROP TABLESPACE pm_Raster_Blk;
   CREATE TABLESPACE pm_Raster_Blk OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Raster_Blk';
   DROP TABLESPACE pm_Raster_Blk_index;
   CREATE TABLESPACE pm_Raster_Blk_index OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Raster_Blk_index';
   --network data
   DROP TABLESPACE pm_Net_Bdata;
   CREATE TABLESPACE pm_Net_Bdata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Net_Bdata';
   DROP TABLESPACE pm_Net_Bindex;
   CREATE TABLESPACE pm_Net_Bindex OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Net_Bindex';
   DROP TABLESPACE pm_Net_Adata;
   CREATE TABLESPACE pm_Net_Adata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Net_Adata';
   DROP TABLESPACE pm_Net_Aindex;
   CREATE TABLESPACE pm_Net_Aindex OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Net_Aindex';
   DROP TABLESPACE pm_Net_Ddata;
   CREATE TABLESPACE pm_Net_Ddata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Net_Ddata';
   DROP TABLESPACE pm_Net_Dindex;
   CREATE TABLESPACE pm_Net_Dindex OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Net_Dindex';
   --topology data
   DROP TABLESPACE pm_Topo_Bdata;
   CREATE TABLESPACE pm_Topo_Bdata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Topo_Bdata';
   DROP TABLESPACE pm_Topo_Bindex;
   CREATE TABLESPACE pm_Topo_Bindex OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Topo_Bindex';
   DROP TABLESPACE pm_Topo_Adata;
   CREATE TABLESPACE pm_Topo_Adata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Topo_Adata';
   DROP TABLESPACE pm_Topo_Aindex;
   CREATE TABLESPACE pm_Topo_Aindex OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Topo_Aindex';
   DROP TABLESPACE pm_Topo_Ddata;
   CREATE TABLESPACE pm_Topo_Ddata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Topo_Ddata';
   DROP TABLESPACE pm_Topo_Dindex;
   CREATE TABLESPACE pm_Topo_Dindex OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Topo_Dindex';
   --terrain data
   DROP TABLESPACE pm_Terrain_Bdata;
   CREATE TABLESPACE pm_Terrain_Bdata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Terrain_Bdata';
   DROP TABLESPACE pm_Terrain_Bindex;
   CREATE TABLESPACE pm_Terrain_Bindex OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Terrain_Bindex';
   DROP TABLESPACE pm_Terrain_Adata;
   CREATE TABLESPACE pm_Terrain_Adata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Terrain_Adata';
   DROP TABLESPACE pm_Terrain_Aindex;
   CREATE TABLESPACE pm_Terrain_Aindex OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Terrain_Aindex';
   DROP TABLESPACE pm_Terrain_Ddata;
   CREATE TABLESPACE pm_Terrain_Ddata OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Terrain_Ddata';
   DROP TABLESPACE pm_Terrain_Dindex;
   CREATE TABLESPACE pm_Terrain_Dindex OWNER postgres LOCATION 
   'D:/postgresqldata/pm/pm_Terrain_Dindex';
   

4. Grant permission to tablespaces.

   The following script can be used to grant permissions.

   GRANT CREATE ON TABLESPACE pm_sde_dict TO sde;
   GRANT CREATE ON TABLESPACE pm_sde_dict_index TO sde;
   
   GRANT CREATE ON TABLESPACE pm_Adata TO pm;
   GRANT CREATE ON TABLESPACE pm_Aindex TO pm;
   GRANT CREATE ON TABLESPACE pm_Ddata TO pm;
   GRANT CREATE ON TABLESPACE pm_Dindex TO pm;
   GRANT CREATE ON TABLESPACE pm_Bdata TO pm;
   GRANT CREATE ON TABLESPACE pm_Bindex TO pm;
   GRANT CREATE ON TABLESPACE pm_Archive_Bdata TO pm;
   GRANT CREATE ON TABLESPACE pm_Archive_Bindex TO pm;
   GRANT CREATE ON TABLESPACE pm_Xml_doc TO pm;
   GRANT CREATE ON TABLESPACE pm_Xml_index TO pm;
   GRANT CREATE ON TABLESPACE pm_Raster TO pm;
   GRANT CREATE ON TABLESPACE pm_Raster_index TO pm;
   GRANT CREATE ON TABLESPACE pm_Raster_Blk TO pm;
   GRANT CREATE ON TABLESPACE pm_Raster_Blk_index TO pm;
   GRANT CREATE ON TABLESPACE pm_Net_Bdata TO pm;
   GRANT CREATE ON TABLESPACE pm_Net_Bindex TO pm;
   GRANT CREATE ON TABLESPACE pm_Net_Adata TO pm;
   GRANT CREATE ON TABLESPACE pm_Net_Aindex TO pm;
   GRANT CREATE ON TABLESPACE pm_Net_Ddata TO pm;
   GRANT CREATE ON TABLESPACE pm_Net_Dindex TO pm;
   GRANT CREATE ON TABLESPACE pm_Topo_Bdata TO pm;
   GRANT CREATE ON TABLESPACE pm_Topo_Bindex TO pm;
   GRANT CREATE ON TABLESPACE pm_Topo_Adata TO pm;
   GRANT CREATE ON TABLESPACE pm_Topo_Aindex TO pm;
   GRANT CREATE ON TABLESPACE pm_Topo_Ddata TO pm;
   GRANT CREATE ON TABLESPACE pm_Topo_Dindex TO pm;
   GRANT CREATE ON TABLESPACE pm_Terrain_Bdata TO pm;
   GRANT CREATE ON TABLESPACE pm_Terrain_Bindex TO pm;
   GRANT CREATE ON TABLESPACE pm_Terrain_Adata TO pm;
   GRANT CREATE ON TABLESPACE pm_Terrain_Aindex TO pm;
   GRANT CREATE ON TABLESPACE pm_Terrain_Ddata TO pm;
   GRANT CREATE ON TABLESPACE pm_Terrain_Dindex TO pm;
   
   GRANT CREATE ON TABLESPACE pm_sde_log TO sde;
   GRANT CREATE ON TABLESPACE pm_sde_log_index TO sde;
   
   GRANT CREATE ON TABLESPACE pm_sde_log TO pm;
   GRANT CREATE ON TABLESPACE pm_sde_log_index TO pm;
   
   GRANT CREATE ON TABLESPACE pm_sde_log TO pmeditor;
   GRANT CREATE ON TABLESPACE pm_sde_log_index TO pmeditor;
   
   GRANT CREATE ON TABLESPACE pm_sde_log TO pmviewer;
   GRANT CREATE ON TABLESPACE pm_sde_log_index TO pmviewer;