?

Log in

During DB development it became clear, that using a version control system (VCS) for sources ... - PostgreSQL [entries|archive|friends|userinfo]
PostgreSQL Users

[ website | PostgreSQL Web site ]
[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

During DB development it became clear, that using a version control system (VCS) for sources ... [Jan. 12th, 2008|08:07 pm]
PostgreSQL Users

postgresql

[poige]
... in PlPgSQL (and might be for table definitions as well) is rather highly preferable. However, I haven't found a suitable tool yet. It seems that the simpliest way is to dump a DB with Postgresql's pg_dump and then put the source under VCS, but this is poorly satisfactory. I deem the better solution would be a tool ``decomposing'' this dump into a chunk-files, containing only one function definition perhaps with other needed entities. Yeah, this is not too hard to write by myself, but if there's something better (or at least already written) available, I'd use it. :-)
linkReply

Comments:
[User Picture]From: xyphoid
2008-01-12 09:27 pm (UTC)
The most successful approach we've had is to write all the schemas ourselves in an editor and put those into source control, rather than try to use pg_dump to reflect changes. That is, all schema / procedure changes are done in the dumpfiles, and the database is treated as something that's rebuilt all the time. This has the dual advantage that it makes it much easier for several people to work on their own database instance.

The thing to avoid is making any changes to the schema apart from through this method - if you're used to working with pgadmin or navicat,
(Reply) (Thread)
[User Picture]From: poige
2008-01-12 09:37 pm (UTC)

> to write all the schemas ourselves in an editor

Yeah, that's the limitation — having no way to extract schema/code back with proper layout (at least conforming to original) from DB urges to write everything in sources, meanwhile, something sometimes would be better done through GUI tools or scripts.
(Reply) (Parent) (Thread)
[User Picture]From: xyphoid
2008-01-13 12:46 am (UTC)

Re: > to write all the schemas ourselves in an editor

Yeah, it required us to get out of the habit of working on the database directly. However, this actually helped a lot, as mentioned - it's really easy to end up with a messy database if you have several people working on one copy, and don't track changes.

Doesn't help much with dealing with changes to a live database, though.

pg_dump was still pretty useful, when you're (for example) adding indexes while optimising, you can just pg_dump after you're happy and grab the CREATE INDEX statements to add to your DDL.
(Reply) (Parent) (Thread)
[User Picture]From: poige
2008-01-12 09:43 pm (UTC)

> all the schemas ourselves in an editor

I wonder, are your textual sources pure SQL with main "uploader" or are they scripts uploading SQL to RDBMS by itself? One function per file or possibly/often several (related)?
(Reply) (Parent) (Thread)
[User Picture]From: decibel45
2008-01-13 12:29 am (UTC)
There is *no* good way to handle database source code in a VCS, period. Every way you do it has non-trivial drawbacks, at least it does if you care about being able to port an existing database from one version of the schema to another.

Often what I end up doing is sticking table definitions into one file, and views/functions/other things that can easily be re-defined on the fly in another. Then, any time I make a schema change, I'll create a separate file that has commands to patch an existing database to reflect the changes. Luckily, you normally only have to do that when changing tables; you can usually change a view or function and just re-run the script that defines everything.
(Reply) (Thread)
[User Picture]From: poige
2008-01-13 12:43 pm (UTC)

> There is *no* good way to handle database source code in a VCS, period

To me mine is quite good, but the question is what's better and is there anything ready-and-suitable.
(Reply) (Parent) (Thread)