Re: [GENERAL] PostgreSQL 9.0 Streaming Replication Configuration
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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/F220 Prior checkpoint location:1E3/F120 Latest checkpoint's REDO location:1E3/F220 And on the slave server (where it is archiving to), I have: Latest checkpoint location: 1E3/EF20 Prior checkpoint location:1E3/EF20 Latest checkpoint's REDO location:1E3/EF20 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 > 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 00010003001E'). >> >> 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@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0 Streaming Replication Configuration
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0 Streaming Replication Configuration
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 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] PostgreSQL 9.0 Streaming Replication Configuration
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 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 00010003001E'). > > 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@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
[GENERAL] 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 http://www.postgresql.org/mailpref/pgsql-general