setting up streaming replication

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/

--
Born in Arizona, moved to Babylonia.

On Monday, October 23, 2023, Brad White <b55white@gmail.com> wrote:

I'm stumped.

Using this command to set up the slave and replication on PG v 15:

You must, by some means:

Have PostgreSQL binaries installed on the machine that is to become the
replica.
Have the output of pg_basebackup present on the replica; removing the
cluster that is initially installed and replacing it with the backup tends
to be easiest.

The means by which you run pg_basebackup aren’t all that interesting since
there is this thing called copy file.

The output of pg_basebackup is the data directory for the cluster so of
course the cluster cannot be running until you’ve put the contents into
place and ensured the proper configuration and added the standby.signal
file. Admittedly, if you choose to destroy an existing cluster in order to
install the new one in its location of course you’ll need to shut down that
existing cluster before destroying it. But this later point isn’t going to
be documented because it isn’t an inherent task but rather one you choose
as a convenience due to using a packager that handles most of cluster
configuration for you and a lack of desire to configure the replica cluster
from scratch.

David J.

On 10/24/23 08:14, b55white wrote:

On Oct 23, 2023 at 8:30 PM, Ron <ronljohnsonjr@gmail.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.

On Tue, Oct 24, 2023 at 8:56 AM Ron <ronljohnsonjr@gmail.com> wrote:

On 10/24/23 08:14, b55white wrote:

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.

--

Wait.

Are you saying that once I get streaming replication set up, it quits
working when I reboot the servers once a week?

Thanks,
Brad.

On Oct 24, 2023, at 11:31, Brad White <b55white@gmail.com> wrote:
Are you saying that once I get streaming replication set up, it quits working when I reboot the servers once a week?

Not unless the downtime is sufficiently long that the replica can't find the WAL information it needs. You can avoid this with PostgreSQL settings, but you might also consider keeping a WAL archive in a cloud storage system like S3 (it can be combined with backups, using a tool like pgBackRest).

On 10/24/23 13:31, Brad White wrote:

On Tue, Oct 24, 2023 at 8:56 AM Ron <ronljohnsonjr@gmail.com> wrote:

On 10/24/23 08:14, b55white wrote:

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.

--

Wait.

Are you saying that once I get streaming replication set up, it quits
working when I reboot the servers once a week?

Maybe with temporary slots, but definitely not with permanent slots.

I just stopped a replicated pair, and started them up again:

Secondary> pg_ctl stop -mfast
Primary> pg_ctl stop -mfast
Primary> pg_ctl start -w
Secondary> pg_ctl start -w
Primary> psql -Xc "select * from pg_stat_replication;"
Secondary> psql -Xc "select * from pg_stat_wal_receiver;"

--
Born in Arizona, moved to Babylonia.