Thread: copying data into another database ? (replication)
Hi everyone on the list.
I've been trying to get a way to copy a postgresql table or some columns from table into another one, but this new table must be in another databse and, also, could be in another server. I was working on a jdbc app , which run well when the destiny table was in the same database than the source ( it was with "create table as select ... into)"), really easy, but we have the other case.
I feel it could be possible writing a function , which receives some variables ( postgresql server, db and table, sql query for filtering data). The problem is that I cant find any example about the following:
-- connecting to a server within the function.
-- retrieve data form this server ( I think some cursors would be useful to save each column, and run by the rows)
-- connecting to another server, und create a table (with a name provided by user), where I can insert data from cursors.
I think it could be answered by replication (Slony ? ), but I have to do it without any other tool.
Does anyone knows If it's possible, and how to do it?
thanks in advance.
Consigue el nuevo Windows Live Messenger Pruébalo
Look up dblink(...) function in PG documentation:
Igor Neyman
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Raul Retamozo
Sent: Wednesday, June 06, 2007 7:09 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] copying data into another database ? (replication)
Hi everyone on the list.
I've been trying to get a way to copy a postgresql table or some columns from table into another one, but this new table must be in another databse and, also, could be in another server. I was working on a jdbc app , which run well when the destiny table was in the same database than the source ( it was with "create table as select ... into)"), really easy, but we have the other case.
I feel it could be possible writing a function , which receives some variables ( postgresql server, db and table, sql query for filtering data). The problem is that I cant find any example about the following:
-- connecting to a server within the function.
-- retrieve data form this server ( I think some cursors would be useful to save each column, and run by the rows)
-- connecting to another server, und create a table (with a name provided by user), where I can insert data from cursors.
I think it could be answered by replication (Slony ? ), but I have to do it without any other tool.
Does anyone knows If it's possible, and how to do it?
thanks in advance.
Consigue el nuevo Windows Live Messenger Pruébalo