Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|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. –  Coder Hawk 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

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

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

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.

share|improve this answer

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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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