CODEWITHSUNDEEP
postgresqlevent-log
ses
ses

Reputation: 13342

Generate event log for Postgres DB in data / structure change to be applied / replied later for older db version

Q: I wonder if it's possible to generate the sql-piece on the fly for each db change (structure or data-wise) that is applied on it ?

So latter it could be applied/re-played over the older one.. like “event log”. and apply it as db restore in order not to deal with the dump.

The goal is to avoid using the dump restore to save build-restore-time, and make things more automated as part of the build process for some app.

Q: Is it common practice to do so for PostgreSQL (i know for some db it is)?

UPDATE: The Ultimate goal

we have a huge db with data. sometimes they change, as well as the structure. the goal is to keep devs up to date with the data&structure on their local machines so their app they develop would point to up to date local db. to download the dump would take time, to apply the dump would take time. to apply some event log changes daily would take much less time. as a daily build process that each dev could do it himher self.

(we still want to use local not cloud env for devs)

Upvotes: 0

Views: 1216

Answers (4)

emtreloa
emtreloa

Reputation: 1

Have you heard of Titan.io? It's open source and it was made just for this situation. If you pair Titan with Liquibase, you can totally accomplish your goal of data provisioning and change. Here's a video where they show you how (a webinar recording): https://vimeo.com/373250751/8bec4cb7ba There are also some Medium articles about using them (do a search for Titan and Liquibase) and you can read up on them. It's pretty cool.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246348

To get a copy of the live database for the developers as quickly as possible, I would proceed like this:

  • Have a streaming replication standby for the live database.

  • To refresh their database, developers start a procedure that

    • takes a pg_basebackup of the standby database.

    • recovers that backup with recovery_target = immediate

Unless the database is really large, that should get done in a coffee break.

Upvotes: 1

user330315
user330315

Reputation:

You are targeting the problem from the wrong side.

Any change to the database ("schema schanges") should be scripted and stored in your source control system (git, svn - wherever the source code of your application lives as well).

Never change the databases "manually" (e.g. by clicking through some GUI tool).

The devs should checkout those scripts and run them. Obviously those changes are first applied in the dev environment, then the test environment and when everything is OK, then (and only then) you apply them to production - not the other way round.

This is commonly known as "schema migrations".

Tools like Liquibase or Flyway will make that easier because the keep track which changes have been applied in which environment.

Upvotes: 2

Amit Sharma
Amit Sharma

Reputation: 11

Try using event triggers. You can use a ddl_command_start trigger to capture the events that you want and log them.

Upvotes: 1

Related Questions