Re: setting up streaming replication, part 2

[Guillaume Lelarge <guillaume@xxxxxxxxxxxx>]

Hi,

Le mer. 25 oct. 2023 à 02:29, Brad White <b55white@xxxxxxxxx> a écrit :

I have the replication server set up and the streaming is working.

The latest data show up, as desired.

3 minor issues.

1) I also have the WAL files being copied to a common location from the primary server with 

      archive_command = 'copy %p "\\\\DISKSTATION\\AccessData\\Prod\\WALfiles\\%f"' 

It's not clear to me how the replication server finds those files.

The secondary finds those files if you set up the restore_command GUC on it.

 

I also have the cleanup set to go, but it doesn't seem to be cleaning up any of the archived files.

    archive_cleanup_command = 'pg_archivecleanup \\\\DISKSTATION\\AccessData\\Prod\\WALfiles %r'

This needs to be set up on the secondary. The primary won't care about that setup.

 

2) I have the connection string set for the replication server to connect,

     primary_conninfo = 'host = 192.168.1.112 port = 5433 user = {pg_rep_user }password = {password}'

But the log file shows

    LOG:  waiting for WAL to become available at 2/A10000B8
    FATAL:  could not connect to the primary server: connection to server at "{IP}", port 5433 failed: fe_sendauth: no password supplied

 

You need to set up the .pgpass file (on Unix) or pgpass.conf (on Windows). Seems quite complex to figure out where to put that file on Windows.

3) When I run

    "C:\Program Files\PostgreSQL\15\bin\pg_ctl.exe" promote -D "C:\Program Files\PostgreSQL\15\data"

on the backup to promote it to Primary, I get

    pg_ctl: could not send promote signal (PID: 3996): Operation not permitted

I guess you need to be the postgres user to execute that. On Linux, I would use sudo. Don't know for WIndows.

--

Guillaume.