Re: setting up streaming replication

On 10/24/23 08:14, b55white wrote:
>> On Oct 23, 2023 at 8:30 PM, Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>>
>> On 10/23/23 18:16, Brad White wrote:
>> > I'm stumped.
>> >
>> > Using this command to set up the slave and replication on PG v 15:
>> > "C:\Program Files\PostgreSQL\15\bin\pg_basebackup" -h 192.168.1.126 -U
>> > pgrep_user -p 5433 -D "C:\Program Files\PostgreSQL\15\data" -Fp -Xs -R
>> >
>> > If I have PG running on the remote server and the data directory is
>> > intact, then I get an error, Data exists and is not empty.
>> >
>> > If I shut down PG, delete data\*, restart PG, then it fails to start
>> > because the conf files are missing.
>> >
>> > If I leave PG shut down and run pg_basebackup, it times out and asks if pg
>> > is running and listening on the port. Of course it is not.
>> >
>> > I see lots of posts on how to use pg_basebackup, but apparently this isn't
>> > interesting because no one mentions whether pg should be running on
>> > the remote server.
>>
>> No, it should not.
>>
>> This is what works for me, in Linux on the standby system, where "buddy" and
>> "basebackup" are references in .pg_service.conf to the primary server:
>> PGHOST=...
>> pg_ctl status && pg_ctl stop -mfast
>> psql service=buddy -Xac "select pg_drop_replication_slot(slot_name)
>>                          from pg_replication_slots
>>                          where slot_name = 'pgstandby1';"
>> /usr/bin/rm -r $PGDATA/*
>> pg_basebackup --dbname=service=basebackup -D $PGDATA --progress \
>>               --checkpoint=fast -v \
>>               --write-recovery-conf --wal-method=stream \
>>               --create-slot --slot=pgstandby1 --compress=server-zstd
>> pg_ctl start -wt90 -l ${MajVer}/pgstart_standby.log
>>
>> Source:
>> https://www.tecmint.com/configure-postgresql-streaming-replication-in-centos-8/
>>

> It sounds like you are saying that pg_basebackup should be run on the backup server, not on the primary.

Correct.

> But a detail that important would be mentioned in the documentation. Especially since it is directly compared to pg_dump, which I only run on the primary.

pg_dump can be run anywhere that can get to the pg server via port 5432 (and
there's enough disk space).

> If you are running it from the secondary, how can you have a pg_service.conf since data is empty?

.pg_service.conf goes in $HOME, not $PGDATA.

> Is all that stuff with slots necessary since the backup will automatically create a temporary slot for replication?

I want a permanent slot so that replication automatically resumes if the
secondary must temporarily be taken down.

--
Born in Arizona, moved to Babylonia.