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:[EMAIL PROTECTED]
Sent: Friday, March 14, 2003 13:22
To: [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Trewern, Ben
Sent: 14 March 2003 17:55
To: 'Matt Clark'
Cc: [EMAIL PROTECTED]
Subject: Re: [ADMIN] LVM snapshots
Try - [GENERAL] A few questions to real pgsql gurus
Ben
pgSnapBack.sh
Description: Binary data
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
