Migrating data from ArcSDE to PostGIS?

Rather than doing a DB -> file -> DB conversion, where Esri Shapefiles are the middleman file-based component, it could be worthwhile investigating a more direct DB -> DB transfer. There are a few tools out there that can do this, but I'm only focusing on the open source GDAL/OGR tools.

Assuming you have a Windows Server, you can easily install GDAL/OGR using OSGeo4W. With the Advanced Install mode, you have the ability to select custom package configurations, such as the SDE driver (see this list of packages to ensure there is a suitable match for your SDE version -- only SDE versions 9.0 to 9.2 are supported). These packages are only the headers and wrappers, and requires you to install the SDE libraries, which you should have on a CD/DVD ROM, and ensure the PATH variables are appropriately set for GDAL/OGR to find it. A successful install will show "SDE" somewhere with the following command in an OSGeo4W shell: ogr2ogr --formats

After you configure GDAL/OGR, you can use a few tools:

• The command-line tool ogr2ogr from the OSGeo4W shell, which will need a command something like: ogr2ogr -f "PostgreSQL" PG:"host=localhost user=someuser dbname=somedb password=somepassword port=5432" \ SDE:server,instance,database,username,password,layer,[version] (you can also explore the various -append, -overwrite or -update options)
• Copy and modify an existing Python script that connects to SDE, and you could modify it to transfer data to another data source.

The major caveat to this method is that it is complicated to setup.

PostGIS has a loader named shp2pgsql that you can use to load shapefiles into a PostGIS database. One of its options ( "-d" ) drops the existing database table before loading the data. It should be pretty simple to create a batch file or script that can loop through all of the shapefiles and load them for you.

If you wanted to get fancier about it, you could install GDAL, and use the OGR tools (which have an ArcSDE connector) and skip the shapefile export. Info on the ArcSDE connector can be found here.