Re: speed up a logical replica setup - Mailing list pgsql-hackers
| From | Fabrízio de Royes Mello |
|---|---|
| Subject | Re: speed up a logical replica setup |
| Date | |
| Msg-id | CAFcNs+pPtw+y7Be00BK0MBpHhLk2s66tLM286g=k5rew8kUxjg@mail.gmail.com Whole thread Raw |
| In response to | RE: speed up a logical replica setup ("Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>) |
| Responses |
Re: speed up a logical replica setup
|
| List | pgsql-hackers |
On Wed, Jan 31, 2024 at 9:52 AM Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.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
✦ ➜ 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)
✦ ➜ 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,
--
Fabrízio de Royes Mello
pgsql-hackers by date: