Re: PITR backup to Novell Netware file server

On Tue, Aug 07, 2007 at 06:29:35AM -0500, Kevin Grittner wrote:
> We have a database in each Wisconsin county which is part of the official
> court record for the circuit courts in that county.  We are required to keep
> a backup in each county, such that we could recover a lost or corrupted
> database with what is in the county (as well as keeping at least four
> separate sources, each regularly confirmed as usable, at our central site).
> Currently, the only two boxes available besides the database server (in most
> counties) are a "utility server" (a Windows desktop class machine used to
> push workstation images and other support tasks) and a Novell Netware file
> server.
>
> We had to get something going quickly when we started rolling PostgreSQL out
> to the counties as a replacement of a commercial database product; the quick
> and (way too) dirty approach was to create a samba share on the utility
> server for the target of the archive_command and our base backups.  We rsync
> from there back to the central site using the samba share, too.  We
> frequently lose the mount points or they become unresponsive.  We also
> frequently have corrupted files when they rsync across the WAN, so we want
> to move away from all use of samba.
>
> The Netware server supports ssh, scp, and an rsync daemon.  I don't see how
> the ssh implementation is helpful, though, since it just gets you to the
> Netware console -- you can't cat to a disk file through it, for example.
> (At least not as far as we have been able to see.)  It appears that the scp
> and rsync techniques both require the initial copy of the file to be saved
> on the database server itself, which we were hoping to avoid for performance
> reasons.
>
> We could create ncp mounts on the database servers; but, frankly, we haven't
> had much better luck in keeping those connected than we have with samba.
> Has anyone else been through this and found a robust and reliable way to
> do PITR backups from a PostgreSQL database on one machine to a Netware file
> server on another?
>
> Apparently we will be moving to a Linux-based implementation of Netware at
> some unspecified future date, at which point we will apparently be able to
> deal directly with the Linux layer.  At that point, there are obvious, clean
> solutions; but we've got to have something reasonable until such date, which
> is most likely a few years off.

Given your situation, I certainly wouldn't trust WAL files from the
Samba server going back to your central site. I think you've got 2
options here:

1) Write a script that copies to the local backup as well as the remote
backup and call that script from archive_command. Make certain that the
script returns a non-zero exit code if *either* copy operation fails.

2) Have archive_command copy to someplace on the database server, and
have another process copy from there to both the local backup as well as
the central backup.

I'd be inclined to go with 2, since it means that if you can hit at
least one backup location you'll keep that backup as up-to-date as
possible, whereas with 1, a failure of a mount point or inability to
connect to the central location would mean no backup.

As for performance, just how hard are you pushing these machines?
Copying a 16MB file that's already in memory isn't exactly an intensive
operation...
--
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)