Postgresql – How to version control PostgreSQL schema with comments

postgresqlschemaversion control

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?

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

Related Question