Using pgsql2shp in server code with PostGIS installed on Amazon RDS instance

Question

I've created a web application using the Meteor web framework, GeoServer and PostGIS. I've deployed my app on Amazon Web Services with the Meteor app and GeoServer running as 2 separate tasks on 1 EC2 instance using ECS, and my PostGIS database being hosted by Amazon RDS. I've run into an issue where I've added a feature allowing users to download a Shapefile of their data by calling pgsql2shp in my server code, but this only works locally for me as I have PostGIS, GeoServer and my app all running on my machine. In the deployed version of my app the download feature is causing my app to crash as I don't have PostGIS installed on the container running my server code, it's installed on the Amazon RDS instance.

Does anyone know how I can export a Shapefile from Amazon RDS using pgsql2shp, when the utility isn't installed on the server machine that is executing the export command? Is there a web API that can be called instead?

Answer 1

You mention that you are already using Docker, so you could run this export process in a Docker container?

What's the command your server code runs?

You can probably replace it with something like this:

docker run mdillon/postgis -v /external/path/to/out:/internal/path/to/out pgsql2shp -args -including -connection -etc

Or, you can do it the old way, and just install the Postgres tools on the AMI you're using.

In fact, you've probably got your meteor app running the pgsql2shp command in that docker container, so why not just add the postgres client tools to that? Have you got a Dockerfile for the meteor container? Just YUM or APT install pgsql2shp, which will give you the right ability. Or use OGR2OGR for something more flexible.

Answer 2

I encountered this issue too. I had to solve this problem for an Elastic Beanstalk deployment.

I used the process documented here to create an EB AMI:

https://docs.aws.amazon.com/elasticbeanstalk/latest/dg/using-features.customenv.html

I created the AMI and compiled the latest version of PostGIS on the AMI with this script:

https://gist.github.com/whyvez/8d19096712ea44ba66b0

I changed the versions of the products to make them the most recent ones. If you're interested, please reply and I will email the new script to you.

This resulted in a pgsql2shp binary that could communicate with the RDS postGIS server with the correct command parameters.

The downside is that a full version of postgreSQL has to be installed on the AMI's, but it doesn't have to be running. That consumes about an extra gig of space, it seems.