Re: PostgreSQL 9.0 Streaming Replication Configuration

Thank you for letting me know about pg_controldata. I have been playing around with this tool.

I notice on my master server I have:

Latest checkpoint location: 1E3/F2000020
Prior checkpoint location: 1E3/F1000020
Latest checkpoint's REDO location: 1E3/F2000020

And on the slave server (where it is archiving to), I have:

Latest checkpoint location: 1E3/EF000020
Prior checkpoint location: 1E3/EF000020
Latest checkpoint's REDO location: 1E3/EF000020

These are the main differences - should these match or is this a sign of being too out of sync? How can I best use this tool?

Thank you

Ogden

On Feb 8, 2011, at 8:47 PM, Ray Stell wrote:

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