pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase schema changes tools
6+ messages / 5 participants
[nested] [flat]
* Database schema changes tools
@ 2022-10-19 12:25 edi mari <edim2525@gmail.com>
0 siblings, 4 replies; 6+ messages in thread
From: edi mari @ 2022-10-19 12:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi All,
Are you using database schema change tools to manage your Postgres, like
Flyway, Liquibase, and DBmaestro ?
Can you please recommend or disrecommend
Thanks
Edi
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database schema changes tools
@ 2022-10-19 13:41 Erik Wienhold <ewie@ewie.name>
parent: edi mari <edim2525@gmail.com>
3 siblings, 1 reply; 6+ messages in thread
From: Erik Wienhold @ 2022-10-19 13:41 UTC (permalink / raw)
To: edi mari <edim2525@gmail.com>; pgsql-admin@lists.postgresql.org
> On 19/10/2022 14:25 CEST edi mari <edim2525@gmail.com> wrote:
>
> Are you using database schema change tools to manage your Postgres, like Flyway, Liquibase, and DBmaestro ?
> Can you please recommend or disrecommend
I recommend looking into Sqitch[1], having switched to it myself one year ago
after using Flyway for every project.
My gripe with Flyway is the separation of versioned migrations (non-idempotent)
and repeatable migrations (idempotent, e.g. CREATE OR REPLACE VIEW). Repeatable
migrations always run after *all* versioned migrations. This makes it impossible
to use views or functions in versioned migrations unless they are created before.
I haven't found an option to change the migration process from "all or nothing"
to something that is more like logical groups or batches of migrations, so that
versioned and repeatable migrations can be interleaved.
Another thing that bugs me with Flyway is that you have to control the order
of migrations via filenames. Not so difficult for versioned migrations if you
stick to dotted version numbers or leading zeros in filenames. But your file
system may not list migration files in the same order as Flyway applies them, so
watch out.
Ordering repeatable migrations has the same issue which is relevant for views
with dependencies on other views. Flyway relies on the filename order in that
case. Refactoring views may require adding new repeatable migrations between
two existing ones.
That's why I switched to Sqitch because the order of migrations is defined
explicitly in a plan file. So it's easy to interleave idempotent and non-idempotent
migrations as one sees fit.
Sqitch runs migrations scripts through psql so you can also use meta-commands
and variables. But beware that every migrations runs as a separate transaction[2]
in contrast to Flyway whose migrate command runs a single transaction with all
pending migrations.
In the end it depends on how complex your schema is (or will be). Flyway is
okay if it's only for a bunch of tables. Anything beyond that, with dependencies
between database objects, is tricky IMO.
[1] https://sqitch.org/
[2] https://groups.google.com/g/sqitch-users/c/7lTJXNhS8Ho/m/fyuZmSe8IT0J
--
Erik
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database schema changes tools
@ 2022-10-19 13:45 Claudio Piffer <claudio.piffer@gmail.com>
parent: edi mari <edim2525@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: Claudio Piffer @ 2022-10-19 13:45 UTC (permalink / raw)
To: edi mari <edim2525@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Edi
I use flyway to apply schema changed in test/production environment. For
change and versioning the schema I use Datanamic Dezign (
https://www.datanamic.com/dezign/)
Best Regards
Claudio
Il giorno mer 19 ott 2022 alle ore 14:26 edi mari <edim2525@gmail.com> ha
scritto:
> Hi All,
> Are you using database schema change tools to manage your Postgres, like
> Flyway, Liquibase, and DBmaestro ?
> Can you please recommend or disrecommend
>
>
> Thanks
> Edi
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database schema changes tools
@ 2022-10-19 13:54 Claudio Piffer <claudio.piffer@gmail.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 0 replies; 6+ messages in thread
From: Claudio Piffer @ 2022-10-19 13:54 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: edi mari <edim2525@gmail.com>; pgsql-admin@lists.postgresql.org
Hi Erik
wow! I did not know Sqitch. I try it now !!
Thank you very much and best regards
Claudio
Il giorno mer 19 ott 2022 alle ore 15:41 Erik Wienhold <ewie@ewie.name> ha
scritto:
> > On 19/10/2022 14:25 CEST edi mari <edim2525@gmail.com> wrote:
> >
> > Are you using database schema change tools to manage your Postgres, like
> Flyway, Liquibase, and DBmaestro ?
> > Can you please recommend or disrecommend
>
> I recommend looking into Sqitch[1], having switched to it myself one year
> ago
> after using Flyway for every project.
>
> My gripe with Flyway is the separation of versioned migrations
> (non-idempotent)
> and repeatable migrations (idempotent, e.g. CREATE OR REPLACE VIEW).
> Repeatable
> migrations always run after *all* versioned migrations. This makes it
> impossible
> to use views or functions in versioned migrations unless they are created
> before.
> I haven't found an option to change the migration process from "all or
> nothing"
> to something that is more like logical groups or batches of migrations, so
> that
> versioned and repeatable migrations can be interleaved.
>
> Another thing that bugs me with Flyway is that you have to control the
> order
> of migrations via filenames. Not so difficult for versioned migrations if
> you
> stick to dotted version numbers or leading zeros in filenames. But your
> file
> system may not list migration files in the same order as Flyway applies
> them, so
> watch out.
>
> Ordering repeatable migrations has the same issue which is relevant for
> views
> with dependencies on other views. Flyway relies on the filename order in
> that
> case. Refactoring views may require adding new repeatable migrations
> between
> two existing ones.
>
> That's why I switched to Sqitch because the order of migrations is defined
> explicitly in a plan file. So it's easy to interleave idempotent and
> non-idempotent
> migrations as one sees fit.
>
> Sqitch runs migrations scripts through psql so you can also use
> meta-commands
> and variables. But beware that every migrations runs as a separate
> transaction[2]
> in contrast to Flyway whose migrate command runs a single transaction with
> all
> pending migrations.
>
> In the end it depends on how complex your schema is (or will be). Flyway
> is
> okay if it's only for a bunch of tables. Anything beyond that, with
> dependencies
> between database objects, is tricky IMO.
>
> [1] https://sqitch.org/
> [2] https://groups.google.com/g/sqitch-users/c/7lTJXNhS8Ho/m/fyuZmSe8IT0J
>
> --
> Erik
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database schema changes tools
@ 2022-10-19 16:04 Thomas Kellerer <shammat@gmx.net>
parent: edi mari <edim2525@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: Thomas Kellerer @ 2022-10-19 16:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
edi mari schrieb am 19.10.2022 um 14:25:
> Hi All,
> Are you using database schema change tools to manage your Postgres, like Flyway, Liquibase, and DBmaestro ?
> Can you please recommend or disrecommend
We are using Liquibase exclusively.
Some projects require support for multiple database, which is quite easy to do with Liquibase's XML format.
For single-DBMS projects we use Liquibase's "annotated SQL" format, which means we are writing SQL scripts
that can also be run without Liquibase (which makes testing them easier)
Thomas
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database schema changes tools
@ 2022-10-20 14:05 Dan Smith <j.daniel.smith1@gmail.com>
parent: edi mari <edim2525@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: Dan Smith @ 2022-10-20 14:05 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
I love flyway as opposed to other tools like db-migrate which provide no
guarantees, do not enforce order, allow history to be changed, and provides
the ability to easily see what migrations have been applied and which are
pending.
As for many complaints about the tool from a previous reply, I see them as
features (naming restrictions) or there is a readily available solution and
good documentation (target var / option flag to migrate up or down to a
specific version).
Flyway is probably the easiest solution to implement in a CI/CD pipeline
(official docker image available) at this point and can easily be worked
into a docker-compose file to support local development and testing.
Generally, I pair this with a linter SQLFluff, anti-pattern checker like
sql-check and a custom lint program I wrote to fail the pipeline fast if
migrations are added to do certain things as I do not run flyway as an
admin.
That being said, what I don't like about flyway is the (Redgate) price tag
or needing to manually handle rollbacks by renaming a rollback migration if
you want to use the free version.
For that reason alone, I may look into some of the other tools mentioned
with flyway being a baseline of what I want in a migration tool.
Best regards,
Dan Smith
On Wed, Oct 19, 2022, 08:26 edi mari <edim2525@gmail.com> wrote:
> Hi All,
> Are you using database schema change tools to manage your Postgres, like
> Flyway, Liquibase, and DBmaestro ?
> Can you please recommend or disrecommend
>
>
> Thanks
> Edi
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-10-20 14:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-10-19 12:25 Database schema changes tools edi mari <edim2525@gmail.com>
2022-10-19 13:41 ` Erik Wienhold <ewie@ewie.name>
2022-10-19 13:54 ` Claudio Piffer <claudio.piffer@gmail.com>
2022-10-19 13:45 ` Claudio Piffer <claudio.piffer@gmail.com>
2022-10-19 16:04 ` Thomas Kellerer <shammat@gmx.net>
2022-10-20 14:05 ` Dan Smith <j.daniel.smith1@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox