pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSwitch log (WAL)
5+ messages / 4 participants
[nested] [flat]
* Switch log (WAL)
@ 2006-02-12 10:47 postgres@pivert.org
0 siblings, 2 replies; 5+ messages in thread
From: postgres@pivert.org @ 2006-02-12 10:47 UTC (permalink / raw)
To: pgsql-admin
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 ?
Regards,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Switch log (WAL)
@ 2006-02-13 17:43 Jim C. Nasby <jnasby@pervasive.com>
parent: postgres@pivert.org
1 sibling, 1 reply; 5+ messages in thread
From: Jim C. Nasby @ 2006-02-13 17:43 UTC (permalink / raw)
To: postgres@pivert.org; +Cc: pgsql-admin
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.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Switch log (WAL)
@ 2006-02-13 22:52 postgres@pivert.org
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 5+ messages in thread
From: postgres@pivert.org @ 2006-02-13 22:52 UTC (permalink / raw)
To: pgsql-admin
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@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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Switch log (WAL)
@ 2006-02-15 21:21 Simon Riggs <simon@2ndquadrant.com>
parent: postgres@pivert.org
1 sibling, 1 reply; 5+ messages in thread
From: Simon Riggs @ 2006-02-15 21:21 UTC (permalink / raw)
To: postgres@pivert.org; +Cc: pgsql-admin
On Sun, 2006-02-12 at 11:47 +0100, postgres@pivert.org wrote:
> How Can I do that ?
This is on my todo list for 8.2, but not yet at the top.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Switch log (WAL)
@ 2006-02-16 18:30 RW <postgres@tauceti.net>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 5+ messages in thread
From: RW @ 2006-02-16 18:30 UTC (permalink / raw)
To: pgsql-admin
Hi,
maybe a bit late but how about DRBD (http://www.drbd.org)?
BTW: Very nice that this feature is on the TODO :-))
Cheers,
Robert
Simon Riggs wrote:
>On Sun, 2006-02-12 at 11:47 +0100, postgres@pivert.org wrote:
>
>
>
>>How Can I do that ?
>>
>>
>
>This is on my todo list for 8.2, but not yet at the top.
>
>Best Regards, Simon Riggs
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-02-16 18:30 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-12 10:47 Switch log (WAL) postgres@pivert.org
2006-02-13 17:43 ` Jim C. Nasby <jnasby@pervasive.com>
2006-02-13 22:52 ` postgres@pivert.org
2006-02-15 21:21 ` Simon Riggs <simon@2ndquadrant.com>
2006-02-16 18:30 ` RW <postgres@tauceti.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox