PostgreSQL 8.4.20热备日志传输方案及自定义同步疑问

Hey there! Since you're working with PostgreSQL 8.4.20 (an older but still functional version) and setting up warm standby, let's break down your questions clearly:

1. Which program handles log shipping from primary to standby? #

In PostgreSQL 8.4, warm standby relies on WAL (Write-Ahead Log) archiving for log shipping—there’s no built-in daemon that automatically transfers logs. Instead, two key pieces work together:

• On the primary: You configure an archive_command that tells PostgreSQL what to do with completed WAL segments. This can be any shell command (like rsync, scp, or a custom script) that copies the WAL file to the standby’s archive directory.
• On the standby: The pg_standby utility (shipped with 8.4) is used in the restore_command within recovery.conf to fetch and apply archived WAL files from the archive directory.

2. Do I need to set up rsync tasks, or does PostgreSQL handle this automatically? #

PostgreSQL doesn’t handle cross-server file transfer on its own—you have to define how WAL files move using the archive_command. Rsync is a solid choice here, and you can integrate it directly into the archive_command to trigger transfers as soon as a WAL segment is finished. For example:

archive_command = 'rsync %p standby_user@standby_host:/var/lib/pgsql/8.4/archive/%f'

This tells the primary to immediately rsync each completed WAL segment (%p is the primary’s WAL file path, %f is the filename) to the standby’s archive directory.

If you want more control (like scheduling syncs at specific intervals instead of real-time), you can first archive WAL files to a local directory on the primary, then set up a cron job to run rsync periodically. For example:

• Primary archive_command: archive_command = 'cp %p /var/lib/pgsql/8.4/local_archive/%f'
• Cron job on primary: */5 * * * * rsync -av /var/lib/pgsql/8.4/local_archive/ standby_user@standby_host:/var/lib/pgsql/8.4/archive/

3. Can I disable PostgreSQL's sync mechanism to control sync with rsync? #

Great question! First, a key note: PostgreSQL 8.4’s warm standby doesn’t have a built-in "sync mechanism" like the streaming replication in newer versions (9.1+). The entire log shipping process is driven by your archive_command (primary) and restore_command (standby).

So yes, you can absolutely take full control with rsync:

• If you want to manage when syncs happen, skip integrating rsync into archive_command and use the local archive + scheduled rsync setup mentioned above.
• PostgreSQL won’t interfere here—it just keeps generating WAL files and archiving them to your local directory, and you handle the rest with rsync.

A quick heads-up for 8.4: Make sure you follow the warm standby setup steps closely (taking a base backup with pg_start_backup/pg_stop_backup, configuring recovery.conf on the standby, etc.)—newer versions have streamlined this process, but 8.4 requires a bit more manual setup.

内容的提问来源于stack exchange，提问作者TSG