Creating the database in PostgreSQL for the Production Mapping workspace

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

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

2. 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

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

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