On 10/24/23 08:14, b55white 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.