Re: lock entire database - Mailing list pgsql-novice

On Sun, 2004-08-08 at 23:35, Benjamin wrote:
> 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?
>

Yes, you should use slony or some other replication method if possible.