pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Setting up replication on Windows, v9.4
Date: Sun, 13 Nov 2022 14:54:54 -0600
Message-ID: <d1196cdf-7a90-e587-8808-7b07f8035050@gmail.com> (raw)
In-Reply-To: <CAA_1=93vc6szcOe-F4W7FwAZkXCrC2-ODY6GV7C8CtQfbg0V0Q@mail.gmail.com>
References: <CAA_1=93vc6szcOe-F4W7FwAZkXCrC2-ODY6GV7C8CtQfbg0V0Q@mail.gmail.com>

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.

view thread (12+ messages)

Message-ID: <d1196cdf-7a90-e587-8808-7b07f8035050@gmail.com>
Permalink:  ../d1196cdf-7a90-e587-8808-7b07f8035050@gmail.com/
Also on:    postgresql.org/message-id/d1196cdf-7a90-e587-8808-7b07f8035050@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: Setting up replication on Windows, v9.4
  In-Reply-To: <d1196cdf-7a90-e587-8808-7b07f8035050@gmail.com>
  References: <CAA_1=93vc6szcOe-F4W7FwAZkXCrC2-ODY6GV7C8CtQfbg0V0Q@mail.gmail.com> <d1196cdf-7a90-e587-8808-7b07f8035050@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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