Administering your PostgreSQL Geodatabase

Jim Gough and Jim McAbee

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 PostgreSQL Geodatabase

Postgres and how to get Started

What is Postgres?

What version is supported with my ArcGIS 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

PostgreSQL
A Free Open Source Option

Introduction to PostgreSQL
-

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 PostgreSQL Geodatabase

Where to get Software?

PostgreSQL.org and Customer Care Portal: customers.esri.com

-

PostgreSQL Installation

PostgreSQL Client Libraries

ArcSDE Installation ESRI Customer Care Portal only

st_geometry library
-

In all ArcGIS clients

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

Requirements @ 10.2.x : PostgreSQL Versions and Supported OS

tested and certified: verify at support.esri.com

64-bit DBMS and OS only (10.1 and newer)

Support PostGIS versions 1.5.1 & 2.0

Windows Server 2003 and 2003R2 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 PostgreSQL

PostgreSQL initialization parameters

Enabling Connections to PostgreSQL

-

More advanced topics discussed later in advanced topics section

PostgreSQL client libraries for ArcGIS

ArcGIS Spatial Type libraries for PostgreSQL

Esri UC 2014 | Technical Workshop | Type Presentation Title Here

PostgreSQL Initialization Parameters

postgresql.conf
-

most defaults ok, testing and monitoring should be done

Memory
-

#shared_buffers=32MB

Windows best 64MB to 512MB, 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 SP1 notes)

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

PostgreSQL Initialization Parameters

Connections
-

#max_connections=100 (default)

one ArcGIS connection = multiple PostgreSQL connections

default max connections for Geodatabase (sde_server_config)

Logging
-

#log_statement = 'none'

Vacuum/ Analyze
-

#autovacuum = on

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

Enabling connections to PostgreSQL: pg_hba. conf

PostgreSQL configuration file for connections

-

Depending on your network , entries for both types of

addresses may be needed

IPv4 and IPv6 Addresses

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

Configuring PostgreSQL Client Libraries for ArcGIS

Copy the PostgreSQL client libraries into Desktop\bin

(32-bit) or Server\bin (64-bit).

Available at Customer Care Portal or PostgreSQL site.

Linux specific (for ArcGIS Server) notes:

-

setup environment variables

/home/ags/arcgis/server/usr

Init_user_param.sh

PostgreSQL Section:
# For Direct Connect with PostgreSQL
#
export PGHOME=/opt/PostgreSQL/9.0
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

Configuring PostgreSQL for ST_Geometry spatial type

Copy st_geometry.dll (correct PostgreSQL version)

10.2

9.1
9.2
9.0

Esri UC 2014
4 | Technical Workshop | Administering your PostgreSQL Geodatabase

Geodatabase Setup and Administration

Administration Tools

Users, Permissions and Roles

Geodatabase = Database + ArcGIS SDE Administrative Schema

Creating or Enabling Geodatabase

Esri UC 2014 | Technical Workshop | Type Presentation Title Here

Administration Tools

PostgreSQL and ArcGIS

ArcSDE 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 PostgreSQL Geodatabase

Administrative Tools: ArcGIS

Esri UC 2014 | Technical Workshop | Administering your PostgreSQL Geodatabase

PostgreSQL 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 users 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 PostgreSQL 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

ArcGIS Tools

Esri UC 2014 | Tech

Technical
hniicall Workshop
W orkksh
hop |

Administering your PostgreSQL Geodatabase

PostgreSQL
L Tools

Create Role

Creates a PostgreSQL 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 PostgreSQL Geodatabase

Database vs. Geodatabase

SDE Schema

Enterprise Geodatabase is a Postgres Database

with an Administrative Schema

Manages behaviors, relationships

and spatial data

Instance
Database
Schema

Schema

Schema

Schema

Geodatabase

Esri UC 2014 | Technical Workshop | Administering your PostgreSQL Geodatabase

SDE

Schema

Schema

Schema

Geodatabase and Administrative Schema

ArcGIS Technology (Desktop, Server)

Manage data through ArcGIS

-

Load, edit, delete, etc..

Manage through tool

that loads data.

Geodatabase

Administrative
Schema

Database
Feature Cl
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 ArcGIS Desktop

Geodatabase
Schema

Schema

Schema

SDE

Esri UC 2014
014 | 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 PostgreSQL

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: gisprod4, 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 PostgreSQL Geodatabase

Connection Architectures
Direct Connect recommended and only method post 10.2.2

ArcSDE
libraries

PostgreSQL
Client

5432

Geodatabase

Application Server legacy connection method, 10.2.2 last release

ArcSDE Libraries

Database Server

giomgr

5151

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

gsrvr

Geodatabase

Connections: New Approach at ArcGIS 10.1/10.2

A new approach to connecting to databases:

-

Connect to databases as well as Geodatabases,

Populate the ArcGIS 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 PostgreSQL Geodatabase

Spatial Types and Functions

Creation of Features through SQL

Spatial analysis through SQL

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL 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 PostgreSQL databases that contain a geodatabase and

those that do not.
SDE.SDE_DBTUNE
table for storing keywords
and associated parameters

Esri UC 2014 | Technical Workshop |

Administering
Ad
dmi
mn
niisstter
erin
ng your
your
yo
our
ur PostgreSQL
Pos
ostgre
tgre
tg
reS
eSQ
SQL Geodatabase
Geodatab
basse

Geodatabase: Editing through SQL

Geodatabase behavior not supported through SQL

Geodatabase

Administrative
Schema

Database

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

Behaviors
Complex Features
Long Transactions
s
Archiving
Cross-RDBMS

Spatial Types
ST_Geometry
PG_Geometry

ArcGIS

SQL

PostGIS spatial type guidelines

Requirements and Limitations

PostGIS 1.5.x, 2.0 (10.1 SP1 forward)

-

must use PostGIS 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

ArcGIS behavior vs. PostGIS behavior (e.g. topology)

Support Geometry, not Geography

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

PostgreSQL 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 PostgreSQL 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:\backups\prod_050612.bak prod

Some Other Backup Methods

-

file based (cold) backup

VM backup

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL 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:\backups\db_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 pgadminIII, use psql with
cascade

drop in reverse order (data owners, then sde)

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

Demo:
Setting up an
Enterprise
Geodatabase on
PostgreSQL
James Gough

Esri UC 2014 | Technical Workshop |

Es
Esri

Advanced Topics, Performance and Tips

Client compatibility

Multiple Geodatabases and PostgreSQL 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 PostgreSQL Geodatabase

Multiple Geodatabase Configuration

Multiple Geodatabases in PostgreSQL

-

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 PostgreSQL Geodatabase

Publishing

Multiple Postgres instances on same server

Create Instance
E:\PostgreSQL\9.2\installer\server>initcluster.vbs postgres postgres gisdata.101
E:\PostgreSQL\9.2" E:\PostgreSQL\9.2\pgdata3" 5434 DEFAULT
Usage: initcluster.vbs <OSUsername> <SuperUsername> <Password> <Install dir> <Data dir> <Port> <Locale>

Create Windows Service

E:\PostgreSQL\9.2\installer\server>startupcfg.vbs 9.2 postgres gisdata.101
E:\PostgreSQL\9.2" E:\PostgreSQL\9.2\pgdata3" postgresql-9.2-3
Usage: startupcfg.vbs <Major.Minor version> <Username> <Password> <Install dir> <Data dir> <ServerName>

Environment Variables available

PGDATABASE
PGHOST
PGPORT
PGUSER

Esri UC 2014 | Technical

chnical Workshop
W or
orks
ksho
hop
p|

Administering
Ad
miniist
mi
steri
t ing your
your PostgreSQL
Pos
osttgreSQ
SQL
L Geodatabase
Geod
Ge
dattab
abas
basse

pg_restore, multiple instances and PostGIS

Remember to enable PostGIS for each
instance
install and enable
create extension postgis;
create extension postgis_topology;

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

Moving or Copying a PostgreSQL Database

Purposes
-

cloning to staging or development environments

migrating to new VM environment or hardware

Methods
-

via ArcGIS 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 8GB if output to TAR

use o option if object identifiers (OIDs) in user-defined data

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

Upgrading
Upgrade Process

Upgrading OS? PostgreSQL? and/or Geodatabase?

Test in a staging or test environment first

Upgrade
-

With python script, gp tool or context menu in ArcGIS

Make sure to upgrade client and st_geometry libraries

Save configuration files compare to new

-

pg_hba.conf, postgresql.conf, dbtune.sde

dont copy old files back in

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL 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)

ices))
Web Server
Network
Applications
Application Server(s) (ArcGIS)
S
S)
Network
Network Devices
Hardware
Disk I/O

Geodatabase
Database

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

Geodatabase: Proper Maintenance = Performance

Geodatabase

Reconcile
c
cile
Post
Compress
r
ress
I d Maintenance
Index

Database

m
Vacuum
i (Analyz
ics
ze)
Statistics
(Analyze)

Well designed automated maintenance

process - nightly, weekly, etc..

Well designed and maintained Version and Replica architecture

include Backup, ETLs, Reporting, etc

Review annually as new workflows are introduced and with new major releases of technology

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL 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 PostgreSQL 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. ArcGIS Server logs)

PostgreSQL Performance
-

Performance views and Postgresql logs (located in \Data\pg_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.sde_state_lineages WHERE lineage_name = $1 AND lineage_id <=
$2 ORDER BY lineage_id

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL 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 > 10ms

Average Disk Seconds / Write > 10ms

If Cloud deployment
-

different locations may have different behavior

(resources/equipment)

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

Monitor Connections and Locks in ArcGIS

Monitor: Connections and Locks

-

kill connections - Superuser

Direct Connections and Application Server Connections

check lock type

Esri UC 2014 | Technical

chn
h ic
hn
icall Workshop
W or
orkksh
kshop
hop |

Administering your PostgreSQL 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 ArcGIS 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 PostgreSQL Geodatabase

PostgreSQL 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_stat_statements (create extension pg_stat_statements)

module provides a means for tracking execution statistics of all SQL

statements executed by server.

shared_preload_libraries =
'"E:\\PostgreSQL\\9.2\\lib\\pg_stat_statements.dll"'

Indexes (Rebuildx)

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

High-Availability (HA), Point-in-time-recovery (PITR) and Failover

HA must be entire Geodatabase and no connections to Standby only failover

PostgreSQL configurations use WAL (write-ahead logging)

PostgreSQL does not provide failure detection itself, add-ons or OS

configurations can.

OpenSCGs pgHA (PITR and pgbouncer), also PITR and Slony replication

Pgpool

Must test with workflows

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL 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 PostgreSQL Geodatabase

Example LDAP pg_hba.conf

SDE and Data Owners login and schema name must match

PostgreSQL
L Authentica
Authenticated Users
A
SDE Administ
Administrative User
Data Owner
# IPv4 local connections:
host
all
sde
0.0.0.0/0
md5
host
all
gisdata 0.0.0.0/0
md5
host
all
postgres 0.0.0.0/0
md5
all
all
127.0.0.1/32
md5
host
127.0.0.1/3
host
all
all
0 0 0 0/0
0.0.0.0/0
0
ldap
ldapserver=vmtester.bos.esri.com
ldapserver=v
ldapserver=vmtester.bos.esr
erver=vmtester.bos.esr
tester
.esr
ldapprefix
ix TESTIN
ix=TESTING
NG\
G\"
# IPv6 local connections:
host
all
all
::1/128
md5
host
all
all
::/0
md5
Editors and Viewers via LDAP

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

Upper Case Database Identifiers

PostgreSQL is case sensitive

-

It stores all of its 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

-

PgAdminIII quotes them automatically

ArcGIS does not look for quoted strings

-

Identifiers with upper case names not found

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

PGGDB

Datatype Mapping

PostgreSQL supports almost 100 datatypes

ArcGIS has 8

Some PostgreSQL datatypes are mapped to

one ArcGIS datatype

Some datatypes are not supported

-

Error: invalid column datatype

Documented in online help

Esri UC 2014 | Technical Workshop |

Administering your PostgreSQL Geodatabase

Thank you for attending

jgough@esri.com
jmcabee@esri.com
Esri UC 2014 | Technical Workshop |

Esri UC 2014 | Technical Workshop | Type Presentation Title Here