On Mon, Apr 13, 2015 at 8:28 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
There's been recent discussion of adding support for read-only tables. If we had those, we might be able to support something like...
INSERT INTO unlogged;
ALTER TABLE unlogged SET READ ONLY;
CHECKPOINT;
/* take backup */
This should be safe as long as we WAL log changes to read-only status (which presumably we would).
How much work that would entail though, I don't know.
Right. I've been keeping an eye on that discussion with the same intention. The big question is how, during recovery, does it know what state the table was in without being able to read from the system catalogs? Perhaps it would be the checkpointer's duty at the end of the checkpoint to remove the init fork for unlogged relations which were turned to read only before that checkpoint started.
Ultimately you still have to get the data over to the other machine anyway. ISTM it'd be a LOT more useful to look at ways to make the WAL logging of bulk inserts (and especially COPY into a known empty table) a lot more efficient.Jeff Janes makes a comment about wanting "...to avoid blowing out the log archive..."; which I also don't quite follow...
I think the WAL logging of bulk COPY is pretty space-efficient already, provided it is not indexed at the time of the COPY. But no amount of efficiency improvement is going to make them small enough for me want to keep the WAL logs around beyond the next base backup.
What I would really want is a way to make two separate WAL streams; changes to this set of tables goes to the "keep forever, for PITR" stream, and changes to this other set of tables go to the "keep until pg_basebackup is next run" stream. Of course you couldn't have fk constraints between the two different sets of tables.
Having to get the data over to the other machine doesn't bother me, it is just a question of how to do it without permanently intermingling it with WAL logs which I want to keep forever.
The FDW would be a good option, except the overhead (both execution overhead and the overhead of poor plans) seems to be too large. I haven't explored it as much as I would like.
Cheers,
Jeff
