1. No category

 2 Working with an Enterprise GeoDatabase Introduction .............................................................................................. 3 Software needed ....................................................................................... 3 1 Installation and configuration of an Enterprise GeoDatabase ............... 4 1.1 Install and configure PostgreSql ................................................................................................................................................ 4 1.2 Download and install PostGIS .................................................................................................................................................. 10 1.3 PostgreSQL configuration .......................................................................................................................................................... 18 1.3.1 Creation of the ArcSDE super user ................................................................................................................................ 18 1.3.2 Configuring PostgreSQL to accept external connections ..................................................................................... 19 1.3.3 Set up PostgreSQL to use ESRI Geometry standard ............................................................................................... 20 1.3.4 Configure ArcGIS Desktop to work with PostgreSQL ............................................................................................ 20 1.3.5 PostgreSQL Tuning ............................................................................................................................................................... 21 1.4 Creation of an ESRI Enterprise GeoDatabase .................................................................................................................... 22 1.4.1 Create a connection file to connect to the Enterprise Geodatabase ............................................................... 24 1.5 Users and groups creation ......................................................................................................................................................... 26 1.5.1 Create Database Users through the Arctoolbox ...................................................................................................... 28 2 Administration and maintenance of an Enterprise GeoDatabase ......... 30 2.1 Granting and revoking privileges on datasets .................................................................................................................. 30 2.2 View users connected to the geo-­‐database and manage locks ................................................................................ 32 2.2.1 View and close user connections ................................................................................................................................... 32 2.3 Geo-­‐database locks ....................................................................................................................................................................... 34 2.4 Maintenance of an Enterprise GeoDatabase ...................................................................................................................... 36 2.4.1 Database backup and recovery ....................................................................................................................................... 37 2.4.2 Database performance improvement .......................................................................................................................... 41 Annex A - Installation of ArcGIS Desktop 10.2.2 ..................................... 44 A.1 Installation of .NET Framework 3.5 SP1 ................................................................................................................................. 45 Annex B - Installation of ArcGIS for Server Enterprise ............................ 51 Configure ArcGIS Server to work with PostgreSQL ................................................................................................................. 54 Working with an Enterprise GeoDatabase 3 Introduction
The GIS training “Working with an Enterprise Geodatabase” is part of the collection
of trainings on geodatabase management produced by the Geospatial Support Unit
(GSU) at the Emergency Preparedness and Response branch to support the
implementation of the GIS Infrastructure within the World Food Programme.
This specific training module has been designed with the aim of guiding the user
through the installation of an ESRI Enterprise Geodatabase on a physical or virtual
server as well as its configuration and management.
The manual is divided into 2 main sections: in the first part the user will follow the
steps needed to install a Postgres instance, configure it, create a geodatabase and
manage users and permissions; in the second section the necessary administration
and maintenance tasks are described in order to enable users who take this
training to independently manage an Enterprise Geodatabase built with the ESRI
technology.
Software needed
During this course you will run some installations that requires software packages
that can be downloaded from the ESRI portal. In case you don’t have access to the
ESRI portal, you can request such software from your regional GIS officer or from
the GSU unit at the HQ, by sending an email to [email protected].
Here the list of necessary software:
o Postgres 9.2.2 DBMS for Windows
o PostgreSQL 9.2.2 Client Libraries (Windows)
o ArcGIS Desktop 10.2.2
o ArcGIS for Server Enterprise 10.2.2 (for Windows)
4 Working with an Enterprise GeoDatabase 1 Installation
and
configuration
of
an
Enterprise
GeoDatabase
This training module has been designed as a complete set of instructions to help
the user in getting started with an enterprise geodatabase based on Postgres 9.2.2
on a Windows server.
It assumes that ArcGIS for Desktop and ArcGIS for Server have been already
installed. If needed, instructions for installation of ArcGIS for Desktop and Server
can be found in Annexes A and B.
We will initially install an instance of Postgres, enable its access on a network and
add the necessary libraries to enable geospatial capabilities. Later on we will create
a geodatabase in Postgres, add users to it and grant editing access and editing
permissions.
1.1 Install and configure PostgreSql
To start the installation double click on the “Postgres 9.2.2 DBMS for Windows”
executable file and the image below will appear. You will need to first extract the
compressed DBMS installation files into any specific folder of your choice.
Figure 1: Extract the PostgreSql installation files Working with an Enterprise GeoDatabase 5 Select the appropriate path and click Ok.
Figure 2: Installation launch Then tick the box “Launch the setup program” and click on Close and the window
below should open automatically. In case this doesn’t happen navigate to the
temporary folder selected at the previous step and click on the “Setup” file.
Figure 3: Postgres installation wizard Just click on “Next” button at this step.
6 Working with an Enterprise GeoDatabase Figure 4: PostgreSQL Installation directory In this step you have to specify the directory where you want to install PostgreSQL.
In most cases the default directory would be fine. In the event that you wish to
install the software in a different location or drive available on your machine, you
can specify the directory of your choice.
Once done click Next.
Working with an Enterprise GeoDatabase 7 Figure 5 PostgreSQL data directory This window let you decide the location where you want the PostgreSQL data to be
saved. Browse to you favorite location and click Next.
The next step allows you to select a password for the Superuser account for
PostgreSQL, which is called postgres. This user is allowed to perform some
extraordinary configuration and maintenance tasks on the database, so please save
the password in a safe place and back it up.
8 Working with an Enterprise GeoDatabase In the following window type the communication port number through which the
DBMS works; the default is 5432. Interaction with your network manager can help
in defining the correct port according to your network settings.
Select the international options for your installation; the default is a good option.
Working with an Enterprise GeoDatabase 9 Just click on “Next” in order to let the machine perform the installation.
A confirmation window acknowledges the successful installation.
10 Working with an Enterprise GeoDatabase 1.2 Download and install PostGIS
In case you want to create in PostgreSQL additional geodatabase accessible
through Open Source client software, it might be useful to store that information in
Open Source geometry types, by means of PostGIS, which is the PostgreSQL
spatial extension. This training document with guide you through the installation
and usage of an ESRI enterprise geodatabase but we would suggest following these
steps to be able to install PostGIS databases at a later stage. In any case, this will
not influence your workflows.
Taking advantage of the PostgreSQL Stack Builder is the easiest way to download
and installing PostGIS.
Working with an Enterprise GeoDatabase 11 The Stack Builder allows you to select among a list of available applications the
ones that you need. Select the right PostGIS version from the list, according to
your machine hardware (v2.0 of PostGIS is supported by ESRI).
12 Working with an Enterprise GeoDatabase A temporary folder is used by the system as a download repository; insert a
temporary path at the following step.
Working with an Enterprise GeoDatabase 13 After the download, the PostGIS installation wizard starts automatically.
At this step you have the option to let the installation process create a new
spatially enabled database; it is more convenient to do it at this step rather than
generating it manually at a later step.
14 Working with an Enterprise GeoDatabase Working with an Enterprise GeoDatabase 15 In the following window insert the name of the spatially enabled database that is
going to be created into the DBMS.
16 Working with an Enterprise GeoDatabase PostGIS takes advantage of some open source libraries in order to perform spatial
processing; therefore enabling this software is required for PostGIS to work
smoothly. In particular the registration of some environmental variables is the key
for this software to work correctly: therefore click on “Yes” in the following three
screens.
Working with an Enterprise GeoDatabase 17 A window acknowledges when the installation of PostGIS is complete.
18 Working with an Enterprise GeoDatabase 1.3 PostgreSQL configuration
Immediately after the installation of PostgreSQL DBMS and optionally of its spatial
extension, some preliminary steps are necessary, before creating an ESRI
Enterprise Geodatabase.
1.3.1 Creation of the ArcSDE super user
This step outlines the creation of a DBMS user for geodatabase administration,
which must be named sde.
It can be created either by means of the Windows power shell or executing the
instruction in PgAdmin (the most used client software for PostgreSQL that has been
installed with the PostgreSQL installation).
Follow these steps to create the sde user through the Windows power shell:
1. Open a Powershell Window or command prompt
2. Browse to the location of the bin folder located in the PostgreSQL installation
folder through this command:
cd “PostgreSQL installation path”\bin
Example:
cd c:\Program Files\PostgreSQL\9.2\bin
3. Connect to postgreSQL running this instruction:
.\psql –U postgres
4. Provide the password for the postgres user.
Working with an Enterprise GeoDatabase 19 5. Once you are in postgres, run the command below (please remember to
create a new password for the sde user and end the instruction with a
semicolon symbol “;”).
CREATE ROLE sde WITH SUPERUSER CREATEDB LOGIN PASSWORD 'password';
Please remember to store the password for the “sde” user in a safe place.
To exit from the PostgreSQL shell type \q and then click enter.
1.3.2 Configuring PostgreSQL to accept external connections
At this step your PostgreSQL DMBS is only accessible from the server.
In order to allow connections to other machines, you have to properly configure the
configuration file pg_hba.conf, which can be found in the location specified for the
PostgreSQL data (by default C:\Program Files\PostgreSQL\9.2\data).
This is needed in order to let ArcGIS Desktop (ESRI client) connect to the
PostgreSQL server.
Open this file and add a new entry for each class of IP addresses you want to allow
access by following the example below:
# TYPE
host
DATABASE
all
USER
all
ADDRESS
192.168.12.10/32
METHOD
md5
If you want to enable access to all IP addresses add the following row (it’s not
recommended in a production environment but can be useful to perform some
initial tests of connectivity to the PostgreSQL instance).
20 Working with an Enterprise GeoDatabase # TYPE
DATABASE
host
all
Specific
USER
ADDRESS
all
documentation
0.0.0.0/0
can
be
found
METHOD
md5
on
the
PostgreSQL
website.
http://www.postgresql.org/docs/9.2/static/auth-pg-hba-conf.html
1.3.3 Set up PostgreSQL to use ESRI Geometry standard
Another important step towards the creation of an Enterprise Geodatabase is to
enable PostgreSQL to store layers using the ESRI geometry format, called
ST_geometry. In order to achieve this, you have to copy the ESRI standard
geometry libraries into the PostgreSQL installation folder (lib).
These libraries are stored originally in a dll file called st_geometry.dll, which can be
found in the DatabaseSupport folder, within the ArcGIS Desktop installation folder.
In case ArcGIS desktop has been installed into the C drive, copy the following file
C:\Program Files (x86)\ArcGIS\Desktop10.2\DatabaseSupport\PostgreSQL\
9.2\Windows64\st_geometry.dll
Into the folder:
C:\Program Files\PostgreSQL\9.2\lib\
1.3.4 Configure ArcGIS Desktop to work with PostgreSQL
Before creating an Enterprise GeoDatabase, ArcGIS Desktop needs to be properly
configured to be able to connect to the PostgreSQL instance. In particular you have
to copy the database client libraries that have been provided with this training
Working with an Enterprise GeoDatabase 21 (PostgreSQL Client Libraries) into the bin folder of the ArcGIS Desktop installation
directory.
1. Run the .exe file (PostgreSQL 9.2.2 Client Libraries ) and extract files in
a new folder;
2. Copy those files (32bit version) into the bin folder of the ArcGIS
Desktop installation directory
(C:\Program Files(x86)\ArcGIS\Desktop10.2\bin)
1.3.5 PostgreSQL Tuning
Better performance can be achieved by properly setting some parameters in the
PostgreSQL configuration file. This file (postgresql.conf) can be usually found in the
data directory (C:\Program Files\PostgreSQL\9.2\data)
Here the list of parameters with related values:
max_connections= 128
shared_buffers = 512MB
autovacuum= on
log_statement = 'none'
In case there is a sharp symbol (#) at the beginning of the line, remove it to
uncomment the configuration parameter.
Once these parameters are configured, restart the PostgreSQL service through the
Server Manager. Select Tools à Services and restart the postgresql-x64-9.2 PostgreSQL Server 9.2 service.
22 Working with an Enterprise GeoDatabase 1.4 Creation of an ESRI Enterprise GeoDatabase
You can create an enterprise geodatabase in PostgreSQL using the Create
Enterprise Geodatabase geo-processing tool. Follows the steps below.
1.
Start ArcCatalog or ArcMap
2.
Open the tool’s dialog box (ArcToolbox)
3.
Select Database Management tool
4.
Select Geodatabase Administration
5.
Launch the Create Enterprise geodatabase tool
6
Working with an Enterprise GeoDatabase 23 6. Follow instructions below to provide information needed:
Database Platform: PostgreSQL
Instance: “IP address of your server”
Database: “database name”
Database Administrator: postgres
Database Administrator Password: “password for the postgres user”
Geodatabase Administrator: sde
Geodatabase Administrator Password: “password for the sde user”
Tablespace Name: leave it blank
Authorization file: browse the keycodes file created after the installation of
ArcGIS
for
Server.
It
can
be
found
in
the
directory
C:\Program
Files\ESRI\License10.2\sysgen.
In case you have not installed ArcGIS for Server yet, please refer to the guide
in the Annex B.
7. Click Ok to launch the tool and complete the creation of your Enterprise
Geodatabase
24 Working with an Enterprise GeoDatabase 1.4.1 Create a connection file to connect to the Enterprise Geodatabase
In order to connect to the newly created Enterprise Geodatabase you need to
create a connection file through ArcCatalog. Follow the process below to create a
connection file with the administrative user sde , You’ll need it to administer your
Enterprise Geodatabase.
1. Open ArcCatalog
2. Search for the Database Connections folder
3. Double-click on Add Database Connection
Working with an Enterprise GeoDatabase 25 4. Follow instructions below to provide information needed:
Database Platform: PostgreSQL
Instance: “IP address of your server”
Authentication Type: “Database Authentication”
User Name: “Type the username” (sde)
Password: “Type the password” (password for sde)
Tick the Save username and password box.
Click on the arrow at the end of the field Database and a list of geodatabases will appear. Select the one you want to connect with.
5. Click Ok to finalize the process
1.4.1.1
Standard for connection files
Each connection files contains specific information to let a user connect to a certain
database. Unfortunately after creating a connections file it is saved with a default
name, which doesn’t contain such information, therefore it is not easy to identify
whether or not the connection file can be used to perform specific tasks according
to the user permissions
For this reason we strongly suggest to rename connection files right after their
creation, reporting in its name the user, the database and IP address of the
server where the database is stored, separated by a double underscore. “user”__”db name”__”ipaddress”.sde
Example: hq__wfp__10_11_40_221.sde
26 Working with an Enterprise GeoDatabase 1.5 Users and groups creation
Up to this stage of the install of the Enterprise Geodatabase there is only an
administrative user for PostgreSQL (postgres) and an administrative user for the
Enterprise GeoDatabase (sde).
An appropriate number of login roles must be created into the DBMS in order to
grant users access to the Enterprise Geodatabase. Moreover, specific privileges
must be granted on these login roles in order to assure that the users can perform
their everyday activities successfully and correctly.
Due to the fact that usually some users have similar profiles in terms of geospatial
data usage, it is suggested to create groups into the DBMS and assign the login
roles to the right groups, respecting users’ specific responsibilities. Furthermore,
granting privileges to groups can make the work of a database administrator
easier.
ESRI documentation suggests creating at least three basic groups:
● Data viewer group, for users who only need to visualize and analyze the
data;
● Data editor group, for users who need also to add, modify or delete
geospatial features;
● Data creator group, for users who need also to create new feature classes.
Running the SQL script provided with this training document can create the basic
set of users and groups needed.
Before running it, please change usernames and passwords in the script according
to your environment.
Working with an Enterprise GeoDatabase 27 To run the script, save it on the server and follow instructions below.
1. Open a Powershell Window
2. Browse to the location of the bin folder located in the PostgreSQL installation
folder through this command:
cd “PostgreSQL installation path”\bin
Example:
cd c:\Program Files\PostgreSQL\9.2\bin
3. Run the command below and provide the password for the postgres user
.\psql –U postgres –d “database name” –a –f “script path”
Example:
.\psql –U postgres –d “cairo” –a –f “C:\GIS\script_users_creation.psql”
Please remember to modify the database name and script path accordingly
Once the basic set of groups and users has been created, you can create additional
users, assign them to groups or assign privileges using geo-processing tools
described in the following section.
28 Working with an Enterprise GeoDatabase 1.5.1 Create Database Users through the Arctoolbox
You can create additional users into your database using the Create Database User
geo-processing tool. It creates a database user with privileges sufficient to create
data in the database.
1. Start ArcCatalog or ArcMap
2. Open the ArcToolBox.
3. Select Database Management tool
4. Select Geodatabase Administration
Francesco Stompanato 23/1/15 09:21
Deleted:
Francesco Stompanato 23/1/15 09:21
Formatted: Indent: Left: 1,75 cm,
Hanging: 0,75 cm, Outline numbered +
Level: 4 + Numbering Style: 1, 2, 3, ... +
Start at: 1 + Alignment: Left + Aligned at:
12,06 cm + Indent at: 6,35 cm
Francesco Stompanato 23/1/15 09:21
Deleted: tool’s dialogbox (DO
YOU MEAN OPEN ArcToolbox???
And then Data Management
Tools and then Geodatabase
Administration? ) if so it would
be nice to have a screen shot of
the arctoolbox dialogue.
Francesco Stompanato 23/1/15 09:21
Deleted:
Unknown
Formatted: Font:Verdana, 12 pt
5. Launch the Create Database User tool
Francesco Stompanato 23/1/15 09:22
Formatted: No bullets or numbering
Working with an Enterprise GeoDatabase 29 6. Follow instructions below to provide information needed:
Input Database Connection: Specify the connection file to the enterprise
geodatabase, which is made as a database administrator (the connection file
created with sde user)
Database User: “Type the name you want to give to the user”
Database User Password: “Type the password you want to assign to the
user”
Role: “type the name of the group to which you want to associate the user
(creator, editor or viewer)
7. Click Ok to launch the tool and complete the creation of a new user
Francesco Stompanato 23/1/15 09:22
Deleted:
... [1]
Francesco Stompanato 23/1/15 09:22
Deleted:
... [2]
30 Working with an Enterprise GeoDatabase 2 Administration and maintenance of an Enterprise
GeoDatabase
There are some geodatabase administration and maintenance tasks that must be
performed once and others to perform on a regular basis to preserve the geodatabase and its existing performance levels. Most of them are standard
maintenance you would perform for any database management system (DBMS),
such as granting privileges, database backups and regenerating database statistics.
Others, such as compression and synchronizing geodatabase replicas, are specific
to geo-databases.
In this chapter we’ll describe how to grant or revoke privileges to existing users, kill
locks,
backup
and
restore
an
enterprise
geodatabase
and
perform
some
maintenance tasks such as compress and analyze the geodatabase.
2.1 Granting and revoking privileges on datasets
You must grant specific privileges to other database users if you want to let them
view or modify the content of any data in the database. In order to do so you have
to connect to the database using a connection file with the user privileges of the
owner of the specific layers or feature datasets you want to modify.
1. Open ArcMap or ArcCatalog and in the Catalog window double-click the
Database Connections folder in the Catalog tree.
2. Connect to the database or geodatabase that contains the data for which you
want to alter privileges. Be sure to connect as the owner of the data.
3. Right-click the feature class or feature dataset, point to Manage, then
click Privileges and the privileges dialog box opens.
Francesco Stompanato 23/1/15 09:17
Deleted: kill locks
Working with an Enterprise GeoDatabase 31 4. If the user whose privileges you want to change is already in the list, check
or uncheck the boxes to grant or revoke the privileges you want.
5. In case the user or role is not already in the list
click Add to open the User/Role dialog box.
6. You can choose the database users or roles you
want to add from the list by checking the box next
to their names.
7. Click OK to close the User/Role dialog box.
8. Check the boxes of the privileges you want each new user or role to have and
click OK.
Through the process we have just described you can alter privileges on one feature
class of feature dataset at a time. To alter privileges on multiple datasets at once
32 Working with an Enterprise GeoDatabase you can use the Change Privileges geo-processing tool. Please have a look at this
guide on the ESRI portal for further information
http://resources.arcgis.com/en/help/main/10.2/index.html#/Change_Privileges/00170000
00n3000000/
2.2 View users connected to the geo-database and manage
locks
Enterprise geo-databases are typically accessed by many users making updates to
features and tables while editing. As a result, the geo-database administrator has a
number of maintenance tasks to perform to ensure the geodatabase is running
smoothly and users can access the data with which they need to work.
This topic offers an overview of the Geodatabase Administration dialog box within
ArcCatalog and ArcMap, a very helpful tool for managing user connections and
locks.
2.2.1 View and close user connections
Geodatabase administrators can use the Connections tab on the Geodatabase
Administration dialog box to view all users connected to ArcSDE geo-databases.
From this dialog box, an administrator can view user connections and also
disconnect users from the geodatabase.
This has to be done for example in case a user might be holding a lock on an
object, preventing another user to perform edits or run synchronization or just to
improve database performance when there are many pending connections.
The following steps explain how you access the list of connected user sessions
from ArcGIS for Desktop:
Working with an Enterprise GeoDatabase 33 1. Start ArcMap or ArcCatalog and create a database connection to your
geodatabase. Remember to use the connection file with the administrator user (sde)
2. Right-click
the
database
connection
in
the
Catalog
tree,
point
to Administration, then click Administer Geodatabase.
3. Click the Connections tab.
4. A list of all user sessions that are currently connected to the geodatabase is
displayed. Your session is displayed in gray, italic text. If necessary, you can
disconnect user sessions by right-clicking a session and clicking Disconnect
User.
34 Working with an Enterprise GeoDatabase 2.3 Geo-database locks
As users edit and query data, ArcGIS automatically applies and releases locks on
datasets in the geodatabase to help users manage changes without causing
conflicts with other users.
The Locks tab on the Geodatabase Administration dialog box allows you to view
and manage locks in the geodatabase. Through this dialog box, you can see active
locks on datasets and versions and which user has acquired the lock. This lets you
identify who may be blocking you from editing operations on specific datasets and
versions, which can help you coordinate editing processes with other users.
In a geodatabase there are two different lock modes, shared locks and exclusive
locks.
A shared lock is created on an individual dataset when it is in use; for example,
any time a user is editing or querying the contents of a feature class or table. This
mechanism is used so other users cannot change the schema of a dataset while it
is in use.
An exclusive lock is used to lock a dataset in the geodatabase from use by others
to make necessary changes to it, for example, a schema change or even delete the
dataset from the geo-database. Once a user with proper permissions starts to
make such changes to a dataset in the geodatabase, ArcGIS automatically
establishes an exclusive lock on the individual attribute table, feature class table,
raster table, or other dataset. However, if a shared lock exists on that dataset, an
exclusive lock cannot be established.
You can see who is holding schema, version, and state locks held in a geodatabase
using the Locks tab of the Geodatabase Administration dialog box.
Working with an Enterprise GeoDatabase 35 1. Start ArcMap or ArcCatalog and connect to the Enterprise Geodatabase for
which you want to see the locks using the administrative user (sde).
2. Right-click
the
database
connection
in
the
Catalog
to Administration, and then click Administer Geodatabase.
3. Click the Locks tab.
4. A list of locks on all objects in the geodatabase is displayed.
tree,
point
36 Working with an Enterprise GeoDatabase You can disconnect a user sessions, thereby dropping the locks held by that
session, by right-clicking a session and clicking Disconnect User.
It’s recommended to disconnect users only when you are sure that they are not
performing important tasks because their work will be lost.
In general locks should be dropped only if a session was left open even when not
being used (for example, during the weekend).
2.4 Maintenance of an Enterprise GeoDatabase
Blocking new connections from being made to an Enterprise geodatabase before
running certain maintenance operations is considered a good practice as well as
removing any existing connections. In the previous paragraph we described how to
disconnect connected users and kill locks, while to block new connections to the
geodatabase follow the steps below:
1. In ArcMap or ArcCatalog connect to the geodatabase as the geodatabase
administrator.
2. Right-click the database connection in the Catalog tree and click Properties.
3. Click the Connections tab.
4. Uncheck Geodatabase is accepting connections.
Working with an Enterprise GeoDatabase 37 5. No new connections can be made to the geodatabase. Existing user
connections remain active.
Note that the geodatabase administrator can always make a new connection to the
geodatabase regardless of how this property is set.
2.4.1 Database backup and recovery
The data stored in your geodatabase must be protected from loss or corruption. To
achieve this, the database administrator makes backups, periodic copies or
snapshots of the database. The database administrator must also be able to use
these backups to recover the data in the event of hardware failure or data
corruption.
The recommended method to backup and recover an Enterprise Geodatabase
running on PostgreSQL is to use a tool provided by PostgreSQL called pg_dump to
create a backup file, drop the database, recreate the database with the same name
and
finally
restore
to
the
re-created
database
using
another
tool
called
pg_restore.
All these operations only block other activities on the geodatabase that require an
exclusive lock.
To create a backup of an ArcSDE geodatabase for PostgreSQL using pg_dump,
execute the pg_dump command as a superuser from a PowerShell window. It will
create an archive file that can be used to re-create a database.
38 Working with an Enterprise GeoDatabase 1. Open a Powershell Window
2. Browse to the location of the bin folder located in the PostgreSQL installation
folder through this command:
cd “PostgreSQL installation path”\bin
Example:
cd c:\Program Files\PostgreSQL\9.2\bin
3. Run the command below and provide the password for the postgres user to
create a backup of your database.
.\pg_dump –U postgres –Fc –f “path where you want to save the backup” dbname
Example:
.\psql –U postgres -Fc –f “C:\GIS\PostgreSQL\cairo_backup_10012015.dump” cairo
The pg_dump command can only create a backup of a single database at a time, and it
doesn’t include information about roles and tablespaces. For this reason, in case your
problem is not limited to the single database but to the entire PostgreSQL, you have to
create a backup for each geodatabase and then recreate roles and tablespaces before
restoring your geodatabases.
To restore a database from an archive file created using the pg_dump command,
use the pg_restore command.
In this section we’ll describe how to restore a malfunctioning database from a
previous backup and then some additional step needed in case you have to reinstall
the entire PostgreSQL Server.
Working with an Enterprise GeoDatabase 39 2.4.1.1
Restoring a geodatabase in the same PostgreSQL Server
If you are restoring into the same PostgreSQL server, you must drop the existing
database before restoring because database names in a PostgreSQL instance must
be unique.
1. After you have created a dump of your geodatabase, drop the geodatabase
running the instruction below:
.\dropdb –U sde “databasename”
Example:
.\dropdb –U sde test_geodb 2. Re-create the geodatabase using the createdb command line tool:
.\createdb –U sde –E UTF8 –O sde “databasename”
Example:
.\createdb –U sde –E UTF8 –O sde test_geodb
3. Log in into your database and create an sde schema owned by the sde user
and grant usage on the sde schema to all login roles or groups that will
access the geodatabase.
.\psql –U sde “database name”
Example:
.\psql –U sde test_geodb
CREATE SCHEMA sde AUTHORIZATION sde;
GRANT USAGE ON SCHEMA sde TO creator;
GRANT USAGE ON SCHEMA sde TO editor;
GRANT USAGE ON SCHEMA sde TO viewer;
40 Working with an Enterprise GeoDatabase 4. Run the instruction below:
ALTER DATABASE “database name” SET SEARCH_PATH=”$user”,sde,public;
Example:
ALTER DATABASE test_geodb SET SEARCH_PATH=”$user”,sde,public;
5. Using the command line, restore the public schema and data using the
pg_restore command. When you restore a geodatabase, you must restore
schemas in a specific order: first the public schema, then the sde schema,
then the remaining schemas. Run the commands as a user with superuser
privileges, such as the postgres or sde user.
.\pg_restore –U sde –d “database name” –n public “path to the dump”
.\pg_restore –U sde –d “database name” –n sde “path to the dump”
.\pg_restore –U sde –d “database name” “path to the dump”
Example:
.\pg_restore –U sde –d test_geodb –n public C:\cairo_backup_10012015.dump
.\pg_restore –U sde –d test_geodb –n sde C:\cairo_backup_10012015.dump
.\pg_restore –U sde –d test_geodb C:\cairo_backup_10012015.dump
4. Once the database restoration is complete, test the database by connecting to it
using ArcGIS for Desktop, examining existing data and importing new one.
Working with an Enterprise GeoDatabase 41 2.4.1.2 Restoring a geodatabase after installing a new PostgreSQL server
In case you had to reinstall the PostgreSQL server before restoring the
geodatabase you have backed up, you have to recreate the sde user, all groups
and roles and configure again the PostgreSQL server following the process
described in the first chapter.
After having created the Enterprise GeoDatabase, you can restore the data from
your backup using the procedure described above, starting from the third step in
the previous paragraph.
Francesco Stompanato 23/1/15 09:19
Deleted: point 3
2.4.2 Database performance improvement
In an Enterprise GeoDatabase data can be versioned to facilitate concurrent editing
over long periods of time. Without versioning, editors would lock data and prevent
other users from editing or even viewing the data. When versioning is used, edits
are saved in delta tables and as edits are made, the number of states and rows in
the
delta
tables
grows,
slowing
database
performance.
Compressing
the
geodatabase removes the states that are no longer referenced by a version and
can move rows in the delta tables to the base table.
The larger the tables and the more states, the more data ArcGIS must process
every time you display or query a version. Therefore, the greatest impact on
performance is not the number of versions but the amount of changes contained in
the delta tables for each version. As a result, versions can have different query
response times.
To
maintain
database
performance,
the
geodatabase
administrator
must
periodically run a compress operation to remove unused data.
The frequency with which you need to perform a compress operation is based on
the amount of editing that takes place in your geodatabase. If you have a high
42 Working with an Enterprise GeoDatabase volume of edits, you should probably compress the geodatabase once a day. For
average or low edit volumes, you should compress at least once a week.
2.4.2.1
Database compression
You can run the database compression through ArcMap or ArcCatalog following the
steps below.
1. Connect to your geodatabase using the administrative user (sde)
2. Right-click
the
database
connection
in
the
Catalog
tree,
point
to Administration, then click Compress Database
3. Click Yes to confirm that you want to run the compression operation on the
selected geodatabase
2.4.2.2
Database statistics
Database management systems determine the most efficient way to execute the
queries sent to them based on database statistics. Therefore, for optimal
performance of datasets created within Enterprise geo-databases, you need to
keep the database statistics current by frequently updating them.
When a feature class is registered as versioned, adds and deletes tables are
created to hold the records that are added to and deleted from a business table.
When a version is registered, the process automatically updates the statistics for all
the required tables. After that, as changes are made to the feature class, the
distribution of information in the tables and indexes changes, causing the database
statistics to become outdated.
It’s good practice update statistics after running a compression.
Working with an Enterprise GeoDatabase 43 You can update the statistics of feature classes and tables you own from
within ArcGIS for Desktop.
To open the Analyze Datasets tool from the Catalog tree in ArcCatalog or ArcMap
and update dataset statistics, do the following:
1. Start ArcMap or ArcCatalog and connect to the database as the owner of the
data for which you want to update statistics.
2. Navigate to the Analyze Datasets geoprocessing tool in the Geodatabase
Administration toolset in the Data Management toolbox.
3. Use the database connection file created with the owner of the datasets you
want to analyze.
4. From the Datasets to Analyze list, choose the tables, feature classes, feature
datasets, raster catalogs, raster datasets, or mosaic datasets for which you
want to update statistics.
44 Working with an Enterprise GeoDatabase 5. Leave the three checkboxes checked.
6. Click OK to run the tool.
Annex A - Installation of ArcGIS Desktop 10.2.2
In this section we’ll report some preliminary steps to install ArcGIS Desktop 10.2.2
on a server running Windows Server 2012. In case your server runs a different
operating system, please get in touch with your regional GIS officer or with the
GSU unit at the HQ, by sending an email to [email protected].
The installation of ArcGIS Desktop is a multistep process:
1) Make sure .NET Framework 3.5 SP1 is installed in your server;
2) Install ArcGIS for Desktop;
3) Authorize ArcGIS for Desktop using a provisioning file.
To complete the ArcGIS for Desktop installation you will need the following
software components and license files:
o ArcGIS Desktop 10.2.2
o Provisioning file for ArcGIS for Desktop Advanced
o Installation media of Windows Server 2012
The first two can be requested to the GSU unit at HQ, while the Windows Server
2012 installation file must be provided by the IT officer who administers the server.
Furthermore make sure your user has Administrator rights to be able install
software components.
Working with an Enterprise GeoDatabase 45 A.1 Installation of .NET Framework 3.5 SP1
ArcGIS for Desktop requires Microsoft .NET Framework Version 3.5 SP1. If .NET
Framework 3.5 SP1 is not detected on your machine, follow the steps below,
otherwise the ArcGIS for Desktop setup will not proceed.
1. Open Server Manager, click Manage and then select Add Roles and
Features to start the wizard.
46 Working with an Enterprise GeoDatabase 2. Click Next;
3. On the Select installation type screen , select Role-based or feature-based
installation;
Working with an Enterprise GeoDatabase 47 9. Click Next;
10.
Click Next;
48 Working with an Enterprise GeoDatabase 11.
On the Select features screen, check the box next to .Net Framework 3.5
Features.
12. On the Confirm installation selections screen, a warning will be displayed
asking Do you need to specify an alternate source path?. Click
the Specify
an
alternate
source
path link
to
specify
the
path
to
the \sources\sxs folder on the installation media and then click OK.
If the installation disc for Windows Server 2012 is mounted as drive D, follow
the example below by specifying the path: d:\sources\sxs.
Working with an Enterprise GeoDatabase 49 13. Click the X next to the warning and then click Install.
50 Working with an Enterprise GeoDatabase Once the installation is finished, restart the server and proceed with the ArcGIS for
Desktop installation.
In this training manual we don’t describe the 2 additional steps, which are part of
the typical ArcGIS for Desktop installation. For any specific need please refer to the
specific documentation on the ESRI portal or get in touch with the GSU unit at HQ.
http://resources.arcgis.com/en/help/install-guides/arcgisdesktop/10.2/index.html#/Installation_overview/00870000000r000000/
Working with an Enterprise GeoDatabase 51 Annex B - Installation of ArcGIS for Server Enterprise
This section guides you through the ArcGIS for Server installation process on a
server running Windows Server 2012.
In order to complete successfully this process, you need the following software
components and license files:
o ArcGIS for Server Enterprise 10.2.2 (for Windows)
o Provisioning file for ArcGIS for Server Advanced Enterprise
o PostgreSQL 9.2.2 Client Libraries (Windows)
As for the ArcGIS for Desktop software, these files can be requested to the GSU
unit at HQ. Furthermore make sure your user has Administrator rights and close all
applications on your server before starting.
Once preparation is completed:
1. Double-click on the installation file and extract it in you preferred path;
2. Go to the path specified in the previous step and double-click on the Setup to
start the installation wizard;
3. Click Next
52 Working with an Enterprise GeoDatabase 4. Click Next. (In case you get an error reporting that the .NET 3.5 framework is
missing, follow steps in the annex A)
5. Specify the account to be used by ArcGIS for Server to perform a variety of GIS
functions. Choose the first option by directly specifying the account name and
password. You can leave the default account name (arcgis).
Please remember to save these credentials in a safe place.
Working with an Enterprise GeoDatabase 53 6.
Click Next and follow the directions on the screen.
7.
After the installation completes, the Software Authorization Wizard is
launched. In case it doesn’t happen, launch it by clicking the launcher that
can be found in the programs directory at: Start > Program Files >
ArcGIS > Software Authorization
8.
Select the 3rd option and browser to the ArcGIS for Server provisioning file,
follow the directions on the screen to complete the authorization.
9.
After completing the authorization, the ArcGIS for Server Manager should
appear automatically. You will be asked weather you want to create a new
site or join an existing one. Choose the first option to add your machine as
the first GIS server in your configuration.
In case the Server Manager doesn’t appear, open a different browser (install
Chrome if it’s not available yet) and type this url:
http://localhost:6080/arcgis/manager
10. When you create a new site you’ll be prompted to enter an account that will
act as the primary site administrator. You will use this account to initially log
in to Manager and configure security. This account has unrestricted access to
the ArcGIS Server site. Enter a user name and password and click next.
Please remember to save these credentials in a safe place.
11. Click Next to choose the default server directories and configuration store.
12. Examine the details about the site you are going to create and click Finish.
54 Working with an Enterprise GeoDatabase Configure ArcGIS Server to work with PostgreSQL
Before publishing geodata services, ArcGIS Server needs to be properly configured
to be able to connect to the PostgreSQL instance. In particular you have to copy
the database client libraries (PostgreSQL Client Libraries) into the bin folder of the
ArcGIS Server installation directory.
1. Run the .exe file PostgreSQL 9.2.2 Client Libraries and extract files
in a new folder;
2. Copy the files (64bit version) into the bin folder of the ArcGIS Server
installation directory. (C:\Program Files\ArcGIS\Server\bin)