Re: Moving WAL files

Tomas,

I plan to do something similar [except snapshots] so if I run into
issues, I will let you know.

Quick question, you mentioned LVM snapshots, and I am not aware what
it does! Is there any doc that explains it? Can you point me to some
such resources?

Regards
Dhaval

On 2/22/07, Tomas Simonaitis <tomas(dot)simonaitis(at)gmail(dot)com> wrote:
> Hi,
>
> I've got following online-backup setup (v. 8.1.8):
> - on master
> -- archive_command = 'mv %p /<wals_dir>/%f
> -- rsyncd with access to DB data and <wals_dir>
>
> - on slave
> -- rsync client running every 10sec. to sync <pg_xlog_dir> and <wals_dir>
> directories to slave:
> rsync -a --delete master::<wals_dir> <slave_wals_dir>
> rsync -a --delete master::<pg_xlog_dir> <slave_pg_xlog_dir>
>
> -- pg_start_backup via ssh/rsync DB dir/pg_stop_backup via ssh/old wals
> cleanup every hour:
> ssh master --command "\"SELECT pg_start_backup('${BKNAME}');\""
> rsync -a --exclude=pg_xlog/ --delete main::<db_dir> <slave_db_dir>
> ssh master psql --command "\"SELECT pg_stop_backup();\""
>
> -- sql dump every 2 hours:
> create LVM snapshot from <slave_db_dir> partition, start postgresql server
> with pgdata set to snapshot mountpoint, pg_dumpall, stop, remove snapshot
>
> Setup works perfectly fine, but I' not sure about several things:
> 1. Is it ok to move instead of copy in archive_command? I use move since
> <wals_dir> is on same partition as <pg_xlog_dir>.
> 2. I just rsync pg_xlog directories instead of hunting down active WAL file
> and copying it. Any problems possible because of that?
> 3. Should I worry about using only "rsync -a" (i.e. checking only mtime,
> instead of checksum to see if files differ).
> 4. during "old wals cleanup" I remove all wal files upto last WAL.backup
> (excluding that), afaik thats always ok?
>
> Thanks.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Dhaval Shah