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
**Query Result on 2nd Machine (after pg_restore):**
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
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;

SELECT coordinates FROM locations LIMIT 5;

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
Last activity: Sep 22, 2023, 10:01 AM