Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qvHGV-004JSh-A9 for pgsql-general@arkaria.postgresql.org; Tue, 24 Oct 2023 13:16:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qvHFV-007Sns-5b for pgsql-general@arkaria.postgresql.org; Tue, 24 Oct 2023 13:15:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qvHFU-007SnE-NE for pgsql-general@lists.postgresql.org; Tue, 24 Oct 2023 13:15:09 +0000 Received: from mail-io1-xd2c.google.com ([2607:f8b0:4864:20::d2c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qvHFN-002SQt-UT for pgsql-general@lists.postgresql.org; Tue, 24 Oct 2023 13:15:08 +0000 Received: by mail-io1-xd2c.google.com with SMTP id ca18e2360f4ac-7a69a1b51baso95913739f.1 for ; Tue, 24 Oct 2023 06:15:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1698153300; x=1698758100; darn=lists.postgresql.org; h=mime-version:subject:references:in-reply-to:message-id:to:from:date :from:to:cc:subject:date:message-id:reply-to; bh=Sh8O3PKIp9jhx47ydwAmCTu2POvC8Xb0s7Dx7h8S1V8=; b=MHK8phwN8UrxDeuNP8v++4QncJzY87b33JayR5b8m1X6/KgtbM33K7JViX451tPHyY G+7HdJAj4PGINe/oPEiualARg1lg4M1LMCDPQj1ie11vB5QAwTjtes4LoeB7Cniwn+bI KALejA+e8aWtfwNOdo+cm/rNy4usJVSMKJpGFODqGQb2BpNZ1twOYTbNoJfw6wmMXM2x eYlF5JMiV7KHwrnw6fFGpLLdODnxvA8I3aP0pDHrucnby4a8IbJphpFFSlG0iJ6x0K14 jjlfP+oye2/qzniEI/1/CURWlkakrMir0klxStUsMO/28J8kv+vTlv6W+MsKDOOKR0p5 UObA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1698153300; x=1698758100; h=mime-version:subject:references:in-reply-to:message-id:to:from:date :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Sh8O3PKIp9jhx47ydwAmCTu2POvC8Xb0s7Dx7h8S1V8=; b=kcgkp+5F7mWWrQ3IAK3NIY9dRfsj7678QAiEC7EFJiZdGBfOXKBnE9QZgAAqFS0AHM tf8zaENWClyxiUuaqDKLZIOppKBIye+7OoJvtgnILnZP5kZZMilpyMhMYc+ZupW2LsUU lCg9iq44gpdIgbHgXe5gF6oyoC1Ik5ukcoxy9Zo9EFlO1yNx/hDauY/ScbVrAvwfKeR9 1KsffhMDbhFk0e0/i25tJNqyrpv1UpdXQzEVpJrIGyGLJwCTxH2SbpIIw6R8jUY+F9SU 8fNkq+mrnEUohSrGLqdrogxwsd8HL+yXafMbghRyF03OllhUCibdIFTSMG48WfBjmdZb wI4A== X-Gm-Message-State: AOJu0Yxvv4nk+5gzJUZUCYZ2YzRBHPTAh/wJdRQPpgn40zVUdd5Zcf6U LOUmjH/iY+4Sv0hy9bHcnNK39bTg7Hg= X-Google-Smtp-Source: AGHT+IG4Tr6XT9Q2EQrKnv3N+RnOrCo3705JO06cmRseylbJhblOdUQdnozy2flOeJy0bJN8l7y8ug== X-Received: by 2002:a6b:7108:0:b0:7a6:7b18:dbf3 with SMTP id q8-20020a6b7108000000b007a67b18dbf3mr15214910iog.7.1698153299826; Tue, 24 Oct 2023 06:14:59 -0700 (PDT) Received: from email.client.edison.tech ([2607:fb91:7e6:4a69:41fc:8f60:8d1c:fb69]) by smtp.gmail.com with ESMTPSA id s21-20020a02cf35000000b0042b1a9f8480sm2793472jar.35.2023.10.24.06.14.58 for (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 24 Oct 2023 06:14:59 -0700 (PDT) Date: Tue, 24 Oct 2023 08:14:58 -0500 From: b55white To: "pgsql-generallists.postgresql.org" Message-ID: <9d194507-1d73-4222-a7e5-fa9417bfd69f@edison> In-Reply-To: <7a9570a6-07a9-4b41-b419-b4a0aa67fc53@gmail.com> References: <7a9570a6-07a9-4b41-b419-b4a0aa67fc53@gmail.com> Subject: Re: setting up streaming replication MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="EdoMail6537c352_41a5b669_6ce0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --EdoMail6537c352_41a5b669_6ce0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline > > On Oct 23, 2023 at 8:30 PM, Ron 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. --EdoMail6537c352_41a5b669_6ce0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
On Oct 23, = 2023 at 8:30 PM, Ron <ronljohnsonjr=40gmail.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:
> =22C:=5CP= rogram =46iles=5CPostgreSQL=5C15=5Cbin=5Cpg=5Fbasebackup=22 -h 192.168.1.126 -U
> pgrep=5Fuser -p 5433 -D= =22C:=5CProgram =46iles=5CPostgreSQL=5C15=5Cdata=22 -=46p -Xs -R
>=
> If I have PG running on the remote server and the data dire= ctory is
> intact, then I get an error, Data exists and is not emp= ty.
>
> If I shut down PG, delete data=5C*, restart PG, then = it fails to start
> because the conf files are missing.
>> If I leave PG shut down and run pg=5Fbasebackup, it times out and a= sks if pg
> is running and listening on the port. Of course it is = not.
>
> I see lots of posts on how to use pg=5Fbaseback= up, but apparently this isn't
> interesting because no o= ne mentions whether pg should be running on
> the remote serv= er.

No, it should not.

This is what works for me, in Linux = on the standby system, where =22buddy=22 and
=22basebackup=22 are ref= erences in .pg=5Fservice.conf to the primary server:
PGHOST=3D...
p= g=5Fctl status && pg=5Fctl stop -mfast
psql service=3Dbuddy -X= ac =22select pg=5Fdrop=5Freplication=5Fslot(slot=5Fname)
  =             &= nbsp;          from pg=5Frep= lication=5Fslots
        &nbs= p;            = ;    where slot=5Fname =3D 'pgstandby1';=22
/usr/bin/rm= -r =24PGDATA/*
pg=5Fbasebackup --dbname=3Dservice=3Dbasebackup -D =24= PGDATA --progress =5C
        = ;      --checkpoint=3Dfast -v =5C
 &nbs= p;            --write-r= ecovery-conf --wal-method=3Dstream =5C
     =          --create-slot --slot=3Dpgstan= dby1 --compress=3Dserver-zstd
pg=5Fctl start -wt90 -l =24=7BMajVer=7D/= pgstart=5Fstandby.log

Source:
https://w= ww.tecmint.com/configure-postgresql-streaming-replication-in-centos-8/

It sounds like you are saying that pg=5Fbasebackup sh=
ould 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=5Fdump, which I only run on the primary. 
If you are running it from the secondary, how can you have a pg=5F=
service.conf since data is empty=3F
Is all that stuff with slot=
s necessary since the backup will automatically create a temporary slot f=
or replication=3F

Thanks,  
Brad. 
--EdoMail6537c352_41a5b669_6ce0--