You asked:
how postgreSQL will handle the recovery with a pg_data content containing some files which are inconsistent.
pg_start_backup()
ensure the data file is at least as new as the checkpoint. On recovery, the logs are applied.
If the data is old, the log will update it..
If the data is new, the log will have same content. There is no hurt writing it again.
The data are never newer then the log, because the logs are write ahead (WAL).
You asked:
... xfs-freeze
...
xfs-freeze
is alike to pg_start_backup()
, it don't take a snapshot. You need a volume manager to do that.
You asked:
... why do create tablespace & create database statements are unsupported if the WAL can replay everything?
It is supported, just some little gotcha. See http://www.postgresql.org/docs/8.1/static/backup-online.html :
23.3.5. Caveats
CREATE TABLESPACE commands are WAL-logged with the literal absolute
path, and will therefore be replayed as tablespace creations with the
same absolute path. This might be undesirable if the log is being
replayed on a different machine. It can be dangerous even if the log
is being replayed on the same machine, but into a new data directory:
the replay will still overwrite the contents of the original
tablespace. To avoid potential gotchas of this sort, the best practice
is to take a new base backup after creating or dropping tablespaces.
There's no exact equivalent to what you want. Options include:
Atomic file-system snapshot
If you're using an atomic file system snapshot you don't need to freeze the database. It might make recovery a little faster if you force a CHECKPOINT
first, but that's about it. When you take a filesystem snapshot and copy it, then start the copy, to PostgreSQL it's as if it crashed and is starting back up. That's perfectly fine, it's crash safe and designed to work that way.
This approach is only valid if the snapshot is atomic - it's all at the same virtual instant. You can't get an atomic snapshot across multiple file systems (on Linux, at least), so if your DB is split across multiple tablespaces or WAL is on a separate disk to the heap you cannot use this approach.
pg_start_backup
/ pg_stop_backup
If you can't do an atomic snapshot you can enable WAL archiving, run pg_start_backup
, copy the DB, run pg_stop_backup
and capture the last WAL archives generated.
It's a bit more complicated, but it gives you a consistent backup without stopping writes and without needing file-system level atomic snapshots.
pg_basebackup
An alternative to using pg_start_backup
and pg_stop_backup
is to use pg_basebackup
to do a streaming copy of the DB over the PostgreSQL replication protocol with --xlog-method=stream
. This requires only a PostgreSQL replication connection, doesn't require the DB to be stopped, and is pretty seamless.
--xlog-method=stream
was only added in pretty recent versions, and pg_basebackup
its self is fairly new.
pg_dump
I didn't initially mention it because you were looking for external tools, but there's always pg_dump
, which gets a SERIALIZABLE
snapshot of the database and dumps it. The DB keeps running like normal (it can still accept writes) and the dump is entirely internally consistent from the time you started the dump.
Write quiescence
Stopping all incoming transactions won't stop PostgreSQL writing. It'll still have VACUUM
work to do with autovacuum, checkpoints to perform, stats to write, etc.
There's no feature in Pg to stop all writes at this point. It might be nice to add, but I'm not aware of anyone working on it.
Some file systems, like XFS, support write freezing at the file system level; this causes all writes to block until the freeze is released. It's safe to freeze all file systems then copy all file systems.
Best Answer
After culling answers elsewhere on the internet, I devised a solution. The other answers were in and of themselves, incomplete. So I am presenting an answer here in hopes it will benefit others.
The Strategy
default_transaction_read_only
setting totrue
.Once that is done, you would (in my solution):
CHECKPOINT
(I think this is the safest, but apg_xlog_switch()
would be appropriate for very high-load servers)Pitfalls
pg_stat_clear_snapshot()
Restoring the read-write state is not so simple. If read-only connections now exist, you must terminate them in order for the new read-write status to take effect. But new connections might arrive while killing existing ones. So again, you must
false
Alternate strategy
Another strategy is to change the permissions on the role used by the application. This can be quite messy and is less general.
For instance, you'd have to revoke/re-grant on not just tables, but sequences, large objects, and probably the schema itself. Further, what exactly is the behavior of existing connections when you change the access? Probably no impact, which means you also need to kill those backends. Finally, let's say the application has read-write access to most tables, but not to others in the schema. You'd have to make sure your re-granting doesn't include those objects as well.
Another possibility is to LOCK all the tables, by querying the catalog and performing a dynamic query. That seemed to perilous for my tastes.
Implementation
Pause_service
The database instance name is 'gitlabhq' and the username of the application is 'gitlab'. Replace it with your own:
Resume
There's a possibility that in this last step you will kill long-running read-only/SELECT queries, but in my experience, such long-running queries can last minutes if not hours, and it's acceptable to kill these in order to ensure uptime for everyone else.