0

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     |              | 

4
  • 1
    It seems that your points got encoded from EWKT to WKB, either when you dumped them, or when your restored them. If you app can handle both encoding, it's normal that you do not see any changes in its behaviour. But it's somehow surprising even if the underlying data are the same. – s.k Feb 21 at 20:56
  • @s.k That explains! Wonder if it is possible to avoid the conversion from EWKT to WKB at either the pg_dump or pg_restore step... – Nyxynyx Feb 21 at 21:10
  • It could be a viewer "feature" too, if the 1st one is configured to display geometries/geographies using st_asEWKT. You can try doing the same using the psql console. After a dump+restore, we can expect the table definitions to be the same, but double check using \d+ tablename on both DB – JGH Feb 22 at 13:13
  • @JGH You are right, d\+ tablename shows the same field type for the coordinates column (updated question to show this). I wonder why the GUI software TablePlus shows them differently... – Nyxynyx Feb 22 at 15:22

Your Answer

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

Browse other questions tagged or ask your own question.