Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ouK0P-00063R-Ob for pgsql-general@arkaria.postgresql.org; Sun, 13 Nov 2022 20:55:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ouK0O-0003jL-I0 for pgsql-general@arkaria.postgresql.org; Sun, 13 Nov 2022 20:55:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ouK0O-0003jB-1b for pgsql-general@lists.postgresql.org; Sun, 13 Nov 2022 20:55:04 +0000 Received: from mail-ot1-x32d.google.com ([2607:f8b0:4864:20::32d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ouK0H-0004BR-GP for pgsql-general@lists.postgresql.org; Sun, 13 Nov 2022 20:55:03 +0000 Received: by mail-ot1-x32d.google.com with SMTP id db10-20020a0568306b0a00b0066d43e80118so5520968otb.1 for ; Sun, 13 Nov 2022 12:54:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; 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=qCATpcyyk3t4mE1HxKjT4H5a/ZiK80umITf6b01lMd8=; b=eDde/GmOTRqy9T+LoIhiuYXEM24otgV+ZNrI3V/O14kTmPXYBxQNlBczOhVtX4P1es 06j/rJOI4Fd3sscmuoAWGiQaKpkGABDMDDE5BBXRmBRJyTzNg5fr+XArX4g3IYccwsjO LM0inc0Ie6g0P7C1Q8D36WXsxk72pWglNru01h1T6Y40oyfWZpCBoXKHwMf7sTgFMxYF ZqAxXsaPncdanXE6RYfzZSWHBvA7fPIl4eEFxKWgfwhOYEwxOVput0WNuxckbqJVOZ2C x+Uj6kR7B6sScaeuAGV0/7Y7EQledYNRPjlwYDE6Xg6UZhYJO+X+3zMhJp8Rrj2Wn5pf tyuQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; 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=qCATpcyyk3t4mE1HxKjT4H5a/ZiK80umITf6b01lMd8=; b=IFJZ7UASmh19Ie9xf7bjZW++3+mh7zdilVu/ffq/9xZooGqKwP8J6ZCI2YVdpVC74v wbH6W3QwICoW2spPRpHkSu6eatzGlWcj9ax+RtlBgbGQBMytjq+F/ITk35uUNhZGVktz H6hMf4Cv9o8E+lxxGpReajFJg2IRgC8DLK9nb2CpOLBP3RZHihzkNLFMl8WYMjH5GMkQ eC1ezMUB7IBH8lRGewKhp5MDhWjf4P82OS/zitiXMu/cbivjQxhI26jT9zo6GM5zEjrx 07PIdfv8YNSOYTSFoS18bK6Db9O6W/HlYgXJMD6CugEJSzT39RNHPODPsdap+KFNMKho MW2A== X-Gm-Message-State: ANoB5pkS6IBguTa6arRgO0Tn0Mn04awjWsPTYpY5/eOPDRzDBl9J800q KqnTlZRavmL233fcPVpjx7e8FFt+VZ8= X-Google-Smtp-Source: AA0mqf4kWcW0kqfeTZ717TzJqw0aBUNyywS6azcSB+Y0hEvxwptQsbBQiiLXi6Zqba+A9qutsWGeTg== X-Received: by 2002:a05:6830:1d41:b0:66c:56e1:ecf6 with SMTP id p1-20020a0568301d4100b0066c56e1ecf6mr5200456oth.319.1668372896119; Sun, 13 Nov 2022 12:54:56 -0800 (PST) Received: from [192.168.88.10] (ip68-230-234-148.no.no.cox.net. [68.230.234.148]) by smtp.googlemail.com with ESMTPSA id j17-20020a056808057100b00353ef11d6c9sm2940386oig.19.2022.11.13.12.54.55 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 13 Nov 2022 12:54:55 -0800 (PST) Content-Type: multipart/alternative; boundary="------------w7kSmJgibTULlhAKqY0JWoEc" Message-ID: Date: Sun, 13 Nov 2022 14:54:54 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Setting up replication on Windows, v9.4 Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: 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. --------------w7kSmJgibTULlhAKqY0JWoEc Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Note that WAL replication replicates *the whole instance* not just specific databases.  You need logical replication for that. Also, I just learned that 9.4 *does* have WAL replication slots (which makes replication *much* easier).  v9.6 is where replication is "like rolling off a log" simple. On 11/4/22 17:59, Brad White wrote: > I'm setting up a backup for our primary postgres server using the archived > WAL files. > Then I'll try to upgrade it to Streaming Replication. > Then I'll upgrade the system to v.latest. > For now, we are on v.9.4. > > I do a base backup from the primary to a directory on the NAS. > >       "C:\Program Files\PostgreSQL\9.4\bin\pg_basebackup.exe" -D > \\diskstation\AccessData\Dev\Backup -P -X s -v -h 192.168.1.118 -p 5432 -U > postgres > > That appears to go fine. > Then I delete data\*.* and copy everything except the config files from > the backup into data. > > Copy in recovery.conf > ------ >     standby_mode          = 'on' >     primary_conninfo      = 'host=192.168.1.118 port=5432 user=replication > password=**********' >     restore_command = 'copy "\\\\DISKSTATION\\AccessData\\WALfiles\\%f" "%p"' > ------ > Copy in postgresql.conf, with settings > ------ > listen_addresses = '127.0.0.1,192.168.1.118' > wal_level = archive > hot_standby = on > ------ > Interestingly, the recovery file says > # Note that recovery.conf must be in $PGDATA directory. > # It should NOT be located in the same directory as postgresql.conf > Those seem contradictory. > And if I remove the postgresql, it just refuses to start. > > With all this in place, I start the service, it runs for a bit, then shuts > down. > No errors in the event log. > 5 postgres processes are left running along with a pid file. > > The log file says > ------ > LOG:  database system was interrupted while in recovery at log time > 2022-11-04 13:17:28 PDT > HINT:  If this has occurred more than once some data might be corrupted > and you might need to choose an earlier recovery target. > FATAL:  the database system is starting up > FATAL:  the database system is starting up > FATAL:  the database system is starting up > LOG:  entering standby mode > LOG:  consistent recovery state reached at 6A/35000090 > LOG:  record with zero length at 6A/35000090 > LOG:  started streaming WAL from primary at 6A/35000000 on timeline 1 > LOG:  redo starts at 6A/35000090 > FATAL:  the database system is starting up > FATAL:  the database system is starting up > FATAL:  the database system is starting up > .... > FATAL:  the database system is starting up > FATAL:  the database system is starting up > > So it seems that the backup didn't work as well as first appeared. -- Angular momentum makes the world go 'round. --------------w7kSmJgibTULlhAKqY0JWoEc Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Note that WAL replication replicates the whole instance not just specific databases.  You need logical replication for that.

Also, I just learned that 9.4 does have WAL replication slots (which makes replication much easier).  v9.6 is where replication is "like rolling off a log" simple.

On 11/4/22 17:59, Brad White wrote:
I'm setting up a backup for our primary postgres server using the archived WAL files.
Then I'll try to upgrade it to Streaming Replication.
Then I'll upgrade the system to v.latest.
For now, we are on v.9.4.

I do a base backup from the primary to a directory on the NAS.

      "C:\Program Files\PostgreSQL\9.4\bin\pg_basebackup.exe" -D \\diskstation\AccessData\Dev\Backup -P -X s -v -h 192.168.1.118 -p 5432 -U postgres

That appears to go fine.
Then I delete data\*.* and copy everything except the config files from the backup into data.

Copy in recovery.conf
------
    standby_mode          = 'on'
    primary_conninfo      = 'host=192.168.1.118 port=5432 user=replication password=**********'
    restore_command = 'copy "\\\\DISKSTATION\\AccessData\\WALfiles\\%f" "%p"'
------
Copy in postgresql.conf, with settings
------
listen_addresses = '127.0.0.1,192.168.1.118'
wal_level = archive
hot_standby = on
------
Interestingly, the recovery file says 
# Note that recovery.conf must be in $PGDATA directory.
# It should NOT be located in the same directory as postgresql.conf
Those seem contradictory.
And if I remove the postgresql, it just refuses to start.

With all this in place, I start the service, it runs for a bit, then shuts down.
No errors in the event log.
5 postgres processes are left running along with a pid file.

The log file says
------
LOG:  database system was interrupted while in recovery at log time 2022-11-04 13:17:28 PDT
HINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
FATAL:  the database system is starting up
FATAL:  the database system is starting up
FATAL:  the database system is starting up
LOG:  entering standby mode
LOG:  consistent recovery state reached at 6A/35000090
LOG:  record with zero length at 6A/35000090
LOG:  started streaming WAL from primary at 6A/35000000 on timeline 1
LOG:  redo starts at 6A/35000090
FATAL:  the database system is starting up
FATAL:  the database system is starting up
FATAL:  the database system is starting up
....
FATAL:  the database system is starting up
FATAL:  the database system is starting up

So it seems that the backup didn't work as well as first appeared.

--
Angular momentum makes the world go 'round.
--------------w7kSmJgibTULlhAKqY0JWoEc--