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

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.simonaitis@gmail.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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> 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?

Newer LVM versions support read/write snapshots [essentially cheap clone of
all partition data].
http://tldp.org/HOWTO/LVM-HOWTO/snapshots_backup.html is good document about
them.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq