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@xxxxxxxxxx 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.