Thread: setting up streaming replication
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. Just that the data dir must be empty.
I know this works because I've done it before. But I don't see the trick I'm missing.
Thanks,
Brad.
Brad.
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 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. 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.If you are running it from the secondary, how can you have a pg_service.conf since data is empty?Is all that stuff with slots necessary since the backup will automatically create a temporary slot for replication?Thanks,Brad.
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:
Correct.
pg_dump can be run anywhere that can get to the pg server via port 5432 (and there's enough disk space).
.pg_service.conf goes in $HOME, not $PGDATA.
I want a permanent slot so that replication automatically resumes if the secondary must temporarily be taken down.
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.
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.
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 thiswith PostgreSQL settings, but you might also consider keeping a WAL archive in a cloud storage system like S3 (it canbe combined with backups, using a tool like pgBackRest).
On 10/24/23 13:31, Brad White wrote:
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;"
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.
Born in Arizona, moved to Babylonia.