Re: Setting up replication on Windows, v9.4 - Mailing list pgsql-general

2022年11月5日(土) 7:59 Brad White <b55white@gmail.com>:
>
> 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
> ------

Is this the postgresql.conf applied to the standby? Just wondering
as one of the listen_addresses is the same as the host in primary_conninfo.

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

I don't know where those lines come from, they're not generated by PostgreSQL.

It is certainly true that recovery.conf *must* be in the $PGDATA directory.  The
above lines would make sense if it's expected that postgresql.conf will be
located in another location (as is usually the case with Debian/Ubuntu
packages), but unless your setup is specifying that, just put both files in
$PGDATA.

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

I'm not familiar with Windows, but it sounds like PostgreSQL is
actually running.
What happens if you try and connect to it?

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

The above state looks very much what would happen if the standby is running with
"hot_standby = off", and connection attempts are being made. Try attempting
to connect to it.

Also, check if there's an entry in pg_stat_replication on the primary.


Regards

Ian Barwick