CODEWITHSUNDEEP
postgresqldblink
Slack Groverglow
Slack Groverglow

Reputation: 856

Cross database query using dblink

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?

Upvotes: 1

Views: 1747

Answers (2)

Slack Groverglow
Slack Groverglow

Reputation: 856

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;

Upvotes: 3

rray
rray

Reputation: 2556

To copy rows from local database to remote database you should run the insert statement on remote host and make the search on local host. the syntax is this:

INSERT INTO t1(id, name, description, "date")
    SELECT id_foo, full_name, des, "date" FROM 
    dblink('host= ? user= ? password= ? dbname= externaldb'::text,
    'SELECT id, name, description, "date" FROM t2'::text, false)
    ttemp(id integer, name character varying,  description character varying, "date" date)
)

Change the ? for the local information.

Upvotes: 1

Related Questions