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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
