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

[Ron Johnson]

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.

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!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250326/dbb44d95/attachment.htm>