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.