Re: History of WAL_LEVEL (archive vs hot_standby)

David Johnston wrote
> 
> Noah Misch-2 wrote
>> On Thu, Mar 27, 2014 at 03:06:02PM -0700, David Johnston wrote:
>>> shamccoy wrote
>>> > Hello.  I've been doing some benchmarks on performance / size
>>> differences
>>> > between actions when wal_level is set to either archive or
>>> hot_standby. 
>>> > I'm not seeing a ton of difference.  I've read some posts about
>>> > discussions as to whether this parameter should be simplified and
>>> remove
>>> > or merge these 2 values.
>>> > 
>>> > I'd like to understand the historic reason between have the extra
>>> > "hot_standby" value.  Was it to introduce replication and not disturb
>>> the
>>> > already working "archive" value?  
>> 
>> I think so.
>> 
>>> > If I'm new to Postgres, is there any
>>> > strategic reason to use "archive" at this point if replication is
>>> > something I'll be using in the future?  I'm not seeing any downside to
>>> > "hot_standby" unless I'm missing something fundamental.
>> 
>> Probably not.  You might manage to construct a benchmark in which the
>> extra
>> master-side work is measurable, but it wouldn't be easy.
>> 
>>> There is a semantic difference in that "archive" is limited to "wal
>>> shipping" to a dead-drop area for future PITR.  "hot_standby" implies
>>> that
>>> the wal is being sent to another running system that is immediately
>>> reading
>>> in the information to maintain an exact live copy of the master.
>>> 
>>> As I think both can be used for PITR I don't believe there is much
>>> downside,
>>> technically or with resources, to using hot_standby instead of archive;
>>> but
>>> I do not imagine it having any practical benefit either.
>> 
>> wal_level=archive vs. hot_standby is orthogonal to the mechanism for
>> transmitting WAL and time of applying WAL.  Rather, it dictates whether a
>> PostgreSQL cluster replaying that WAL can accept read-only connections
>> during
>> recovery.  You can send wal_level=archive log data over streaming
>> replication,
>> even synchronous streaming replication.  However, any standby will be
>> unable
>> to accept connections until failover ends recovery.  On the flip side, if
>> you
>> use wal_level=hot_standby, a backup undergoing PITR can accept read-only
>> connections while applying years-old WAL from an archive.  That is useful
>> for
>> verifying, before you end recovery, that you have replayed far enough.
> Went looking for this in the docs...
> 
> http://www.postgresql.org/docs/9.3/interactive/runtime-config-wal.html#GUC-WAL-LEVEL
> 
> So I guess, no-restore/offline/online would be good names (and maybe
> wal_restore_mode instead of wal_level) if we started from scratch.  Note
> that no-restore does not preclude same-system recovery.
> 
> Just something to help me remember...
> 
> David J.

Slightly tangential but are the locking operations associated with the
recent bugfix generated in both (all?) modes or only hot_standby?  I thought
it strange that transient locking operations were output with WAL though I
get it if they are there to support read-only queries.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/History-of-WAL-LEVEL-archive-vs-hot-standby-tp5797717p5797735.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.