Re: Can a whole server be restored over to another server in a single command?

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