How to version control PostgreSQL schema with comments?

Question

I version control most of my work with Git: code, documentation, system configuration. I am able to do that because all my valuable work is stored as text files.

I have also been writing and dealing with lot of SQL schema for our Postgres database. The schema includes views, SQL functions, and we will be writing Postgres functions in R programing language (via PL/R).

I was trying to copy and past the chunks schema that I and my collaborators write but I forget to do that. The copy and past action is repetitive and error prone.

The pg_dump / pg_restore method will not work because it looses comments.

Ideally I would like to have some way to extract my current schema into a file or files and preserve the comments so that I can do version control.

What is the best practice to version control schema with comments?

Answer 1

Why don't you COMMENT ON the various SCHEMA components, that way your comments are in the schema, and will get dumped.

COMMENT stores a comment about a database object.

To modify a comment, issue a new COMMENT command for the same object. Only one comment string is stored for each object. To remove a comment, write NULL in place of the text string. Comments are automatically dropped when the object is dropped.

Answer 2

Version controlling schemas has always been problematic for me. I generally version control the schema generated by the data modeling tool I am using. The model is also version controlled. I use diffs between the current and previous schema to build the patch required to update the schema. Some modeling tools create usable schema update scripts. The update scripts are also version controlled.

I occasionally see scripts that are intended to dump the schema in a format suitable to regenerate the schema. One of these may be what you are looking for. Some of the modeling and query tools are capable of creating schema regeneration scripts form an existing schema. If you can script this it may give you a file suitable for version control.

Answer 3

An alternative (or you can combine them) to my earlier proposal is to write your SQL code in your editor (IDE) and save the files, and commit them to your VCS, after that run the code on the database using psql -1f. This way the code is version controlled before ever being executed.

Answer 4

I am working in similar project. This is my design proposal:

1. Comment DB objects on a regular time basis lets say every two weeks or twice a month.
2. do pg_dump all (yes get everything to make sure you get all small details and relationships). Name them by yyyymmdd-VERSION.dump
3. If using Git use a plugin for large files

4. If not using a repo then create a simple table in text .CSV format like the table below:

   version | file name | date | description | 1.0 | yyyymmdd-v10.dump | yyyymmdd | new version of user table | 1.1 | backupDB-v11.dump | yyyymmdd | normalized reports tables |

5. by keeping a relationship in CSV file of the generated dumps by filename you can track them somehow easily and you make sure the restore will work because you dumped absolutely everything.

Nowadays any cloud storage or on site storage shouldnt be so expensive even if talking about TBs of data. there are some raging from 700 to 1000 USD with up to 16 TB.

You can even save $$$ a lot more if you move to a storage cloud like the sorts of the most popular one AWS S3

If a good design and organization's standards are defined to keep track of all the IT infrastructure and assets it shouldnt be painful once implemented, it can be relatively simple and will save you configuration's pains and most importantly time...