Thread: DB archiving

Hello.

I was speaking with my colleague about PostgreSQL maintenance and he asked
me a question which I wasn't able to answer.

Is it possible to make a full DB backup (one database, say 'test') online
and be sure, that after restoring it it'll contain ALL and ONLY data at the
time of backuping routine start.

I want to be sure, that nothing additional will get into dump after I'll
start pg_dump.

Is this possible? Or, may be, such behaviour is on by default (I
didn't find anything concerning my problem in the docs about pg_dump).

Thank you.

--

Victor Yegorov

This is exactly the way pg_dump works. Essentially when you pg_dump, you
get a "snapshot" of your database at the moment you start pg_dump. While
other transaction can be occuring during your dump, the dump wont see
them thanks to MVCC.

Robert Treat

On Mon, 2003-03-17 at 05:33, Victor Yegorov wrote:
> Hello.
>
> I was speaking with my colleague about PostgreSQL maintenance and he
> asked
> me a question which I wasn't able to answer.
>
> Is it possible to make a full DB backup (one database, say 'test')
> online
> and be sure, that after restoring it it'll contain ALL and ONLY data at
> the
> time of backuping routine start.
>
> I want to be sure, that nothing additional will get into dump after I'll
> start pg_dump.
>
> Is this possible? Or, may be, such behaviour is on by default (I
> didn't find anything concerning my problem in the docs about pg_dump).
>
> Thank you.
>
> --
>
> Victor Yegorov

* Robert Treat <xzilla@users.sourceforge.net> [17.03.2003 16:49]:
> This is exactly the way pg_dump works. Essentially when you pg_dump, you
> get a "snapshot" of your database at the moment you start pg_dump. While
> other transaction can be occuring during your dump, the dump wont see
> them thanks to MVCC.
>

I see, thank you.

One more thing. I still cannot find any info on HOW pg_dump locks data it
is going to backup. Is this described somewhere in the manual? (I've found
nothing neither in "Concurency Control" nor in pg_dump description).

What I'd like is to understand the algorithm. I know how backuping is
realized in ORACLE, and I'm sure there, that when I start it, lock is set on
entire tablespace (it's put in readonly mode), so I'm shure, that nothing
will get there.

In PostgreSQL I don't know how lock is aquired. I'm trying to avoid
situations, like (assumption): lock is aquired on 'per table' level and
while first table in the list is locked, last one is being updated, so -
my db won't be consistent after backup.

Thanks in advance.

--

Victor Yegorov

Victor Yegorov <viy@pirmabanka.lv> writes:
> One more thing. I still cannot find any info on HOW pg_dump locks data it
> is going to backup.

It doesn't.  Well, it does take a reader's lock (AccessShareLock) on
each table it intends to back up, but that's essentially just preventing
the table from being dropped completely.  The individual data rows need
not be locked.  pg_dump does the whole dump as a single transaction, so
it can rely on MVCC semantics to give it a consistent picture of the
data in every table.  Basically, it doesn't "see" changes that commit
after its transaction starts.  See the discussion of MVCC.

            regards, tom lane