How can I put a database under git (version control)?

Take a database dump, and version control that instead. This way it is a flat text file.

Personally I suggest that you keep both a data dump, and a schema dump. This way using diff it becomes fairly easy to see what changed in the schema from revision to revision.

If you are making big changes, you should have a secondary database that you make the new schema changes to and not touch the old one since as you said you are making a branch.

I'm starting to think of a really simple solution, don't know why I didn't think of it before!!

• Duplicate the database, (both the schema and the data).
• In the branch for the new-major-changes, simply change the project configuration to use the new duplicate database.

This way I can switch branches without worrying about database schema changes.

EDIT:

By duplicate, I mean create another database with a different name (like my_db_2); not doing a dump or anything like that.

Use something like LiquiBase this lets you keep revision control of your Liquibase files. you can tag changes for production only, and have lb keep your DB up to date for either production or development, (or whatever scheme you want).

• Irmin (branching + time travel)
• Flur.ee (immutable + time travel + graph query)
• XTDB (formerly called 'CruxDB') (time travel + query)
• TerminusDB (immutable + branching + time travel + Graph Query!)
• DoltDB (branching + time-travel + SQL query)
• Quadrable (branching + remote state verification)
• EdgeDB (no real time travel, but migrations derived by the compiler after schema changes)
• Migra (diffing for Postgres schemas/data. Auto-generate migration scripts, auto-sync db state)
• ImmuDB (immutable + time-travel)

You can't do it without atomicity, and you can't get atomicity without either using pg_dump or a snapshotting filesystem.

My postgres instance is on zfs, which I snapshot occasionally. It's approximately instant and consistent.

I think X-Istence is on the right track, but there are a few more improvements you can make to this strategy. First, use:

$pg_dump --schema ... 

to dump the tables, sequences, etc and place this file under version control. You'll use this to separate the compatibility changes between your branches.

Next, perform a data dump for the set of tables that contain configuration required for your application to operate (should probably skip user data, etc), like form defaults and other data non-user modifiable data. You can do this selectively by using:

$pg_dump --table=.. <or> --exclude-table=..

This is a good idea because the repo can get really clunky when your database gets to 100Mb+ when doing a full data dump. A better idea is to back up a more minimal set of data that you require to test your app. If your default data is very large though, this may still cause problems though.

If you absolutely need to place full backups in the repo, consider doing it in a branch outside of your source tree. An external backup system with some reference to the matching svn rev is likely best for this though.

Also, I suggest using text format dumps over binary for revision purposes (for the schema at least) since these are easier to diff. You can always compress these to save space prior to checking in.

Finally, have a look at the postgres backup documentation if you haven't already. The way you're commenting on backing up 'the database' rather than a dump makes me wonder if you're thinking of file system based backups (see section 23.2 for caveats).

I want to make something similar, add my database changes to my version control system.

I am going to follow the ideas in this post from Vladimir Khorikov "Database versioning best practices". In summary i will

• store both its schema and the reference data in a source control system.
• for every modification we will create a separate SQL script with the changes

In case it helps!

Here is what i am trying to do in my projects:

• separate data and schema and default data.

The database configuration is stored in configuration file that is not under version control (.gitignore)

The database defaults (for setting up new Projects) is a simple SQL file under version control.

For the database schema create a database schema dump under the version control.

The most common way is to have update scripts that contains SQL Statements, (ALTER Table.. or UPDATE). You also need to have a place in your database where you save the current version of you schema)

Take a look at other big open source database projects (piwik,or your favorite cms system), they all use updatescripts (1.sql,2.sql,3.sh,4.php.5.sql)

But this a very time intensive job, you have to create, and test the updatescripts and you need to run a common updatescript that compares the version and run all necessary update scripts.

So theoretically (and thats what i am looking for) you could dumped the the database schema after each change (manually, conjob, git hooks (maybe before commit)) (and only in some very special cases create updatescripts)

After that in your common updatescript (run the normal updatescripts, for the special cases) and then compare the schemas (the dump and current database) and then automatically generate the nessesary ALTER Statements. There some tools that can do this already, but haven't found yet a good one.

Update Aug 26, 2019:

Netlify CMS is doing it with GitHub, an example implementation can be found here with all information on how they implemented it netlify-cms-backend-github

I say don't. Data can change at any given time. Instead you should only commit data models in your code, schema and table definitions (create database and create table statements) and sample data for unit tests. This is kinda the way that Laravel does it, committing database migrations and seeds.