Problems importing large datasets into PostgreSQL/SDE

10-22-2010 03:30 PM
New Contributor
We are currently testing and benchmarking ArcSDE with PostgreSQL and I've run into some strange problems that I haven't been able to resolve on my own.

I have been able to import some rasters and feature classes through both the ArcSDE Service and through a direct connection, but larger rasters and feature classes fail to load completely.

For rasters, it appears that ArcCatalog just stops sending data when the PostgreSQL table size reaches 539 MB �?? this happens consistently and with several separate rasters and LZ77 compression.  When using no compression, the upload stops consistently at 531 MB. (This usually takes anywhere from three to five minutes)

When using sderaster, the raster stops uploading after just 97 MB (about two minutes).

For features, I am able to import only about 3.5 million features (904 MB in PostgreSQL) of a shapefile containing about ten million points.

When these limits are hit, ArcCatalog acts as if it's still uploading data and on the Postgres server I can see that the connections are still open but idle. I've left it running for quite some time in this state and it never seems to recover. Sometimes it will fail with a Visual C++ Runtime Library error stating that the "application has requested the Runtime to terminate in an unusual way." Canceling the operation causes ArcCatalog to crash. When the ten million points fail to import, ArcCatalog lists an "ERROR 000224."

There are no errors reported in the Postgres logs.

There is more than enough disk space available for the Postgres tablespace (423 GB) and other applications are able to insert large amounts of data through long running transactions.

Again, I am able to import smaller datasets �?? for example, I've repeatedly imported a shapefile containing one million points �?? and have consistently and repeatedly imported smaller rasters.

I've attempted raising the shared buffer size on the postgres server (needed to be done anyway), reinstalling ArcGIS Desktop, running ArcCatalog in both Windows XP and Windows 7, and many different raster sets. Nothing I've tried has produced any different result.

The PostgreSQL server is running on a Centos 5 box, fully updated. We're using ArcGIS Server 10 and I'm using ArcGIS Desktop 10 with an ArcInfo license.

Can anybody point me towards a solution or at least some other things to try to troubleshoot the problem?

0 Kudos
1 Reply
Esri Esteemed Contributor
I regularly load five and seven million row GeoNames tables into a number of
different RDBMS instances (Oracle 10g & 11g, SQL-Server 2008 & PostgreSQL).
Each of these tables uses several GB on disk.  I've never seen the behavior you
are describing. 

Have you tried to use a direct loader like 'shp2sde' to load the shapefile?

Have you tried appending the 1m source data until it fails?

Have you tried loading a gigabyte of strings into your PG instance using SQL
(without any geometry at all)?

Did you use the PG838 that ships with ArcSDE 10?

Please open a Tech Support incident, since they're going to be your best resource
for determining what's going on.  Be sure to provide all the tuning parameters
and complete information about your PostgreSQL instance, since this seems to
be the most likely issue.

- V
0 Kudos