setting up streaming replication, part 2

On 10/24/23 19:29, Brad White wrote:

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.

It doesn't, because streaming replication continuous streams WAL records.

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'

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

But you said that streaming is working.

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

Thanks for all the help so far,
Brad.

--
Born in Arizona, moved to Babylonia.

On Tue, Oct 24, 2023, 9:02 PM Ron <ronljohnsonjr@gmail.com> wrote:

On 10/24/23 19:29, Brad White wrote:

I have the replication server set up and the streaming is working.
The latest data show up, as desired.

3 minor issues.

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

But you said that streaming is working.

Yes, I realized after I posted that this would be confusing.

I cheated and changed hba from password to trust until I get this resolved.
Not a long term solution.

Having replication working was more important in the short term.

On 10/24/23 23:47, Brad White wrote:

On Tue, Oct 24, 2023, 9:02 PM Ron <ronljohnsonjr@gmail.com> wrote:

On 10/24/23 19:29, Brad White wrote:

I have the replication server set up and the streaming is working.
The latest data show up, as desired.

3 minor issues.

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

But you said that streaming is working.

Yes,  I realized after I posted that this would be confusing.

I cheated and changed hba from password to trust until I get this
resolved. Not a long term solution.

Having replication working was more important in the short term.

Follow the tecmint link in my original email.
/messages/by-id/7a9570a6-07a9-4b41-b419-b4a0aa67fc53@gmail.com

It worked perfectly for me.

--
Born in Arizona, moved to Babylonia.

From: Ron <ronljohnsonjr@gmail.com>
Sent: Wednesday, October 25, 2023 4:35:59 AM
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: setting up streaming replication, part 2

On 10/24/23 23:47, Brad White wrote:

On Tue, Oct 24, 2023, 9:02 PM Ron <ronljohnsonjr@gmail.com<mailto:ronljohnsonjr@gmail.com>> wrote:
On 10/24/23 19:29, Brad White wrote:

I have the replication server set up and the streaming is working.
The latest data show up, as desired.

3 minor issues.

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

But you said that streaming is working.

Yes, I realized after I posted that this would be confusing.

I cheated and changed hba from password to trust until I get this resolved. Not a long term solution.

Having replication working was more important in the short term.

Follow the tecmint link in my original email. /messages/by-id/7a9570a6-07a9-4b41-b419-b4a0aa67fc53@gmail.com

It worked perfectly for me.

Looks like they have double single quotes around the password.

Hi,

Le mer. 25 oct. 2023 à 02:29, Brad White <b55white@gmail.com> 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.