Re: [HACKERS] allowing wal_level change at run time
On 8/20/15 3:50 PM, Andres Freund wrote: But, under any scheme to set wal_level automatically, how will the primary know whether it needs to use level archive or hot_standby? I'd have said archive_mode triggered archive and everything else hot_standby. That might be a decent heuristic, but it's not correct if there is no way to override it. People are using archive-only replication with hot standby (for delayed replication, for example). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On 8/19/15 9:32 AM, Andres Freund wrote: I agree that we want both. But requiring a restart is a hard stop, whereas making configuration easier is a soft feature. I don't think it makes that much of a difference for people new to postgres. People new to postgres are not the only audience for this change. To deal with streaming replication, we automatically create slots for replicas, but, by default, *without* having them reserve WAL. The slot name would be determined in some automatic fashion (uuid or something) and stored in a new file in the data directory. That allows us to increase the internal wal_level to hot_standby/archive whenever a replica has connected (and thus a physical slot exists), and to logical whenever a logical slot exists. That seems kind of weird. So every time a replication client connects, we create a new replication slot? Won't they accumulate? No, that's not what I was thinking of. The name of the slot would be stored somewhere in the data directory, and then be reused henceforth. It seems to me that this would effectively replace the wal_level parameter with the presence or absence of a magic replication slot. That doesn't seem like a net improvement. It just replaces one well-known configuration mechanism with a new ad hoc one. Also note that this scheme and any similar one requires merging the archive and hot_standby levels, which was the core of your original proposal [1] It doesn't really, we could continue to keep them separate. I'm proposing to maintain wal_level automatically, so it'd not be configurable anymore, so it'd not matter much, as long as we're not less efficient. But, under any scheme to set wal_level automatically, how will the primary know whether it needs to use level archive or hot_standby? There is no way to know. So if we are going to keep these levels separate, there would need to be a manual way to switch between them. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On 2015-08-20 15:11:02 -0400, Peter Eisentraut wrote: It seems to me that this would effectively replace the wal_level parameter with the presence or absence of a magic replication slot. That doesn't seem like a net improvement. It just replaces one well-known configuration mechanism with a new ad hoc one. Well, with the difference that it can be changed automatically. We could alternatively automagically use ALTER SYSTEM, but that's not really guaranteed to work and isn't available via the replication protocol currently. But I could live with that as well. Also note that this scheme and any similar one requires merging the archive and hot_standby levels, which was the core of your original proposal [1] It doesn't really, we could continue to keep them separate. I'm proposing to maintain wal_level automatically, so it'd not be configurable anymore, so it'd not matter much, as long as we're not less efficient. But, under any scheme to set wal_level automatically, how will the primary know whether it needs to use level archive or hot_standby? I'd have said archive_mode triggered archive and everything else hot_standby. I am still of the opinion though that the difference between those two levels is meaningless and that we should remove archive. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On 2015-08-19 10:49:46 +0200, Magnus Hagander wrote: What happens the first time? Meaning I'm on wal_level=minimal and take a base backup. Then when the replica first connects 10 minutes later, it needs WAL back in time, which was logged at wal_level=minimal. So you'd need to bump it up whenever a base backup is done -- but then you can't drop it back down again or your base backup will be useless. Or am I missing something? Nope. Requiring pg_basebackup to automatically create such a 'non-reserving' slot doesn't seem to be too bad to me. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On 2015-08-18 21:47:51 -0400, Peter Eisentraut wrote: On 8/18/15 1:46 PM, Andres Freund wrote: I don't think not requiring restarts is sufficient, having to twiddle a bunch of parameters manually still is a lot more effort than people see as necessary. I agree that we want both. But requiring a restart is a hard stop, whereas making configuration easier is a soft feature. I don't think it makes that much of a difference for people new to postgres. To deal with streaming replication, we automatically create slots for replicas, but, by default, *without* having them reserve WAL. The slot name would be determined in some automatic fashion (uuid or something) and stored in a new file in the data directory. That allows us to increase the internal wal_level to hot_standby/archive whenever a replica has connected (and thus a physical slot exists), and to logical whenever a logical slot exists. That seems kind of weird. So every time a replication client connects, we create a new replication slot? Won't they accumulate? No, that's not what I was thinking of. The name of the slot would be stored somewhere in the data directory, and then be reused henceforth. Do we need the replication slot, or could we just trigger this on the existence of a replication client? I don't think that can work, because a replication connection obviously can go away temporarily. If we'd then fall back to wal_level minimal the standby would be cut off. Also note that this scheme and any similar one requires merging the archive and hot_standby levels, which was the core of your original proposal [1] It doesn't really, we could continue to keep them separate. I'm proposing to maintain wal_level automatically, so it'd not be configurable anymore, so it'd not matter much, as long as we're not less efficient. , which was then objected to, which subsequently lead to Robert's proposal to make wal_level SIGHUP, which lead to my message, which lead to your message, which is similar to your initial one. Somehow we have to find a way break out of this circle. ;-) My reading of the objections was that it was primarily about increasing the amount of WAL in the default configuration, and this proposal wouldn't anymore. An out-of-the-box configuration wouldn't emit more WAL than today, until it's been used with streaming rep. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On 18 August 2015 at 18:46, Andres Freund and...@anarazel.de wrote: ISTM that it's not too hard to a) make archive_mode PGC_SIGHUP b) make wal_level PGC_SIGHUP +1 c) automatically increase wal_level to logical whenever a logical replication slot is defined -1 It would be easier to just have wal_level = logical always, but allow it to be set lower if desired. Increasing wal_level dynamically might otherwise happen too late. it seems considerably harder to d) make max_wal_senders PGC_SIGHUP e) make max_replication_slots PGC_SIGHUP because they need shmem, locks, and everything. Therefore I propose something slightly different: We change the default of max_wal_senders, max_replication_slots, to some reasonably high setting Agreed, I suggest 8 as the default for each. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] allowing wal_level change at run time
On 2015-08-19 17:51:47 +0200, Magnus Hagander wrote: That's doable - but what about manual base backups? And if they don't go away, what about the ones that are generated by the nightly/weekly/hourly/whatever pg_basebackup -x ones? Good questions. I guess we could just make do_pg_start_backup() elevate the WAL level (using a fixed slot name or some other mechanism) he first time they're run, until there's explicit admin action. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On Wed, Aug 19, 2015 at 3:34 PM, Andres Freund and...@anarazel.de wrote: On 2015-08-19 10:49:46 +0200, Magnus Hagander wrote: What happens the first time? Meaning I'm on wal_level=minimal and take a base backup. Then when the replica first connects 10 minutes later, it needs WAL back in time, which was logged at wal_level=minimal. So you'd need to bump it up whenever a base backup is done -- but then you can't drop it back down again or your base backup will be useless. Or am I missing something? Nope. Requiring pg_basebackup to automatically create such a 'non-reserving' slot doesn't seem to be too bad to me. That's doable - but what about manual base backups? And if they don't go away, what about the ones that are generated by the nightly/weekly/hourly/whatever pg_basebackup -x ones? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [HACKERS] allowing wal_level change at run time
On Tue, Aug 18, 2015 at 7:46 PM, Andres Freund and...@anarazel.de wrote: On 2015-08-18 13:24:54 -0400, Peter Eisentraut wrote: On 8/18/15 12:35 PM, Robert Haas wrote: If archive_mode=on or max_wal_senders0, then we need at least wal_level=archive. Otherwise wal_level=minimal is enough. Totally forgot about max_wal_senders. However, the thread I linked to earlier aimed for a different master plan (or if not, I'm aiming for it now). There is camp 1, which wants to keep all the defaults the same, for performance or something like that. And there is camp 2, which wants to have a replication-friendly setup by default. Instead of fighting over this, your idea was to be able to switch between 1 and 2 easily (which means in particular without restarts). I don't think not requiring restarts is sufficient, having to twiddle a bunch of parameters manually still is a lot more effort than people see as necessary. The only reason I think changing the default is a good approach is that it's doable within a relatively short amount of time. But if we tie the effective wal_level to archive_mode or max_wal_senders, both of which are restart-only, then we haven't gained anything. (We would have removed half a GUC parameter, effectively. Not bad, but not very exciting.) ISTM that it's not too hard to a) make archive_mode PGC_SIGHUP b) make wal_level PGC_SIGHUP c) automatically increase wal_level to logical whenever a logical replication slot is defined it seems considerably harder to d) make max_wal_senders PGC_SIGHUP e) make max_replication_slots PGC_SIGHUP because they need shmem, locks, and everything. Therefore I propose something slightly different: We change the default of max_wal_senders, max_replication_slots, to some reasonably high setting and make wal_level an internal automagically determined variable. archive_mode = on automatically increases the level to what's now hot-standby. To deal with streaming replication, we automatically create slots for replicas, but, by default, *without* having them reserve WAL. The slot name would be determined in some automatic fashion (uuid or something) and stored in a new file in the data directory. That allows us to increase the internal wal_level to hot_standby/archive whenever a replica has connected (and thus a physical slot exists), and to logical whenever a logical slot exists. What happens the first time? Meaning I'm on wal_level=minimal and take a base backup. Then when the replica first connects 10 minutes later, it needs WAL back in time, which was logged at wal_level=minimal. So you'd need to bump it up whenever a base backup is done -- but then you can't drop it back down again or your base backup will be useless. Or am I missing something? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [HACKERS] allowing wal_level change at run time
On Tue, Aug 18, 2015 at 7:59 AM, Peter Eisentraut pete...@gmx.net wrote: How would we handle decreases at run time? We can prevent =archive - minimal if archiving is running or there are physical replication slots, and we can prevent logical - something less if there are logical replication slots, but AFAICT, we don't have a way to check whether anyone currently needs level hot_standby. What do you mean by prevent? If the user edits postgresql.conf and reduces the setting, and then reloads the configuration file, they have a right to expect that the changes got applied. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On Tue, Aug 18, 2015 at 1:59 PM, Peter Eisentraut pete...@gmx.net wrote: In [1], it was discussed to make wal_level changeable at run time (PGC_SIGHUP), as part of an effort to make replication easier to set up and/or provide better defaults. I was wondering what it would take to do that. I looks like increasing the setting is doable, as long as we WAL-log the change using existing facilities. I don't understand the hot_standby - logical transition, so maybe something else is necessary there. How would we handle decreases at run time? We can prevent =archive - minimal if archiving is running or there are physical replication slots, and we can prevent logical - something less if there are logical replication slots, but AFAICT, we don't have a way to check whether anyone currently needs level hot_standby. (Thread [1] originally proposed to get rid of the archive/hot_standby distinction, which would help here.) Or we could just let users make their own mess if they want to. I still think we should get rid of the difference between archive/hot_standby. It creates more trouble than I've ever seen it save. I think we can safely label that as something we added because we didn't know if it was going to be needed (because back in 9.0 nobody knew what the impact *really* would be), but now we know it's not necessary so we can just get rid of it. Especially as it makes something like this easier. Removing complexity of such important parts of the code is a feature in itself! -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [HACKERS] allowing wal_level change at run time
On 2015-08-18 13:24:54 -0400, Peter Eisentraut wrote: On 8/18/15 12:35 PM, Robert Haas wrote: If archive_mode=on or max_wal_senders0, then we need at least wal_level=archive. Otherwise wal_level=minimal is enough. Totally forgot about max_wal_senders. However, the thread I linked to earlier aimed for a different master plan (or if not, I'm aiming for it now). There is camp 1, which wants to keep all the defaults the same, for performance or something like that. And there is camp 2, which wants to have a replication-friendly setup by default. Instead of fighting over this, your idea was to be able to switch between 1 and 2 easily (which means in particular without restarts). I don't think not requiring restarts is sufficient, having to twiddle a bunch of parameters manually still is a lot more effort than people see as necessary. The only reason I think changing the default is a good approach is that it's doable within a relatively short amount of time. But if we tie the effective wal_level to archive_mode or max_wal_senders, both of which are restart-only, then we haven't gained anything. (We would have removed half a GUC parameter, effectively. Not bad, but not very exciting.) ISTM that it's not too hard to a) make archive_mode PGC_SIGHUP b) make wal_level PGC_SIGHUP c) automatically increase wal_level to logical whenever a logical replication slot is defined it seems considerably harder to d) make max_wal_senders PGC_SIGHUP e) make max_replication_slots PGC_SIGHUP because they need shmem, locks, and everything. Therefore I propose something slightly different: We change the default of max_wal_senders, max_replication_slots, to some reasonably high setting and make wal_level an internal automagically determined variable. archive_mode = on automatically increases the level to what's now hot-standby. To deal with streaming replication, we automatically create slots for replicas, but, by default, *without* having them reserve WAL. The slot name would be determined in some automatic fashion (uuid or something) and stored in a new file in the data directory. That allows us to increase the internal wal_level to hot_standby/archive whenever a replica has connected (and thus a physical slot exists), and to logical whenever a logical slot exists. Now, that'll mean that the wal_level doesn't decrease automatically until a slot has been dropped. But that seems relatively harmless if it's not reserving WAL. Too crazy? Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On 8/18/15 12:35 PM, Robert Haas wrote: If archive_mode=on or max_wal_senders0, then we need at least wal_level=archive. Otherwise wal_level=minimal is enough. Totally forgot about max_wal_senders. However, the thread I linked to earlier aimed for a different master plan (or if not, I'm aiming for it now). There is camp 1, which wants to keep all the defaults the same, for performance or something like that. And there is camp 2, which wants to have a replication-friendly setup by default. Instead of fighting over this, your idea was to be able to switch between 1 and 2 easily (which means in particular without restarts). But if we tie the effective wal_level to archive_mode or max_wal_senders, both of which are restart-only, then we haven't gained anything. (We would have removed half a GUC parameter, effectively. Not bad, but not very exciting.) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On Wed, Aug 19, 2015 at 2:46 AM, Andres Freund and...@anarazel.de wrote: On 2015-08-18 13:24:54 -0400, Peter Eisentraut wrote: But if we tie the effective wal_level to archive_mode or max_wal_senders, both of which are restart-only, then we haven't gained anything. (We would have removed half a GUC parameter, effectively. Not bad, but not very exciting.) ISTM that it's not too hard to a) make archive_mode PGC_SIGHUP b) make wal_level PGC_SIGHUP c) automatically increase wal_level to logical whenever a logical replication slot is defined Switching archive_mode and wal_level to PGC_SIGHUP would be nice. We can already faking that by setting archive_command to '/usr/bin/true' for the first one or similar but that's not really the same as switching it completely to off. it seems considerably harder to d) make max_wal_senders PGC_SIGHUP e) make max_replication_slots PGC_SIGHUP because they need shmem, locks, and everything. Yes. Those have effects on the shared memory size allocated at postmaster startup. Therefore I propose something slightly different: We change the default of max_wal_senders, max_replication_slots, to some reasonably high setting and make wal_level an internal automagically determined variable. archive_mode = on automatically increases the level to what's now hot-standby. Or surely max_wal_senders 0. To deal with streaming replication, we automatically create slots for replicas, but, by default, *without* having them reserve WAL. The slot name would be determined in some automatic fashion (uuid or something) and stored in a new file in the data directory. That allows us to increase the internal wal_level to hot_standby/archive whenever a replica has connected (and thus a physical slot exists), and to logical whenever a logical slot exists. So, wal_level is automatically bumped to hot_standby when the physical slot is created (or logical for a logical slot) even if WAL is not reserved, right? When would those slots be created? Now, that'll mean that the wal_level doesn't decrease automatically until a slot has been dropped. But that seems relatively harmless if it's not reserving WAL. Too crazy? Perhaps, craziest ideas are usually worth it :) At least, we have a couple of things we can consider: - Make archive_mode SIGHUP - Remove wal_level and set it as follows: -- archive/hot_standby if max_wal_sender 0 (depends surely on restart) or archive_mode = on (gets more complicated if archive_mode switches to SIGHUP) at startup. -- logical should a logical slot be created. -- If max_wal_senders = 0 and archive_mode = off, switch wal_level to hot_standby once a physical slot is created. In short switching archive_mode to SIGHUP has as requirement to get rid of wal_level first. Regards, -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On 8/18/15 1:46 PM, Andres Freund wrote: I don't think not requiring restarts is sufficient, having to twiddle a bunch of parameters manually still is a lot more effort than people see as necessary. I agree that we want both. But requiring a restart is a hard stop, whereas making configuration easier is a soft feature. ISTM that it's not too hard to a) make archive_mode PGC_SIGHUP That has been contentious in the past, but I would agree that it seems that it should be doable. (I consider archiving a legacy feature at this point, so for this purpose I don't really care whether it's possible.) b) make wal_level PGC_SIGHUP c) automatically increase wal_level to logical whenever a logical replication slot is defined it seems considerably harder to d) make max_wal_senders PGC_SIGHUP e) make max_replication_slots PGC_SIGHUP because they need shmem, locks, and everything. check Therefore I propose something slightly different: We change the default of max_wal_senders, max_replication_slots, to some reasonably high setting and make wal_level an internal automagically determined variable. archive_mode = on automatically increases the level to what's now hot-standby. check To deal with streaming replication, we automatically create slots for replicas, but, by default, *without* having them reserve WAL. The slot name would be determined in some automatic fashion (uuid or something) and stored in a new file in the data directory. That allows us to increase the internal wal_level to hot_standby/archive whenever a replica has connected (and thus a physical slot exists), and to logical whenever a logical slot exists. That seems kind of weird. So every time a replication client connects, we create a new replication slot? Won't they accumulate? Do we need the replication slot, or could we just trigger this on the existence of a replication client? Also note that this scheme and any similar one requires merging the archive and hot_standby levels, which was the core of your original proposal [1], which was then objected to, which subsequently lead to Robert's proposal to make wal_level SIGHUP, which lead to my message, which lead to your message, which is similar to your initial one. Somehow we have to find a way break out of this circle. ;-) [1] http://www.postgresql.org/message-id/20150203124317.ga24...@awork2.anarazel.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
Peter Eisentraut pete...@gmx.net writes: On 8/18/15 8:48 AM, Robert Haas wrote: What do you mean by prevent? If the user edits postgresql.conf and reduces the setting, and then reloads the configuration file, they have a right to expect that the changes got applied. We have certain checks in place that require a minimum wal_level before other things are allowed. For example, turning on archiving requires wal_level = archive. The issue is then, if you have archiving on and then turn wal_level to minimal at run time, we need to prevent that to preserve the integrity of the original check. IIRC, the reason for having a wal_level parameter separate from those other ones was precisely that only wal_level had to be PGC_POSTMASTER, and you could change the others if you wanted. If we are going to fix the mechanisms to allow dynamic changing in wal log verbosity, maybe we could simply get rid of wal_level as a user-settable parameter, and have its effective value be inferred from the active settings of the other parameters. IOW: let's simplify, not complicate even further. Try to get rid of the interdependencies between settable parameters. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On 8/18/15 8:48 AM, Robert Haas wrote: On Tue, Aug 18, 2015 at 7:59 AM, Peter Eisentraut pete...@gmx.net wrote: How would we handle decreases at run time? We can prevent =archive - minimal if archiving is running or there are physical replication slots, and we can prevent logical - something less if there are logical replication slots, but AFAICT, we don't have a way to check whether anyone currently needs level hot_standby. What do you mean by prevent? If the user edits postgresql.conf and reduces the setting, and then reloads the configuration file, they have a right to expect that the changes got applied. We have certain checks in place that require a minimum wal_level before other things are allowed. For example, turning on archiving requires wal_level = archive. The issue is then, if you have archiving on and then turn wal_level to minimal at run time, we need to prevent that to preserve the integrity of the original check. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On Tue, Aug 18, 2015 at 9:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: On 8/18/15 8:48 AM, Robert Haas wrote: What do you mean by prevent? If the user edits postgresql.conf and reduces the setting, and then reloads the configuration file, they have a right to expect that the changes got applied. We have certain checks in place that require a minimum wal_level before other things are allowed. For example, turning on archiving requires wal_level = archive. The issue is then, if you have archiving on and then turn wal_level to minimal at run time, we need to prevent that to preserve the integrity of the original check. IIRC, the reason for having a wal_level parameter separate from those other ones was precisely that only wal_level had to be PGC_POSTMASTER, and you could change the others if you wanted. If we are going to fix the mechanisms to allow dynamic changing in wal log verbosity, maybe we could simply get rid of wal_level as a user-settable parameter, and have its effective value be inferred from the active settings of the other parameters. Mmm, I like that idea. If we can do it, it seems much cleaner that way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On Tue, Aug 18, 2015 at 12:27 PM, Peter Eisentraut pete...@gmx.net wrote: On 8/18/15 9:50 AM, Tom Lane wrote: IIRC, the reason for having a wal_level parameter separate from those other ones was precisely that only wal_level had to be PGC_POSTMASTER, and you could change the others if you wanted. I think you are thinking of having split archive_mode/archive_command, so you can change archive_command at run time. If we are going to fix the mechanisms to allow dynamic changing in wal log verbosity, maybe we could simply get rid of wal_level as a user-settable parameter, and have its effective value be inferred from the active settings of the other parameters. That would be nice, but those other parameters aren't really things that we can easily look at. In the old days, you could say that archive_mode = on was a pretty sure sign that you'd need wal_level = archive, but nowadays you can run replication without archiving. We could dial wal_level up and down every time someone connects to stream WAL, but that would surely lead to complications. Also, we have no way of knowing whether someone needs wal_level hot_standby until the WAL is on the standby and the standby decides to set hot_standby = on. The permutations of what could possibly influence this setting are quite enormous, if you consider archiving, streaming, hot standby, hot standby feedback, replication slots, etc., and synchronizing all of that sounds like a mess. If archive_mode=on or max_wal_senders0, then we need at least wal_level=archive. Otherwise wal_level=minimal is enough. If we eliminate the distinction between wal_level=archive and wal_level=hot_standby, then we just need a way to distinguish between hot_standby and logical. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] allowing wal_level change at run time
On 8/18/15 9:50 AM, Tom Lane wrote: IIRC, the reason for having a wal_level parameter separate from those other ones was precisely that only wal_level had to be PGC_POSTMASTER, and you could change the others if you wanted. I think you are thinking of having split archive_mode/archive_command, so you can change archive_command at run time. If we are going to fix the mechanisms to allow dynamic changing in wal log verbosity, maybe we could simply get rid of wal_level as a user-settable parameter, and have its effective value be inferred from the active settings of the other parameters. That would be nice, but those other parameters aren't really things that we can easily look at. In the old days, you could say that archive_mode = on was a pretty sure sign that you'd need wal_level = archive, but nowadays you can run replication without archiving. We could dial wal_level up and down every time someone connects to stream WAL, but that would surely lead to complications. Also, we have no way of knowing whether someone needs wal_level hot_standby until the WAL is on the standby and the standby decides to set hot_standby = on. The permutations of what could possibly influence this setting are quite enormous, if you consider archiving, streaming, hot standby, hot standby feedback, replication slots, etc., and synchronizing all of that sounds like a mess. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
