X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 52A4C9DCC2A for ; Mon, 13 Feb 2006 18:52:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43919-09 for ; Mon, 13 Feb 2006 18:52:08 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from oola.is.scarlet.be (oola.is.scarlet.be [193.74.71.23]) by postgresql.org (Postfix) with ESMTP id 6C74A9DCC1B for ; Mon, 13 Feb 2006 18:52:02 -0400 (AST) Received: from (ip-213-49-180-175.dsl.scarlet.be [213.49.180.175]) by oola.is.scarlet.be with ESMTP id k1DMq3x09298 for ; Mon, 13 Feb 2006 23:52:03 +0100 From: postgres@pivert.org To: pgsql-admin@postgresql.org Subject: Re: Switch log (WAL) Date: Mon, 13 Feb 2006 23:52:02 +0100 User-Agent: KMail/1.9 References: <200602121147.11349.postgres@pivert.org> <20060213174350.GA57845@pervasive.com> In-Reply-To: <20060213174350.GA57845@pervasive.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200602132352.02560.postgres@pivert.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.63 required=5 tests=[AWL=0.080, NO_REAL_NAME=0.55] X-Spam-Score: 0.63 X-Spam-Level: X-Archive-Number: 200602/172 X-Sequence-Number: 20593 Thanks... Mhh too bad. So, that's what I'll do. I'm actually working on a script in=20 order to create a standby database, without even logging to it, and I would= =20 like to avoid such a resident program on the master database... So, I'll=20 probably do smth with cron. Here is my actual script : #!/bin/sh # # This script will create a hot standby database replicated of your Postgre= SQL # 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 with= out # authentication. # - Your primary database is in archive mode and that the postgresql.conf # contains : # archive_command =3D 'cp "%p" /var/lib/postgresql/data/archives/"%f" && = scp=20 =2DB "%p" mydb2:/var/lib/postgresql/data/archives_mydb1/"%f"' # # This script can probably run in other conditions, but in my case I had : # - PGDATA=3D/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, becaus= e=20 the # recovery.sh script will wait forever... You need to : # killall recovery.sh # After the "killall recovery.sh" the standby database will go online,=20 except # if you initiate a database shutdown before. # # Fran=E7ois Delpierre 02/2006 export PGDATA=3D'/var/lib/postgresql/data' #export PGDATA_ROOT_SB=3D'/var/lib/postgresql/' export STANDBY_HOST=3D'mydb2' export DATE=3D`date` DBNAME=3D"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=3D$PGDATA pg_ctl stop -m immediate || echo 'WARNI= NG :=20 =46ailed 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=20 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=3D"`echo $PGDATA | sed -e 's/\//\\\\\//g'`" cat $PGDATA/postgresql.conf | sed -e=20 "s/[[:space:]]*archive_command.*/archive_command=3D'cp \"%p\"=20 $PGDATAESC\/archives\/\"%f\"'/" | ssh $STANDBY_HOST "cat - >=20 $PGDATA/postgresql.conf" echo "Create the standby script on $STANDBY_HOST" ssh $STANDBY_HOST "cat - > $PGDATA/standby.sh" < $PGDATA/recovery.sh < $PGDATA/recovery.conf < $PGDATA/postmaster.log 2>&1 On Sun, Feb 12, 2006 at 11:47:11AM +0100, postgres@pivert.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.