Re: lock entire database - Mailing list pgsql-novice

Scott Marlowe wrote:

>On Fri, 2004-08-06 at 12:19, Ron St-Pierre wrote:
>
>>Benjamin wrote:
>>
>>>Thanx Ron for that.
>>>I got the listing of the tables.
>>>The \d option gives u a formatter output. I just wanted a list , that
>>>i cud loop on.
>>>I guess that cud be done with a simple "SELECT tablename from
>>>pg_tables where schemaname='public' ".
>>>
>>>Now to the need to lock the database. I need to backup the database at
>>>run-time, on another machine, which is a backup for the first one. So
>>>the data has to match exactly as on the first. So till the backup
>>>faithfuly copies everything down, there shud be no changes on the main
>>>machine.
>>>
>>Postgres uses MVCC (multiversion concurrency control) which basically
>>means that each transaction takes a snapshot of the database when a user
>>performs a query (read, update, whatever). So if a pg_dump of the entire
>>database occurs within a single transaction, this would ensure that you
>>have a valid snapshot at one particular instance. *Can anyone verify if
>>this is indeed true*?
>>
>>If that is true then a pg_dump should solve your problem.
>>
>
>Yes it is true, but only for a single database in the cluster at a
>time.  If you have data in two databases in the pgsql cluster, each will
>be started at a different point in time.
>
>However, it may be that the poster is trying to do something like Point
>in Time recovery (he could just test 8.0 and see how PITR works, it
>might be a better option).  IFF he needs the two databases to be exactly
>the same, then something like a pooling connection thingie like pgpool
>will get turned on and starts writing the same updates to both databases
>at the same time, he might need to truly lock out all changes for a
>bit.  OR maybe he's wanting to backup the one server and take it
>offline, so changes made would be lost there.
>
>I think we may need a bit more explanation on just what Benjamin is
>trying to accomplish to give the right answer.
>
Ok. The scene now.
Machine A is the Primary, and Machine B is the backup for A.
When B is booting up, it has to duplicate the entire pgsql db from A.
As Ron said, cud do with a pg_dump. But, i guess, pg_dump takes quite
some time. As A is already up, it wud be unwise to lock the db for so
long. Also, even if i do go ahead with pg_dump, and then do a pg_restore
on B, by the time data is being pg_restore'ed on B, a query cud modify/
update the db on A.
My idea was to lock the db on A, scp the required files onto B and then
unlock db on A.

Is the picture clear now?


--

Benjamin Jacob.

Disclaimer :
------------------------------------------------------------------------------
If you are not the intended recipient of this transmission to whom it is
addressed, or have received this transmission in error, you are hereby
notified that any dissemination, distribution or copying of this transmission
is strictly prohibited. Please notify us immediately and delete this e-mail
from your system. The sender does not accept liability for any errors or
omissions in the contents of this message which arise as a result of e-mail
transmission, which cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, arrive at wrong address or contain viruses. If verification
is required please request a hard-copy version.  This e-mail contains only the
personal opinions of the sender and does not represent an official
communication from NetYantra of any manner.
------------------------------------------------------------------------------