Re: PostgreSQL 9.0 Streaming Replication Configuration

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

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

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

That is my confusion - Archiving wal does not conflict in any way with streaming replication? What if streaming replication lags behind (especially with a lot of connections).

Thank you

Ogden

If the standby server cannot pull the WAL file from the master using
streaming replication, then it will attempt to pull it from the archive. If
the WAL segment isn't archived (for example because you aren't using
archiving), then your streaming replication is unrecoverable and you have to
take a fresh backup from the master and transfer it over to the standby
machine to start replication again. So the value of having archiving setup
is that in case a standby falls way behind, then the standby can recover
without having to copy your database over to the standby machine again.

Another setting you can tweak is "wal_keep_segments" on the master machine,
which is the minimum numbers of WAL segments it will keep without deleting.
So just with some simple math: (wal_keep_segments * 16MB /
your_wal_write_rate) you can determine a ballpark of how long your standby
machines can fall behind while still being able to recover without
archiving.

-Dan

On Tue, Feb 8, 2011 at 6:51 PM, Ogden <lists(at)darkstatic(dot)com> wrote:

>
> 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.
> >
> >
>
> That is my confusion - Archiving wal does not conflict in any way with
> streaming replication? What if streaming replication lags behind (especially
> with a lot of connections).
>
> Thank you
>
> Ogden
> --
> 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
>

On Tue, Feb 08, 2011 at 08:51:42PM -0600, Ogden wrote:
>
> 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.
> >
> >
>
> That is my confusion - Archiving wal does not conflict in any way with streaming replication? What if streaming replication lags behind (especially with a lot of connections).
>

I don't know about the "any way" deal. The admin cookbook says:

"There are two main ways to set up streaming replication: with or without
an additional archive. Set up without an external archive is presented
here, as it is both the most simple and efficient way. There is one
downside that suggests the simple approach may not be appropriate for
larger databases, explained later in the recipe."

It looks like that has to do with the initial backup for building the
standby taking to long.

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

On Wed, Feb 09, 2011 at 01:14:05AM -0600, Ogden wrote:
> Thank you for letting me know about pg_controldata. I have been playing around with this tool.
>

really interesting event/failure last night for me. I started a new
thread on the failure in the admin list. my streaming rep without
wal archiving in place seems to be corrupted. I think you will be
interested in it. I could have tacked it on here, but I thought it
needed to stand out.

Regards,
Ray