Hello,
I am curious to find out how to use a PGSQL interface, from the server,
within a function.
What I am trying to do is build a replication scheme. I have a set of
tables, and these tables have a bit field that will be used to indicate the
replication state. Null for new records, set to 0 for being replicated, then
1 for replicated.
I want to, from the target server, initiate a:
**********************************************************************
/*Yes I know that PostgreSQL does not like [] but used them to indicate *the
remote table: ) Sorry if my syntax is off. You will get the idea *though...
*/
BEGIN TRANSACTION REPLICATE;
UPDATE [REMOTE_SERVER].remote_db.remote_table
SET replication = 0 WHERE replication IS NULL;
SELECT INTO local_table
(SELECT * FROM [REMOTE_SERVER].remote_db.remote_table
WHERE replication = 0);
UPDATE [REMOTE_SERVER].remote_db.remote_table
SET replication = 1 WHERE replication = 0;
COMMIT TRANSACTION REPLICATE;
*****************************************************************
In this manner I mark the records, transfer data, then update the original
table to show that the records in question have been replicated so that they
will not be replicated twice.
I can do this in a Perl Script, or C, scheduled with cron, but would like to
build all of the code into the database for obvious reasons (encapsulation,
speed).
In MSSQL you define a remote db, and the local user that the remote user
will be impersonating. You do the same for the localdb on the remote. From
there you can reference a remote table like so:
[hostname].dbname.tablename
BTW: To pre-empt questions and a possible idiot flame about this... I do
things this way in MSSQL because I am transferring data on a connection that
we pay for, by the bandwidth we use. MSSQL replication has a terrible amount
of connection overhead, and is very slow; we cut it down to < 10% by rolling
our own. It is also much faster now. Maybe we were doing something wrong...
but it is also much easier to maintain, add and remove servers, and do
backups this way.
Is there a way to do this in PostGreSQL? Is there an easy way to replicate
data, from one server to another, that I am overlooking? I can initiate the
function calls from a cron job, but would prefer to have the actual code in
the db server so that it will move with the db for dumps, duplication, and
backups.
I have been researching this for the past week and am still empty handed,
excepting an external Perl script or possible C or C++ solutions.
thx,
Neil P Davis
_____________________________________________________________________________________
Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com