Replicating database with postgis extension

[PisikeSipelgas]

Hello,

I have problem with pglogical when trying to migrate database with postgis extension to new server. Following is an illustrative example with empty database.

postgres=# create database testdb;
CREATE DATABASE

postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".

testdb=# create extension postgis;
CREATE EXTENSION

testdb=# \dx
                                     List of installed extensions
  Name   | Version |   Schema   |                             Description
---------+---------+------------+---------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis | 3.0.7   | public     | PostGIS geometry, geography, and raster spatial types and functions

testdb=# create publication test_pub for all tables;
CREATE PUBLICATION
testdb=# \dRp
                                 List of publications
   Name   |  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+----------+------------+---------+---------+---------+-----------+----------
 test_pub | postgres | t          | t       | t       | t       | t         | f

From shell migrating globals/roles and schemas like this:

shell# pg_dumpall -g | psql -q -U postgres -h 192.168.1.2 -w -f - postgres
shell# pg_dump -Fp -C -s db | psql -q -U postgres -h 192.168.1.2 -w -f - postgres

On target host:

testdb=# \dx
                                     List of installed extensions
  Name   | Version |   Schema   |                             Description
---------+---------+------------+---------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis | 3.3.2   | public     | PostGIS geometry, geography, and raster spatial types and functions

testdb=# CREATE SUBSCRIPTION db_sub CONNECTION 'dbname=testdb host=192.168.1.1 user=postgres password=pass' PUBLICATION testdb_pub;

And now the problem appears:

2022-12-06 11:03:44.705 CET [121812] LOG:  logical replication table synchronization worker for subscription "testdb_sub", table "spatial_ref_sys" has started
2022-12-06 11:03:44.746 CET [121812] ERROR:  duplicate key value violates unique constraint "spatial_ref_sys_pkey"
2022-12-06 11:03:44.746 CET [121812] DETAIL:  Key (srid)=(3819) already exists.
2022-12-06 11:03:44.746 CET [121812] CONTEXT:  COPY spatial_ref_sys, line 1
2022-12-06 11:03:44.748 CET [121711] LOG:  background worker "logical replication worker" (PID 121812) exited with exit code 1

I do understand the problem from both sides (pglogical and postgis), but it's like hitting a wall of dead end. Is there a reasonable way to overcome this?

[eulerto]

It has nothing to do with pglogical. You are using native logical replication. AFAICS the table spatial_ref_sys should be excluded from the publication. It is part of the PostGIS extension and it is populated by CREATE EXTENSION. Don't use FOR ALL TABLES if you have extensions that create tables. Instead, explicitly inform the tables or use TABLE IN SCHEMA (Postgres 15 or later). Other alternative is to truncate the referred tables on the subscriber before creating the subscription.

[PisikeSipelgas]

Hi, Alright. I'll try to truncate. Thanks for the hint. Regards,

Update: at first i did'nt get what You ment with "It has nothing to do with pglogical. You are using native logical replication. " It took me several minutes to fully comprehend what You ment. And this is even more valuable hint than truncate. Thank You so much for clarifying!