Thread: 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