pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steve Crawford <scrawford@pinpointresearch.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Archive Command Configuration
Date: Thu, 23 Mar 2006 12:28:41 -0800
Message-ID: <442304F9.4090708@pinpointresearch.com> (raw)
In-Reply-To: <4422FD80.5080009@livedatagroup.com>
References: <4422FD80.5080009@livedatagroup.com>

>...I have a
> cron job from the production database that runs every 5-10min to check 
> if there are any new archive logs and copy new archive logs to the 
> remote stand by failover machine.
> 
> The problem with this scenario is that there might be a possibility that 
> I might scp a partially filled archive log over to the remote machine on 
> a heavily updated databases with batch jobs runnings most of the time  
> like ours.
> 
>   So, inorder to over come this we want to change the archive_command to
> 
> archive_command = 'cp %p /archives-temp/%f && mv /archives-temp/%f 
> /archives/%f'
> ( I could do remote copy also with the command but I dont want to go 
> that route because of network problems. )
> 
> ....
> 
>  My question is there any problem in using this approach ? will I run 
> into problems in the future ? are there any other better ways of solving 
> this problem ?

Looks like it should work but have you considered using rsync instead?
If the file grows from one pass to the next, rsync will send the 
differences.

Given the small number of files (at least far fewer thatn the 
half-million file trees my rsyncs process every 10 minutes) in the 
archive directory, rsync will probably only take a fraction of a second 
to determine whether or not any transfers are needed. I'll bet that you 
could run rsync every 15 seconds with virtually no increase in system 
load other than whatever is required to transfer the data and you have 
that load already.

Rsync can also handle deleting files from the receiving end if you wish.

I am starting to work on a wal-shipping backup and I plan to try rsync 
first.

Some advice. I make extensive use of rsync. The core skeleton of my 
scripts goes something like this:

cd /the/appropriate/place
if [ -f rsync.lockfile ]
   # Exit - new pass started before previously
   # started rsync completed
   exit
fi
date > rsync.lockfile
rsync <your parameters as appropriate>
rm rsync.lockfile

I run another script on the target machine that periodically checks the 
age of rsync.lockfile and sends alerts if it is excessively old (where 
excessive will be determined by the specifics of your setup - I use one 
hour).

Cheers,
Steve



view thread (9+ messages)  latest in thread

Message-ID: <442304F9.4090708@pinpointresearch.com>
Permalink:  ../442304F9.4090708@pinpointresearch.com/
Also on:    postgresql.org/message-id/442304F9.4090708@pinpointresearch.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: scrawford@pinpointresearch.com
  Subject: Re: Archive Command Configuration
  In-Reply-To: <442304F9.4090708@pinpointresearch.com>
  References: <4422FD80.5080009@livedatagroup.com> <442304F9.4090708@pinpointresearch.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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