Re: speed up a logical replica setup

On Wed, Jan 31, 2024 at 9:52 AM Hayato Kuroda (Fujitsu) <
kuroda(dot)hayato(at)fujitsu(dot)com> wrote:
>
> Dear Euler,
>
> I extracted some review comments which may require many efforts. I hope
this makes them
> easy to review.
>
> 0001: not changed from yours.
> 0002: avoid to use replication connections. Source: comment #3[1]
> 0003: Remove -P option and use primary_conninfo instead. Source: [2]
> 0004: Exit earlier when dry_run is specified. Source: [3]
> 0005: Refactor data structures. Source: [4]
>
> [1]:
https://www.postgresql.org/message-id/TY3PR01MB9889593399165B9A04106741F5662%40TY3PR01MB9889.jpnprd01.prod.outlook.com
> [2]:
https://www.postgresql.org/message-id/TY3PR01MB98897C85700C6DF942D2D0A3F5792%40TY3PR01MB9889.jpnprd01.prod.outlook.com
> [3]:
https://www.postgresql.org/message-id/TY3PR01MB98897C85700C6DF942D2D0A3F5792%40TY3PR01MB9889.jpnprd01.prod.outlook.com
> [4]:
https://www.postgresql.org/message-id/TY3PR01MB9889C362FF76102C88FA1C29F56F2%40TY3PR01MB9889.jpnprd01.prod.outlook.com
>

Hey folks,

Jumping into this a bit late here... I'm trying a simple
pg_createsubscriber but getting an error:

~/pgsql took 19s
✦ ➜ pg_createsubscriber -d fabrizio -r -D /tmp/replica5434 -S 'host=/tmp
port=5434'
pg_createsubscriber: error: could not create subscription
"pg_createsubscriber_16384_695617" on database "fabrizio": ERROR: syntax
error at or near "/"
LINE 1: ..._16384_695617 CONNECTION 'user=fabrizio passfile='/home/fabr...
^
pg_createsubscriber: error: could not drop replication slot
"pg_createsubscriber_16384_695617" on database "fabrizio":
pg_createsubscriber: error: could not drop replication slot
"pg_subscriber_695617_startpoint" on database "fabrizio": ERROR:
replication slot "pg_subscriber_695617_startpoint" does not exist

And the LOG contains the following:

~/pgsql took 12s
✦ ➜ cat
/tmp/replica5434/pg_createsubscriber_output.d/server_start_20240131T110318.730.log

2024-01-31 11:03:19.138 -03 [695632] LOG: starting PostgreSQL 17devel on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0,
64-bit
2024-01-31 11:03:19.138 -03 [695632] LOG: listening on IPv6 address "::1",
port 5434
2024-01-31 11:03:19.138 -03 [695632] LOG: listening on IPv4 address
"127.0.0.1", port 5434
2024-01-31 11:03:19.158 -03 [695632] LOG: listening on Unix socket
"/tmp/.s.PGSQL.5434"
2024-01-31 11:03:19.179 -03 [695645] LOG: database system was shut down in
recovery at 2024-01-31 11:03:18 -03
2024-01-31 11:03:19.180 -03 [695645] LOG: entering standby mode
2024-01-31 11:03:19.192 -03 [695645] LOG: redo starts at 0/4000028
2024-01-31 11:03:19.198 -03 [695645] LOG: consistent recovery state
reached at 0/504DB08
2024-01-31 11:03:19.198 -03 [695645] LOG: invalid record length at
0/504DB08: expected at least 24, got 0
2024-01-31 11:03:19.198 -03 [695632] LOG: database system is ready to
accept read-only connections
2024-01-31 11:03:19.215 -03 [695646] LOG: started streaming WAL from
primary at 0/5000000 on timeline 1
2024-01-31 11:03:29.587 -03 [695645] LOG: recovery stopping after WAL
location (LSN) "0/504F260"
2024-01-31 11:03:29.587 -03 [695645] LOG: redo done at 0/504F260 system
usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 10.39 s
2024-01-31 11:03:29.587 -03 [695645] LOG: last completed transaction was
at log time 2024-01-31 11:03:18.761544-03
2024-01-31 11:03:29.587 -03 [695646] FATAL: terminating walreceiver
process due to administrator command
2024-01-31 11:03:29.598 -03 [695645] LOG: selected new timeline ID: 2
2024-01-31 11:03:29.680 -03 [695645] LOG: archive recovery complete
2024-01-31 11:03:29.690 -03 [695643] LOG: checkpoint starting:
end-of-recovery immediate wait
2024-01-31 11:03:29.795 -03 [695643] LOG: checkpoint complete: wrote 51
buffers (0.3%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.021 s,
sync=0.034 s, total=0.115 s; sync files=17, longest=0.011 s, average=0.002
s; distance=16700 kB, estimate=16700 kB; lsn=0/504F298, redo lsn=0/504F298
2024-01-31 11:03:29.805 -03 [695632] LOG: database system is ready to
accept connections
2024-01-31 11:03:30.332 -03 [695658] ERROR: syntax error at or near "/" at
character 90
2024-01-31 11:03:30.332 -03 [695658] STATEMENT: CREATE SUBSCRIPTION
pg_createsubscriber_16384_695617 CONNECTION 'user=fabrizio
passfile='/home/fabrizio/.pgpass' channel_binding=prefer host=localhost
port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1
ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres
gssdelegation=0 target_session_attrs=any load_balance_hosts=disable
dbname=fabrizio' PUBLICATION pg_createsubscriber_16384 WITH (create_slot =
false, copy_data = false, enabled = false)

Seems we need to escape connection params similar we do in dblink [1]

Regards,

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/dblink/dblink.c;h=19a362526d21dff5d8b1cdc68b15afebe7d40249;hb=HEAD#l2882

--
Fabrízio de Royes Mello