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