Creating the database in PostgreSQL for the Production Mapping workspace

Mit der Production Mapping-Lizenz verfügbar.

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;