Here's the script I use to mount a snapshot and rsync to a remote server;
postgresql is started and stopped on the remote server as part of the backup
procedure to make sure that the backup log contains any errors that might
show up. The restore strategy is to start the postmaster on the remote
server and dump restore like so:
pgdump -Fc -h <backup-server> -U <user> -d <database> -t <table> | pgrestore
-h <production-server> -U <user> -d <database> -t <table> [-c]
Note that this is not particularly safe with 7.3, since pgrestore will try
to restore every table in the database with the given name. If you use the
schema feature that allows indentically named tables in different schema,
you're in for trouble when you recover it.
Backup restore has been tested so far by restoring a table and comparing the
contents; they were identical. Thanks to rsync performance is fantastic
compared to pgdump to a file (my past e-mail to the list will give some idea
of the problems we've had with that). I'd be interested in comments on the
strategy and the script itself.
Cheers,
Murthy
-----Original Message-----
From: Matt Clark [mailto:matt@ymogen.net]
Sent: Friday, March 14, 2003 13:22
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] LVM snapshots
Thanks all.
The conclusion there seemed to be that it ought to work just fine, but
should be tested. Well, I'll test it and see if anything interesting comes
up. If anything LVM snapshots will be less tricky than NetApp snapshots as
LVM has access to the OS's local cache buffers, whereas the NetApp doesn't
(though presumably the OS shouldn't/doesn't do any write caching when
writing to a network FS).
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Trewern, Ben
Sent: 14 March 2003 17:55
To: 'Matt Clark'
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] LVM snapshots
Try - [GENERAL] A few questions to real pgsql gurus
Ben