I'm not familiar with postgres but I'm sure there has to be a way to source control its functions. Currently my team adds functions on a postgresql database directly, and when it's time to deploy to production they just move the functions manually, so it's natural to forget to move certain functions.
Is there a tool similar to git that would allow them to branch out, merge and deploy?
You can use a custom PostgreSQL extension created with CREATE EXTENSION.
Put all of your extension code in a build system and have your developers work on that. Then you can install the extensions easily and version control them, roll them back, upgrade them, etc.
Here is a tutorial to get started.
A simple approach, using the PostgreSQL tools and git (or the CVS of your choice), is to have a periodic task (with cron or equivalent) that runs a script doing two things:
pg_dump the schema of your development database into a schema.sql file, by means of: pg_dump --host my_host --username "postgres" --format plain --schema-only --create --encoding --file schema.sql my_database (you should have .pgpass adequally set to deal with identification/authorization).git add schema.sql | git commit -a -m "Possible schema changes to the DB"Even if this is not perfect, it allows to have a (daily?) record of schema changes. When you need to deploy all those changes, your schema.sql has all the SQL instructions necessary.
It is not easy to have branches and merges, but it would be actually possible if, as part of your "switch branch process", you actually drop your database and recreate it from schema.sql, and have some extra mechanism to fill it with adequate data. git can deal with schema.sql because it's a text file, it could also deal with a database.sql that would include all the data (assuming the size is reasonable).
You can build your own template Database and store just the custom functions there.
When deploying you can just backup and restore this database as SQL.
Link to documentation
