Home Backup complete postgis database with geometry transformation

# Backup complete postgis database with geometry transformation

user9173
1#
user9173 Published in September 19, 2018, 9:57 am

I have a PostGIS enabled PostgreSQL database that is no longer needed in production. I would like to back it up, but the geometry columns of PostGIS should be transformed to a simple, long-term stable text format like WKT.

I'm aware of the ST_AsText function.

SELECT road_id, ST_AsText(road_geom) AS geom, road_name FROM roads;


But how do I apply this to backup the complete database with several tables with geometries and many without?

• Would you have so many tables, that writing a script wouldn't be possible? In case there aren't that many, a copy statement would do the trick. Let me know if you need help with this. – Jim Jones Apr 16 at 16:13
• btw, why exactly do you need the backup as wkt? – Jim Jones Apr 16 at 16:20
• Yes, the default output format is EWKB, the Extended Well-Known Binary format. It is as good as EWKT. – Laurenz Albe Apr 16 at 17:23
• @JimJones By writing a script you mean a set of select statements to pull the individual tables? Wkt is not necessary, but I'm familiar with it and I've seen that the transformation as already implemented in ST_AsText(). – nevrome Apr 16 at 17:45
• I totally understand your concern. Well, I'm not sure if it is possible to change the output format for geometries, but in case you don't have many tables with geometries, I'd go for a solution like CREATE TABLE roads2 AS SELECT road_id, ST_AsText(road_geom) AS geom, road_name FROM roads;. In that way you would create a second table containing the geometries as WKT and so your backup. Not elegant, but perhaps okay for archival purposes. – Jim Jones Apr 17 at 17:40