Re: dump & create with a cron job

On Tue, 22 Mar 2005 14:17:39 -0500, Tom Lane wrote
> Jack Miller <jgm(at)miller-group(dot)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