Re: streaming replication - pgsql 9.2.3

[Jason Mathis <jmathis@xxxxxxxxxxxxxxxxxxx>]

Yep, as long as you are ok with losing the data in between the “dump all” then it sounds good.

For example: 

9pm = pg_dumpall 

during the day you modified and added a bunch of data. 

Then the next day 8pm = you accidentally drop the database. 

Your recovery is last night’s backup and everything that happened during the day is gone. 

Also you may want to check out the keep_segments. 

http://www.postgresql.org/docs/9.2/static/warm-standby.html

"If you use streaming replication without file-based continuous archiving, you have to set wal_keep_segments in the master to a value high enough to ensure that old WAL segments are not recycled too early, while the standby might still need them to catch up. If the standby falls behind too much, it needs to be reinitialized from a new base backup. If you set up a WAL archive that's accessible from the standby, wal_keep_segments is not required as the standby can always use the archive to catch up."

-jason 

On April 14, 2014 at 3:51:50 PM, Jim Mercer (jim@xxxxxxxxxxxx) wrote:

On Mon, Apr 14, 2014 at 03:14:46PM -0600, Jason Mathis wrote:
> Just keep in mind that while wal archiving and replication sound very similar they are intended for two different things.??
>
> Wal Archiving = backup/DR/PITR/standby
> Replication = High availability/reporting??

it would be nice if the documentation reflected this.

the current information i derived, basically says to do both.

i'm looking to revise my settings to:
- nullify/NOP the archive_command and restore_command
- use the slave to do a pg_dumpall as the backup

the standby node serves as a read-only slave, which allows for some
performance gains.

the standby node could be promoted to master, should the master have issues.

the backup would allow for a recovery from a catestrophic/cascading error
(ie. drop all tables).

does this make sense?

--
Jim Mercer Reptilian Research jim@xxxxxxxxxxxx +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

This transmission contains confidential and privileged information intended solely for the party identified above. If you receive this message in error, you must not use it or convey it to others. Please destroy it immediately and contact the sender at (303) 386-3955 or by return e-mail to the sender.