Обсуждение: setting up streaming replication, part 2

Поиск
Список
Период
Сортировка

setting up streaming replication, part 2

От
Brad White
Дата:
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

От
Ron
Дата:
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

От
Brad White
Дата:

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

От
Ron
Дата:
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

От
Brad White
Дата:
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

От
Guillaume Lelarge
Дата:
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.