Re: Query regarding managing Replication

[Ashok Patil]

Hello,

I have one query regarding client/server authentication using certificate
in postgres.

I am able to establish client server connection with OpenSSL certificate
creation.

We can create server and root certificates using OpenSSL and keep them in
data directory.
We need to update field in postgresql.conf (example ssl = ON, ssl_cert_file
= "Server.crt", ssl_key_file = "Server.key", also for client we need to
create certificate and key and need to update it in user directory.

My query is, instead of using a physical certificate from a
particular directory, can we use a certificate from the Windows certificate
store for both server and client? Server will refer to the installed
certificate from the server windows certificate store and the client will
use the client windows certificate store.

Please let me know if such a thing we can use. if yes , please share some
references.

Thanks and Regards,
Ashok

On Mon, Jul 10, 2023 at 1:32 PM Julien Rouhaud  wrote:

> On Mon, Jul 10, 2023 at 3:41 PM Ashok Patil  wrote:
>
>> Hello Julien,
>>
>> I need some more help regarding replication.
>>
>> Where Primary server can switch to Standby server role and stand by
>> server can switch to Primary server role.
>> [image: image.png]
>> In our case we maintain two servers Server1 and Server2.. Server1 will be
>> active and Server2 will be passive.
>> In case when Server1 is crashed Server2 will be active in result it
>> starts Keyclock instance and keyclock will connect with Standby postgres
>> server.
>>
>> As per current replication steps, we found that Standby server will
>> always be in read only mode. we want to switch it to r/w mode and primary
>> to read only mode.
>>
>> or even it is ok both primary and stand by will always be in read-write
>> mode. but both should replicate data.
>>
>> I searched so many option on internet but did not found exact one.. Will
>> you please propose me some good solutions..
>>
>> Proper steps should be appreciable..
>>
>
> I don't know what KeyClock is, but I'm assuming that what you're looking
> for is a High Availability solution, not how to setup replication, as
> postgres itself only provides way to perform specific actions (promote a
> standby to primary, build a new standby...) and third-party tools adds the
> necessary logic for something more fancy.  For general overview or how to
> perform a failover you can look at the documentation at
> https://www.postgresql.org/docs/current/high-availability.html.  If
> you're looking for a HA solution, I would recommend looking into Patroni:
> https://patroni.readthedocs.io/en/latest/
>

Re: Query regarding managing Replication

[Julien Rouhaud]

On Mon, Jul 10, 2023 at 3:41 PM Ashok Patil  wrote:

> Hello Julien,
>
> I need some more help regarding replication.
>
> Where Primary server can switch to Standby server role and stand by server
> can switch to Primary server role.
> [image: image.png]
> In our case we maintain two servers Server1 and Server2.. Server1 will be
> active and Server2 will be passive.
> In case when Server1 is crashed Server2 will be active in result it starts
> Keyclock instance and keyclock will connect with Standby postgres server.
>
> As per current replication steps, we found that Standby server will always
> be in read only mode. we want to switch it to r/w mode and primary to read
> only mode.
>
> or even it is ok both primary and stand by will always be in read-write
> mode. but both should replicate data.
>
> I searched so many option on internet but did not found exact one.. Will
> you please propose me some good solutions..
>
> Proper steps should be appreciable..
>

I don't know what KeyClock is, but I'm assuming that what you're looking
for is a High Availability solution, not how to setup replication, as
postgres itself only provides way to perform specific actions (promote a
standby to primary, build a new standby...) and third-party tools adds the
necessary logic for something more fancy.  For general overview or how to
perform a failover you can look at the documentation at
https://www.postgresql.org/docs/current/high-availability.html.  If you're
looking for a HA solution, I would recommend looking into Patroni:
https://patroni.readthedocs.io/en/latest/

Re: Query regarding managing Replication

[Ashok Patil]

Hello Julien,

I need some more help regarding replication.

Where Primary server can switch to Standby server role and stand by server
can switch to Primary server role.
[image: image.png]
In our case we maintain two servers Server1 and Server2.. Server1 will be
active and Server2 will be passive.
In case when Server1 is crashed Server2 will be active in result it starts
Keyclock instance and keyclock will connect with Standby postgres server.

As per current replication steps, we found that Standby server will always
be in read only mode. we want to switch it to r/w mode and primary to read
only mode.

or even it is ok both primary and stand by will always be in read-write
mode. but both should replicate data.

I searched so many option on internet but did not found exact one.. Will
you please propose me some good solutions..

Proper steps should be appreciable..

Regards,
Ashok




On Mon, Jul 3, 2023 at 3:06 PM Julien Rouhaud  wrote:

> Hi,
>
> On Mon, Jul 03, 2023 at 02:07:54PM +0530, Ashok Patil wrote:
> >
> > I am able to perform replication of a single machine where primary and
> > stand-by are on same localhost..
> > I think you are right.. it might be blocking the backup process by
> > firewall..
>
> Agreed
>
> > Just one signal question: is multi-master replication possible on
> Postgres?
> > I searched on google.. but did not find a proper solution..
>
> This is not possible with vanilla postgres.  There are some commercial
> products
> / postgres forks that implement it, but I've never used them so I can't
> comment
> on how well they work nor how much they cost.
>

Re: Query regarding managing Replication

[Julien Rouhaud]

Hi,

On Mon, Jul 03, 2023 at 02:07:54PM +0530, Ashok Patil wrote:
>
> I am able to perform replication of a single machine where primary and
> stand-by are on same localhost..
> I think you are right.. it might be blocking the backup process by
> firewall..

Agreed

> Just one signal question: is multi-master replication possible on Postgres?
> I searched on google.. but did not find a proper solution..

This is not possible with vanilla postgres.  There are some commercial products
/ postgres forks that implement it, but I've never used them so I can't comment
on how well they work nor how much they cost.

Re: Query regarding managing Replication

[Ashok Patil]

Hello Julien,

I am able to perform replication of a single machine where primary and
stand-by are on same localhost..
I think you are right.. it might be blocking the backup process by
firewall..

Just one signal question: is multi-master replication possible on Postgres?
I searched on google.. but did not find a proper solution..

If it is possible, then can you please share a link where I can get that
information..

Thanks in advance.

Regards,
Ashok Patil

On Fri, Jun 30, 2023 at 2:02 PM Julien Rouhaud  wrote:

> Hi,
>
> On Fri, Jun 30, 2023 at 01:47:29PM +0530, Ashok Patil wrote:
> > Hello MAZIÈRE,
> >
> > I tried update by
> >
> > listen_addresses = '*'   and listen_addresses = 'server_address' but
> still
> > i am getting same error.
>
> Did you restart postgres after changing listen_addresses, and is the
> primary
> port 5432?
>
> If yes and if you still get the same connection errors, it mean that
> either the
> primary IP is wrong or something is blocking the traffic between the two
> machines (like a firewall).
>

Re: Query regarding managing Replication

[Julien Rouhaud]

Hi,

On Fri, Jun 30, 2023 at 01:47:29PM +0530, Ashok Patil wrote:
> Hello MAZIÈRE,
>
> I tried update by
>
> listen_addresses = '*'   and listen_addresses = 'server_address' but still
> i am getting same error.

Did you restart postgres after changing listen_addresses, and is the primary
port 5432?

If yes and if you still get the same connection errors, it mean that either the
primary IP is wrong or something is blocking the traffic between the two
machines (like a firewall).

Re: Query regarding managing Replication

[Ashok Patil]

Hello MAZIÈRE,

I tried update by

listen_addresses = '*'   and listen_addresses = 'server_address' but still
i am getting same error.

Searched on internet for probable solution but does not get any proper
answer.

Will you please reply me for this issue?

Regards,
Ashok


On Tue, Jun 27, 2023 at 7:39 PM Romain MAZIÈRE <
romain.mazi...@sigmaz-consilium.fr> wrote:

> Hello,
>
> You can have a look at the parameter : listen_addresses in the file
> postgresql.conf.
> By default the value is localhost.
>
> Regards
>
> Romain MAZIÈreromain.mazi...@sigmaz-consilium.fr
> +33.535.545.085
> +33.781.46.36.96https://sigmaz-consilium.fr
>
> Le 27/06/2023 à 16:04, Ashok Patil a écrit :
>
> Hello Sir/Madam,
>
> I have to perform task of database replication. For that for testing
> purpose I have installed Postgress 14 on two different machine. One is
> primary (We can say it as server) and another one is secondary (stand by).
>
> Below steps i have performed
>
> *On Primary*
>
> 1. Update Postgres.conf with below settings
> wal_level = hot_standby
> full_page_writes = on
> wal_log_hints = on
> max_wal_senders = 6
> max_replication_slots = 6
> hot_standby = on
> hot_standby_feedback = on
>
> 2. update pg_hba.conf
> host replication 172.20.32.63/32 scram-sha-256
> host replication 172.20.32.43/32 scram-sha-256
>
> 3. Create a repl_user
> psql  -d postgres   -U postgres   -c "CREATE ROLE repl_user LOGIN
> REPLICATION ENCRYPTED PASSWORD 'xxx';"
>
> 4. create the replication slot using below command
> psql -d postgres -U postgres  -c "SELECT * FROM
> pg_create_physical_replication_slot('standby1', true);"
>
> *On Standby*
>
> 5.Perform a base backup of primary to standby
> pg_ctl -D ..\data. stop -mi
>
> 6. After stopping the cluster delete the data directory
> rmdir /s ..\data
>
> 7. run pg_basebackup on the standby to copy primary’s data directory to it.
> pg_basebackup -D ..\data -Fp -R -Xs -c fast -l 'initial_clone' -P -v -h
> 172.20.32.63 y -U repl_user
>
> but here i am getting error as
> pg_basebackup: error: connection to server at "172.20.32.63", port 5432
> failed: Connection timed out (0x274C/10060)
> Is the server running on that host and accepting TCP/IP
> connections?
>
> Will you please let me know which steps is wrong.
>
> Also is there any proper steps given in any document, if yes, will you
> please share that.
>
> Thanks in advance.
>
> Regards,
> Ashok
>
>
>

Re: Query regarding managing Replication

[Romain MAZIÈRE]

Hello,

You can have a look at the parameter : listen_addresses in the file 
postgresql.conf.

By default the value is localhost.

Regards

Romain MAZIÈRE
romain.mazi...@sigmaz-consilium.fr
+33.535.545.085
+33.781.46.36.96
https://sigmaz-consilium.fr

Le 27/06/2023 à 16:04, Ashok Patil a écrit :

Hello Sir/Madam,

I have to perform task of database replication. For that for testing 
purpose I have installed Postgress 14 on two different machine. One is 
primary (We can say it as server) and another one is secondary (stand by).


Below steps i have performed

*On Primary*

1. Update Postgres.conf with below settings
wal_level = hot_standby
full_page_writes = on
wal_log_hints = on
max_wal_senders = 6
max_replication_slots = 6
hot_standby = on
hot_standby_feedback = on

2. update pg_hba.conf
host replication 172.20.32.63/32  scram-sha-256
host replication 172.20.32.43/32  scram-sha-256

3. Create a repl_user
psql  -d postgres   -U postgres   -c "CREATE ROLE repl_user LOGIN 
REPLICATION ENCRYPTED PASSWORD 'xxx';"


4. create the replication slot using below command
psql -d postgres -U postgres  -c "SELECT * FROM 
pg_create_physical_replication_slot('standby1', true);"


*On Standby*
*
*
5.Perform a base backup of primary to standby
pg_ctl -D ..\data. stop -mi

6. After stopping the cluster delete the data directory
rmdir /s ..\data

7. run pg_basebackup on the standby to copy primary’s data directory 
to it.
pg_basebackup -D ..\data -Fp -R -Xs -c fast -l 'initial_clone' -P -v 
-h 172.20.32.63 y -U repl_user


but here i am getting error as
pg_basebackup: error: connection to server at "172.20.32.63", port 
5432 failed: Connection timed out (0x274C/10060)
        Is the server running on that host and accepting TCP/IP 
connections?


Will you please let me know which steps is wrong.

Also is there any proper steps given in any document, if yes, will you 
please share that.


Thanks in advance.

Regards,
Ashok

Query regarding managing Replication

[Ashok Patil]

Hello Sir/Madam,

I have to perform task of database replication. For that for testing
purpose I have installed Postgress 14 on two different machine. One is
primary (We can say it as server) and another one is secondary (stand by).

Below steps i have performed

*On Primary*

1. Update Postgres.conf with below settings
wal_level = hot_standby
full_page_writes = on
wal_log_hints = on
max_wal_senders = 6
max_replication_slots = 6
hot_standby = on
hot_standby_feedback = on

2. update pg_hba.conf
host replication 172.20.32.63/32 scram-sha-256
host replication 172.20.32.43/32 scram-sha-256

3. Create a repl_user
psql  -d postgres   -U postgres   -c "CREATE ROLE repl_user LOGIN
REPLICATION ENCRYPTED PASSWORD 'xxx';"

4. create the replication slot using below command
psql -d postgres -U postgres  -c "SELECT * FROM
pg_create_physical_replication_slot('standby1', true);"

*On Standby*

5.Perform a base backup of primary to standby
pg_ctl -D ..\data. stop -mi

6. After stopping the cluster delete the data directory
rmdir /s ..\data

7. run pg_basebackup on the standby to copy primary’s data directory to it.
pg_basebackup -D ..\data -Fp -R -Xs -c fast -l 'initial_clone' -P -v -h
172.20.32.63 y -U repl_user

but here i am getting error as
pg_basebackup: error: connection to server at "172.20.32.63", port 5432
failed: Connection timed out (0x274C/10060)
Is the server running on that host and accepting TCP/IP connections?

Will you please let me know which steps is wrong.

Also is there any proper steps given in any document, if yes, will you
please share that.

Thanks in advance.

Regards,
Ashok