TransWikia.com

PostGIS Geography Data Changed After pg_restore

Geographic Information Systems Asked on April 27, 2021

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 a Docker image kartoza/postgis:13.1.

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 using different representations.

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

Update 2: d+ locations on both databases show the same type for coordinates

1st Machine (pg_dump source):

                                                                Table "public.locations"        
        Column         |         Type          | Collation | Nullable |                Default                 | Storage  | Stats target | Description 
-----------------------+-----------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
 id                    | integer               |           | not null | nextval('locations_id_seq'::regclass)  | plain    |              |                         
 coordinates           | geography(Point,4326) |           |          |                     

2nd Machine (after pg_restore):

                                                                Table "public.locations"
        Column         |         Type          | Collation | Nullable |                Default                 | Storage  | Stats target | Description 
-----------------------+-----------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
 id                    | integer               |           | not null | nextval('locations_id_seq'::regclass)  | plain    |              |                       
 coordinates           | geography(Point,4326) |           |          |                                        | main     |              | 

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP