I'd like to use dblink in postgres to copy data from a local database to a remote one. This what I came up with:
SELECT * FROM dblink('archive', 'INSERT INTO data SELECT * FROM local.data')
It wont work and I think I understand why. I get the error relation "local.data" does not exist, If possible, Id like to run this inside a stored procedure on the local database, in other words I want to push the data to the 'archive' server, rather than pull it to the 'archive'.
How can I tell postgres to look in the local database? How can I address the local database from inside the dblink query?
I couldn't find a way to do this through dblink, but was able to do it using postgres_fwd. I had to create a foreign table first associated with the table I want to insert into.
CREATE EXTENSION postgres_fdw;
CREATE SERVER archive FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'archive_db');
CREATE USER MAPPING FOR CURRENT_USER SERVER archive OPTIONS (user 'user', password 'pass');
CREATE FOREIGN TABLE archivedata (..) SERVER archive OPTIONS (schema_name 'public', table_name 'data');
INSERT INTO archivedata SELECT * FROM data;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments