Home > mailing lists

Thread: setting up streaming replication, part 2

setting up streaming replication, part 2

From

Brad White

Date:

25 October 2023, 03:29:33

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.

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

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.

Re: setting up streaming replication, part 2

From

Ron

Date:

25 October 2023, 05:02:12

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.

Re: setting up streaming replication, part 2

From

Brad White

Date:

25 October 2023, 07:47:10

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.

Re: setting up streaming replication, part 2

From

Ron

Date:

25 October 2023, 12:35:46

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. https://www.postgresql.org/message-id/7a9570a6-07a9-4b41-b419-b4a0aa67fc53%40gmail.com

It worked perfectly for me.

--
Born in Arizona, moved to Babylonia.

Re: setting up streaming replication, part 2

From

Brad White

Date:

25 October 2023, 18:57:51

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> 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. https://www.postgresql.org/message-id/7a9570a6-07a9-4b41-b419-b4a0aa67fc53%40gmail.com

It worked perfectly for me.

Looks like they have double single quotes around the password.

Re: setting up streaming replication, part 2

From

Guillaume Lelarge

Date:

26 October 2023, 03:22:48

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.