Обсуждение: pg_dump-restore concurrency
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... -- View this message in context: http://old.nabble.com/pg_dump-restore-concurrency-tp29224477p29224477.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 21/07/10 19:26, paladine wrote: > > 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. There's no particular reason why you can't just feed a data-only dump into psql. It should copy all that data into the table without affecting new inserts. If you're doing anything more complex, messing with many tables at once etc, then just use the commands built in to PostgreSQL for the purpose. If you don't like VACUUM FULL for some reason, you should fix your autovacuuming so that you don't need it. See the postgresql documentation and the wiki entries on VACUUM FULL, autovacuum, etc. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/
I have a linux daemon that parses some datas and writes to db continuously but my db is growing unexpectedly so I must reduce disk space once a week. vacuum full is one solution but pg_dump-restore gives back more space to OS. I wrote a script like this # pg_dump db > asd.sql (1) # dropdb db (2) # createdb db (3) # psql -f asd.sql -d db (4) PostgreSQL doc says that pg_dump doesn't lock any table but I didn't find information about restoring dump file. My question is that while db was processing (4) line, can my daemon continue its normal job (inserting data) Craig Ringer wrote: > > On 21/07/10 19:26, paladine wrote: >> >> 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. > > There's no particular reason why you can't just feed a data-only dump > into psql. It should copy all that data into the table without affecting > new inserts. > > If you're doing anything more complex, messing with many tables at once > etc, then just use the commands built in to PostgreSQL for the purpose. > > If you don't like VACUUM FULL for some reason, you should fix your > autovacuuming so that you don't need it. > > See the postgresql documentation and the wiki entries on VACUUM FULL, > autovacuum, etc. > > -- > Craig Ringer > > Tech-related writing: http://soapyfrogs.blogspot.com/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://old.nabble.com/pg_dump-restore-concurrency-tp29224477p29225050.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
yasinmalli@gmail.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
On 21/07/10 20:21, paladine wrote: > > I have a linux daemon that parses some datas and writes to db continuously > but my db is growing unexpectedly so I must reduce disk space once a week. I assume you're also deleting from the database, given that its growth is a problem. It sounds like you need to fix your autovacuum settings. Make autovacuum much more aggressive. This should prevent the growth, so you don't need to do all this messing around with dumps and reloads or VACUUM FULL. See: http://www.postgresql.org/docs/current/static/routine-vacuuming.html#AUTOVACUUM http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT http://wiki.postgresql.org/wiki/VACUUM_FULL -- Craig Ringer