On Wed, Jan 10, 2024 at 23:22 Ron Johnson
wrote:
>> On Wed, Jan 10, 2024 at 5:51 PM Keaney, Will
>> wrote:
>> Hello,
>>
>> I'm building a new 2-node Postgreql 13 streaming replication cluster. I'm
>> able to clone the primary to the standby using pg_basebackup.
>> However, the standby is unable to authenticate to the primary to begin
>> recovery during startup. It logs an error, "FATAL: could not connect to the
>> primary server: fe_sendauth: no password supplied".
>>
>> I've tried using both .pgpass and passing the password explicitly in the
>> myrecovery.conf primary_conninfo string. Debug logs on the primary show the
>> initial connection. but no user provided and no authentication attempt.
>>
>> pg_hba.conf on the primary:
>> hostall,replication replprimary-database-server
>> scram-sha-256
>> hostall,replication replstandby-database-server
>> scram-sha-256
>>
>> myrecovery.conf on the standby:
>> primary_conninfo = 'host=primary-database-server port=5432 user=repl
>> application_name=standby-server-name'
>> recovery_target_timeline = 'latest'
>> primary_slot_name = 'standby_replication_slot'
>>
>> .pgpass on the standby:
>> # hostname:port:database:username:password
>> *:*:replication:repl:repl_user_password
>>
>> I've triple-checked that the password in .pgpass matches the password set
>> for the repl user in the database, and the repl user has REPLICATION access.
>> I'm able to connect to the primary server using the repl user and the psql
>> client, both via .pgpass and providing the password directly.
>> I can't figure out why the standby postgres server is skipping the provided
>> credentials when connecting to the primary.
>>
> Let pg_basebackup do all the work for you:
>
> $ cat ~postgres/.pg_service.conf
> [basebackup]
> host=
> port=5432
> user=replicator
> passfile=/var/lib/pgsql/.pgpass
>
> $ pg_basebackup \
> --pgdata=$PGDATA \
> --dbname=service=basebackup \
> --verbose --progress \
> --checkpoint=fast \
> --write-recovery-conf \
> --wal-method=stream \
> --create-slot --slot=pgstandby1
Thank you! That's both extremely useful and helped me identify the source of my
problem.
A previous experiment with repmgr had left a stale primary_conninfo string in
postgresql.auto.conf, which was somehow interfering with my other recovery
settings.
After cleaning that up, my standby can connect to my primary without issue.
Best,
Will
This e-mail message, including any attachments, is for the sole use of the
intended recipient(s) and may contain information that is confidential and
protected by law from unauthorized disclosure. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply e-mail and destroy all copies of
the original message.
