On 4/13/15 7:32 PM, David G. Johnston wrote:
That particular use-case would probably best be served with a separate
replication channel which pushes data files from the primary to the
slaves and allows for the slave to basically "rewrite" its existing
table by pointing to the newly supplied version. Some kind of "CREATE
STATIC TABLE" and "PUSH STATIC TABLE TO {all | replica name}" command
combo...though ideally with less manual intervention...
You still have the same problem of knowing if someone has scribbled on the data since the last checkpoint.
That seems like an automation concern though...the more limited idea was to simply have a means for a table to exist on the master and allow the user to cause an exact copy of that table to appear on a replica via direct data transfer (i.e., without need to create a backup/dump). If the table already exists on the replica the existing version remains as-is until the new table is fully push and then a filenode pointer update happens. If changes are made to the master the two tables will remain diverged until a new push occurs.
I imaging this same idea could be handled external to the database though I'm don't know enough to comment on the specific technical merits of each.
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.
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...
WAL does seem to be designed to solve a different problem that what is described here - lots of small changes versus few large changes. Improving WAL to move the size at which small becomes large is a win but another channel designed for few large changes may be less complex to implement. The current work in logical replication likely has merit here as well but my familiarity with that technology is fairly limited.
David J.
