Re: dump & create with a cron job

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