Administering your Postgre SQL Geodatabase Jim Gough and

Administering your Postgre. SQL Geodatabase Jim Gough and Jim Mc. Abee jgough@esri. com jmcabee@esri. com Esri UC 2014 | Technical Workshop |

Agenda • Workshop will be structured in 2 parts • Part 1: Scenario – Using Postgres for your Enterprise Geodatabase and how to get started. • Part 2: Advanced Topics, Performance and Tips Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Postgres and how to get Started • What is Postgres? • What version is supported with my Arc. GIS technology? • Getting Started - Configuring Postgres - Connecting to Postgres - Creating Users and Roles - Administrative Tools - Creating or Enabling Enterprise Geodatabase - Spatial Types - Backup and Recovery Esri UC 2014 | Technical Workshop | Type Presentation Title Here

Postgre. SQL A Free Open Source Option • • Introduction to Postgre. SQL - http: //www. postgresql. org/ - Open Source Enterprise level RDBMS - Free, distributed with bsd license - Supported by an active online development community Learn more: - planet. postgresql. org, - PGCON: http: //www. pgcon. org/2012/ Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Where to get Software? • Postgre. SQL. org and Customer Care Portal: customers. esri. com - Postgre. SQL Installation - Postgre. SQL Client Libraries - Arc. SDE Installation – ESRI Customer Care Portal only - st_geometry library - In all Arc. GIS clients Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Requirements @ 10. 2. x : Postgre. SQL Versions and Supported OS tested and certified: verify at support. esri. com • 64 -bit DBMS and OS only (10. 1 and newer) • Support Post. GIS versions – 1. 5. 1 & 2. 0 • Windows Server 2003 and 2003 R 2 no longer supported at 10. 2. 2 Esri UC 2014 | Technical Workshop | 9. 3 10. 3 9. 2. 2 10. 2. 2, 10. 2. 1 9. 1. 3 10. 2. 2, 10. 2. 1, 10. 2. 0 9. 0. 5 10. 2. 1, 10. 2. 0, 10. 1 8. 3. 8 & 8. 4. 1 10. 0

Configuring Postgre. SQL • Postgre. SQL initialization parameters • Enabling Connections to Postgre. SQL - More advanced topics discussed later in advanced topics section • Postgre. SQL client libraries for Arc. GIS • Arc. GIS Spatial Type libraries for Postgre. SQL Esri UC 2014 | Technical Workshop | Type Presentation Title Here

Postgre. SQL Initialization Parameters • postgresql. conf - • • most defaults ok, testing and monitoring should be done Memory - #shared_buffers=32 MB … - Windows – best 64 MB to 512 MB, little benefit to set higher, use OS cache - Linux – 25% of Physical Memory to start and as possible (no. of instances) Query Optimization - cursor_tuple_fraction - set to 1. 0 vs. default of 0. 1 (per 10. 1 SP 1 notes) Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Postgre. SQL Initialization Parameters • • Connections - #max_connections=100 (default) - one Arc. GIS connection = multiple Postgre. SQL connections - default max connections for Geodatabase (sde_server_config) Logging - • #log_statement = 'none' Vacuum/ Analyze - #autovacuum = on Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Enabling connections to Postgre. SQL: pg_hba. conf • Postgre. SQL configuration file for connections - • Depending on your network , entries for both types of addresses may be needed IPv 4 and IPv 6 Addresses Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Configuring Postgre. SQL Client Libraries for Arc. GIS • Copy the Postgre. SQL client libraries into Desktopbin (32 -bit) or Serverbin (64 -bit). • Available at Customer Care Portal or Postgre. SQL site. • Linux specific (for Arc. GIS Server) notes: - setup environment variables - /home/ags/arcgis/server/usr - Init_user_param. sh - Postgre. SQL Section: # For Direct Connect with Postgre. SQL # export PGHOME=/opt/Postgre. SQL/9. 0 export PATH=$PGHOME/bin: $PATH export LD_LIBRARY_PATH=$PGHOME/lib: $LD_LIBRARY_PATH Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Configuring Postgre. SQL for ST_Geometry spatial type • Copy st_geometry. dll (correct Postgre. SQL version) 10. 2 9. 1 9. 2 9. 0 Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Geodatabase Setup and Administration • Administration Tools • Users, Permissions and Roles • “Geodatabase” = Database + Arc. GIS “SDE” Administrative Schema • Creating or Enabling Geodatabase Esri UC 2014 | Technical Workshop | Type Presentation Title Here

Administration Tools • Postgre. SQL and Arc. GIS • Arc. SDE Command Line Tools (10. 2. 2 last release) to be replaced by new geoprocessing (GP) tools at 10. 3 Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Administrative Tools: Arc. GIS Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Postgre. SQL User Permissions Login Roles (Users) and Group Roles (Groups) Data Viewer Usage on SDE Schema Usage on data schemas to be viewed Data Editor Usage on SDE Schema Usage on data schemas to be edited Data Creator Usage on SDE Schema Authorization on user’s own schema Usage on any other data schemas where access is required GDB Admin (SDE) Esri UC 2014 | Technical Workshop | Authorization on SDE schema Usage on all other user schemas Administering your Postgre. SQL Geodatabase

Database Users - Logins • SDE Administrative user, Data Owners, Editors, Viewers • login and schema must be same name for logins that will own objects in the geodatabase. • You cannot create a schema for a group role. • Can rerun tool to create a schema in a second database Arc. GIS Tools Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase Postgre. SQL Tools

Create Role • Creates a Postgre. SQL Group role • The GP tool does the same as the sql: CREATE ROLE gis LOGIN ENCRYPTED PASSWORD ‘gis‘ INHERIT; CREATE ROLE bunch VALID UNTIL ‘infinity’; GRANT bunch to gis; Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Database vs. Geodatabase SDE Schema • Enterprise Geodatabase is a Postgres Database with an Administrative Schema Instance • Manages behaviors, relationships and spatial data Database Schema Geodatabase Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase SDE Schema

Geodatabase and Administrative Schema Arc. GIS Technology (Desktop, Server) • Manage data through Arc. GIS - • Load, edit, delete, etc. . Manage through tool that loads data. Geodatabase Administrative Schema Database Feature Class (Data) Esri UC 2014 | Technical Workshop | Behaviors Complex Features Versioning Distributed Data Archiving Spatial Type Transactions Authorization/Security Data Management Backup

Setup and Configuration: Creating or Enabling via Arc. GIS Desktop Geodatabase Esri UC 2014 | Technical Workshop |

Geodatabase Setup and Administration • Connecting to Geodatabase • Spatial Types • Backup and Recovery - Test Esri UC 2014 | Technical Workshop | Type Presentation Title Here

Connecting to Postgre. SQL • Must specify an instance (name or IP address of server) & database. • If instance is listening on a different port than the default (5432), include the port number in the instance. For example: gisprod 4, 5435 • The database name is limited to 31 characters. • Make sure to give the connection a unique name to identify (non-standard port) Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Connection Architectures “Direct Connect” – recommended and only method post 10. 2. 2 Arc. SDE libraries Postgre. SQL Client 5432 Geodatabase “Application Server” – legacy connection method, 10. 2. 2 last release Arc. SDE Libraries Database Server giomgr 5151 Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase gsrvr Geodatabase

Connections: New Approach at Arc. GIS 10. 1/10. 2 • A new approach to connecting to databases: - Connect to databases as well as Geodatabases, - Populate the Arc. GIS with database client libraries - Use a simplified connection dialog, Direct Connect default 10. 2 sde: postgresql: localhost prod 10. 0 Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Spatial Types and Functions • Creation of Features through SQL • Spatial analysis through SQL Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

ST_Geometry: Default Geometry Datatype • SQL 3 specification of user-defined data types (UDTs) – ISO and OGC compliant • Provides structured query language (SQL) access to the geodatabase and database. • Can be used in Postgre. SQL databases that contain a geodatabase and those that do not. SDE_DBTUNE table for storing keywords and associated parameters Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Geodatabase: Editing through SQL • Geodatabase behavior not supported through SQL Geodatabase Administrative Schema Database Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase Behaviors Complex Features Long Transactions Archiving Cross-RDBMS Spatial Types • ST_Geometry • PG_Geometry Arc. GIS SQL

Post. GIS spatial type guidelines Requirements and Limitations • Post. GIS 1. 5. x, 2. 0 (10. 1 SP 1 forward) - must use Post. GIS database template to create database - must use spatial references in public. spatial_ref_sys table - must use PG_GEOMETRY keyword - cannot rename tables (public. geometry_columns not modified) • 64 -bit: linux build for 1. 5 and 2. 0, windows build only 2. 0 • Arc. GIS behavior vs. Post. GIS behavior (e. g. topology) • Support Geometry, not Geography Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Postgre. SQL Recovery Models • • What are needs - how often does data change? - how long can application(s) be down? - how fast does recovery need to be? Weekly or nightly backups - • • recovery to most recent backup – most common Write-Ahead Logging (WAL) - point-in-time recovery - must test thoroughly to understand resource requirements (e. g. disk i/o) Other options - Standby or Failover configurations - High Availability configurations Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Backup pg_dump, pg_dumpall and other methods • Typical Backup Methods - database – pg_dump (typical method) - instance – pg_dumpall (backs up logins and roles) pg_dump -h localhost -p 5432 -U postgres -F c -v -f E: backupsprod_050612. bak prod • Some Other Backup Methods - file based (cold) backup - VM backup Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Restore pg_restore • Restoring – pg_restore - restore schemas in order – public, sde, data owners pg_restore -n public -p 5432 -U postgres -d db_name –c -v E: backupsdb_050712. bak - • rebuild spatial indexes and gather statistics once restored Some Notes - many dependent objects between schemas, may need to drop cascade in psql (sde and data owners) and recreate - may get errors if trying to drop via pgadmin. III, use psql with cascade - drop in reverse order (data owners, then sde) Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Demo: Setting up an Enterprise Geodatabase on Postgre. SQL James Gough Esri UC 2014 | Technical Workshop |

Advanced Topics, Performance and Tips • Client compatibility • Multiple Geodatabases and Postgre. SQL Instance • Moving, Copying, Cloning • Upgrading Esri UC 2014 | Technical Workshop | Type Presentation Title Here

Client – Geodatabase Compatibility • 10. x forwards and backwards compatibility Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Multiple Geodatabase Configuration • Multiple Geodatabases in Postgre. SQL In same instance - If using same name in multiple instances (e. g. clone prod to stage) - • Reasons for multiple geodatabases Editing and publishing (web) - Production and Staging - Different application needs - - Separate version management Production Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase Publishing

Multiple Postgres instances on same server • Create Instance E: Postgre. SQL9. 2installerserver>initcluster. vbs postgres gisdata. 101 “E: Postgre. SQL9. 2" “E: Postgre. SQL9. 2pgdata 3" 5434 DEFAULT Usage: initcluster. vbs <OSUsername> <Super. Username> <Password> <Install dir> <Data dir> <Port> <Locale> • Create Windows Service E: Postgre. SQL9. 2installerserver>startupcfg. vbs 9. 2 postgres gisdata. 101 “E: Postgre. SQL9. 2" “E: Postgre. SQL9. 2pgdata 3" postgresql-9. 2 -3 Usage: startupcfg. vbs <Major. Minor version> <Username> <Password> <Install dir> <Data dir> <Server. Name> • Environment Variables available • • PGDATABASE PGHOST PGPORT PGUSER Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

pg_restore, multiple instances and Post. GIS • Remember to enable Post. GIS for each instance • install and enable • create extension postgis; • create extension postgis_topology; Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Moving or Copying a Postgre. SQL Database • • Purposes - cloning to staging or development environments - migrating to new VM environment or hardware Methods - via Arc. GIS to new database – copy/paste, export/import, etc… - pg_dump/pg_restore commands - Restore PUBLIC schema first, then SDE, then data owner schemas - run as superuser - run ANALYZE after to update statistics - re-create Tablespaces - Text version of a table cannot be larger than 8 GB if output to TAR - use –o option if object identifiers (OIDs) in user-defined data Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Upgrading Upgrade Process • Upgrading OS? Postgre. SQL? and/or Geodatabase? • Test in a staging or test environment first • Upgrade - With python script, gp tool or context menu in Arc. GIS • Make sure to upgrade client and st_geometry libraries • Save configuration files – compare to new - pg_hba. conf, postgresql. conf, dbtune. sde - don’t copy old files back in Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Performance and the Platform Services Based • Services • Desktops • Editing vs Viewing/Analysis Esri UC 2014 | Technical Workshop | Type Presentation Title Here

Performance: Understand the Stack and Isolate is the problem in the database? Clients (Desktop, Browser, Devices) Web Server Network Applications Application Server(s) (Arc. GIS) Network Devices Hardware Disk I/O Geodatabase Database Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Geodatabase: Proper Maintenance = Performance Geodatabase Reconcile Post Compress Index Maintenance Database Vacuum Statistics (Analyze) • Well designed automated maintenance process - nightly, weekly, etc. . • Well designed and maintained Version and Replica architecture • include Backup, ETL’s, Reporting, etc… • Review annually as new workflows are introduced and with new major releases of technology Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Monitoring: Why monitor? • • Establish performance benchmarks to measure impacts: - upgrades and patches - new applications or workflows - new server resources or deployment patterns Assist in troubleshooting - assist in isolating a problem when one takes place Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

What to monitor? • • Server Resources (cpu utilization, memory, storage i/o) - Windows and Linux tools (top, vmstat, iostat, etc. . ) - Esri System Monitor Client Performance - • various tools and logs (e. g. Arc. GIS Server logs) Postgre. SQL Performance - Performance views and Postgresql logs (located in …Datapg_log) 2013 -05 -21 13: 00: 43 PDT DETAIL: parameters: $1 = '13580' 2013 -05 -21 13: 00: 43 PDT LOG: duration: 0. 000 ms parse sde_1369166443_0_793: SELECT lineage_id FROM prod. sde_state_lineages WHERE lineage_name = $1 AND lineage_id <= $2 ORDER BY lineage_id Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Monitor Resources Physical and Virtual Environments • • Be careful of any of the following thresholds: - Processor utilization > 70% - Memory utilization > 80% of physical - Storage utilization > 80% of storage capacity - Average Disk Seconds / Read > 10 ms - Average Disk Seconds / Write > 10 ms If Cloud deployment - different locations may have different behavior (resources/equipment) • Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Monitor Connections and Locks in Arc. GIS • Monitor: Connections and Locks - “kill” connections - Superuser - Direct Connections and Application Server Connections - check lock type Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Performance Considerations • Excessive normalization - Too many indexes - No optimizer hints, index use can not be forced - Need not worry about the Spatial Index - GIST index used, self correcting • Can change postgresql. conf initialization parameters • Issue with long running Arc. GIS edit sessions - The larger the number of states - The larger the bloat in indexing belonging to the Feature Class Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Postgre. SQL Geodatabase Performance Proper Maintenance • • • Vacuum - removes dead tuples (rows) - Autovacuum – on by default, can do an analyze Statistics (Analyze) - Statistics – table size, cardinality of joins, distribution of indexes, etc… - pg_statements (create extension pg_statements) - module provides a means for tracking execution statistics of all SQL statements executed by server. - shared_preload_libraries = '"E: \Postgre. SQL\9. 2\lib\pg_statements. dll"' Indexes (Rebuildx) Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

High-Availability (HA), Point-in-time-recovery (PITR) and Failover • HA must be entire Geodatabase and no connections to Standby only failover • Postgre. SQL configurations use WAL (write-ahead logging) • Postgre. SQL does not provide failure detection itself, add-ons or OS configurations can. • - Open. SCG’s pg. HA (PITR and pgbouncer), also PITR and Slony replication - Pgpool Must test with workflows Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Windows AD, LDAP and Single Sign-On • Editors and Viewers http: //wiki. postgresql. org/wiki/LDAP_Authentication_against_AD http: //wiki. postgresql. org/wiki/Configuring_for_single_sign-on_using_SSPI_on_Windows http: //support. esri. com/en/knowledgebase/techarticles/detail/38151 Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Example LDAP pg_hba. conf SDE and Data Owners login and schema name must match Postgre. SQL Authenticated Users SDE Administrative User Data Owner # IPv 4 local connections: host all sde 0. 0/0 md 5 host all gisdata 0. 0/0 md 5 host all postgres 0. 0/0 md 5 host all 127. 0. 0. 1/32 md 5 host all 0. 0/0 ldapserver=vmtester. bos. esri. com ldapprefix=“TESTING" # IPv 6 local connections: host all : : 1/128 md 5 host all : : /0 md 5 Editors and Viewers via LDAP Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Upper Case Database Identifiers • Postgre. SQL is case sensitive - - It stores all of it’s object identifies in lower case - Names of: Databases, Tables, Indexes, Column - SDE/GDB also stores all identifiers in lower case - User data can be in any case To use identifiers in upper case, they need to be quoted - - Pg. Admin. III quotes them automatically Arc. GIS does not look for quoted strings - Identifiers with upper case names not found Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase “PGGDB”

Datatype Mapping • Postgre. SQL supports almost 100 datatypes • Arc. GIS has 8 • Some Postgre. SQL datatypes are mapped to one Arc. GIS datatype • Some datatypes are not supported - Error: “invalid column datatype” - Documented in online help Esri UC 2014 | Technical Workshop | Administering your Postgre. SQL Geodatabase

Enterprise Geodatabase Architecture, Health Check and Performance Consider EEAP • Esri Enterprise Advantage Program • Use for Training • Use for Professional Services Guidance and Assistance • - Enterprise Geodatabase Health Checks - Architecture Reviews - Performance and System Monitoring Review - Jumpstarts/Launch Kits Why? - A support net to ensure success - New applications and uses of technology in organization over time - New technology and improvements over And maturing of use, new workflows introduced as organization adopts more use of technology. Esri UC 2014 | Technical Workshop | Type Presentation Title Here

Thank you… Please fill out the session evaluation ID: 1383 Online – www. esri. com/ucsessionsurveys Paper – pick up and put in drop box Esri UC 2014 | Technical Workshop |

Thank you for attending… jgough@esri. com jmcabee@esri. com Esri UC 2014 | Technical Workshop |

Esri UC 2014 | Technical Workshop | Type Presentation Title Here
- Slides: 58