HA best pratices with postgreSQL

From: "Albretch Mueller" <lbrtchx(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: HA best pratices with postgreSQL
Date: 2008-06-19 00:44:17
Message-ID: 9ef66fac0806181744l649980femb53f823a41508ad2@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

~
I am developing a J2EE application that needs for users to only read
DB tables. All queries are select ones, no updates, no inserts, no
deletes for web users, so I keep this ro DB tables in certain
partitions which I mount as ro
~
For performance reasons I keet the DB in the same box as the server
~
Now, the data in those tables need to actually be updated not by web
users, but from the back end and not that often at all, say just once
of twice a day in a totally controlled way and updates shouldn't take
long. Just some insert stats in single tables
~
I had in mind:
~
1) always keeping two partitions in synch, with exactly the same data
~
2) once I need to update the data (and while users still access partition1):
~
2.1) rw mount the standby partition2
~
2.2) start another instance of PG running on another port
~
2.3) update the data on partition2
~
2.4) unmount partition2
~
2.5) mount partition2 as ro
~
2.6) let all processes know to redirect all new requests to the new
instance running on the new port
~
2.7) wait for all web users with previously open and ongoing sessions
accessing partition1 to phase out (in order to make sure that there
are not going to be any inconsistencies duw to the new data) and then
~
2.8) unmount partition1
~
2.9) mount partition1 as rw
~
2.10) update partition1 (at this time no user should be accessing it
anymore) with the data on partition2
~
3) unmount partition1 (and possibly even physically unpower the hard
drives themselves)
~
partition1 becomes partition2 for the next update cycle
~
Is this safe generally speaking?
~
Has any of you guys heard of something like that or how to basically
achieve the same thing by other, more standard means?
~
thanks
lbrtchx

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Craig Ringer 2008-06-19 00:56:20 Re: Understanding fsync
Previous Message Jeff Davis 2008-06-19 00:39:59 pg_locks "at-a-glance" view