Re: PostgreSQL 9.0 Streaming Replication Configuration

pg_controldata command is helpful.

Archiving wal not required, but you can roll it either way.

On Tue, Feb 08, 2011 at 04:46:51PM -0600, Ogden wrote:
> Hello all,
>
> I have set up PostgreSQL Streaming Replication and all seems to work fine when updating records as the records are instantaneously updated on the slave, however, I was wondering perhaps if someone can give me some verification that what I am doing is alright or some more insight into what I am doing. Perhaps this will also help others in the future.
>
> First on the master, I have the following in /var/lib/pgsql/data/standby.sh:
>
>
> #!/bin/sh
>
> LOG_FILE="/tmp/postgres_wal_archiving.log"
>
> log() { echo "`date --rfc-3339=ns` $1" >> "$LOG_FILE"; }
> log_error() { echo "`date --rfc-3339=ns` $1" >> "$LOG_FILE"; exit 1; }
>
> wal_path="$1"
> wal_file="$2"
> backup_server="slave01"
> remote_archive="/var/lib/pgsql/walfiles/$wal_file"
>
> log "Transfering file to backup server, filename: $wal_file"
> rsync "$wal_path" "$backup_server:$remote_archive"
> if [ "$?" -eq 0 ]; then
> log "Transfer to slave server completed"
> else
> log_error "Sending $wal_file failed."
> fi
>
> On the slave, I create the directory /var/lib/pgsql/walfiles (remote_archive) for the script to copy the walfiles over to.
>
> Then, within the master's postgresql.conf I have:
>
> wal_level = hot_standby
> archive_mode = on
> archive_command = '/var/lib/pgsql/data/standby.sh %p %f </dev/null' # The same script as above
> archive_timeout = 30
> max_wal_senders = 5
> wal_keep_segments = 32
> #hot_standby = off
>
> I start up the master server and verify that files are indeed being SCPed over to /var/lib/pgsql/walfiles (also processes shows: 'archiver process last was 00000001000000030000001E').
>
> After starting up on the master, I rsync over the data/ directory to the slave:
>
> /path/to/psql -c "SELECT pg_start_backup('label', true)"
> rsync -avz --delete /var/lib/pgsql/data/ slave01:/var/lib/pgsql/data --exclude postmaster.pid
> /path/to/psql -c "SELECT pg_stop_backup()"
>
> And I add recovery.conf over on the the slave's data/ directory:
>
> standby_mode = 'on'
> primary_conninfo = 'host=master_ip port=5432 user=postgres'
> trigger_file = '/tmp/trigger'
> restore_command='cp /var/lib/pgsql/walfiles/%f "%p"'
>
> And in the slave's postgresql.conf, I remove the comment on :
>
> hot_standby = on
>
> Upon starting the slave, everything works fine and updates to records occur on the slave immediately (what is the actual timing for this)?
>
> My confusion is: does streaming replication require WAL archiving as I have illustrated above or is it a "just in case" scenario? Also, the restore_command on the slave - is this correct, assuming that the master is dropping off files via SCP to /var/lib/pgsql/walfiles ?
>
> Thank you very much
>
> Ogden Nefix
>
>
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general