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 1qvHpr-004LRF-Cj for pgsql-general@arkaria.postgresql.org; Tue, 24 Oct 2023 13:52:43 +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 1qvHpp-0081rf-Bl for pgsql-general@arkaria.postgresql.org; Tue, 24 Oct 2023 13:52:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qvHpo-0081qq-Rx for pgsql-general@lists.postgresql.org; Tue, 24 Oct 2023 13:52:41 +0000 Received: from mail-yw1-x1129.google.com ([2607:f8b0:4864:20::1129]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qvHpm-0024xA-In for pgsql-general@lists.postgresql.org; Tue, 24 Oct 2023 13:52:40 +0000 Received: by mail-yw1-x1129.google.com with SMTP id 00721157ae682-5a8ada42c2aso45071767b3.3 for ; Tue, 24 Oct 2023 06:52:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1698155557; x=1698760357; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=DZSOpeTmkHukWPm8jQiyqXMveYLlw/ymEJVVfouLSWo=; b=JsvVA2cwbJiyiounBEmKyhJwhIE99jg4UhL526M/315CRv6YUJC87Wsgmc5cFZY2y5 3lUbAFVpH49TPOb/5dFP7G1C0487cav04POJ6iEwayTkVES8u5W+pgTS+k5pWKNKqizo 56hXbWgnZIvHCv7jZmmOVCOExm2l3ZUDiSlb4ILtv/ME6mcHZePPu/lvYxXhsFzZAzx9 2YeLxWJB1RBJzH6ElzVyCyMXIYxqYG0wBtscCotehM1fvB9Ee68HyuZ1nRtSeF7deOFs iHbEyye1NLD0nzwb2njgzWczU6JjO970rHWY/f7668d8W/cqH8hoxI134UNzlf0E0v1i 55Yg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1698155557; x=1698760357; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=DZSOpeTmkHukWPm8jQiyqXMveYLlw/ymEJVVfouLSWo=; b=gB9DXW9DgfMcKga8CGbDfImcUxupQOUXN4LSUKDRV/obJ4+Q8gEbQaM+2dliBFJwor hfesbbIxvQmtJgnyXwCG2GsqAOe78GAH7CZc6xoomCZVclPQWF7k+uD+tsSV1b28P04c nctKgvM3yWI9Ke0/NXYWtiKiz5ZrbSteWuBr1oq5Ol1ppmyMI9kTIsbskwK4J7dOhjv8 2iWbJ81jcbolRJGORhdLmcFvK+UAFSaAujHi4QFMOMY4TTlmn7Vs1mZOi6eX5ticiFeN TP4diQnP3XAXn+AGUO57E+tdqDtHJYDZ0C483YHjCTVmGJPyGiCpWyONAjISeYBcdlSn L+Ng== X-Gm-Message-State: AOJu0YzdhAIIXXVu/DbDOUAKLVe13G2mxegT8jgUWBNq8UQmS9DOEGMT RUYPSWLeKN5X4X96gJbMsLDFYKgtgJs= X-Google-Smtp-Source: AGHT+IE+2ncI3IoOZIs9DWObO303OlWtn6oP8MinxjS0wcycYGloOgX8U1D0+jPOnN9rnivbQJuzgg== X-Received: by 2002:a05:690c:16:b0:5a7:b973:db3c with SMTP id bc22-20020a05690c001600b005a7b973db3cmr13205926ywb.34.1698155557369; Tue, 24 Oct 2023 06:52:37 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:990b:551:8028:180e? ([2600:1700:bd2c:b810:990b:551:8028:180e]) by smtp.googlemail.com with ESMTPSA id h127-20020a816c85000000b005a7bf9749c8sm4093386ywc.4.2023.10.24.06.52.36 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 24 Oct 2023 06:52:37 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------MHcJ4CQap1IicYHVk0QdDgVh" Message-ID: <08345d16-a49a-43e1-8dfd-b8bc9b04b314@gmail.com> Date: Tue, 24 Oct 2023 08:52:35 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: setting up streaming replication Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <7a9570a6-07a9-4b41-b419-b4a0aa67fc53@gmail.com> <9d194507-1d73-4222-a7e5-fa9417bfd69f@edison> From: Ron In-Reply-To: <9d194507-1d73-4222-a7e5-fa9417bfd69f@edison> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------MHcJ4CQap1IicYHVk0QdDgVh Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10/24/23 08:14, b55white wrote: >> 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. 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. --------------MHcJ4CQap1IicYHVk0QdDgVh Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------MHcJ4CQap1IicYHVk0QdDgVh--