Creating the database in PostgreSQL for the product library 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 PRODLIB database using ST_Geometry.

  CREATE DATABASE prodlib WITH OWNER = postgres ENCODING = 'UTF8' 
  TABLESPACE=prodlib_sde_dict;
  
  ALTER DATABASE prodlib SET search_path="$user", public, sde;
  GRANT ALL ON DATABASE prodlib TO public;
  GRANT ALL ON DATABASE prodlib TO postgres;
  

• Install PostGIS geometry.

  connect prodlib
  CREATE EXTENSION postgis;
  

Creating schemas in the product library database

1. Use the following script example to create schemas in the PRODLIB database.

   \connect prodlib
   
   -- 'sde' schema.
   CREATE SCHEMA sde AUTHORIZATION sde;
   GRANT ALL ON SCHEMA sde TO sde;
   GRANT ALL ON SCHEMA sde TO public;
   
   --'prodlib' schema.
   CREATE SCHEMA prodlib authorization prodlib;
   GRANT USAGE ON SCHEMA prodlib to public;
   
   --'prodlibuser' schema.
   CREATE SCHEMA prodlibuser authorization prodlibuser;
   GRANT USAGE ON SCHEMA prodlibuser to public;
   GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA prodlib TO ckb_users;
   

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 prodlib;
   GRANT SELECT ON public.spatial_ref_sys to prodlib;
   

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;