Right way to restore logical replication

[Игорь Выскорко]

Hi, community!Unfortunately can't find answer in docs and google. Hope only for you)Have running 2 postgres locally: :5433 - postgres 11 as master:5434 - postgres 12 as slave Creating basic setup for testing:[local]:5433 postgres@postgres=# create table tbl(id serial, d text, primary key(id));CREATE TABLETime: 7,147 ms [local]:5434 postgres@postgres=# create table tbl(id serial, d text, primary key(id));CREATE TABLETime: 11,557 ms [local]:5433 postgres@postgres=# create publication pub for table tbl;CREATE PUBLICATIONTime: 6,646 ms [local]:5434 postgres@postgres=# create subscription sub connection 'host=localhost port=5433 dbname=postgres user=postgres' publication pub;NOTICE:  created replication slot "sub" on publisherCREATE SUBSCRIPTIONTime: 18,584 ms [local]:5433 postgres@postgres=# insert into tbl(d) values ('test');INSERT 0 1Time: 3,401 ms [local]:5434 postgres@postgres=# select * from tbl; id |  d   +--  1 | test(1 row) works like a charm. Ok. Lets drop "accidentally" publication and insert new data: [local]:5433 postgres@postgres=# drop publication pub ;DROP PUBLICATIONTime: 3,793 ms [local]:5433 postgres@postgres=# insert into tbl(d) values ('test2');INSERT 0 1Time: 9,002 ms Log for master: 2021-02-08 22:13:14.970 +07 [14075] postgres@postgres LOG:  starting logical decoding for slot "sub"2021-02-08 22:13:14.970 +07 [14075] postgres@postgres DETAIL:  Streaming transactions committing after 8/FD435A80, reading WAL from 8/FD436948.2021-02-08 22:13:14.970 +07 [14075] postgres@postgres LOG:  logical decoding found consistent point at 8/FD4369482021-02-08 22:13:14.970 +07 [14075] postgres@postgres DETAIL:  There are no running transactions.2021-02-08 22:13:14.970 +07 [14075] postgres@postgres ERROR:  publication "pub" does not exist2021-02-08 22:13:14.970 +07 [14075] postgres@postgres CONTEXT:  slot "sub", output plugin "pgoutput", in the change callback, associated LSN 8/FD4369E8 slave:2021-02-08 22:13:45.071 +07 [14110] LOG:  logical replication apply worker for subscription "sub" has started2021-02-08 22:13:45.078 +07 [14110] ERROR:  could not receive data from WAL stream: ERROR:  publication "pub" does not exist    CONTEXT:  slot "sub", output plugin "pgoutput", in the change callback, associated LSN 8/FD4369E82021-02-08 22:13:45.079 +07 [18374] LOG:  background worker "logical replication worker" (PID 14110) exited with exit code 1 Looks reasonable - publication pub does not exist. Ok, trying to recreate publication:[local]:5433 postgres@postgres=# create publication pub for table tbl;CREATE PUBLICATIONTime: 6,646 ms result: nothing changed, same errors appears again and again. I couldn't find how to restore replication without drop&create subscription again.  Questions here:1. what is going under the hood here - why walsender thinks that "publication "pub" does not exist" when it actually exists?2. what is the right way to restore replication in my example? Thanks! 

identifier will be truncated

[Joao Miguel Ferreira]

Hello all,

I'm running unit/integration tests on a pg based application. I recently
started getting the following indication on the test suite pg client:

NOTICE:  identifier
"y1972_1004_vat_periodic_declaration_suppliers_lines_dupl_c24_liq" will be
truncated to
"y1972_1004_vat_periodic_declaration_suppliers_lines_dupl_c24_li"

Please advise on what I should do about it or if this might be a potential
problem that I should address.

Thank you
Joao

Re: identifier will be truncated

[Adrian Klaver]

On 2/8/21 8:39 AM, Joao Miguel Ferreira wrote:

Hello all,

I'm running unit/integration tests on a pg based application. I recently 
started getting the following indication on the test suite pg client:


NOTICE:  identifier 
"y1972_1004_vat_periodic_declaration_suppliers_lines_dupl_c24_liq" will 
be truncated to 
"y1972_1004_vat_periodic_declaration_suppliers_lines_dupl_c24_li"


Please advise on what I should do about it or if this might be a 
potential problem that I should address.


https://www.postgresql.org/docs/12/sql-syntax-lexical.html

"The system uses no more than NAMEDATALEN-1 bytes of an identifier; 
longer names can be written in commands, but they will be truncated. By 
default, NAMEDATALEN is 64 so the maximum identifier length is 63 bytes. 
If this limit is problematic, it can be raised by changing the 
NAMEDATALEN constant in src/include/pg_config_manual.h."




Thank you
Joao






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: identifier will be truncated

[Paul Förster]

Hi Joao,

> On 08. Feb, 2021, at 17:39, Joao Miguel Ferreira 
>  wrote:
> 
> NOTICE:  identifier 
> "y1972_1004_vat_periodic_declaration_suppliers_lines_dupl_c24_liq" will be 
> truncated to "y1972_1004_vat_periodic_declaration_suppliers_lines_dupl_c24_li"
> 
> Please advise on what I should do about it or if this might be a potential 
> problem that I should address.

this is an indication that your object name is longer than 63 characters, which 
is a hard coded limit.

You have two options:

a) compile the PostgreSQL software from source and increase that limit (which I 
wouldn't recommend), or

b) use shorter object names (table-, view-, sequence-, column-, whatever names) 
with a maximum length of 63 characters.

Cheers,
Paul

Re: Right way to restore logical replication

[Kyotaro Horiguchi]

At Mon, 08 Feb 2021 22:42:21 +0700, Игорь Выскорко  
wrote in 
> Hi, community!
> Unfortunately can't find answer in docs and google. Hope only for you)
> [local]:5433 postgres@postgres=# drop publication pub ;
> DROP PUBLICATION
> Time: 3,793 ms
> 
> [local]:5433 postgres@postgres=# insert into tbl(d) values ('test2');
> INSERT 0 1
> Time: 9,002 ms
> 
> [local]:5433 postgres@postgres=# create publication pub for table tbl;
> CREATE PUBLICATION
> Time: 6,646 ms
> 
> result: nothing changed, same errors appears again and again. I couldn't find
> how to restore replication without drop&create subscription again.

If you recreated the publication before the insert, replication would
continue.

> Questions here:
> 1. what is going under the hood here - why walsender thinks that "publication
> "pub" does not exist" when it actually exists?

The answer is "because the publication did not exist at the time of
the INSERT".  Thus the insert cannot be replicated using the new
publication.

It is because logical replication tries to find publications using the
same snapshot with the WAL record to be sent. Although it is the
designed behavior, I'm not sure that is true also for pg_publication.

> 2. what is the right way to restore replication in my example?

The most conservative way is just to drop the subscription then delete
all rows from the subscriber table then recreate the
subscription. This allows the newly created publication to work.

Also you can drop the subscription, then manually fix the subscriber
table to sync with the publisher table, then create a new subscription
using WITH (copy_data = false);

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: Right way to restore logical replication

[Игорь Выскорко]

Thanks for reply!It makes sense now why that happened and what to do in case of emergency 09.02.2021, 10:01, "Kyotaro Horiguchi" :At Mon, 08 Feb 2021 22:42:21 +0700, Игорь Выскорко  wrote in Hi, community! Unfortunately can't find answer in docs and google. Hope only for you) [local]:5433 postgres@postgres=# drop publication pub ; DROP PUBLICATION Time: 3,793 ms  [local]:5433 postgres@postgres=# insert into tbl(d) values ('test2'); INSERT 0 1 Time: 9,002 ms  [local]:5433 postgres@postgres=# create publication pub for table tbl; CREATE PUBLICATION Time: 6,646 ms  result: nothing changed, same errors appears again and again. I couldn't find how to restore replication without drop&create subscription again.If you recreated the publication before the insert, replication wouldcontinue.  Questions here: 1. what is going under the hood here - why walsender thinks that "publication "pub" does not exist" when it actually exists?The answer is "because the publication did not exist at the time ofthe INSERT". Thus the insert cannot be replicated using the newpublication.It is because logical replication tries to find publications using thesame snapshot with the WAL record to be sent. Although it is thedesigned behavior, I'm not sure that is true also for pg_publication.  2. what is the right way to restore replication in my example?The most conservative way is just to drop the subscription then deleteall rows from the subscriber table then recreate thesubscription. This allows the newly created publication to work.Also you can drop the subscription, then manually fix the subscribertable to sync with the publisher table, then create a new subscriptionusing WITH (copy_data = false);regards. --Kyotaro HoriguchiNTT Open Source Software Center