pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
setting up streaming replication
8+ messages / 5 participants
[nested] [flat]

* setting up streaming replication
@ 2023-10-23 23:16  Brad White <b55white@gmail.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Brad White @ 2023-10-23 23:16 UTC (permalink / raw)
  To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: setting up streaming replication
@ 2023-10-24 01:28  Ron <ronljohnsonjr@gmail.com>
  parent: Brad White <b55white@gmail.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Ron @ 2023-10-24 01:28 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: setting up streaming replication
@ 2023-10-24 13:14  b55white <b55white@gmail.com>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: b55white @ 2023-10-24 13:14 UTC (permalink / raw)
  To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

 
 
>  
> 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. 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. 

 
 

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: setting up streaming replication
@ 2023-10-24 13:36  David G. Johnston <david.g.johnston@gmail.com>
  parent: Brad White <b55white@gmail.com>
  1 sibling, 0 replies; 8+ messages in thread

From: David G. Johnston @ 2023-10-24 13:36 UTC (permalink / raw)
  To: Brad White <b55white@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: setting up streaming replication
@ 2023-10-24 13:52  Ron <ronljohnsonjr@gmail.com>
  parent: b55white <b55white@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Ron @ 2023-10-24 13:52 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: setting up streaming replication
@ 2023-10-24 18:31  Brad White <b55white@gmail.com>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Brad White @ 2023-10-24 18:31 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: setting up streaming replication
@ 2023-10-24 18:37  Christophe Pettus <xof@thebuild.com>
  parent: Brad White <b55white@gmail.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Christophe Pettus @ 2023-10-24 18:37 UTC (permalink / raw)
  To: Brad White <b55white@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org



> 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).




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: setting up streaming replication
@ 2023-10-24 19:59  Ron <ronljohnsonjr@gmail.com>
  parent: Brad White <b55white@gmail.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Ron @ 2023-10-24 19:59 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2023-10-24 19:59 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-23 23:16 setting up streaming replication Brad White <b55white@gmail.com>
2023-10-24 01:28 ` Ron <ronljohnsonjr@gmail.com>
2023-10-24 13:14   ` b55white <b55white@gmail.com>
2023-10-24 13:52     ` Ron <ronljohnsonjr@gmail.com>
2023-10-24 18:31       ` Brad White <b55white@gmail.com>
2023-10-24 18:37         ` Christophe Pettus <xof@thebuild.com>
2023-10-24 19:59         ` Ron <ronljohnsonjr@gmail.com>
2023-10-24 13:36 ` David G. Johnston <david.g.johnston@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox