topic Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS) in Geodatabase Questions

Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

danbecker — Tue, 22 Nov 2022 20:26:42 GMT

Existing on-prem server:

Ubuntu Server 18.04 LTS
PostgreSQL 11.12
Contains ~90 SDE Geodatabases; total size ~500GB
ST_GEOMETRY is used

All ArcGIS Enterprise components are getting moved to Azure VMs, workstations will be Azure Virtual Desktops and we want to deploy Azure database for PostgreSQL (database-as-a-service).

Since ST_GEOMETRY cannot be enabled on databases in the cloud can anyone provide instructions for converting the existing ST_GEOMETRY spatial types to POSTGIS?

thanks.

Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

MarceloMarques — Wed, 23 Nov 2022 00:07:24 GMT

You need to reload the data.

You need to create new PostgreSQL Enterprise Geodatabases on premises then change the default geometry type in the sde.dbtune ##defaults from st_geometry to postgis and reload the data using ArcCatalog or ArcGIS Pro.

Note, if you have traditional versioning or branch versioning on the old st_geometry geodatabases then you need to post/reconcile all child versions with the sde.default version, then delete the versions and execute a geodatabase sde compress (if traditional versioning), then perform the PostgreSQL maintenance to gather new statistics and then proceed to reload the data.

----------------------------------------

sde dbtune configuration keywords

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/overview/configuration-keywords-for-enterprise-geodatabases.htm

Export Geodatabase Configuration Keyword

modify the dbtune export file and save

from:

##DEFAULTS
GEOMETRY_STORAGE "ST_GEOMETRY"

to:

##DEFAULTS
GEOMETRY_STORAGE "PG_GEOMETRY"

Import Geodatabase Configuration Keyword

----------------------------------------

Create New Geodatabase PostgreSQL

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-postgresql/setup-geodatabase-postgresql-windows.htm

11. Use the Spatial Type drop-down list to choose the spatial type the geodatabase will use: either ST_Geometry or PostGIS.

----------------------------------------

Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

VinceAngelo — Wed, 23 Nov 2022 16:36:46 GMT

When you state that you have "~90 SDE Geodatabases", does that mean you have ~90 PostgreSQL databases? That would be quite a few.

While the migration procedure is basic enough, migrating a half-terabyte to the cloud is not a trivial undertaking. Have you benchmarked performance, and/or plan to move the compute resources (Enterprise components) to the same cloud?

Keep in mind that database latency is increased when using on-premise applications with remote/cloud databases -- I had a PG RDS migration delayed by more than a year until network infrastructure could support adequate bandwidth to the RDS mission, after initial benchmarking showed a 90-minute publishing export process took 4-6 days from the RDS host.

You might want to look at migrating the SDE.ST_GEOMETRY columns to PostGIS Geometry in the on-premise instance, so that the migration will be a simple pg_dump/pg_restore. But note that pg_dump corrupts PG 11 enterprise geodatabase datasets, because of the deprecation of WITH OIDS=TRUE (used in iN tables). From that regard, you might be better off standing up PG12 or PG13 in the target instance (after making sure your other Esri tools are compatible with 12/13)

To summarize:

90 databases/500Gb is a huge migration
Beware of increased latency in an off-premise database (unless you also move the data operation servers into the same/nearby servers)
PG 11 is now the eldest supported database. PG 11-PG 11 migration via database tools is risky, and in situ enterprise geodatabase upgrade from PG 11 to PG 12/13 is not supported. Consider migrating to PostGIS geometry before migration, then going directly to the most recent release your Esri stack can support using database tools.

Good luck.

- V

Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

MarceloMarques — Wed, 23 Nov 2022 16:41:18 GMT

You might be interested to read my white paper.

White Paper: How to Move the PostgreSQL Enterprise Geodatabase using pg_dump and pg_restore

community.esri.com article | PDF document

This white paper is intended for database administrators as recommendations
to move the PostgreSQL Enterprise Geodatabase with pg_dump & pg_restore.

For more best practices see: Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

danbecker — Wed, 23 Nov 2022 17:15:32 GMT

Thanks everyone, this should be enough to get me started with the test environment!

Yes, we have ~90 PostgreSQL databases, but I would guess only 1/2 of them will make the migration. A lot of db cleanup needs to happen for projects (db's) that are no longer active.

Sounds like the best path is to convert on-prem SDE.ST_GEOMETRY columns to PostGIS Geometry, then pg_dump. Transfer that pg_dump (.post file) to Azure Storage Account, then use pg_restore to the Azure instance.

Our IT consultant is following this doc: Deploy Esri ArcGIS Pro in Azure Virtual Desktop, except Azure db for PG, not SQL.

Our entire base Enterprise deployment is being moved to Azure VMs that are in the same region (Azure GOV) as the Azure db for PG instance. Our analysts' workstations are also being moved to Azure Virtual Desktops, and our file share (Pro projects, FGDBs, ect.) is being moved to Azure NetApp files. So, everything should be co-located in the same Azure region for minimal latency. I didn't even want to attempt on-prem workstations accessing cloud databases.

Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

danbecker — Thu, 08 Dec 2022 18:56:11 GMT

Here's what I tried:

1) Created a checkpoint of VM (obivously)

2) Installed PostGIS 3.0.1 on the PostgreSQL 11.12 instance (that's currently configured for ST_Geometry storage)

3) Used Create Enterprise Geodatabase tool to create "demogdb", specifying spatial type as POSTGIS

4) Created appropriate users in demogdb

5) Created appropriate feature datasets in demogdb

6) Feature class to feature class tool in Pro to move data from ST_Geometry gdb to new demogdb.

7) demogdb feature classes show:

ST_geometry (original) feature classes show this:

The above workflow seemed to work fine. What's the purpose of exporting/importing the configuration keywords?

I did export both the orginal and demogdb configuration keyworks and they each correctly show "ST_GEOMETRY" and "PG_GEOMETRY", respectively.

thanks!

Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

MarceloMarques — Thu, 08 Dec 2022 21:05:23 GMT

Question: What's the purpose of exporting/importing the configuration keywords?

Answer: to make sure ##DEFAULTS GEOMETRY_STORAGE is set to "PG_GEOMETRY"

You can export the dbtune or

query the sde.sde_dbtune table directly "select * from sde.sde_dbtune order by 1,2;"

you only need to import the dbtune if you make a change to the dbtune file that you exported.

Example:

from:

##DEFAULTS
GEOMETRY_STORAGE "ST_GEOMETRY"

to:

##DEFAULTS
GEOMETRY_STORAGE "PG_GEOMETRY"

I hope this clarifies your question.

Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

danbecker — Wed, 18 Jan 2023 19:34:21 GMT

Can someone elaborate on the process of updating the SDE connections after migration?

Our old on-premise DNS name will be different in Azure and there are issues with setting up a split-horizon DNS policy that resolves Azure requests to the new hosted postgres instance.

After migration, do we just update the datastore connections, or does it require re-publishing each service?

Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

MarceloMarques — Wed, 18 Jan 2023 19:42:39 GMT

@danbecker - if any of the geodatabase connection file properties e.g. database server name, port number, user name, password, changes then the ArcGIS Server Services will stop working and you will need to create new geodatabase connection files, resource the map documents and then republish the services.

Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

danbecker — Wed, 18 Jan 2023 21:47:44 GMT

After migration, (ArcGIS Enterprise site & SDE gdbs from on-prem to Azure db for Postgres), what if we had the oldhostname.ourdomain.com FQDN resolve to the IP of the Azure db for Postgres, where the dbo and password are identical.

Would this split-horizon DNS setup require re-publishing every service?

Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

MarceloMarques — Wed, 18 Jan 2023 21:47:31 GMT

@danbecker - you will still have to republish the services because the services were created with geodatabase connection files that point to the old server that info gets stored with the service, and in the ArcGIS Server > Sites > Data Store the Geodatabases connection Info also point to the old server, unfortunately is not possible to change the connection information in the services nor in the data stores, so you will need to republish the services, this is the best migration option anyways, because is a good idea to republish the services using ArcGIS Pro to take advanced of the new ArcGIS Server Service engine for Pro. I hope you are aware of this.
https://enterprise.arcgis.com/en/server/10.9.1/get-started/windows/what-s-new-in-arcgis-for-server.htm

ArcGIS Enterprise 10.9.1 will be the last release to support ArcMap based services. After this release, it will no longer be possible to publish or consume services from ArcMap (including ArcPy based in ArcMap) in ArcGIS Server sites. (read more in the link above)

Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

danbecker — Thu, 19 Jan 2023 00:13:42 GMT

Thanks for the help. Yes, we're aware of the ArcMap services, and are staying at 10.9.1 during the migration. I figured it was best to not upgrade versions in the middle of the lift-and-shift to Azure.

How about we deploy an Azure VM for PostgreSQL, replicating our on-premise environment. We can deploy it as newdb.ourdoamin.com, migrate the db's, then, when we edit the external DNS record to point traffic to Azure, disjoin currentdb.ourdomain.com from AD, then rename the Azure equivalent?

We can then casually start using Azure hosted PostGresql, and eventually re-publish all services pointing to the hosted instance and shut down the Azure PostgreSQL VM.

Re: Migrate on-prem PostgreSQL to Azure db for PostgreSQL (DBaaS)

MarceloMarques — Thu, 19 Jan 2023 01:20:57 GMT

@danbecker - that might work as long as the PG server "dns computer object name alias" and PG port number in the Geodatabase Connection Files used to publish the ArcGIS Server Services do not change then the services will not break. That is exactly what I do for on premises migrations as well. But I suggest you do a dry run test (prototype) of the migration first, to make sure everything will work as you expect, and to catch any issues and learn how to fix them.