schema change tracking

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

schema change tracking

Benedict Holland
Hi All,

I am fairly this question has many answers but here it goes:

I need a tool that can track schema changes in a postgesql database, write scripts to alter the tables, and store those changes in git. Are there tools that exist that can do this?

BTW, I know if I use a framework with some form of model tracking (laravel and Django), those can do it. The application I am writing does not track model files and I really cannot compare sql schema dumps across versions. It would be far too time-consuming. 

Thanks,
~Ben
Reply | Threaded
Open this post in threaded view
|

Re: schema change tracking

Steve Atkins


> On May 16, 2019, at 5:41 PM, Benedict Holland <[hidden email]> wrote:
>
> Hi All,
>
> I am fairly this question has many answers but here it goes:
>
> I need a tool that can track schema changes in a postgesql database, write scripts to alter the tables, and store those changes in git. Are there tools that exist that can do this?

If you're looking for something that'll reverse engineer schema change scripts from a database someone has made ad-hoc changes to ... try and avoid doing that if at all possible. Use the change scripts in git as the source of truth.

If you do that there are many answers, starting at "it's fairly easy to do yourself, with a simple schema version and upgrade / downgrade scripts". Depending on what language you're writing your app in there may be libraries that can help.

But if you're looking for something standalone, look at https://sqitch.org

Cheers,
  Steve




Reply | Threaded
Open this post in threaded view
|

Re: schema change tracking

Paul A Jungwirth
In reply to this post by Benedict Holland
On 5/16/19 9:41 AM, Benedict Holland wrote:
> I need a tool that can track schema changes in a postgesql database,
> write scripts to alter the tables, and store those changes in git. Are
> there tools that exist that can do this?
>
> BTW, I know if I use a framework with some form of model tracking
> (laravel and Django), those can do it. The application I am writing does
> not track model files and I really cannot compare sql schema dumps
> across versions. It would be far too time-consuming.

For Python database migrations when I'm not using Django or SQLAlchemy
I've enjoyed yoyo migrations:

https://ollycope.com/software/yoyo/latest/

If you are using SQLAlchemy then I'd go with Alembic:

https://pypi.org/project/alembic/

Btw I don't *think* this is what you're looking for, but on the Postgres
side you can also set `log_statement='ddl'` to audit the changes:

https://blog.hagander.net/why-are-you-not-logging-your-ddl-142/

Or for something more elaborate you could use Event Triggers. The docs
have an example you could start from:

https://www.postgresql.org/docs/current/event-trigger-example.html

--
Paul              ~{:-)
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: schema change tracking

Mark Fletcher
In reply to this post by Benedict Holland
On Thu, May 16, 2019 at 9:41 AM Benedict Holland <[hidden email]> wrote:

I need a tool that can track schema changes in a postgesql database, write scripts to alter the tables, and store those changes in git. Are there tools that exist that can do this?

We ended up rolling our own. We do schema dumps and then use https://www.apgdiff.com/ to diff them. For our relatively simple schemas, it's worked fine. One thing to note, apgdiff doesn't support `ALTER TABLE ONLY [a-z\.]+ REPLICA IDENTITY FULL;` lines, which we just remove before diffing.

Cheers,
Mark 
Reply | Threaded
Open this post in threaded view
|

Re: schema change tracking

Benedict Holland
Hi All!

Thanks for the replies. I am looking for a solution, possibly 3rd party, where I write changes to the schema and it keeps track of the changes I made. I am used to doing stuff in pgadmin4 but I don't mind something else. 

This isn't really ad-hoc. This is more like, I have a v1.0 release coming out soon and I have made dozens or hundreds of changes to the schema from the alpha. Right now, I can destroy the databases and recreate them without data loss because it is in active development. When this gets released, we need some way to make sure that we can easily migrate between release versions and that means schema change tracking. Basically, to Steve's concern, I don't have to go back and figure out changes (thankfully, I have done that and it sucks) but we need something from an initial release moving forward. 

I don't have time to write my own and this problem is complex enough to either buy a solution or go with a 3rd party application. 

So far, SQITCH is looking like a winner. Yoyo can work as I have access to python. We are not using SQLAlchemy but I don't see a reason why we can't. 

Thanks all for the suggestions. This is great. 
~Ben


On Thu, May 16, 2019 at 1:06 PM Mark Fletcher <[hidden email]> wrote:
On Thu, May 16, 2019 at 9:41 AM Benedict Holland <[hidden email]> wrote:

I need a tool that can track schema changes in a postgesql database, write scripts to alter the tables, and store those changes in git. Are there tools that exist that can do this?

We ended up rolling our own. We do schema dumps and then use https://www.apgdiff.com/ to diff them. For our relatively simple schemas, it's worked fine. One thing to note, apgdiff doesn't support `ALTER TABLE ONLY [a-z\.]+ REPLICA IDENTITY FULL;` lines, which we just remove before diffing.

Cheers,
Mark 
Reply | Threaded
Open this post in threaded view
|

Re: schema change tracking

Adrian Klaver-4
On 5/16/19 10:55 AM, Benedict Holland wrote:

> Hi All!
>
> Thanks for the replies. I am looking for a solution, possibly 3rd party,
> where I write changes to the schema and it keeps track of the changes I
> made. I am used to doing stuff in pgadmin4 but I don't mind something else.
>
> This isn't really ad-hoc. This is more like, I have a v1.0 release
> coming out soon and I have made dozens or hundreds of changes to the
> schema from the alpha. Right now, I can destroy the databases and
> recreate them without data loss because it is in active development.
> When this gets released, we need some way to make sure that we can
> easily migrate between release versions and that means schema change
> tracking. Basically, to Steve's concern, I don't have to go back and
> figure out changes (thankfully, I have done that and it sucks) but we
> need something from an initial release moving forward.
>
> I don't have time to write my own and this problem is complex enough to
> either buy a solution or go with a 3rd party application.
>
> So far, SQITCH is looking like a winner. Yoyo can work as I have access
> to python. We are not using SQLAlchemy but I don't see a reason why we
> can't.

I will second the motion on Sqitch. It has met all my needs and is
framework agnostic.

>
> Thanks all for the suggestions. This is great.
> ~Ben
>

>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: schema change tracking

bricklen
In reply to this post by Benedict Holland
On Thu, May 16, 2019 at 9:41 AM Benedict Holland <[hidden email]> wrote:
I need a tool that can track schema changes in a postgesql database, write scripts to alter the tables, and store those changes in git. Are there tools that exist that can do this?

For dev and QA, my company is using Migra (https://github.com/djrobstep/migra) as part of the developer workflow to track DDL changes. The generated changes are then put into git as patches. Migra itself is just used for the diff'ing capabilities it provides, and will require some glue code if you want to use it for any CI/CD work.