Setting up replication on Windows, v9.4

From: Brad White <b55white(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Setting up replication on Windows, v9.4
Date: 2022-11-04 22:59:33
Message-ID: CAA_1=93vc6szcOe-F4W7FwAZkXCrC2-ODY6GV7C8CtQfbg0V0Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Larry Sevilla 2022-11-04 23:14:49 Is there a guide to use PostgresSQL as alternative to MariaDB/MySQL in OpenStack?
Previous Message shashidhar Reddy 2022-11-04 10:08:21 Re: Unable to use pg_verify_checksums