dblink(Database Link)数据库链接顾名思义就是数据库的链接，就像电话线一样，是一个通道，当我们要跨本地数据库，访问另外一个数据库表中的数据时，本地数据库中就必须要创建远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。
1. 在postgresql中创建dblink
1.1. 在什么业务场景下需要创建一个dblink？
往往随着系统的不断演进，系统往往会根据需要对系统以及数据库做拆分，在数据库层面单体数据库往往在应对“三高”(高并发、高可用、高效率)问题上捉襟见肘，而这个时候往往会对数据进行拆分，而在拆分数据库的过程中，虽然遵循一定的方法论但是最后呈现出来的剖分方式上可以说天马行空，而在这种天马行空的设计后，当需求发生变化或升级中总会有数据库中的交互，比如子库需要在发生变化时，将子库的一些信息推送到主库中。
而笔者在面临的业务场景时分布式平台中，往往分布式节点(下文中均称呼为NodeDB)中的数据库会发生数据变化，而在数据发生变化对于分布式主库(下文中均称呼为XXLJobDB)需要实时更新统计信息。所以笔者对于此种业务场景的解决方法为在NodeDB中创建一个XXLJobDB的数据连接DBlink。每当NodeDB中数据信息发生变化时，我们将变化信息通过数据连接DBlink推送到XXLJobDB数据库中。
1.2. 在postgresql中创建一个dblink实操。
- 创建一个dblink
- 创建dblink
SELECT dblink_connect('xxljobDB','hostaddr=200.200.200.200 port=5432 dbname=xxljob user=postgres password=postgres');
但是直接创建时会发生错误。
这个需要开启postgresql数据库的扩展。
create extension dblink;--查看pgsql数据库已安装的扩展
select * from pg_extension;
开启扩展之后，再创建dblink既可在当前会话中直接使用。
SELECT dblink_connect('xxljobDB','hostaddr=200.200.200.200 port=5432 dbname=xxljob user=postgres password=postgres');
- 使用dblink操作远端数据库
- 查询远程数据库中的数据表
select * from dblink('xxljobDB','select id from tbtest;') as t1(id int);select * from dblink('xxljobDB','select id,name from tbtest;') as t1(id int,name varchar);
- 将远程数据库上的数据插入本地数据库
insert into test1 select * from dblink('test','select id tbtest;') as t1(id int);
- 断开连接
select dblink_disconnect('test');
- 创建一个跨库dblink视图，避免每一次都要创建dblink
CREATE VIEW view_remote_tb1 AS
select * from
dblink('hostaddr=200.200.200.200 port=5432 dbname=xxljob user=postgres password=postgres'::text,'select * from tbtest'::text)t
(id integer,name character varying));
- dblink中的事务管理
SELECT dblink_exec('mycoon', 'BEGIN');
SELECT dblink_exec('mycoon', 'COMMIT');
SELECT dblink_exec('mycoon', 'ROLLBACK');
- 执行数据操作命令
SELECT dblink_exec('mycoon', 'insert into tb1 select generate_series(10,20),''hello''');
- 在存储过程中创建dblink然后"为所欲为"
create or replace function SyncStat2RemoteDB(out RunState bool )
AS
$BODY$
declarev_task_info_rec record;xxljob_dblink_conn text;
beginRunState=True;for v_task_info_rec in EXECUTE 'select * from check_task_info' loopxxljob_dblink_conn:=v_task_info_rec.xxljob_dblink_conn;EXECUTE 'select dblink_connect(''xxljobDB'','''||xxljob_dblink_conn||''')';--在这里可以做一些为所欲为的事情...--在这里可以做一些为所欲为的事情...SELECT dblink_disconnect('xxljobDB');Return;end loop;exception when others then SELECT dblink_disconnect('xxljobDB');raise exception 'error--(%)(%)',sqlerrm,insertSql;
end;
$BODY$
language plpgsql;
2. postgresql中dblink注意事项
-
postgresql官网地址：https://www.postgresql.org
-
postgresql推荐书籍：https://www.postgresql.org/docs/books/
-
pg中的dblink需要创建扩展“create extension dblink;”否则无法使用，当然创建扩展只需要一次即可。
-
pg中的dblink无法保存，只可以作为会话中的一种连接信息，无法作为像Oracle数据库中连接对象保存在数据库中，即每次使用都需要创建连接，无法直接使用。（笔者使用的pg版本为pg12.5）【如果有更新的版本或者什么方式可以保留下连接dblink对象，希望各位大佬能够不吝赐教】
-
能否用dblink去连接oracle数据库呢？像oracle的dblink一样，连接SQL Server、MySQL、PostgreSQL？
答案是不行的，源码里面可以看到PostgreSQL的dblink是使用的pg的c语言接口去创建连接的，而不是使用ODBC来创建：
dblink_connect(PG_FUNCTION_ARGS)
{char *conname_or_str = NULL;char *connstr = NULL;char *connname = NULL;char *msg;PGconn *conn = NULL;remoteConn *rconn = NULL;dblink_init();if (PG_NARGS() == 2){conname_or_str = text_to_cstring(PG_GETARG_TEXT_PP(1));connname = text_to_cstring(PG_GETARG_TEXT_PP(0));}else if (PG_NARGS() == 1)conname_or_str = text_to_cstring(PG_GETARG_TEXT_PP(0));if (connname)rconn = (remoteConn *) MemoryContextAlloc(TopMemoryContext,sizeof(remoteConn));/* first check for valid foreign data server */connstr = get_connect_string(conname_or_str);if (connstr == NULL)connstr = conname_or_str;/* check password in connection string if not superuser */dblink_connstr_check(connstr);conn = PQconnectdb(connstr);if (PQstatus(conn) == CONNECTION_BAD){msg = pchomp(PQerrorMessage(conn));PQfinish(conn);if (rconn)pfree(rconn);ereport(ERROR,(errcode(ERRCODE_SQLCLIENT_UNABLE_TO_ESTABLISH_SQLCONNECTION),errmsg("could not establish connection"),errdetail_internal("%s", msg)));}/* check password actually used if not superuser */dblink_security_check(conn, rconn);/* attempt to set client encoding to match server encoding, if needed */if (PQclientEncoding(conn) != GetDatabaseEncoding())PQsetClientEncoding(conn, GetDatabaseEncodingName());if (connname){rconn->conn = conn;createNewConnection(connname, rconn);}else{if (pconn->conn)PQfinish(pconn->conn);pconn->conn = conn;}PG_RETURN_TEXT_P(cstring_to_text("OK"));
}
3. 借鉴资源
- https://www.cnblogs.com/kuang17/p/10833458.html
- https://blog.csdn.net/cliviabao/article/details/78398992
- https://blog.csdn.net/luojinbai/article/details/45032683
- https://www.zhihu.com/pub/reader/119564896/chapter/975097201710587904?mode=book_read_ordinary
