Snapshot and Rollback feature

[nafg]

I previously used Stellar but it corrupted my database a few times. What I want is Snapshot and Rollback subcommands, which are equivalent to existing commands:

Snapshot is the same as Clone except it doesn't perform a Switch

Rollback to X when current is Y is the same as Switch to X, Destroy Y, Clone as Y (performing Switch)

[sastraxi]

@nafg This is an interesting feature request. I worry about users losing data easily using the rollback functionality. Generally though, I'm all for useful aliases / subcommands to properly support different workflows.

Do you want the ability to name your snapshots or just have them auto-named based on the current timestamp? In your ideal world, would snapshots show up in pgsh list?

[nafg]

I would want them in list because I want to have the option to roll back to various points in time.

Regarding losing data via rollback, you might want to replace my "Destroy Y" step with "Rename Y to Z" where Z is something that conveys something like "backup of Y at time T."

My usual use case is, I'm not using Knex or Javascript (I'm using Flyway in a Scala codebase), and as a solitary developer I mostly don't work on branches, but sometimes I do need to revert to the state of the database before some migration was applied. For instance I wrote a migration locally and applied it, but then some time later, before it has been pushed and deployed, I realize that I'd actually like the migration to be done a bit differently. Or, I need to fix a bug that was committed before the migration so I want to do interactive rebase and edit it. So in my use case, the naming would typically be based on latest migration version applied.

[nafg]

In terms of subcommand naming, I would not say necessarily to go with snapshot and rollback given your existing command set, and the paradigm is not about a chronology but parallel databases. Similar to git checkout -b you could add a flag to clone to prevent switching. And instead of rollback you might want overwrite or replace. Perhaps if you overwrite the current database with something it should require -f / --force.

So before applying a migration I might use pgsh clone --no-switch my_app_v5.0.0 and to roll back I might use pgsh overwrite -f my_app --from=my_app_v5.0.0 (the --from is to make it unambiguous which is replacing which).

[sastraxi]

@nafg instead of overwrite or replace what about simply:

$ pgsh clone [from] to [-f|--force] [-s|--switch] [-S|--no-switch]

We'd default to switching if we didn't have a from database, but default to not switching if we were passing in the optional from database. I think your force flag idea is good: we could add in a prompt to confirm the overwrite by typing in the database name if the force flag isn't passed in.

This feels very git-like, which is I think the direction this library should take. Even though git has some well-documented UX challenges, I think it's what developers have gotten used to.

By the way, If you have any thoughts on how pgsh could support flyway better, I'd love to hear your thoughts (perhaps on another issue). The current system is very centered on the idea of migrations being numbered which I think has to be dropped in favour of general lexicographic order, but there may be other flyway-specific tweaks in order to support multiple migration system backends (see #15 and #43).

[nafg]

The syntax is fine. I think by git-like you mean the syntax is more mechanics-driven than use-case-driven, which is fine, but then you'd want to have documentation enumerating various use cases and their requisite syntax. Usually a user's starting point is their use case and a quest to solve it, so indexing documentation by use case is important, but ultimately a tool that reflects the mechanics to its user can be understood more deeply and used more flexibly. A variation on the syntax is to prefix from with --from making it a named parameter rather than a positional one. The benefit is that usually optional positional parameters are to the right of more mandatory ones. The downside of course is it's more verbose. I'm fine either way... Regarding migrations, I haven't looked into it. I usually have my apps apply migrations on startup rather than expecting them to be applied externally. And I don't write down migrations (which is why pgsh is so useful to me). So to me pgsh is pretty orthogonal to migrations. That said, Flyway can be run from the shell in multiple ways, so integration could just be a matter of the right commands. If you want to be more general you might just let the user put custom commands in the config file and shell out to run them.

…

On Wed, May 22, 2019, 11:36 PM Cameron Gorrie ***@***.***> wrote: @nafg <https://github.com/nafg> instead of overwrite or replace what about simply: $ pgsh clone [from] to [-f|--force] [-s|--switch] [-S|--no-switch] We'd default to switching if we didn't have a from database, but default to not switching if we were passing in the optional from database. I think your force flag idea is good: we could add in a prompt to confirm the overwrite by typing in the database name if the force flag isn't passed in. This feels very git-like, which is I think the direction this library should take. Even though git has some well-documented UX challenges, I think it's what developers have gotten used to. By the way, If you have any thoughts on how pgsh could support flyway better, I'd love to hear your thoughts (perhaps on another issue). The current system is very centered on the idea of migrations being numbered which I think has to be dropped in favour of general lexicographic order, but there may be other flyway-specific tweaks in order to support multiple migration system backends. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#40?email_source=notifications&email_token=AAAYAUHA5D6A6I4VHNGKLMTPWYGMNA5CNFSM4HJF4ILKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODWA7CSA#issuecomment-495055176>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAAYAUGGECNYMBA3HBWXOJTPWYGMNANCNFSM4HJF4ILA> .

[sastraxi]

I agree that a use-case-driven syntax is best. I'm trying to balance my preference for that with a preference for keeping things consistent with tools that users of pgsh are almost certainly using day-to-day.

And I don't write down migrations (which is why pgsh is so
useful to me).

I've had a similar thread of conversation with another member of the community and I agree it's not always feasible to write a down edge. I'll try to validate my thinking on this ticket through that lens so that the workflow feels natural.

That said, Flyway can be run from the shell in multiple ways, so
integration could just be a matter of the right commands. If you want to be
more general you might just let the user put custom commands in the config
file and shell out to run them.

I'm not sure I like this style of extensibility, but it might be a reasonable solution for more esoteric tools.

[nafg]

To be clear I'm not advocating for user-case driven syntax. Personally I prefer mechanic-driven, as I explained, it just creates an extra documentation burden. Regarding integrating with tools, I'm not advocating for general shell commands. I'm simply saying that I don't see any *other* way of integrating with Flyway (unless it's running on Graal so you have a Node runtime and Java runtime in one... obviously almost no one has such a set up.). Again, I have no desire for it to integrate with Flyway; I was only responding to your question about the possibility.

…

On Thu, May 30, 2019 at 10:09 PM Cameron Gorrie ***@***.***> wrote: I agree that a use-case-driven syntax is best. I'm trying to balance my preference for that with a preference for keeping things consistent with tools that users of pgsh are almost certainly using day-to-day. And I don't write down migrations (which is why pgsh is so useful to me). I've had a similar thread of conversation with another member of the community and I agree it's not always feasible to write a down edge. I'll try to validate my thinking on this ticket through that lens so that the workflow feels natural. That said, Flyway can be run from the shell in multiple ways, so integration could just be a matter of the right commands. If you want to be more general you might just let the user put custom commands in the config file and shell out to run them. I'm not sure I like this style of sensibility, but it might be a reasonable solution for more esoteric tools. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#40?email_source=notifications&email_token=AAAYAUC4EIMJUYMNZJWN2R3PYCCGRA5CNFSM4HJF4ILKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODWT76GI#issuecomment-497549081>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAAYAUCWSKZNXV3YPHT5PRLPYCCGRANCNFSM4HJF4ILA> .