3

I am new to PostgreSQL logical replication. I did test, after I added a new table to a publication, I found the replication didn't work, until I re-created subscription, I am sure it's not the best practice to re-create subscription, could you please advise how make subscriber apply transactions for the new table?

Test as below:

  1. Create the first table on primary and replicate side:

     create table rep_test (a int primary key, b int);

  2. Create publication on primary side:

     CREATE PUBLICATION rep_test_pub FOR table public.rep_test;

  3. Create subscription on replicate side:

     CREATE SUBSCRIPTION rep_test_sub CONNECTION 'host=XXX port=5432 dbname=rocket user=XXX password=XXX' PUBLICATION rep_test_pub WITH (copy_data = false);

  4. Test the replication, the replication works. Primary side:

     insert into rep_test values (1, 1); insert into rep_test values (2, 1);

    Replicate:

     select * from rep_test;

 *---*---*
 | a | b |
 *---*---*
 | 1 | 1 |
 | 2 | 1 |
 *---*---*

  5. Create a new table on both primary and replicate side

     create table rep_test (a int primary key, b text);

  6. Add new table to publication on primary side

     alter publication rep_test_pub add table public.rep_test2;

  7. Test the replication, the replication is not working. Primary:

     insert into rep_test values (3, 1); insert into rep_test2 values (1,'text');

Replicate:

    select * from rep_test;

    *---*---*
    | a | b |
    *---*---*
    | 1 | 1 |
    | 2 | 1 |
    | 3 | 1 |
    *---*---*

    select * from rep_test2;

Note: No data in rep_test2, the replication didn't replicate rep_test2.

  1. Reboot replicate postgres, the replication still not working.

  2. Drop and re-create subscription, the replication works.

Primary:

truncate table rep_test; truncate table rep_test2;

Replicate:

drop subscription rep_test_sub;
CREATE SUBSCRIPTION rep_test_sub CONNECTION 'host=XXX port=5432 dbname=rocket user=XXX password=XXX' PUBLICATION rep_test_pub WITH (copy_data = false);

Primary:

insert into rep_test values (1, 1); insert into rep_test2 values (1, 'text');

Replicate:

select * from rep_test;select * from rep_test2;
 
*---*---*
| a | b |
*---*---*
| 1 | 1 |
*---*---*
(1 row)

*---*-----*
| a | b   | 
*---*-----*
| 1 | text|
*---*-----*
(1 row)

The replication works after re-created subscription.

Could you please advise is there another way to make subscriber apply transactions to the new table?

BTW, my version:

PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

Thanks

BinaryButterfly
  • 18,137
  • 13
  • 50
  • 91
aris yang
  • 41
  • 1
  • 2

1 Answers1

11

The new table is not replicated until you do REFRESH PUBLICATION on the subscription.

https://www.postgresql.org/docs/current/sql-altersubscription.html

REFRESH PUBLICATION Fetch missing table information from publisher. This will start replication of tables that were added to the subscribed-to publications since the last invocation of REFRESH PUBLICATION or since CREATE SUBSCRIPTION.

jjanes
  • 37,812
  • 5
  • 27
  • 34