Currently I feel that the sync script I want is within my capabilities
- the re-init stuff would need investigation and testing. I'll get on
with the script and post up the result. There are parts which others
may find useful.
Here's the script in it's current format. After all the work on it it
has become fairly concise. A couple of points first.
1. I suppose that to be a proper sync it should delete all user
databases on the *Secondary* server.
2. It wouldn't be too difficult to have a section first which would make
sure the Secondary server has matching user accounts.
# Here we loop through a list of databases - the flags cut out most
formatting from the database list produced.
for db_name in $(psql -U admin -h ${REMOTE} -A -t --command "SELECT
datname FROM pg_catalog.pg_database ORDER BY datname;" postgres)
do
if [ $db_name != 'postgres' -a $db_name != 'template0' -a $db_name !=
'template1' ]; then
echo
echo `date`
echo "Dumping out ${db_name}..."
pg_dump -U admin -h ${REMOTE} --create ${db_name} >
/var/backups/${db_name}.pgd
echo
echo `date`
echo "Dropping database ${db} and reloading it..."
psql -U admin -h localhost --command "DROP DATABASE
\"${db_name}\";" postgres
psql -U admin -h localhost -f /var/backups/${db_name}.pgd
postgres > /dev/null
fi
done
Hope it's of use to others.
BTW - This is what I've used when the database needs to be copied to a
different DB on the same server with a different owner.
echo
echo `date`
echo "Dumping database stalive..."
pg_dump -U stalive -h localhost --no-owner stalive >
/home/statraining/stalive_pg_backup.pgd
echo
echo `date`
echo "Dropping database statraining..."
psql -U statraining -h localhost --command "DROP DATABASE statraining;"
template1
echo
echo `date`
echo "Recreating database statraining..."
psql -U aplus2admin -h localhost --command "CREATE DATABASE statraining
WITH OWNER statraining;" template1
echo
echo `date`
echo "Restoring database statraining from stalive backup..."
psql -U statraining -h localhost -d statraining -f
/home/statraining/stalive_pg_backup.pgd
Kev
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
