[pgpool-general: 9407] Re: Question about backend_application_name

[Tatsuo Ishii]

> On Wed, Mar 26, 2025 at 9:43 PM Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> > On Wed, Mar 26, 2025 at 7:56 PM Tatsuo Ishii <ishii at postgresql.org>
>> wrote:
>> >
>> >> > PgPool 4.5.4
>> >> >
>> >> > Three node pgpool cluster:
>> >> > Node0 = Postgresql primary
>> >> > Node1 = Postgresql streaming replica
>> >> > Node2 = PgPool leader, where the VIP sits.
>> >> >
>> >> >
>> >>
>> https://www.pgpool.net/docs/45/en/html/runtime-config-backend-settings.html
>> >> >
>> >> > The docs consistently refer to setting backend_application_nameX to
>> >> > "serverX", so that's what I did:
>> >>
>> >> "serverX" is just an example. You can use arbitrary name for
>> >> backend_application_nameX as long as:
>> >>
>> >> a) It matches with the application name set in the primary_conninfo in
>> >> postgresql.conf.
>> >>
>> >
>> > On the replica (aka Node 1), there is no primary_coninfo in either
>> > postgresql.conf or postgresql.auto.conf.
>>
>> Maybe you are using older version of PostgreSQL (11 or earlier)? Then
>> you will have recovery.conf instead.
>>
> 
> Nope.  PG 16.8.
> 
> This is all I need to do on Node 1 to get replication running from Node 0.
> 
> rm -r $PGDATA
> pg_basebackup \
>             --pgdata=$PGDATA \
>             --dbname=service=basebackup \
>             --verbose --progress \
>             --checkpoint=fast \
>             --write-recovery-conf \
>             --wal-method=stream \
>             --create-slot --slot=pgstandby1 \
>             --compress=server-zstd
> pg_ctl start -wt999
> 
> pg_basebackup appends a conninfo string to $PGDATA/postgresql.auto.conf.

Can you share the conninfo appended by pg_basebackup?

> Should I move that to postgresql.conf and then add an
> application_name="mumble" clause before the "pg_ctl start"?
> 
> 
>> > pg_stat_wal_receiver.conninfo has this section:
>> > fallback_application_name="walreceiver".
>> >
>> > (I initiate replication using "pg_basebackup --wal-method=stream
>> > --create-slot --slot=foo.)
>> >
>> >
>> >>
>> >> b) No same application names are used in all primary_conninfo.
>> >>
>> >> > But should it really be set to "walreceiver"?  Should both
>> >> > backend_application_name0 and backend_application_name1 be set to
>> >> > "walreceiver" even though it's only running on Node1?
>> >>
>> >> I never tried it but probably yes. However I recommend in general to
>> >> set unique application names for each
>> >> backend_application_nameX. Someday you might want to add more replica.
>> >>
>> >
>> > How does the second replica know to set it's name different from the
>> first?
>>
>> I do not understand your question. Maybe I should have explained the
>> background:
>>
>> The application name uniqueness is necessary requirement for streaming
>> replication check process to show pool_status's replication_state and
>> replication_sync_state columns. The process sends "SELECT
>> pg_stat_replication" to the primary PostgreSQL to know the synchronous
>> state. In this query the application name is used to distinguish each
>> standby's information. So if same application names are used, the
>> states will not be correctly shown.
> 
> 
>> >> > cdsmultip=# pgpool show backend_application_name2;
>> >> >  backend_application_name2
>> >> > ---------------------------
>> >> >
>> >> > (1 row)
>> >>
>> >> What's this? You said you only have one replica.
>> >>
>> >>
>> > You can successfully run "pgpool show backend_application_name2;" even
>> > though only _name0 and _name1 are defined in pgpool.conf.
>> >
>> > I ran the command just for completeness.
>>
>> Oh, I didn't know that! "pgpool show" should errors out in this case.
>>
>> Best reagards,
>> --
>> Tatsuo Ishii
>> SRA OSS K.K.
>> English: http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>>
> 
> 
> -- 
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!