Sample Header Ad - 728x90

pg_restore using PostGIS/PostgreSQL Dump Changes Data in Geography Column

0 votes
1 answer
427 views
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
Asked by Nyxynyx (1131 rep)
Feb 21, 2021, 12:22 AM
Last activity: Sep 22, 2023, 10:01 AM