Hi. I'm jon.404, a Unix/Linux/Database/Openstack/Kubernetes Administrator, AWS/GCP/Azure Engineer, mathematics enthusiast, and amateur philosopher. This is where I rant about that which upsets me, laugh about that which amuses me, and jabber about that which holds my interest most: *nix.
Getting serious with PostgreSQL
Posted 01-29-2009 at 10:45 PM by rocket357
Ok, so the company I work for has set a hard date for the release/launch of our new flagship product...June 1st.
This is interesting for a number of reasons. First, the 5.0 project has been in "under heavy development" status for a seriously long time now (late 2007 I joined the 5.0 team, which at that time was roughly 20% done. I later moved from that team to the reporting division, spent a year there and then moved to my current role as DBA). I figured the 5.0 project was maybe 50% done by now.
Har dee har har. The joke's on me. 5.0 entered beta recently and now I'm aware of the monumental task ahead: migrating 170+ production MSSQL databases to PostgreSQL.
Now, a straight mssql2pgsql convert would be painless in the short term, but as DBA, it's my job to take advantage of these opportunities to improve the quality of service our customers receive. First and foremost, production databases running on single machines (regardless of how good our backup strategy is) represents a single point of failure. We're contracted with our customers to provide a certain percentage of uptime, and failure to provide that results in a) angry customers, b) angry bosses, and c) loss of revenue for the company. I'm not a big fan of angry bosses or angry customers, nor am I a fan of losing money for the company I work for.
So today I got to thinking and researching. First, we need a more distinct replication operation. MSSQL is fine and dandy, but replication (especially over distances) proves challenging. I'd also like to start clustering our database machines such that failure of one doesn't result in a complete outage for a customer.
A bit of googling later, I've read up on tons of PostgreSQL replication and clustering solutions, and I believe slony or pgpool-II is where I'll start testing. My main concern here is that slony only allows for a single master machine...again, a single point of failure. That fact aside, slony looks promising. pgpool-II is something like a proxy for PostgreSQL that allows updates to propagate to numerous slave machines...again, looks promising.
So I get to thinking hard about Slony and the single point of failure master. How can I make 2 machines act like one (one passive, one active) until a failure occurs in the active master (causing the passive master to take over)? Seems OpenBSD solved that problem in terms of firewalls...CARP/pfsync.
After tossing some ideas around with our Cisco guru (I actually was inquiring about the availability of networking hardware, but it turned into a brainstorm session), I think as long as we can maintain a gigabit+ link for the "pfsync" interconnect (over which active replication will take place to sync the master machines), it may very well be possible to pull this off. The missing link is, of course, synchronizing the slony masters. This will take a bit of coding, I think...more research is needed.
As for pgpool's proxy capabilities, I do believe I'll test that out as well. I like the idea that pgpool can pool connections (we tend to tunnel application login info over a site-specific login, meaning many of our sites could operate over fewer database connections than are currently necessary). I also like the idea that SELECT statements can be round-robin'd to slaves with a "weight" given to slaves to increase traffic to more powerful machines. The problem I see here is (again) the single point of failure: the proxy.
I'll post again as I narrow down the list.
This is interesting for a number of reasons. First, the 5.0 project has been in "under heavy development" status for a seriously long time now (late 2007 I joined the 5.0 team, which at that time was roughly 20% done. I later moved from that team to the reporting division, spent a year there and then moved to my current role as DBA). I figured the 5.0 project was maybe 50% done by now.
Har dee har har. The joke's on me. 5.0 entered beta recently and now I'm aware of the monumental task ahead: migrating 170+ production MSSQL databases to PostgreSQL.
Now, a straight mssql2pgsql convert would be painless in the short term, but as DBA, it's my job to take advantage of these opportunities to improve the quality of service our customers receive. First and foremost, production databases running on single machines (regardless of how good our backup strategy is) represents a single point of failure. We're contracted with our customers to provide a certain percentage of uptime, and failure to provide that results in a) angry customers, b) angry bosses, and c) loss of revenue for the company. I'm not a big fan of angry bosses or angry customers, nor am I a fan of losing money for the company I work for.
So today I got to thinking and researching. First, we need a more distinct replication operation. MSSQL is fine and dandy, but replication (especially over distances) proves challenging. I'd also like to start clustering our database machines such that failure of one doesn't result in a complete outage for a customer.
A bit of googling later, I've read up on tons of PostgreSQL replication and clustering solutions, and I believe slony or pgpool-II is where I'll start testing. My main concern here is that slony only allows for a single master machine...again, a single point of failure. That fact aside, slony looks promising. pgpool-II is something like a proxy for PostgreSQL that allows updates to propagate to numerous slave machines...again, looks promising.
So I get to thinking hard about Slony and the single point of failure master. How can I make 2 machines act like one (one passive, one active) until a failure occurs in the active master (causing the passive master to take over)? Seems OpenBSD solved that problem in terms of firewalls...CARP/pfsync.
After tossing some ideas around with our Cisco guru (I actually was inquiring about the availability of networking hardware, but it turned into a brainstorm session), I think as long as we can maintain a gigabit+ link for the "pfsync" interconnect (over which active replication will take place to sync the master machines), it may very well be possible to pull this off. The missing link is, of course, synchronizing the slony masters. This will take a bit of coding, I think...more research is needed.
As for pgpool's proxy capabilities, I do believe I'll test that out as well. I like the idea that pgpool can pool connections (we tend to tunnel application login info over a site-specific login, meaning many of our sites could operate over fewer database connections than are currently necessary). I also like the idea that SELECT statements can be round-robin'd to slaves with a "weight" given to slaves to increase traffic to more powerful machines. The problem I see here is (again) the single point of failure: the proxy.
I'll post again as I narrow down the list.
Total Comments 0
