Re: pg_dump-restore concurrency

yasinmalli(at)gmail(dot)com (paladine) writes:
> Hi all
> I prefer doing pg_dump - psql restore to vacuum full and
> is there anyone know whether postgresql can insert data concurrently while
> restoring a table for not losing any data.
>
> thanks in advance...

The problem scenario that I'd expect is with the handling of sequences.

A pg_dump puts setval() calls to set the values of sequences near
the end of processing, which means that anything using sequences to
assign IDs is likely to lead to some anomalous behaviour:

- At the start, the sequence will have [some value]

- Your concurrent inserts will store data that starts at that point

- A setval() call from the pg_dump will set the sequence to [another
value]

It is entirely possible that a failure will take place anywhere in here
due to clashes between sequence values, if the sequence is used to
ensure uniqueness of values used for unique indices.

You could presumably avoid some of the problems with this by using UUIDs
in such cases, but that adds a different set of challenges.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
"On a normal ascii line, the only safe condition to detect is a
'BREAK' - everything else having been assigned functions by Gnu
EMACS." -- Tarl Neustaedter