Обсуждение: dump & create with a cron job

We need to "recreate" our demonstration database about every two hours,
and we'd like to develop a cron job, calling scripts, that will
accomplish that for us.

The problem is.... if someone is on our web demo at the time that the
cron job goes off, it does not "dump" the database, it only "adds" the
data to the database as the existing one.

Basically, here's what we're trying to do:
dump (pg_dump) our demo_model database to a "demo_model.sql" file

dropdb active_demo
create active_demo
/usr/bin/psql active_demo < demo_model.sql > /dev/null

Any ideas?

Jack Miller <jgm@miller-group.net> writes:
> The problem is.... if someone is on our web demo at the time that the
> cron job goes off, it does not "dump" the database, it only "adds" the
> data to the database as the existing one.

Evidently you are neglecting to check for failure of the dropdb step.
Perhaps something like

    while ! dropdb active_demo; do
        sleep 10
    done
    createdb active_demo
    /usr/bin/psql active_demo < demo_model.sql > /dev/null

This kinda begs the whole question of interlocking though; won't people
see funny behavior when you do this?

            regards, tom lane

On Tue, 22 Mar 2005 14:17:39 -0500, Tom Lane wrote
> Jack Miller <jgm@miller-group.net> writes:
> > The problem is.... if someone is on our web demo at the time that the
> > cron job goes off, it does not "dump" the database, it only "adds" the
> > data to the database as the existing one.
>
> Evidently you are neglecting to check for failure of the dropdb step.
> Perhaps something like
>
>     while ! dropdb active_demo; do
>         sleep 10
>     done
>     createdb active_demo
>     /usr/bin/psql active_demo < demo_model.sql > /dev/null
>
> This kinda begs the whole question of interlocking though; won't people
> see funny behavior when you do this?
>
>             regards, tom lane

I have scripts that you could have but they are only simple wrappers around
the pg_dump command.  As Tom suggests you need to make sur the drop is
working.  Do you utilize PGDATABASE in your setup?  Could you run two or more
databases at a time and utilize a round robin approach?

As an outline:

PGDATABASE=demo1
# create connections to demo1 for a while
# check if anyone is still connected to demo2
dropdb demo2
createdb demo2
PGDATABASE=demo2
# create connections to demo2 for a while
# check if anyone is still connected to demo1
dropdb demo1
createdb demo1
# back to the top

Kind Regards,
Keith