Database replication for High Availability Standby system
Linux - GeneralThis Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
Database replication for High Availability Standby system
Hi all,
I am making a standby High Availibility system with 2 nodes using Pacemaker, Corosync and PCS. The resource is an Apache webserver with SQL database storing dynamic values from a program. This program is running simultaneously on both the PCs and populating their respective databases.
Now, I have to set up a failover mechanism for database. For example, if the main fails and standby takes over, and the main recovers after that, how will the SQL database of main will synchronise with that of standby at bootup.
There are several things to consider that might lead to different but still "working" solution while looking at your problem.
From a HA perspective, you have your apache resource that is handled by pacemaker, so I guess that you also have a filesystem resource for the folder /var/www.
So, you can also have shared filesystem (gluster or clvm) for your database data (normally /var/lib/pgsql if you are using PostgreSQL).
Or you can look at the native mechanism of your database engine.
All recent and decent database engines should have that.
There are several things to consider that might lead to different but still "working" solution while looking at your problem.
From a HA perspective, you have your apache resource that is handled by pacemaker, so I guess that you also have a filesystem resource for the folder /var/www.
So, you can also have shared filesystem (gluster or clvm) for your database data (normally /var/lib/pgsql if you are using PostgreSQL).
Or you can look at the native mechanism of your database engine.
All recent and decent database engines should have that.
Thanx Tshikose, but am really a noob in this matter and didnt quite get what u wanted to imply. Can u please give me some more details or references please.
Basically you have two choices for your database high availability.
You can rely on HA with pacemaker, or on the own database replication capabilities.
What is yours?
Basically you have two choices for your database high availability.
You can rely on HA with pacemaker, or on the own database replication capabilities.
What is yours?
Which database engine are you using?
I have some experience with SQL DB so will use that sir.
While there are plans form Microsoft to port MS SQL Server to Linux, that will not happen before next year.
So, I doubt that your experience with MS SQL Server will be of much help.
At least SQL is SQL everywhere, so there are similarities, but also big differences.
My understanding was that you already have running system for which you wanted to add HA.
Now, it seems as if you are not yet there.
If you can I advise you to use Postgres as database engine.
I have never worked on postegres SQL before but if its advisable...ill shift on that.
I have successfully setuo my cluster on 2 nodes with httpd apache resource and its working fine.
Now i have to include the database part and make the webpage dynamic.
That is good.
Try to build a Postgres resource similar to httpd with a shared folder /var/lib/pgsql.
I have never done that (just because I never came in a situation where I needed to build a HA DB), so I am just sharing guidelines.
I have the same application running in 2 PCs - one main and one standby - and this application is updating the SQL DB and same is queried by the client via a webpage.
The solution given in the documentation replicates the main DB into standby DB at all times whereas my requirement is of replication only once when the main system recovers from a failover and need to replicate data from standby for the time it was down.
Is there a solution for this kind of scenario sir?
Database replication generally involves huge data.
So, trying to start a replication when a failover occcurs is not pratical. The replication time will just take long enough, keeping the service unavailable, and then defeating the goal of HA.
You need to permanently replicate the data between your two database instances. Like that when a failover occurs, just few not yet replicated data need to be shipped across.
Googling a little for "Linux Postgres replication" gave me this.
Database replication generally involves huge data.
So, trying to start a replication when a failover occcurs is not pratical. The replication time will just take long enough, keeping the service unavailable, and then defeating the goal of HA.
You need to permanently replicate the data between your two database instances. Like that when a failover occurs, just few not yet replicated data need to be shipped across.
Googling a little for "Linux Postgres replication" gave me this.
Will this take care of my HA needs too?
I mean, with this implemented, if my main system goes down then the client will be automatically redirected to standby database or do i still need to implement HA using pacemaker and corosync sir?
Yes, I think that you can have a pgsql resource managed by your HA pacemaker.
And if the database information normally located in /var/lib/pgsql/data iis well synced by the Postgres replication, then the pgsql resource should take over where the previous left.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.