Chris Travers wrote:
One simple option is a shell script like such: dropdb -h $EXTERNAL_HOST -U postgres $SL_DATASET createdb -h $EXTERNAL_HOST -U postgres $SL_DATASET pg_dump -h $INTERNAL_HOST -U postgres $SL_DATASET | psql -U postgres -h $EXTERNAL_HOST $SL_DATASET Set the appropriate variables and set up a .pgpass file, run it at midnight via cron.
This is what we do but with a slight variation:We rename the original database instead of dropping it. That way, if something goes wrong, all we have to do is drop the new one and rename the original back to the new.
Charley