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.
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 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;
- 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
- 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';
-
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;