Originally Posted by saumitra
i have some queries about replication of data from one postgresql.. but by some different approch.
as a small summry of my project,
I am working on a project of replication of data. and I have done with kernel module programming in kernel 2.6 that has two machines A and B, when i update any file(in whole directory tree) on some specified directory on machine A, my programs updates the same file on machine B..
(on each write system call on machine A, the difference in new file and old file is patched on machine B)
So, now i can have my PostgreSQL database on some directory say /usr/share/data (on machine A)
and have same on machine B initially.
now what i want to do is replicate the changes made by machine A to B.
so i started my program in this situations by passing whole directory.
1> stopped postgres on B
2> updated on A
3> started postgres on B
4> checked database on B IT WAS UPDATED..
now just problem is, the updation is taking much time.. I WANT THAT TO EXECUTE FASTER.
so can i AVOID replication of SOME FILES?? like log files etc?
like 000010000000 file in pg_xlog its 16MB and taking too much time for patching.
Or should i replicate only files that are in ..../base/ directory?
(currently for checking my code i'm using diff and patch, which i know are not effecient ways..i'll implement those algorithms later)
Even if certain files could be omitted, you'd face two
basic issues w/ that approach.
1) Table data and index for on table will live in
on file each; they will have a certain size, and
the bigger they get, the longer the replication will
take. Your issue is that you have to transfer the whole
file even for a minute change. Or are you taking binary
diffs when you refer to patching?
2) Whether you take diffs or not, the second instance
of Postgres may try to write back local changes while
you're updating the file. You'll run into concurrency
issues. My suggestion would be that you have a close
look at only transferring the WAL and tackling it from
All that said: I think asking on Postgres' General
mailing list would be a marvelous idea.