0

I have database and table collection_city on one server. It has 21 rows. I have database and table collection_city on second server. It also has 21 rows.

They both have this row:

tinker=# table collection_city;
 id |     name      |    alias     | postal_code | region_id 
----+---------------+--------------+-------------+-----------
  2 | Obrenovac     | obrenovac    |             |         1

id column is the primary key.

I created Publication on first server:

CREATE PUBLICATION tinkerpub FOR ALL TABLES;

tinker=# \dRp[+]
                      Publication tinkerpub
  Owner   | All tables | Inserts | Updates | Deletes | Truncates 
----------+------------+---------+---------+---------+-----------
 postgres | t          | t       | t       | t       | t
(1 row)

I created Subscription on second server:

CREATE SUBSCRIPTION tinkersub CONNECTION 'dbname=tinker host=192.168.150.5 user=postgres password=test port=5432' PUBLICATION tinkerpub;

But I see errors like this (it is trying to copy the row with id 2 AGAIN???):

2019-06-25 15:51:37.178 CEST [2270] ERROR:  duplicate key value violates unique constraint "collection_city_pkey"
2019-06-25 15:51:37.178 CEST [2270] DETAIL:  Key (id)=(2) already exists.
2019-06-25 15:51:37.178 CEST [2270] CONTEXT:  COPY collection_city, line 1
2019-06-25 15:51:37.181 CEST [21905] LOG:  background worker "logical replication worker" (PID 2270) exited with exit code 1

Why system is trying to copy the data which already exists with exactly the same data? I see errors for all tables. How we can resolve this? Does the second table should be totally empty before the subscription process? But it does not have any sense...why I should not have both tables populated and then to start the process of replication at one moment?

1

By default, CREATE SUBSCRIPTION will first copy all the existing data from the source tables. The idea is that you usually start with an empty destination database created with something like pg_restore -s.

If you don't want to have the initial data copy, create the subscription like this:

CREATE SUBSCRIPTION ...
WITH (copy_data = false);

See the documentation for details.

  • Hi, thanks for info - does this mean that every new data after the subscription is created will be copied? Basically I am populating two tables with Django framework so they both have the data.... after they are populated I would like to copy from publication.. so is it OK now to first DROP the SUBSCRIPTION and then to CREATE it with this new parameter? thanks – Dejan Jun 25 at 15:12
  • I don't follow. Of course all data modifications will be replicated once the subscription is created. That's the point of replication, isn't it? – Laurenz Albe Jun 25 at 15:16
  • Because I already created SUBSCRIPTION should I DROP it and recreate it? Do I need to somehow remove replication slot which is created also before I create SUBSCRIPTION again? – Dejan Jun 25 at 18:42
  • You can either drop the replication slot again or create a new subscription that uses an existing replication slot. – Laurenz Albe Jun 26 at 7:00

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.