Re: Switch log (WAL)

Thanks...

Mhh too bad. So, that's what I'll do. I'm actually working on a script in
order to create a standby database, without even logging to it, and I would
like to avoid such a resident program on the master database... So, I'll
probably do smth with cron.

Here is my actual script :

#!/bin/sh
#
# This script will create a hot standby database replicated of your PostgreSQL
# database.
# This has been tested on PostgreSQL 8.1
#
# In order to work, this script assume that :
# - postgresql is installed on the standby database host.
# - The PGDATA is at the same place on both primary and standby database.
# - You made an ssh-keygen -t dsa on the primary db (with no passphrase )
# and that you copied the /var/lib/postgresql/.ssh/id_dsa.pub into
# the /var/lib/postgresql/.ssh/authorized_keys on the standby
# host, in order to be able to ssh from your primary to your standby without
# authentication.
# - Your primary database is in archive mode and that the postgresql.conf
# contains :
# archive_command = 'cp "%p" /var/lib/postgresql/data/archives/"%f" && scp
-B "%p" mydb2:/var/lib/postgresql/data/archives_mydb1/"%f"'

#
# This script can probably run in other conditions, but in my case I had :
# - PGDATA=/var/lib/postgresql/data
# - database user : postgres
# - postgres home in /var/lib/postgres
# - sed
# - postgresql 8.1.2
#
#
# Known problems/missing features :
# - The rotate log is done only when the log is full (default 16M), this can
# be a problem on DB with few UPDATES/INSERT, because if the primary
# database fails, and that no log has been transmitted for several hours,
# the standby db is late of several hours.
# - You cannot stop the standby database simply by shutting it down, because
the
# recovery.sh script will wait forever... You need to :
# killall recovery.sh
# After the "killall recovery.sh" the standby database will go online,
except
# if you initiate a database shutdown before.
#
# François Delpierre 02/2006

export PGDATA='/var/lib/postgresql/data'
#export PGDATA_ROOT_SB='/var/lib/postgresql/'
export STANDBY_HOST='mydb2'
export DATE=`date`
DBNAME="mydbname"

echo "Put the primary DB in backup mode"
psql $DBNAME -c "SELECT pg_start_backup('Backup $DATE');"

echo "Stop the recovery process if running"
ssh $STANDBY_HOST "killall recovery.sh"
sleep 3
ssh $STANDBY_HOST "killall -9 recovery.sh"

sleep 2
echo "Stop the standby database"
ssh $STANDBY_HOST "PGDATA=$PGDATA pg_ctl stop -m immediate || echo 'WARNING :
Failed to stop standby DB'"
sleep 2
ssh $STANDBY_HOST "killall postmaster"
ssh $STANDBY_HOST "killall postgresql"

echo "Copy datafiles"
rsync -avc --exclude '*archives/' --exclude 'pg_log/' --exclude
postgresql.conf --exclude *pg_xlog/ --delete $PGDATA/ $STANDBY_HOST:$PGDATA/

echo "Stop the backup mode on primary DB"
psql $DBNAME -c "SELECT pg_stop_backup();"

echo "Copy the postgresql.conf file and change the archive command."
export PGDATAESC="`echo $PGDATA | sed -e 's/\//\\\\\//g'`"
cat $PGDATA/postgresql.conf | sed -e
"s/[[:space:]]*archive_command.*/archive_command='cp \"%p\"
$PGDATAESC\/archives\/\"%f\"'/" | ssh $STANDBY_HOST "cat - >
$PGDATA/postgresql.conf"

echo "Create the standby script on $STANDBY_HOST"
ssh $STANDBY_HOST "cat - > $PGDATA/standby.sh" <<EOF
#!/bin/sh
export PGDATA=$PGDATA

if [ ! -d $PGDATA/archives ]
then
mkdir $PGDATA/archives
fi

mkdir $PGDATA/archives_mydb1

cat > $PGDATA/recovery.sh <<EOF2
#!/bin/sh
WAL=$PGDATA/archives_mydb1/\\\$1

if [ \\\`expr match "\\\$WAL" '.*\\\(history\\\)'\\\$\\\` ]
then
echo "History file requested"
[ -r "\\\$WAL" ] && exit 0
echo "History file not present"
exit 1
fi

LOGDATE=\\\`date +'%Y-%m-%d %H:%M'\\\`
echo "\\\$LOGDATE : Waiting for file \\\$1"
while [ ! -r \\\$WAL ]
do
sleep 1
done
LOGDATE=\\\`date +'%Y-%m-%d %H:%M'\\\`
echo "\\\$LOGDATE : Received file \\\$1"
sleep 3
cp \\\$WAL \\\$2
exit 0
EOF2

chmod u+x $PGDATA/recovery.sh

cat > $PGDATA/recovery.conf <<EOF3
restore_command = '$PGDATA/recovery.sh %f %p'
EOF3

sleep 1
echo "Start the standby database"
nohup postmaster > $PGDATA/postmaster.log 2>&1 </dev/null &
sleep 1

EOF

echo "Make the standby.sh script executable"
ssh $STANDBY_HOST "chmod u+x $PGDATA/standby.sh"
echo "Execute the standby.sh script on $STANDBY_HOST"
ssh $STANDBY_HOST $PGDATA/standby.sh

Le Monday 13 February 2006 18:43, Jim C. Nasby a écrit :
> On Sun, Feb 12, 2006 at 11:47:11AM +0100, postgres(at)pivert(dot)org wrote:
> > Hello,
> >
> > I'm new to the list, and I'm intereted in PostgreSQL replication (using
> > WAL) As I didn't find any script to do that, I make mine, that seems to
> > work.
> >
> > However, I wonder how can I "switch WAL logs", in order to have the
> > standby database not too much late.
> > The database actually generates few logs, and I would like one WAL to be
> > generated once per hour.
> >
> > How Can I do that ?
>
> You can't. What you can do is copy the most recently touched log file as
> often as you'd like. That will limit your data loss should you need to
> fail-over.