Check out repmrg:
repmgr is a set of open source tools that helps DBAs and System
administrators manage a cluster of PostgreSQL databases..
By taking advantage of the Hot Standby capability introduced in
PostgreSQL 9, repmgr greatly simplifies the process of setting up and
managing database with high availability and scalability requirements.
repmgr simplifies administration and daily management, enhances
productivity and reduces the overall costs of a PostgreSQL cluster by:
- monitoring the replication process; allowing DBAs to issue high
- availability operations such as switch-overs and fail-overs.
It does two things:
- repmgr: command program that performs tasks on your cluster and then exits
- repmgrd: management and monitoring daemon that watches the cluster and can automate remote actions.
For automatic failover, repmgrd does the trick and is not a SPOF in your network, like pgPool. However, it is still important to monitor all deamons and bring them back up after failure.
Version 2.0 is about to be released, including RPM's.
You can dump the whole PostgreSQL cluster with pg_dumpall. That's all the databases and all the globals for a single cluster. From the command line on the server, I'd do something like this. (Mine's listening on port 5433, not on the default port.) You may or may not need the --clean option.
$ pg_dumpall -U postgres -h localhost -p 5433 --clean --file=dump.sql
This includes the globals--information about users and groups, tablespaces, and so on.
If I were going to backup a single database and move it to a scratch server, I'd dump the database with pg_dump, and dump the globals with either
pg_dumpall --globals-only, orpg_dumpall --roles-only (if you only need roles)
like this.
$ pg_dump -U postgres -h localhost -p 5433 --clean --file=sandbox.sql sandbox
$ pg_dumpall -U postgres -h localhost -p 5433 --clean --globals-only --file=globals.sql
Outputs are just text files.
After you move these files to a different server, load the globals first, then the database dump.
$ psql -U postgres -h localhost -p 5433 < globals.sql
$ psql -U postgres -h localhost -p 5433 < sandbox.sql
I thought pg_dumpall would at least backup foreign keys, but even that
seems to be an 'option'. According to:
http://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html even
with pg_dumpall I need to use a -o option to backup foreign keys
No, that reference says "Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used." (Emphasis added.) I think it's unlikely that your application references the OID columns. You don't need to use this option to "backup foreign keys". (Read the dump file in your editor or file viewer.)
Best Answer
It's not a bug, as the psql documentation warns about this:
The expansion of
:'env'in-c "SELECT :'env';"is a psql-specific feature.I would suggest that the solution is to use heredoc rather than
-c:Output: