Postgresql – pg_restore using PostGIS/PostgreSQL Dump Changes Data in Geography Column

pg-restorepostgispostgresqlrestorespatial

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;

Query Result on 2nd Machine (after pg_restore):

SELECT coordinates FROM locations LIMIT 5;

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

Best Answer

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.

Related Solutions

PostgreSQL – Handling Encoding Issues with pg_dump/restore Between Windows and Linux

I suppose the dump file you have is encoded in WIN1252 and the target database on Linux has LATIN1 as the server encoding. That's not going to work, as the error message says.

I suggest you reinitialize the target database using UTF8.

Alternatively, create the dump in UTF8 using the pg_dump -E option.

PostgreSQL – pg_restore Error: Schema ‘public’ Already Exists

The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in:

dropdb -U postgres mydb && \
 pg_restore --create --dbname=postgres --username=postgres pg_backup.dump

The --clean option in pg_restore doesn't look like much but actually raises non-trivial problems.

For versions up to 9.1

The combination of --create and --clean in pg_restore options used to be an error in older PG versions (up to 9.1). There is indeed some contradiction between (quoting the 9.1 manpage):

--clean Clean (drop) database objects before recreating them

and

--create Create the database before restoring into it.

Because what's the point of cleaning inside a brand-new database?

Starting from version 9.2

The combination is now accepted and the doc says this (quoting the 9.3 manpage):

--clean Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)

--create Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

Now having both together leads to this kind of sequence during your restore:

DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...

There is no DROP for each individual object, only a DROP DATABASE at the beginning. If not using --create this would be the opposite.

Anyway this sequence raises the error of public schema already existing because creating mydb from template0 has imported it already (which is normal, it's the point of a template database).

I'm not sure why this case is not handled automatically by pg_restore. Maybe this would cause undesirable side-effects when an admin decides to customize template0 and/or change the purpose of public, even if we're not supposed to do that.

Best Answer

Related Solutions

PostgreSQL – Handling Encoding Issues with pg_dump/restore Between Windows and Linux

PostgreSQL – pg_restore Error: Schema ‘public’ Already Exists

Related Question