Thread: A Modest Upgrade Proposal

Folks,

We have a problem.

With all due respect to the valiant efforts of people over the years
who have tried to make an upgrade-in-place system work, I would like
to note that such a system faces what I believe are insurmountable
barriers to being done correctly.  I will then propose an alternative.

We have seen each one of the following on multiple occasions:

- It's extraordinarily unglamorous work.  This further restricts the already tiny pool of people who might work on it.
Ifsomebody has a sustainable way to increase the glamour, that might help, but...
 

- To do correctly, it requires broad and intimate knowledge of the storage system and the systems below it (what is and
isn'tactually invariant across filesystems and kernels, e.g.) at a level that even most core engine hackers do not
possess.

- It's always done under extreme time pressure, namely between feature freeze (more properly, all-other-code-freeze, if
it'sto be actually correct) and release.  We haven't even attempted the "properly" version for what I hope are pretty
obviousreasons.
 

- It's extraordinarily difficult to test even normal cases, let alone corner cases, especially in light of the time
pressure.

- Failure modes tend to be silent (or at least whispering) data corruption, not infrequently permanent.

That all sounds grim because it is.

HOWEVER

All is not lost.

We can relax the in-place requirement because of the economics of
computing.  The components of a node have been getting drastically
cheaper for decades while (amazingly, if you think about it)
increasing in quality. Rented ("cloud") nodes have gotten steadily
cheaper and better, too, although not over quite as long a haul.

In light of the above, it is perfectly reasonable to require, at least
temporarily, setting up duplicate storage, or another node.

I am aware that some cases exist where this is not possible, but I
don't think we should twist ourselves into pretzels to accommodate a
tiny minority of our users, which my experience in the field leads me
to believe is the case.

As a relatively (to our users) minor course correction, I would like
to propose the following:

- Keep the current pg_upgrade code, but put loud deprecation warnings all over it, most emphatically all over its
documentation.

- Develop a logical upgrade path as a part of the (Yay! Sexy!) logical replication that's already in large part built.

This path would, of course, run either locally or across a network,
and be testable in both cases.  There would be a downgrade path,
namely switching origin nodes.

What say?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 05/16/2016 05:52 PM, David Fetter wrote:
> Folks,

> This path would, of course, run either locally or across a network,
> and be testable in both cases.  There would be a downgrade path,
> namely switching origin nodes.
>
> What say?

What happens when the database is 5TB in size and you only have 500GB 
available but that 500GB won't exhaust before the 18 month lease expiry?

JD


>
> Cheers,
> David.
>


-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

David Fetter wrote:

> As a relatively (to our users) minor course correction, I would like
> to propose the following:

> - Develop a logical upgrade path as a part of the (Yay! Sexy!) logical
>   replication that's already in large part built.
>   
> This path would, of course, run either locally or across a network,
> and be testable in both cases.

This is one use case that pglogical intends to fulfill.  If you're able
to contribute to that project, I'm sure many would appreciate it.  Right
now the hottest question seems to be: is this something that should be
an extension, or should it be part of core with its own set of DDL etc?
The current patch is geared towards the former, so if the community at
large prefers to have it as the latter and would oppose the former, now
is the time to speak up so that the course can be corrected.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 05/16/2016 06:22 PM, Alvaro Herrera wrote:
> David Fetter wrote:
>
>> As a relatively (to our users) minor course correction, I would like
>> to propose the following:
>
>> - Develop a logical upgrade path as a part of the (Yay! Sexy!) logical
>>    replication that's already in large part built.
>>
>> This path would, of course, run either locally or across a network,
>> and be testable in both cases.
>
> This is one use case that pglogical intends to fulfill.  If you're able
> to contribute to that project, I'm sure many would appreciate it.  Right
> now the hottest question seems to be: is this something that should be
> an extension, or should it be part of core with its own set of DDL etc?
> The current patch is geared towards the former, so if the community at
> large prefers to have it as the latter and would oppose the former, now
> is the time to speak up so that the course can be corrected.

Alvaro,

Thank you for bringing this to light. Is there a contributor FAQ for 
PgLogical so that people can help?

Sincerely,

jD

-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

Joshua D. Drake wrote:
> Alvaro,
> 
> Thank you for bringing this to light. Is there a contributor FAQ for
> PgLogical so that people can help?

Hmm, I don't think there's any contributor FAQ.  It's supposed to be a
regular patch submission, after all -- it needs user interface review, a
review of the communication protocol, tests, code-level review, etc.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 05/16/2016 06:32 PM, Alvaro Herrera wrote:
> Joshua D. Drake wrote:
>
>> Alvaro,
>>
>> Thank you for bringing this to light. Is there a contributor FAQ for
>> PgLogical so that people can help?
>
> Hmm, I don't think there's any contributor FAQ.  It's supposed to be a
> regular patch submission, after all -- it needs user interface review, a
> review of the communication protocol, tests, code-level review, etc.

O.k. so we should discuss all PgLogical things here and not on Github? I 
am just trying to figure out what the proper mode here is. I don't think 
anybody wants us to double up efforts.

JD

>


-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

On Mon, May 16, 2016 at 06:20:34PM -0700, Joshua D. Drake wrote:
> On 05/16/2016 05:52 PM, David Fetter wrote:
> > Folks,
> 
> > This path would, of course, run either locally or across a
> > network, and be testable in both cases.  There would be a
> > downgrade path, namely switching origin nodes.
> > 
> > What say?
> 
> What happens when the database is 5TB in size and you only have
> 500GB available but that 500GB won't exhaust before the 18 month
> lease expiry?

We cannot prepare for every eventuality.

The downside risk of a binary upgrade in the type of case you describe
is in no conceivable instance better than "rent or borrow another
server with more storage attached and replicate to it."

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Joshua D. Drake wrote:
> On 05/16/2016 06:32 PM, Alvaro Herrera wrote:
> >Joshua D. Drake wrote:
> >
> >>Alvaro,
> >>
> >>Thank you for bringing this to light. Is there a contributor FAQ for
> >>PgLogical so that people can help?
> >
> >Hmm, I don't think there's any contributor FAQ.  It's supposed to be a
> >regular patch submission, after all -- it needs user interface review, a
> >review of the communication protocol, tests, code-level review, etc.
> 
> O.k. so we should discuss all PgLogical things here and not on Github? I am
> just trying to figure out what the proper mode here is. I don't think
> anybody wants us to double up efforts.

As far as I am concerned, by all means ignore Github and discuss issues
in pgsql-hackers.  Github is being used only because it provides a
convenient Git mirror, which is said to be easier to use than attaching
huge patches back and forth.  I think it may be more convenient also to
keep track of issues people have reported so that they can be marked as
fixed in commit messages, etc.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, May 16, 2016 at 9:22 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> David Fetter wrote:
>> As a relatively (to our users) minor course correction, I would like
>> to propose the following:
>
>> - Develop a logical upgrade path as a part of the (Yay! Sexy!) logical
>>   replication that's already in large part built.
>>
>> This path would, of course, run either locally or across a network,
>> and be testable in both cases.
>
> This is one use case that pglogical intends to fulfill.  If you're able
> to contribute to that project, I'm sure many would appreciate it.  Right
> now the hottest question seems to be: is this something that should be
> an extension, or should it be part of core with its own set of DDL etc?
> The current patch is geared towards the former, so if the community at
> large prefers to have it as the latter and would oppose the former, now
> is the time to speak up so that the course can be corrected.

There was an unconference session on this topic at PGCon and quite a
number of people there stated that they found DDL to be an ease-of-use
feature and wanted to have it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, May 16, 2016 at 8:52 PM, David Fetter <david@fetter.org> wrote:
> In light of the above, it is perfectly reasonable to require, at least
> temporarily, setting up duplicate storage, or another node.
>
> I am aware that some cases exist where this is not possible, but I
> don't think we should twist ourselves into pretzels to accommodate a
> tiny minority of our users, which my experience in the field leads me
> to believe is the case.

So, on the one hand, I agree that logical replication is a great way
to facilitate major version upgrades.  On the other hand, I think it's
completely wrong to suppose that only a tiny minority of people can't
use it.  In some cases, hardware availability is definitely an issue.
But even when people have the hardware, being able to cleanly do a
cutover from one master to another is not necessarily something people
are set up to do.  Getting that to work well requires more brainpower
than many users are willing to give to their database.  A lot of
people want to just shut the database down, upgrade it, and start it
back up.

pg_upgrade does that, kinda.  I'd like to have something better, but
in the absence of that, I think it's quite wrong to think about
deprecating it, even if we had logical replication fully integrated
into core today.  Which we by no means do.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 07/07/2016 01:10 PM, Robert Haas wrote:
> On Mon, May 16, 2016 at 8:52 PM, David Fetter <david@fetter.org> wrote:
>> In light of the above, it is perfectly reasonable to require, at least
>> temporarily, setting up duplicate storage, or another node.

> pg_upgrade does that, kinda.  I'd like to have something better, but
> in the absence of that, I think it's quite wrong to think about
> deprecating it, even if we had logical replication fully integrated
> into core today.  Which we by no means do.

I would much rather see more brain power put into pg_upgrade or in place 
upgrades than logical replication (as a upgrade solution).

JD

>


-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

On 07/07/2016 05:14 PM, Simon Riggs wrote:

>     I would much rather see more brain power put into pg_upgrade or in
>     place upgrades than logical replication (as a upgrade solution).
>
>
> Why is that?

First, let me state that I don't have a problem with logical replication 
as an upgrade solution. I have used one form or another many times. I 
have also used pg_upgrade and will use pg_upgrade every single time I 
can over replication (even pg_logical which is reasonably simple) if I 
can. *KISS* is the mantra.

I certainly think logical replication has an absolute place (especially 
if upgrading from something like 9.2 -> 9.5). I just don't think it is 
as useful (generally) as a solid pg_upgrade or in-place upgrade solution.

We have had logical replication as a solution for over a decade. First 
there was slony then londiste and then others. They all suffered from 
various issues and limitations.
* Horrible overhead* Long running transaction* Need for lots of extra space

It is true that something like pg_logical doesn't suffer from those 
three things but it does suffer from others:
* No DDL - Agreed, not "required" but certainly a very nice feature.
* Lack of simplicity

Users, like simple. It is one of the key reasons there is a migration to 
the cloud, simplicity. Everything from scaling, to pricing, to 
provisioning etc...

If I take a step back and say to myself, "What would *really* rock in 
terms of PostgreSQL upgrades?" The answer is pretty simple:

apt-get update; apt-get upgrade;
service postgresql upgrade;

Which would pass a flag to "insert technology here" that started 
PostgreSQL in a mode that told it, "Hey, you are going to need to check 
a few things and probably modify a few things before you enter "ready 
for transactions"".

I am fully aware that what I am saying is not easy. There are a whole 
ton of issues (what if we are replicating to a slave?).

Anyway, that's why. I am by far more a consultant than an engineer now 
and I can only relay what I run into when I speak either at conferences 
or clients.

Sincerely,

JD



        



-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

On Thu, Jul 7, 2016 at 7:15 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Yes, I ran the unconference session. It was a shame you weren't able to stay
> for the whole discussion.

I thought I sat through, at least, most of it, but you barely gave
anyone else a chance to talk, which kind of misses the point of an
unconference.  The portion which I attended was not about how to move
the development of the feature forward, but just involved describing
it.  I thought it was a shame that the time wasn't used better.

> We all agreed that an in-core solution was desirable, if only for wider
> adoption.

Yep.

> About half the people wanted DDL and about half the people didn't. When we
> discussed why we wanted DDL there wasn't any answers apart from the thought
> that we want to be able to backup the replication configurations, which
> seemed to be possible with or without DDL. Any such backup would need to be
> easily removed from the objects themselves, to avoid external dependencies
> on making recovery work.

I really don't think that's accurate.  There might have been 50% of
people who thought that not having DDL was acceptable, but I think
there were very few people who found it preferable.

> Chris Browne finally summed it up by saying we could wait on having DDL
> until some time later, once we've decided on things like how we configure
> it, how we secure it and what/how to store it in the catalog. "We could
> probably live without DDL in the first version."

Right.  In other words, DDL would be desirable, but he'd be willing to
live without it if that somehow made things easier.  But it really
doesn't.  Adding new DDL commands is not particularly difficult.

> Personally, I'm in the group of people that don't see the need for DDL.
> There are already many successful features that don't utilize DDL, such as
> backup, advisory locks and some features that use DDL that don't really need
> to such as LISTEN/NOTIFY, full text search etc.. Also note that both Oracle
> and SQLServer have moved away from DDL in favour of function APIs, most
> NoSQL databases and almost all languages prefer functional interfaces over
> parsed text languages, so I don't see a huge industry revival for DDL as
> means of specifying things.

DDL is our standard way of getting things into the system catalogs.
We have no system catalog metadata that is intended to be populated by
any means other than DDL.  If you want to add a column to a table, you
say ALTER TABLE .. ADD COLUMN.  If you want to add a column to an
extension, you say ALTER EXTENSION .. ADD TABLE.   If you want to add
an option to a foreign table, you say ALTER FOREIGN TABLE .. OPTIONS
(ADD ..).  Therefore, I think it is entirely reasonable and obviously
consistent with existing practice that if you want to add a table to a
replication set, you should write ALTER REPLICATION SET .. ADD TABLE.
I don't understand why logical replication should be the one feature
that departs from the way that all of our other features work.  Sure,
we have other features that do not involve DDL, but (1) one of your
examples is full text search, which of course does have DDL, and was
moved from an interface that did not involve DDL to one that did
because the latter is better and (2) your other examples don't involve
defining catalog contents, which makes them apples-to-oranges
comparisons.

The burden of proof isn't on me to demonstrate why this feature "needs
DDL"; it's on you to explain why replication-related operations that
establish persistent database state don't need to behave just like all
other commands.  Really, where this jumped the shark for me is when
you argued that this stuff didn't even need pg_dump support.  Come on.
This feature doesn't get a pass from handling all of the things that
every existing similar feature needs to deal with.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Simon Riggs <simon@2ndquadrant.com> writes:
> On 8 July 2016 at 02:41, Robert Haas <robertmhaas@gmail.com> wrote:
>> DDL is our standard way of getting things into the system catalogs.
>> We have no system catalog metadata that is intended to be populated by
>> any means other than DDL.

> pg_am has existed for decades without supporting DDL

That argument has been obsoleted by events ;-) ... and in any case, the
reason we went without CREATE ACCESS METHOD for so long was not that we
encouraged "INSERT INTO pg_am" but that non-core index AMs were
effectively unsupported anyway, until we thought of a reasonable way to
let them generate WAL.  Without the WAL stumbling block, I'm sure we would
have built CREATE ACCESS METHOD long ago.  It's just way too hard to deal
with issues like cross-version changes otherwise.

> and we have gone to
> great lengths over many years to allow catalog tables to be
> inserted/updated/deleted by normal SQL rather than DDL, so not all catalog
> access is via DDL.

But *all* of that is on "if you break it you get to keep both pieces"
terms.  In particular we do not promise the catalogs will remain stable
across versions, so that inserts or updates on catalogs are very likely
to break in new versions.  I think that all such operations should be
understood as emergency procedures, not recommended standard practice.

> One of my examples was full text search and it does have
> DDL, but that was an anti-example; all the feedback I have is that it was
> much easier to use before it had DDL and that forcing it to use DDL pretty
> much killed it for most users.

That's just unsupported FUD.  I would say that most of the problems we've
had with text search DDL came from the fact that previously people had
done things in other ways and transitioning was hard.  That experience
doesn't make me want to repeat it; but building a feature that's supposed
to be managed by direct catalog updates is precisely repeating that
mistake.

I'm okay with things like replication configuration being managed outside
the system catalogs entirely (as indeed they are now).  But if a feature
has a catalog, it should have DDL to manipulate the catalog.  Direct SQL
on a catalog should *never* become standard operating procedure.
        regards, tom lane

Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:

> > pg_am has existed for decades without supporting DDL
> 
> That argument has been obsoleted by events ;-) ... and in any case, the
> reason we went without CREATE ACCESS METHOD for so long was not that we
> encouraged "INSERT INTO pg_am" but that non-core index AMs were
> effectively unsupported anyway, until we thought of a reasonable way to
> let them generate WAL.  Without the WAL stumbling block, I'm sure we would
> have built CREATE ACCESS METHOD long ago.

Note that the alternative to DDL-based replication handling is not
INSERT INTO pg_replication, but a function-based interface such as
SELECT pg_replication_node_create(foo, bar); so there's no need to
hardcode catalog definitions; nor there is a need to skip backup-ability
of logical replication config: pg_dump support can be added by having it
output function calls -- not catalog INSERTs!

The only difference between DDL and no DDL is that a function-based
interface can be added with a few pg_proc.h entries, whereas the DDL
stuff requires gram.y additions, new nodes, etc.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 08/07/16 10:59, Simon Riggs wrote:
> On 8 July 2016 at 03:55, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     > One of my examples was full text search and it does have
>     > DDL, but that was an anti-example; all the feedback I have is that it was
>     > much easier to use before it had DDL and that forcing it to use DDL pretty
>     > much killed it for most users.
>
>     That's just unsupported FUD.
>
>
> No, its me relaying opinions I have heard back to this list, for the
> purposes of understanding them.
>
> ("Fear, Uncertainty and Doubt" or FUD is doesn't apply here, unless its
> meant in the same way as "that's rubbish, I disagree".)
>
>     I would say that most of the problems we've
>     had with text search DDL came from the fact that previously people had
>     done things in other ways and transitioning was hard.  That experience
>     doesn't make me want to repeat it; but building a feature that's
>     supposed
>     to be managed by direct catalog updates is precisely repeating that
>     mistake.
>
>     I'm okay with things like replication configuration being managed
>     outside
>     the system catalogs entirely (as indeed they are now).  But if a feature
>     has a catalog, it should have DDL to manipulate the catalog.
>
>
> It's a good rule. In this case all it does is move the discussion to
> "should it have a catalog?".
>

I think it should have catalog for most things. Otherwise it will be 
really hard to track mapping of tables to replication sets, etc. We'd 
have to invent mechanism of tracking dependencies outside of catalog, 
which is not endeavor that I want to go through. The feature is complex 
enough without this. Which means there should be DDL for those things as 
well. So the work I am doing now is based on this assumption. The DDL 
versus function from the point of implementation means tons of 
additional boilerplate code but it's not complex thing to do.

One interesting thing will be making sure we can replicate from physical 
standby in the future as you mentioned elsewhere in the thread but I 
think that should be possible as long as you define the catalogs on 
master (not really sure yet if there are any barriers or not).

About the pg_dump support. While I don't think we'll necessarily want to 
dump all the information related to logical replication (like 
subscriptions), I definitely think we should dump replication sets and 
their membership info for example.

--   Petr Jelinek                  http://www.2ndQuadrant.com/  PostgreSQL Development, 24x7 Support, Training &
Services

On 08/07/16 12:47, Craig Ringer wrote:
> On 8 July 2016 at 09:41, Robert Haas <robertmhaas@gmail.com
> <mailto:robertmhaas@gmail.com>> wrote:
>
>     If you want to add a column to a table, you
>     say ALTER TABLE .. ADD COLUMN.  If you want to add a column to an
>     extension, you say ALTER EXTENSION .. ADD TABLE.   If you want to add
>     an option to a foreign table, you say ALTER FOREIGN TABLE .. OPTIONS
>     (ADD ..).  Therefore, I think it is entirely reasonable and obviously
>     consistent with existing practice that if you want to add a table to a
>     replication set, you should write ALTER REPLICATION SET .. ADD TABLE.
>     I don't understand why logical replication should be the one feature
>     that departs from the way that all of our other features work.
>
>
> Because unlike all the other features, it can work usefully *across
> versions*.

I don't see how that matters for definitions in catalogs though. It's 
not like we want to do any kind of RPC to add table to replication set 
on the remote node.

--   Petr Jelinek                  http://www.2ndQuadrant.com/  PostgreSQL Development, 24x7 Support, Training &
Services

On Thu, Jul 7, 2016 at 8:53 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> I thought I sat through, at least, most of it, but you barely gave
>> anyone else a chance to talk, which kind of misses the point of an
>> unconference.  The portion which I attended was not about how to move
>> the development of the feature forward, but just involved describing
>> it.  I thought it was a shame that the time wasn't used better.
>
> I think the problem was that I gave everybody an even shot at commenting,
> rather than focusing on a few key developers.

If that had been what happened, I wouldn't consider it a problem, but
I don't think that's what happened.

>> I really don't think that's accurate.  There might have been 50% of
>> people who thought that not having DDL was acceptable, but I think
>> there were very few people who found it preferable.
> Without being in the room, its kinda hard for you to know, right?

I was in the room for that part.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Jul 7, 2016 at 9:25 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> I note also that replication slots aren't backed up by pg_dump; I see
> analogy here and think that at least some parts of logical replication will
> be similar and not require DDL at all, just as slots do not.

I agree with that.  Of course, it's *impossible* to usefully back up a
slot because the key ingredient in a slot is the LSN after which WAL
should be preserved - and it's meaningless to preserve that across a
dump and restore cycle.  But, for example, replication set definitions
can be preserved across a dump and restore and I am quite sure users
will find it very unfortunate if they aren't.

> We have much to discuss in terms of security, the way it should work and
> what options to support and a sidetrack into syntax isn't warranted at this
> early stage. Please lets discuss those important things first, then return
> to whether DDL makes sense or not; it may do, or may not, or more likely
> which parts of it need DDL and which do not.

We've sort of hijacked this whole thread which was originally about
something different, so maybe it would be better to start a new thread
specifically to talk about the design of logical replication.  For my
money, though, I don't find the designs I've seen so far to be
particularly compelling - and I think that the problem is that we tend
to think about this from the point of view of the capabilities that
must be available within a single instance.  Physical replication has
the same issue.  Users don't want to configure archive_command and
wal_keep_segments and max_wal_senders and wal_level and set up an
archive and create recovery.conf on the standby.  They want to spin up
a new standby - and we don't provide any way to just do that.
pg_basebackup's -X stream and -R options represent significant
progress in that direction, but I don't think we've really taken it as
far as it can go yet, which is not to say I know exactly what's
missing.  Similarly, when the master fails, users want to promote a
standby (either one they choose or the one that is determined to be
furthest ahead) and remaster the others and that's not something you
can "just do".

Similarly, for logical replication, users will want to do things like
(1) spin up a new logical replication slave out of thin air,
replicating an entire database or several databases or selected
replication sets within selected databases; or (2) subscribe an
existing database to another server, replicating an entire database or
several databases; or (3) repoint an existing subscription at a new
server after a master change or dump/reload, resynchronizing table
contents if necessary; or (4) stop replication, either with or without
dropping the local copies of the replicated tables.  (This is not an
exhaustive list, I'm sure.)

I don't mean to imply that the existing designs are bad as far as they
go.  In each case, the functionality that has been built is good.  But
it's all focused, as it seems to me, on providing capabilities rather
than on providing a way for users to manage a group of database
servers using high-level primitives.  That higher-level stuff largely
gets left to add-on tools, which I don't think is serving us
particularly well.  Those add-on tools often find that the core
support doesn't quite do everything they'd like it to do: that's why
WAL-E and repmgr, for example, end up having to do some creative
things to deliver certain features.  We need to start thinking of
groups of servers rather than individual servers as the unit of
deployment.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 07/07/2016 01:01 PM, Robert Haas wrote:

> There was an unconference session on this topic at PGCon and quite a
> number of people there stated that they found DDL to be an ease-of-use
> feature and wanted to have it.

Yeah, I haven't meet anyone yet that would like to have:

select replicate_these_relations('['public']);

vs:

ALTER SCHEMA public ENABLE REPLICATION;

(or something like that).


Sincerely,

JD


>


-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

On Fri, Jul 8, 2016 at 5:47 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
>> DDL is our standard way of getting things into the system catalogs.
>> We have no system catalog metadata that is intended to be populated by
>> any means other than DDL.
>
> Replication slots? (Arguably not catalogs, I guess)
>
> Replication origins?

Those things aren't catalogs, are they?  I mean, as I said in the
other email I just sent in reply to Simon, if you did a pg_dump and a
pg_restore, I don't think it would be useful to preserve replication
slot LSNs afterwards.  If I'm wrong, and that is a useful thing to do,
then we should have a pg_dump flag to do it.  Either way, I think we
do have some work to do figuring out how you can dump, restore, and
then resume logical replication, probably by establishing a new slot
and then incrementally resynchronizing without having to copy
unchanged rows.

That having been said, I think the choice not to use DDL for slots was
somewhat unfortunate.  We now have CREATE_REPLICATION_SLOT that can be
used via the replication protocol but there is no corresponding CREATE
REPLICATION SLOT for the regular protocol; I think that's kinda
strange.

>> If you want to add a column to a table, you
>> say ALTER TABLE .. ADD COLUMN.  If you want to add a column to an
>> extension, you say ALTER EXTENSION .. ADD TABLE.   If you want to add
>> an option to a foreign table, you say ALTER FOREIGN TABLE .. OPTIONS
>> (ADD ..).  Therefore, I think it is entirely reasonable and obviously
>> consistent with existing practice that if you want to add a table to a
>> replication set, you should write ALTER REPLICATION SET .. ADD TABLE.
>> I don't understand why logical replication should be the one feature
>> that departs from the way that all of our other features work.
>
> Because unlike all the other features, it can work usefully *across
> versions*.

So what?

> We have no extension points for DDL.
>
> For function interfaces, we do.
>
> That, alone, makes a function based interface overwhelmingly compelling
> unless there are specific things we *cannot reasonably do* without DDL.

I don't understand this.  We add new DDL in new releases, and we avoid
changing the meaning existing of DDL.  Using function interfaces won't
make it possible to change the meaning of existing syntax, and it
won't make it any more possible to add new syntax.  It will just make
replication commands be spelled differently from everything else.

> In many cases it's actively undesirable to dump and restore logical
> replication state. Most, I'd say. There probably are cases where it's
> desirable to retain logical replication state such that restoring a dump
> resumes replication, but I challenge you to come up with any sensible and
> sane way that can actually be implemented. Especially since you must
> obviously consider the possibility of both upstream and downstream being
> restored from dumps.

Yes, these issues need lots of thought, but I think that replication
set definitions, at least, are sensible to dump and reload.

> IMO the problem mostly devolves to making sure dumps taken of different DBs
> are consistent so new replication sessions can be established safely. And
> really, I think it's a separate feature to logical replication its self.

I think what is needed has more to do with coping with the situation
when the snapshots aren't consistent.  Having a way to make sure they
are consistent is a great idea, but there WILL be situations when
replication between two 10TB databases gets broken and it will not be
good if the only way to recover is to reclone.

> To what extent are you approaching this from the PoV of wanting to use this
> in FDW sharding? It's unclear what vision for users you have behind the
> things you say must be done, and I'd like to try to move to more concrete
> ground. You want DDL? OK, what should it look like? What does it add over a
> function based interface? What's cluster-wide and what's DB-local? etc.

I've thought about that question, a little bit, but it's not really
what underlies my concerns here.  I'm concerned about dump-and-restore
preserving as much state as is usefully possible, because I think
that's critical for the user experience, and I'm concerned with having
the commands we use to manage replication not be spelled totally
differently than our other commands.

However, as far as sharding is concerned, no matter how it gets
implemented, I think logical replication is a key feature.
Postgres-XC/XL has the idea of "replicated" tables which are present
on every data node, and that's very important for efficient
implementation of joins.  If you do a join between a little table and
a big sharded table, you want to be able to push that down to the
shards, and you can only do that if the entirety of the little table
is present on every shard or by creating a temporary copy on every
shard.  In many cases, the former will be preferable.  So, think it's
important for sharding that logical replication is fully integrated
into core in such a manner as to be available as a building block for
other features.

At the least, I'm guessing that we'll want a way for whatever code is
planning join execution to figure out which tables have up-to-date
copies on servers that are involved in the query.  As far as the
FDW-based approach to sharding is concerned, one thing to think about
is whether postgres_fdw and logical replication could share one notion
of where the remote servers are.

> FWIW, Petr is working on some code in the area, but I don't know how far
> along the work is.

OK, thanks.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 13/07/16 21:06, Robert Haas wrote:
>
>> We have much to discuss in terms of security, the way it should work and
>> what options to support and a sidetrack into syntax isn't warranted at this
>> early stage. Please lets discuss those important things first, then return
>> to whether DDL makes sense or not; it may do, or may not, or more likely
>> which parts of it need DDL and which do not.
>
> We've sort of hijacked this whole thread which was originally about
> something different, so maybe it would be better to start a new thread
> specifically to talk about the design of logical replication.  For my
> money, though, I don't find the designs I've seen so far to be
> particularly compelling - and I think that the problem is that we tend
> to think about this from the point of view of the capabilities that
> must be available within a single instance.
> ...>
> Similarly, for logical replication, users will want to do things like
> (1) spin up a new logical replication slave out of thin air,
> replicating an entire database or several databases or selected
> replication sets within selected databases; or (2) subscribe an
> existing database to another server, replicating an entire database or
> several databases; or (3) repoint an existing subscription at a new
> server after a master change or dump/reload, resynchronizing table
> contents if necessary; or (4) stop replication, either with or without
> dropping the local copies of the replicated tables.  (This is not an
> exhaustive list, I'm sure.)
>

Well this all can be done using pglogical so I don't really understand 
what you mean when you say that you don't like the design or what's the 
actual problem here (although I don't plan to implement everything in 
the first patch submission).

> I don't mean to imply that the existing designs are bad as far as they
> go.  In each case, the functionality that has been built is good.  But
> it's all focused, as it seems to me, on providing capabilities rather
> than on providing a way for users to manage a group of database
> servers using high-level primitives.  That higher-level stuff largely
> gets left to add-on tools, which I don't think is serving us
> particularly well.  Those add-on tools often find that the core
> support doesn't quite do everything they'd like it to do: that's why
> WAL-E and repmgr, for example, end up having to do some creative
> things to deliver certain features.  We need to start thinking of
> groups of servers rather than individual servers as the unit of
> deployment.
>

You can't build the highlevel management parts without first having the 
per node lower level parts done. It would be nice to have highlevel 
parts as well but nobody wrote that so far. I hope you don't expect 
logical replication patch to do all that, because if you do, you'll be 
disappointed.

--   Petr Jelinek                  http://www.2ndQuadrant.com/  PostgreSQL Development, 24x7 Support, Training &
Services

On 7/13/16 2:06 PM, Joshua D. Drake wrote:
> On 07/07/2016 01:01 PM, Robert Haas wrote:
>
>> There was an unconference session on this topic at PGCon and quite a
>> number of people there stated that they found DDL to be an ease-of-use
>> feature and wanted to have it.
>
> Yeah, I haven't meet anyone yet that would like to have:
>
> select replicate_these_relations('['public']);
>
> vs:
>
> ALTER SCHEMA public ENABLE REPLICATION;
>
> (or something like that).

I generally agree, but I think the more important question is "Why?". Is 
it becouse DDL looks more like a sentence? Is it because arrays are a 
PITA? Is it too hard to call functions?
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On 7/7/16 8:17 PM, Simon Riggs wrote:
> Simplicity is key, I agree. But that's just a user interface feature,
> not a comment on what's underneath the covers. pg_upgrade is not simple
> and is never likely to be so, under the covers.

Right, and what I'd prefer effort put into is making managing 
replication in all forms easier. Replication has a lot of uses outside 
of upgrades.

FWIW, I've actually never used pg_upgrade because I view it as high-risk 
for the environments I've dealt with. There's no ability to fall back to 
the old version without losing data, and because of it's binary nature 
the odds of some kind of a corruption event happening are far higher 
than with something like londiste. Certainly many environments don't 
have those concerns though. Having options are good.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On Thu, Jul 14, 2016 at 2:29 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
> Yes, I'd like that too. I'd also like to have fully parallized writeable
> queries right now. But we can't build everything all at once.

I agree.

> Before doing parallelized writes, things like dsm, dsm queues, group
> locking, worker management, and read parallelism were all necessary.

Yep.

> It's the same with cluster-wide management, dump and restore of replication
> state to re-create a replication setup elsewhere, etc. We have to build the
> groundwork first. Trying to pour the top storey concrete when the bottom
> storey isn't even there yet isn't going to work out. You've argued
> effectively the same thing elsewhere, saying that the pglogical submission
> tried to do too much and should be further cut down.

Absolutely.  I didn't mean to imply that the scope of that submission
should be expanded.  What I'm a bit concerned about is that maybe we
haven't given enough thought to how some of this stuff is going to
work.  Petr replied earlier with an assertion that all of the things
that I mentioned could be done using pglogical, but I'm not convinced.
I don't see how you can use pglogical to build a self-healing
replicated cluster, which is ultimately what people want.  Maybe
that's just because I'm not personally deeply enmeshed in that
project, but I do try to read all of the relevant threads on
pgsql-hackers and keep tabs on what is happening.

Suppose server A is publishing to server B.  Well, clearly, A needs to
have a slot for server B, but does that slot correspond precisely to
the publication, or is that represented in some other way?  How is the
subscription represented on server B?  What happens if either A or B
undergoes a dump-and-restore cycle?  It's just fuzzy to me how this
stuff is supposed to work in detail.

>> I don't understand this.  We add new DDL in new releases, and we avoid
>> changing the meaning existing of DDL.  Using function interfaces won't
>> make it possible to change the meaning of existing syntax, and it
>> won't make it any more possible to add new syntax.  It will just make
>> replication commands be spelled differently from everything else.
>
> Say you want to upgrade from 9.4 to 10.0 using the new logical replication
> features. How would that be possible if you can't add the required
> interfaces for setting up the downstream side to 9.4 as an extension?
>
> I think what we're leaning toward here is "don't do that". Tools like
> pglogical will have to carry that load until the Pg versions with built-in
> replication become the "old" versions to be upgraded _from_.

That may be true, but it's hard to tell whether that's going to be
feasible anyway without a fleshed-out proposal for how this is all
going to work.  If this can be made to work for upgrades from 9.4 with
only an extension, that would IMHO be worth trying to do.  But, just
for example, adding a replication set capability to 10 isn't going to
affect that one way or the other.  For upgrades, you'll want to
replicate the whole database.

> Ideally the new infrastructure won't have to make normal (non-walsender)
> libpq connections and will work entirely over the walsender protocol. That's
> not extensible at all, so the point becomes kind of moot, it just can't be
> used for downversion upgrades. Pity, but cleaner in the long run.

Yeah.  I'm entirely willing to leave downgrades to earlier versions to
extensions.  "Cleaner in the long run" has got to be a high priority
for core features; if we had not followed that policy in the past,
we'd have an unmaintainable mess now.

> It does make me wonder if we should look at extension points for the
> walsender protocol though, now we're like to have a future desire for newer
> versions to connect to older versions - it'd be great if we could do
> something like pg_upgrade_support to allow an enhanced logical migration
> from 10.0 to 11.0 by installing some extension in 10.0 first.

Maybe, but let's get something that can work from >=10.0 to >=10.0 first.

>> I'm concerned about dump-and-restore
>> preserving as much state as is usefully possible, because I think
>> that's critical for the user experience
>
> Right. See the pain points caused by our current dump issues like the
> brokenness around dumping security labels, grants, etc on the database its
> self. It certainly matters.
>
> The keyword there is "usefully" though. Replication sets: definitely useful.
> Knowledge about what peers we were connected to and what we were up to on
> those peers: possibly useful, if we have some way to meaningfully encode
> that knowledge, but far from crucial, especially since we can't actually
> resume replay from them without replication slots and replication
> identifiers we can't dump.
>
> It seems we were mostly crossing wires about different assumptions about
> what dump and restore would include.

Yes, that may be the case.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 17/07/16 20:08, Jim Nasby wrote:
> On 7/13/16 2:06 PM, Joshua D. Drake wrote:
>> On 07/07/2016 01:01 PM, Robert Haas wrote:
>>
>>> There was an unconference session on this topic at PGCon and quite a
>>> number of people there stated that they found DDL to be an ease-of-use
>>> feature and wanted to have it.
>>
>> Yeah, I haven't meet anyone yet that would like to have:
>>
>> select replicate_these_relations('['public']);
>>
>> vs:
>>
>> ALTER SCHEMA public ENABLE REPLICATION;
>>
>> (or something like that).
>
> I generally agree, but I think the more important question is "Why?". Is
> it becouse DDL looks more like a sentence? Is it because arrays are a
> PITA? Is it too hard to call functions?

For me it's many small reasons. I want to store it in catalogs and some 
things there are nicer when you manipulate using standard DDL processing 
(like dependencies for example). The syntax is also bit nicer. Our 
documentation works better for DDLs than functions (that's something we 
should fix but I am not doing it as part of this patch). Same goes for 
psql tab completion. We automatically gain things like event triggers. 
The support in pg_dump is also more straightforward with DDL.

It might make sense to have functions for manipulating slots and origins 
as those are just primitives which user should not have to fiddle with 
but for things that are directly meant for user interaction DDL just 
feels better.

--   Petr Jelinek                  http://www.2ndQuadrant.com/  PostgreSQL Development, 24x7 Support, Training &
Services

On 17/07/16 20:50, Robert Haas wrote:
>
>> It's the same with cluster-wide management, dump and restore of replication
>> state to re-create a replication setup elsewhere, etc. We have to build the
>> groundwork first. Trying to pour the top storey concrete when the bottom
>> storey isn't even there yet isn't going to work out. You've argued
>> effectively the same thing elsewhere, saying that the pglogical submission
>> tried to do too much and should be further cut down.
>
> Absolutely.  I didn't mean to imply that the scope of that submission
> should be expanded.  What I'm a bit concerned about is that maybe we
> haven't given enough thought to how some of this stuff is going to
> work.  Petr replied earlier with an assertion that all of the things
> that I mentioned could be done using pglogical, but I'm not convinced.
> I don't see how you can use pglogical to build a self-healing
> replicated cluster, which is ultimately what people want.  Maybe
> that's just because I'm not personally deeply enmeshed in that
> project, but I do try to read all of the relevant threads on
> pgsql-hackers and keep tabs on what is happening.
>

That really depends on what you call self-healing replicated cluster.

> Suppose server A is publishing to server B.  Well, clearly, A needs to
> have a slot for server B, but does that slot correspond precisely to
> the publication, or is that represented in some other way?  How is the
> subscription represented on server B?  What happens if either A or B
> undergoes a dump-and-restore cycle?  It's just fuzzy to me how this
> stuff is supposed to work in detail.

Yeah, that's because it is. The dump/restore cycle would work provided 
you stopped the replication before doing it. That might not be perfect 
but it's still more than physical can do. Solving more complex scenarios 
is something for the future. Logical PITR might be the answer for that, 
not sure yet.

About slots. Slots are just primitive which helps us to get snapshot 
mapped to LSN, keep the historical catalog and wal files. There is no 
reason for single replication path to be forever tied to single slot 
though. In fact in pglogical (and my plan for core is same) we already 
create limited lifespan slots to get new snapshots when either adding 
new table or re-syncing the existing one. This is one of the reasons why 
I don't really see much usefulness in being able to do snapshot inside 
the slot once it started replicating already btw, using multiple slots 
has also advantage of parallelism (replication of other tables does not 
lag because we are syncing another one).

>
>> It does make me wonder if we should look at extension points for the
>> walsender protocol though, now we're like to have a future desire for newer
>> versions to connect to older versions - it'd be great if we could do
>> something like pg_upgrade_support to allow an enhanced logical migration
>> from 10.0 to 11.0 by installing some extension in 10.0 first.
>
> Maybe, but let's get something that can work from >=10.0 to >=10.0 first.
>

Agreed.

--   Petr Jelinek                  http://www.2ndQuadrant.com/  PostgreSQL Development, 24x7 Support, Training &
Services

On 07/17/2016 11:55 AM, Jan Wieck wrote:

>         Yeah, I haven't meet anyone yet that would like to have:
>
>         select replicate_these_relations('['public']);
>
>         vs:
>
>         ALTER SCHEMA public ENABLE REPLICATION;
>
>         (or something like that).
>
>
>     I generally agree, but I think the more important question is
>     "Why?". Is it becouse DDL looks more like a sentence? Is it because
>     arrays are a PITA? Is it too hard to call functions?

IMO, because it isn't code. I think that people forget that many, many 
DBAs are not developers, they are business analysts that happen to also 
be DBAs. Similarly, there is a reason why MongoDB/NoSQL will never be as 
popular as good old fashion SQL.

>
>
> Once you get fine grained enough to support replicating different sets
> of possibly overlapping objects/namespaces to different groups of
> recipients, the DDL approach becomes just as convoluted as calling
> functions and nobody will memorize the entire syntax.
>

Ehh, partially true. For example, I don't know every single nuance of 
ALTER TABLE but that is what the \h is for. Replication would be no 
different.

JD

-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

On 7/17/16 2:22 PM, Petr Jelinek wrote:
>> I generally agree, but I think the more important question is "Why?". Is
>> it becouse DDL looks more like a sentence? Is it because arrays are a
>> PITA? Is it too hard to call functions?
>
> For me it's many small reasons. I want to store it in catalogs and some
> things there are nicer when you manipulate using standard DDL processing
> (like dependencies for example).

Fair point.

> The syntax is also bit nicer. Our
> documentation works better for DDLs than functions (that's something we
> should fix but I am not doing it as part of this patch). Same goes for
> psql tab completion. We automatically gain things like event triggers.

I'd think all of those we'd want to be able to support for functions as 
well...

> The support in pg_dump is also more straightforward with DDL.

Hmm... not sure why that is. It does seem to me that support for 
extension configuration isn't as strong as it could be.

> It might make sense to have functions for manipulating slots and origins
> as those are just primitives which user should not have to fiddle with
> but for things that are directly meant for user interaction DDL just
> feels better.

I do agree that DDL "feels better" (which I think is what JD was 
alluding too).

I had a secret agenda in asking why it's better though: can we find a 
way to allow extensions to do "DDL-ish" things in a better way than how 
they're stuck doing them today. I suspect it will never be practical to 
have extensions modifying grammar willy-nilly, but maybe there's some 
other things we could do to make life easier. One thought is an 
"extension command" mode you can enter that means everything you're 
typing gets treated as a call to a function in that extension:

EXTENSION MODE citus;
master_create_distributed_table 'github_events', 'created_at', 'append';
EXTENSION MODE;

instead of SELECT master_create_distributed_table('github_events', 
'created_at', 'append');

obviously that's completely pointless for a single command, but if you 
needed to do a bunch of things it starts saving typing.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On 07/18/2016 03:17 PM, Jim Nasby wrote:
> On 7/17/16 2:22 PM, Petr Jelinek wrote:

> I do agree that DDL "feels better" (which I think is what JD was
> alluding too).

Yes and no. It reads better and is more clear to those who are not 
developers or have a developer background which is, many in the database 
field. It is also easier to type. I type 120 a minute on a roll, that is 
until I have to do this ('[ ..... Simple wording base command structure 
is much more efficient.

ALTER TABLE FOO ENABLE REPLICATION ON SLAVE 0;

vs

select enable_replication_for_table('['foo']',0);

Guess which one was typed without a single error and more quickly.

Sincerely,

JD




-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

On Fri, Jul  8, 2016 at 12:18:28AM +0100, Simon Riggs wrote:
> On 7 July 2016 at 21:10, Robert Haas <robertmhaas@gmail.com> wrote:
> 
>     pg_upgrade does that, kinda.  I'd like to have something better, but
>     in the absence of that, I think it's quite wrong to think about
>     deprecating it, even if we had logical replication fully integrated
>     into core today.  Which we by no means do.
> 
> I don't see any problem with extending pg_upgrade to use logical replication
> features under the covers.
> 
> It seems very smooth to be able to just say
> 
>    pg_upgrade --online 
> 
> and then specify whatever other parameters that requires.
> 
> It would be much easier to separate out that as a use-case so we can be sure we
> get that in 10.0, even if nothing else lands.

Uh, while "pg_upgrade --online" looks cool, I am not sure a solution
based on logical replication would share _any_ code with the existing
pg_upgrade tool, so it seems best to use another binary for this.  

I guess we could use the pg_dump/pg_restore pg_upgrade code to create
the objects, and use logical replication to copy the rows, but what does
this gain us that pg_dump/pg_restore doesn't?  Wouldn't you just create
the standby using logical replication and just switch-over?  Why use
pg_upgrade at all?  Am I missing something?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +

On Thu, Jul 28, 2016 at 10:22 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
> It might, actually. One approach for online upgrade is to:
>
> * pg_basebackup the master
> * start the replica and let it catch up
> * create a logical replication slot on the master
> * replace the replication.conf on the basebackup so it stops recovery at the
> lsn of the replication slot's confirmed_flush_lsn
> * stop the replica and pg_upgrade it
> * have the upgraded replica, now a master, replay from the old master over
> logical replication
> * once caught up, switch over
>
> This means a full dump and reload with a full rebuild of all indexes, etc,
> isn't needed. All shared catalog stuff is copied (until we switch to logical
> rep for the final catch-up).

This is a per-database logic to perform an upgrade of a single
database, right? If a cluster has multiple databases you need one
logical slot per database to complete an upgrade, which is where
sync_synchronous_names which is able to take now multiple entries
helps as well to ensure that the former master is in sync with the all
the logical slots in place.
-- 
Michael

On Sun, Jul 17, 2016 at 02:55:20PM -0400, Jan Wieck wrote:
> On Sun, Jul 17, 2016 at 2:08 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> > On 7/13/16 2:06 PM, Joshua D. Drake wrote:
> >> On 07/07/2016 01:01 PM, Robert Haas wrote:
> >> There was an unconference session on this topic at PGCon and quite a
> >>> number of people there stated that they found DDL to be an ease-of-use
> >>> feature and wanted to have it.
> >>
> >> Yeah, I haven't meet anyone yet that would like to have:
> >>
> >> select replicate_these_relations('['public']);
> >>
> >> vs:
> >>
> >> ALTER SCHEMA public ENABLE REPLICATION;
> >>
> >> (or something like that).
> >>
> >
> > I generally agree, but I think the more important question is "Why?". Is
> > it becouse DDL looks more like a sentence? Is it because arrays are a PITA?
> > Is it too hard to call functions?
> 
> Once you get fine grained enough to support replicating different
> sets of possibly overlapping objects/namespaces to different groups
> of recipients, the DDL approach becomes just as convoluted as
> calling functions and nobody will memorize the entire syntax.

I don't see this as an actual problem.  I've written parts of the
SELECT syntax, but I haven't memorized even all of that.

DDL doesn't promise to be more complicated or easier to get wrong than
function calls, as far as I can tell.  The opposite could well be
true.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Thu, Jul 28, 2016 at 09:22:17AM +0800, Craig Ringer wrote:
> It might, actually. One approach for online upgrade is to:
> 
> * pg_basebackup the master
> * start the replica and let it catch up
> * create a logical replication slot on the master
> * replace the replication.conf on the basebackup so it stops recovery at the
> lsn of the replication slot's confirmed_flush_lsn
> * stop the replica and pg_upgrade it
> * have the upgraded replica, now a master, replay from the old master over
> logical replication
> * once caught up, switch over
> 
> This means a full dump and reload with a full rebuild of all indexes, etc,
> isn't needed. All shared catalog stuff is copied (until we switch to logical
> rep for the final catch-up).

Right, using pg_upgrade as part of a logical upgrade procedure makes
sense.  I was referring to having pg_upgrade --online do all of those
bullets plus what is does now --- that just seems like it would fail as
too complex, and if someone wanted to do just pg_upgrade without the
logical, the manual page would be incomprehensible.

In summary, I think we need to keep pg_upgrade doing what it does well,
and come up with another tool that does those bullets.  Heck, people
already can't find --link.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +