[ale] Speaking of Postgresql...

[Michael B. Trausch]

On Thu, 2011-09-22 at 20:28 +0000, Lightner, Jeff wrote:
> Does postgresql have a hot backup mode similar to Oracles wherein one
> can backup files while the DB is still running?    (In Oracle this
> means transactions are being logged rather than committed and then get
> committed after it is taken out of hot backup mode.)  

All you really need to do is use pg_dumpall.

You can also just use pg_dump on your own, for example if you are only
concerned about your particular application's database.  pg_dumpall will
dump everything in the database cluster, while pg_dump itself will only
dump the table specified.

Because of the way that PostgreSQL works, you can run the dump process
while the database server is online and you will get a consistent
backup.  Simply note that pg_dumpall does not take out any sort of a
global lock.  That is, a PostgreSQL client may connect to a single
database at a time, and so transactions are necessarily limited to a
single database.  Therefore, while every individual database will be
consistent with itself, it is possible that the individual databases
won't be consistent with each other, temporally.  Given well-designed
applications, this should never arise in production, so it shouldn't
really be an issue.

You can also do something like have a script that manages the process
directly, effectively replacing pg_dumpall.  Or, you can just use
streaming, synchronous replication to ensure that at least N number of
backup systems have confirmed the database state, and make sure that you
have at least N+1 slaves so that you can take one off-line temporarily
and do a full backup of its database cluster.

There are a lot of possibilities, actually.

For more information, see Chapter 24 of the PostgreSQL manual:
http://www.postgresql.org/docs/9.1/static/backup.html

	--- Mike

-- 
A man who reasons deliberately, manages it better after studying Logic
than he could before, if he is sincere about it and has common sense.
                                  --- Carveth Read, “Logic”