pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Switch 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