Thread: 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
instantaneouslyupdated on the slave, however, I was wondering perhaps if someone can give me some verification that
whatI 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
processesshows: '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
actualtiming 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
SCPto /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
instantaneouslyupdated on the slave, however, I was wondering perhaps if someone can give me some verification that
whatI 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
overto.  
>
> 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
processesshows: '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
actualtiming 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
viaSCP to /var/lib/pgsql/walfiles ? 
>
> Thank you very much
>
> Ogden Nefix
>
>
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.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
replicationlags 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.

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
replicationlags 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
instantaneouslyupdated on the slave, however, I was wondering perhaps if someone can give me some verification that
whatI 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
overto.  
>>
>> 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
processesshows: '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
actualtiming 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
viaSCP to /var/lib/pgsql/walfiles ? 
>>
>> Thank you very much
>>
>> Ogden Nefix
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.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