I would strongly recommend that you treat your database basically the same way as you treat your application code. You can script your database out to it's component parts and check those into source control and then use the same labels & versions there that you use for your apps.
To get the objects into source control there are a number of tools you can use. Microsoft has a tool that is nicknamed Data Dude. It works with Visual Studio. They're also preparing to release a new tool called SQL Server Database Tools (SSDT), again, working with Visual Studio. My company, Red Gate Software, makes a tool that works with SSMS called SQL Source Control.
In terms of process, I wrote several chapters for the book Red Gate Guide to Team Development. It's available as a free download (or if you want to kill a tree you can purcahse one from Amazon). I go into a lot more details about working with databases in development teams there.
To be truly highly available you will need some logic built into the application to handle this. You can do this sort of thing with pg_pool, but what if pg_pool breaks?
I think there are many ways you could work this one, but the way I would do it would be:
Master DB (lets call it A), and two slaves (lets call them B and C). B pulls changes from A and C pulls changes from B in a cascading replication setup. This means if A fails and is a gonner promote B, no need to change C as its already a slave of B, when A is back you make it a slave of C and so on.
You will need to make changes to your application so that it has some intelligence in it regarding the state of the database. This would be typically done by having a layer that manages the connections, and it runs a test query (like "SELECT current_timestamp") if the query passes all is fine and it gives the connection to your application to use. Each application will need to know a list of servers to use in which order so the one that writes will use A first then B. So in the event the primary fails it tries the secondary (and potentially the third and so on), until it gets one where the test query works.
With this in place if C fails its only read only, the applications will just fail over to B or A and start using C if you get it back. If B fails, same deal though you might want to do something with C pointing it to A. If A fails then the system will go read only (but will still work for all read queries). There will then need to be some human intervention to say "yep A is gone", promote B (which will only take seconds), when B is promoted the writers will go though the connection layer, it will test A and fail test B and pass to the application will get a connection to B which is now a master so writes will be allowed.
I appreciate that this is still not a full HA solution (as there is still a manual step, but its a very simple and quick one), but I think we are going to have to wait for PostgreSQL 9.5 for that.
As I say I dont think there is one dead cert right answer to this, but this is how I would have done it.
Hope it helps
Best Answer
Strictly speaking, the term "stored procedures" points to SQL procedures in Postgres, introduced with Postgres 11. Related:
There are also functions, doing almost but not quite the same, and those have been there from the beginning.
Functions with
LANGUAGE sql
are basically just batch files with plain SQL commands in a function wrapper (and therefore atomic, always run inside a single transaction) accepting parameters. All statements in an SQL function are planned at once, which is subtly different from executing one statement after the other and may affect the order in which locks are taken.For anything more, the most mature language is PL/pgSQL (
LANGUAGE plpgsql
). It works well and has been improved with every release over the last decade, but it serves best as glue for SQL commands. It is not meant for heavy computations (other than with SQL commands).PL/pgSQL functions execute queries like prepared statements. Re-using cached query plans cuts off some planning overhead and makes them a bit faster than equivalent SQL statements, which may be a noticeable effect depending on circumstances. It may also have side effects like in this related question:
This carries the advantages and disadvantages of prepared statements - as discussed in manual. For queries on tables with irregular data distribution and varying parameters dynamic SQL with
EXECUTE
may perform better when the gain from an optimized execution plan for the given parameter(s) outweighs the cost of re-planning.Since Postgres 9.2 generic execution plans are still cached for the session but, quoting the manual:
We get best of both worlds most of the time (less some added overhead) without (ab)using
EXECUTE
. Details in What's new in PostgreSQL 9.2 of the PostgreSQL Wiki.Postgres 12 introduces the additional server variable
plan_cache_mode
to force generic or custom plans. For special cases, use with care.You can win big with server side functions that prevent additional round-trips to the database server from your application. Have the server execute as much as possible at once and only return a well defined result.
Avoid nesting of complex functions, especially table functions (
RETURNING SETOF record
orTABLE (...)
). Functions are black boxes posing as optimization barriers to the query planner. They are optimized separately, not in the context of the outer query, which makes planning simpler, but may result in less than perfect plans. Also, cost and result size of functions cannot be predicted reliably.The exception to this rule are simple SQL functions (
LANGUAGE sql
), which can be "inlined" - if some preconditions are met. Read more about how the query planner works in this presentation by Neil Conway (advanced stuff).In PostgreSQL a function always automatically runs inside a single transaction. All of it succeeds or nothing. If an exception occurs, everything is rolled back. But there is error handling ...
That's also why functions are not exactly "stored procedures" (even though that term is used sometimes, misleadingly). Some commands like
VACUUM
,CREATE INDEX CONCURRENTLY
orCREATE DATABASE
cannot run inside a transaction block, so they are not allowed in functions. (Neither in SQL procedures, yet, as of Postgres 11. That might be added later.)I have written thousands of plpgsql functions over the years.