Обсуждение: Hot standby from Debian to Windows

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

Hot standby from Debian to Windows

От
"Andrus"
Дата:
Hi!

Postgres 12 server is running on Debian 10 and has number of databases.

How to mirror changes of those databases to Windows 10 workstation which 
runs also Postgres 12.

Changes in server databases in Debian should sent to Postgres 12 database in 
Windows over internet.

If Debian server goes down, users can change server address to Windows 
computer as temporary workaround and continue working.

Andrus. 




Re: Hot standby from Debian to Windows

От
Adrian Klaver
Дата:
On 3/29/20 5:35 AM, Andrus wrote:
> Hi!
> 
> Postgres 12 server is running on Debian 10 and has number of databases.
> 
> How to mirror changes of those databases to Windows 10 workstation which 
> runs also Postgres 12.
> 
> Changes in server databases in Debian should sent to Postgres 12 
> database in Windows over internet.
> 
> If Debian server goes down, users can change server address to Windows 
> computer as temporary workaround and continue working.

Since you are moving between different OSes you will need to use some 
form of logical replication as binary replication will not work. Given 
that you are Postgres 12 you could use the builtin logical replication:

https://www.postgresql.org/docs/12/logical-replication.html

How that needs to managed is going to need more information. As a start:

1) Downtime allowed?

2) All databases to be replicated or just some?

3) Permissible lag between servers?

4) How are you going to deal with the down server and how do you plan on 
bringing it up again?


> 
> Andrus.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Hot standby from Debian to Windows

От
"Andrus"
Дата:
Hi!

>Since you are moving between different OSes you will need to use some form 
>of logical replication as binary replication will not work.

I can use Hyper-V or something other to run Debian with Windows.

This hopefully will also allow to bypass Windows 20 connection limit so that 
more than 20 users can connect.

>Given  that you are Postgres 12 you could use the builtin logical 
>replication:
>https://www.postgresql.org/docs/12/logical-replication.html

I see possibilities:

1. Use Hyper-V to run Debian container and Postgres binary replication
2. Use Hyper-V to run Debian container and Postgres logical replication
3. Use Windows and Postgres logical replication.
4. Use Hyper-V to run Debian container and backup utility  for whole disk 
block level backup

>How that needs to managed is going to need more information. As a start:
>1) Downtime allowed?

Yes. If main server stops, I can tell users to enter backup server address 
instead of main server.

>2) All databases to be replicated or just some?

I can create separate cluster so that all databases and users will be 
replicated.
There are 30 databases with total size 70 GB. Size will increase slowly 
every day when new data is entered.
There are some test and demo databases whose replcation is not really 
required but those can also replicated if this

>3) Permissible lag between servers?

Currently backups are created every night and restored in new server.
Backup of 67GB data takes 1 hour, transfer 1 hour, restore and analyze to 
new server 4 hours. Total 6 hours. So current lag in 6 .. 24 hours.

Goal is to decrease this lag.

>4) How are you going to deal with the down server and how do you plan on 
>bringing it up again?

VPS hosting company will bring it up again. I will then manually synchronize 
two clusters when users continue to enter data, this is not time critical.

Andrus. 




Re: Hot standby from Debian to Windows

От
Adrian Klaver
Дата:
On 3/29/20 12:52 PM, Andrus wrote:
> Hi!
> 
>> Since you are moving between different OSes you will need to use some 
>> form of logical replication as binary replication will not work.
> 
> I can use Hyper-V or something other to run Debian with Windows.
> 
> This hopefully will also allow to bypass Windows 20 connection limit so 
> that more than 20 users can connect.
> 
>> Given  that you are Postgres 12 you could use the builtin logical 
>> replication:
>> https://www.postgresql.org/docs/12/logical-replication.html
> 
> I see possibilities:
> 
> 1. Use Hyper-V to run Debian container and Postgres binary replication
> 2. Use Hyper-V to run Debian container and Postgres logical replication
> 3. Use Windows and Postgres logical replication.
> 4. Use Hyper-V to run Debian container and backup utility  for whole 
> disk block level backup
> 
>> How that needs to managed is going to need more information. As a start:
>> 1) Downtime allowed?
> 
> Yes. If main server stops, I can tell users to enter backup server 
> address instead of main server.
> 
>> 2) All databases to be replicated or just some?
> 
> I can create separate cluster so that all databases and users will be 
> replicated.
> There are 30 databases with total size 70 GB. Size will increase slowly 
> every day when new data is entered.
> There are some test and demo databases whose replcation is not really 
> required but those can also replicated if this
> 
>> 3) Permissible lag between servers?
> 
> Currently backups are created every night and restored in new server.
> Backup of 67GB data takes 1 hour, transfer 1 hour, restore and analyze 
> to new server 4 hours. Total 6 hours. So current lag in 6 .. 24 hours.
> 
> Goal is to decrease this lag.
> 
>> 4) How are you going to deal with the down server and how do you plan 
>> on bringing it up again?
> 
> VPS hosting company will bring it up again. I will then manually 
> synchronize two clusters when users continue to enter data, this is not 
> time critical.

Would it not be easier to just set up another Debian server, run binary 
replication and put them behind something like pgpool?

> 
> Andrus.


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Hot standby from Debian to Windows

От
"Andrus"
Дата:
Hi!

>Would it not be easier to just set up another Debian server, run binary 
>replication

Breaks occurs rarely, 0-2 times per year.
I want try it first.

> and put them behind something like pgpool?

Backup server will use single core and minimal RAM. It may be needed 0-2 
times per year.

pgpool should switch to use it for production work only if main server does 
not respond.
I havent found this feature in pgpool documentation (maybe missed).

Andrus. 




Re: Hot standby from Debian to Windows

От
Adrian Klaver
Дата:
On 3/29/20 11:35 PM, Andrus wrote:
> Hi!
> 
>> Would it not be easier to just set up another Debian server, run 
>> binary replication
> 
> Breaks occurs rarely, 0-2 times per year.
> I want try it first.

Got it. Just thought it would be easier not to have to deal with cross 
OS issues.

> 
>> and put them behind something like pgpool?
> 
> Backup server will use single core and minimal RAM. It may be needed 0-2 
> times per year.
> 
> pgpool should switch to use it for production work only if main server 
> does not respond.
> I havent found this feature in pgpool documentation (maybe missed).

Here is one example:

https://www.pgpool.net/docs/latest/en/html/example-watchdog.html

> 
> Andrus.


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Hot standby from Debian to Windows

От
"Andrus"
Дата:
Hi!

>Got it. Just thought it would be easier not to have to deal with cross OS 
>issues.
>Here is one example:
>https://www.pgpool.net/docs/latest/en/html/example-watchdog.html

Hopefully Windows Hyper-V virtual network adapter will not check windows 20 
connection limit.
In this case using Debian+Hyper-V+ binary replication allows to connect more 
than 20 users and may be best solution.

Both have 64-bit OS. Will binary replication work in this case.

Andrus. 




Re: Hot standby from Debian to Windows

От
Adrian Klaver
Дата:
On 3/30/20 11:06 AM, Andrus wrote:
> Hi!
> 
>> Got it. Just thought it would be easier not to have to deal with cross 
>> OS issues.
>> Here is one example:
>> https://www.pgpool.net/docs/latest/en/html/example-watchdog.html
> 
> Hopefully Windows Hyper-V virtual network adapter will not check windows 
> 20 connection limit.
> In this case using Debian+Hyper-V+ binary replication allows to connect 
> more than 20 users and may be best solution.

That is something I would verify. It would surprise me if MS would allow 
you to turn a desktop OS(Windows 10) into a server OS.

> 
> Both have 64-bit OS. Will binary replication work in this case.

Take a look at:

https://www.postgresql.org/docs/12/warm-standby.html#STANDBY-PLANNING

It is not specifically ruled out, nor is it is explicitly ruled in. 
Myself, I would not bet on it being stable.

> 
> Andrus.


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Hot standby from Debian to Windows

От
Adrian Klaver
Дата:
On 3/30/20 11:36 AM, Adrian Klaver wrote:
> On 3/30/20 11:06 AM, Andrus wrote:
>> Hi!
>>
>>> Got it. Just thought it would be easier not to have to deal with 
>>> cross OS issues.
>>> Here is one example:
>>> https://www.pgpool.net/docs/latest/en/html/example-watchdog.html
>>
>> Hopefully Windows Hyper-V virtual network adapter will not check 
>> windows 20 connection limit.
>> In this case using Debian+Hyper-V+ binary replication allows to 
>> connect more than 20 users and may be best solution.
> 
> That is something I would verify. It would surprise me if MS would allow 
> you to turn a desktop OS(Windows 10) into a server OS.
> 
>>
>> Both have 64-bit OS. Will binary replication work in this case.
> 
> Take a look at:
> 
> https://www.postgresql.org/docs/12/warm-standby.html#STANDBY-PLANNING
> 
> It is not specifically ruled out, nor is it is explicitly ruled in. 
> Myself, I would not bet on it being stable.

Clarification. The above was based on running Postgres under Windows 
itself. My mind had not updated to the Postgres on Debian in Hyper-V 
plan. In that case I would not see an issue.

> 
>>
>> Andrus.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com