On Wed, Jan 10, 2024 at 5:51 PM Keaney, Will <will.kea...@kroger.com> 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: > host all,replication repl primary-database-server > scram-sha-256 > host all,replication repl standby-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=<primary-server> 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