Available with Production Mapping license.
You can choose to create the database using pgAdmin or psql.
Creating the database
Use one of the following script examples to create a database in PostgreSQL for the workspace.
- Use the following script example to create the PMDB database using ST_Geometry.
CREATE DATABASE pmdb WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE=pm_sde_dict; ALTER DATABASE pmdb SET search_path="$user", public, sde; GRANT ALL ON DATABASE pmdb TO public; GRANT ALL ON DATABASE pmdb TO postgres;
- Install PostGIS geometry.
\connect pmdb CREATE EXTENSION postgis;
Creating schemas in the Production Mapping database
- Use the following script example to create schemas in the PMDB database.
\connect pmdb -- 'sde' schema. CREATE SCHEMA sde AUTHORIZATION sde; GRANT ALL ON SCHEMA sde TO sde; GRANT ALL ON SCHEMA sde TO public; --'pm' schema. CREATE SCHEMA pm authorization pm; GRANT USAGE ON SCHEMA pm to public; --'pmeditor' schema. CREATE SCHEMA pmeditor authorization pmeditor; GRANT USAGE ON SCHEMA pmeditor to public; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA pm TO role_pm_pm_editor; --'pmviewer' schema. CREATE SCHEMA pmviewer authorization pmviewer; GRANT USAGE ON SCHEMA pmviewer to public; GRANT SELECT ON ALL TABLES IN SCHEMA pm TO role_pm_pm_viewer;
- Run the following script example for each user that will create data using PostGIS geometry storage.
GRANT SELECT, INSERT, UPDATE, DELETE ON public.geometry_columns to pm; GRANT SELECT ON public.spatial_ref_sys to pm;
For more information about creating a geodatabase, see Create an enterprise geodatabase.
Moving indices to pm_sde_dict_index
- Use the following script example to reorder the indices:
select schemaname, tablename, indexname, tablespace from pg_indexes where schemaname = 'sde' order by schemaname, tablename, indexname, tablespace;
- Use the following script example to move the indices:
select 'alter text'|| schemaname||'.'||indexname||'set tablespace pm_sde_dict_index;' as SQLTXT from pg_indexes where schemaname = 'sde' order by schemaname, tablename, indexname, tablespace;