9

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?

| improve this question | |
  • 2
    I don't think the question is psql specific. Have you read some of the answers at SO stackoverflow.com/…? There might be something for you. – DrColossos Feb 27 '11 at 14:53
  • @DrColossos - some of those questions are good migration candidates. – CoderHawk Feb 28 '11 at 8:21
  • @DrColossos is COMMENT ON available in a non postgres environment? I don't think it's standard SQL. which means this could be postgres specific. – xenoterracide Feb 28 '11 at 10:00
  • @xenoterracide You are right, I was more talking about problem of the versioning of a database itself – DrColossos Feb 28 '11 at 12:28
9

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.

| improve this answer | |
  • Truly helpful, but I don't want to mark this as Answer just yet because I'm hoping to get a best practices answer. – Aleksandr Levchuk Feb 28 '11 at 11:57
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.

| improve this answer | |
2

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.

| improve this answer | |
  • "This way the code is version controlled before ever being executed." And it should be. – Mike Sherrill 'Cat Recall' Mar 1 '11 at 2:38
  • @catcall yeah but if you read the ops post, I don't think that's the case. – xenoterracide Mar 1 '11 at 9:27
  • It's unfortunately not the case in most places I've seen. But that's the only way to guarantee that the code you test and QA is the same code you move to production. The idea that the "true" database is in the VCS, not in the DBMS, is not widespread. – Mike Sherrill 'Cat Recall' Mar 1 '11 at 11:20
0

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...

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.