Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using Docker images.

A dump of the database was taken from the first machine using

pg_dump --host=db1.foo.com --dbname=foo --username=postgres -Fc --file=/tmp/foo.dump

and then restored on the database on the second machine using

pg_restore --clean --dbname=foo /tmp/foo.dump

When we view a query result using a GUI software TablePlus, we noticed that the column named coordinates of type Geography contains values that look very different after restoring.

Query Result on 1st Machine (pg_dump source):

SELECT coordinates FROM locations LIMIT 5;

enter image description here

Query Result on 2nd Machine (after pg_restore):

SELECT coordinates FROM locations LIMIT 5;

enter image description here

However, our app that queries this database for coordinate data appears to be plotting the data correctly on a map. This makes me think that the data on both machines are the same, but stored in different representation.

Question: Why did the Geography data values in the column coordinates changed, and how can we restore from the dump while keeping the original data values?

Update: Tried using -b when performing pg_dump, but the problem persist.

pg_dump --host=db1.foo.com --dbname=foo --username=postgres -Fc -b --file=/tmp/foo.dump

Your first output is showing as EWKT (Extended Well Known Text) while the second output is WKB (Well Known Binary). These are both standard representations of the internal geometry but are designed for different end users (human v machine).

To be honest I have never see PostGIS return WKT in a standard select statement, usually you would wrap the geometry in ST_AsTEXT or ST_AsEWKT to see that.

Any way I think your data is just fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.