PostgreSQL跨庫(kù)訪問(wèn)有3種方法：Schema，dblink，postgres_fdw。
方法A：在PG上建立不同SCHEMA，將數(shù)據(jù)和存儲(chǔ)過(guò)程分別放到不同的schema上，經(jīng)過(guò)權(quán)限管理后進(jìn)行訪問(wèn)。
方法A的示例如下：
測(cè)試1（測(cè)試postgres超級(jí)用戶對(duì)不同schema下對(duì)象的訪問(wèn)）
查看當(dāng)前數(shù)據(jù)庫(kù)中的schema
postgres=# \dn
List of schemas
Name | Owner
-------------------+---------
dbms_job_procedure | postgres pgagent | postgres
postgres | postgres
public | postgres
(4 rows)
（當(dāng)前連接數(shù)據(jù)庫(kù)的用戶為postgres）
postgres=# select user;
user
----------
postgres
(1 row)
創(chuàng)建名為test1的schema
postgres=# create schema test1;
CREATE SCHEMA
創(chuàng)建模式test1下的對(duì)象，表ticket1
postgres=# create table test1.ticket1(id int);
CREATE TABLE
可以看到并沒(méi)有我們之前建立的表
postgres=# \d
List of relations
Schema | Name | Type | Owner
-------------------------+---------
public | dept | table | postgres
public | emp | table | postgres
public | jobhist | table | postgres
public | next_empno | sequence | postgres
public | salesemp | view | postgres
(5 rows)
在對(duì)象前加schema，postgres用戶可以訪問(wèn)ticket1表
postgres=# select * from test1.ticket1;
id
-------------------------------------------------
(0 rows)
查看模式 搜索路徑
postgres=# show search_path ;
search_path
----------------
"$user",public
(1 row)
把創(chuàng)建的模式test1添加到模式搜索路徑
postgres=# set search_path to "$user",public,test1;
SET
postgres=# show search_path ;
search_path
------------------------
"$user", public, test1
(1 row)
為了訪問(wèn)方便，在搜索路徑中添加schema對(duì)象之后既可以看到該模式下的表，也可以直接進(jìn)行搜索，而不用添加schema前綴。（這里因?yàn)槭浅?jí)用戶，所以不用給postgres賦權(quán)，如果是普通用戶，想要訪問(wèn)，需要賦權(quán)）
postgres=# \d
List of relations
Schema | Name | Type | Owner
-------------------------+---------
public | dept | table | postgres
public | emp | table | postgres
public | jobhist | table | postgres
public | next_empno | sequence | postgres
public | salesemp | view | postgres
test1 | ticket1 | table | postgres
(6 rows)
postgres=# select * from ticket1;
id
--------------------------------------------
(0 rows)
測(cè)試2：
在postgres用戶下建立名為test2的schema
postgres=# create schema test2;
CREATE SCHEMA
postgres=# create table test2.ticket2(id int);
CREATE TABLE
建立兩個(gè)普通用戶
postgres=# create role test1 login password '123';
CREATE ROLE
postgres=# create role test2 login password '123';
CREATE ROLE
普通用戶連接數(shù)據(jù)庫(kù)
postgres=# \c postgres test2;
Password for user test2:
You are now connected to database "postgres" as user "test2".
postgres=> \d
List of relations
Schema | Name | Type | Owner
-------------------------+---------
public | dept | table | postgres
public | emp | table | postgres
public | jobhist | table | postgres
public | next_empno | sequence | postgres
public | salesemp | view | postgres
(5 rows)
postgres=> show search_path ;
search_path
----------------
"$user",public
(1 row)
postgres=> set search_path to "$user",public,test1;
SET
postgres=> \d
List of relations
Schema | Name | Type | Owner
-------------------------+---------
public | dept | table | postgres
public | emp | table | postgres
public | jobhist | table | postgres
public | next_empno | sequence | postgres
public | salesemp | view | postgres
test1 | ticket1 | table | postgres
test2 | ticket2 | table | postgres
(11 rows)
可以看到test2用戶模式下的ticket2表，但是訪問(wèn)時(shí)權(quán)限不足。
postgres=> select * from test2.ticket2;
ERROR: permission denied for relation ticket2
postgres=> select * from ticket2;
ERROR: permission denied for relation ticket2
通過(guò)postgres超級(jí)用戶賦予權(quán)限，即可訪問(wèn)
postgres=# grant select on all tables in schema test2 to test1;
GRANT
postgres=> select * from test2.ticket2;
id
---------------------------------------------------
(0 rows)
postgres=> select * from ticket2;
id
---------------------------------------------------
(0 rows)
方法B：通過(guò)dblink實(shí)現(xiàn)跨庫(kù)訪問(wèn)
方法B測(cè)試示例如下：
環(huán)境：本地：192.168.56.88 數(shù)據(jù)庫(kù)：postgres
遠(yuǎn)程：192.168.56.99 數(shù)據(jù)庫(kù)：test
PostgreSQL通過(guò)dblink實(shí)現(xiàn)跨庫(kù)訪問(wèn)
測(cè)試1：在同一個(gè)實(shí)例下分別建立兩個(gè)數(shù)據(jù)庫(kù)，通過(guò)dblink 實(shí)現(xiàn)跨庫(kù)訪問(wèn)
postgres=# create database test;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | C | C |
(4 rows)
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table test(id int);
CREATE TABLE
test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
test=# create table test2(id int);
CREATE TABLE
test=# insert into test values ('1111');
INSERT 0 1
test=# \c postgres
You are now connected to database "postgres" as user "postgres".
在postgres數(shù)據(jù)庫(kù)中建立dblink連接到test數(shù)據(jù)庫(kù)
postgres=# create extension dblink;
CREATE EXTENSION
postgres=# select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
---------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
dblink | 10 | 2200 | t | 1.1 | |
(2 rows)
postgres=# select dblink_connect('test_dblink','dbname=test host=localhost port=5432 user=postgres password=postgres');
dblink_connect
----------------
OK
(1 row)
postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);
id
------
1111
(1 row)
通過(guò)建立dblink，在postgres數(shù)據(jù)庫(kù)可以很容易的訪問(wèn)到test數(shù)據(jù)庫(kù)中的數(shù)據(jù)。
為了訪問(wèn)test數(shù)據(jù)庫(kù)中的數(shù)據(jù)方便，我們可以建立一個(gè)視圖，操作如下，我們只需要查詢視圖中的內(nèi)容即可。
postgres=# CREATE VIEW testdb_dblink AS
postgres-# SELECT * FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=test user=postgres password=postgres', 'SELECT * From test') AS t(id int);
CREATE VIEW
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+----------
public | ptest1 | table | postgres
public | ptest2 | table | postgres
public | remote_people_user_name | view | postgres
public | testdb_dblink | view | postgres
(4 rows)
postgres=# select * from testdb_dblink ;
id
------
1111
(1 row)
測(cè)試2：
在兩個(gè)實(shí)例下分別創(chuàng)建數(shù)據(jù)庫(kù)，然后通過(guò)dblink實(shí)現(xiàn)垮庫(kù)訪問(wèn)。
實(shí)例1：
首先需要配置下路由配置，添加一行命令-A INPUT -s 192.168.0.0/16 -j ACCEPT
[root@darry etc]# vi /etc/sysconfig/iptables
...
添加-A INPUT -s 192.168.0.0/16 -j ACCEPT 即允許192.168.0.0的網(wǎng)段訪問(wèn)
....
[root@darry etc]# service iptables reload
iptables: Trying to reload firewall rules: [ OK ]
在IP為192.168.56.88（本地）的postgres數(shù)據(jù)庫(kù)中建立extension
postgres=# create extension dblink;
CREATE EXTENSION
postgres=# select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
---------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
dblink | 10 | 2200 | t | 1.1 | |
(2 rows)
建立dblink 訪問(wèn)IP為192.168.56.99（遠(yuǎn)程）數(shù)據(jù)庫(kù)
postgres=# select dblink_connect('test_dblink','dbname=test host=192.168.56.99 port=5432 user=postgres password=postgres');
dblink_connect
----------------
OK
(1 row)
postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);
id
------
1111
(1 row)
跨庫(kù)事務(wù)測(cè)試
連接遠(yuǎn)程數(shù)據(jù)庫(kù)
postgres=# select dblink_connect('test_dblink','dbname=test host=192.168.56.99 port=5432 user=postgres password=postgres');
dblink_connect
----------------
OK
(1 row)
在遠(yuǎn)程服務(wù)器上開始一個(gè)事務(wù)
postgres=# select dblink_exec('test_dblink','begin;');
dblink_exec
-------------
BEGIN
(1 row)
插入一條數(shù)據(jù)
postgres=# select dblink_exec('test_dblink','insert into test values(7777);');
dblink_exec
-------------
INSERT 0 1
(1 row)
經(jīng)查看遠(yuǎn)程服務(wù)器上已經(jīng)插入一條數(shù)據(jù)
postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);
id
-------
1111
2222
3333
4444
6666
33333
7777
(11 rows)
在遠(yuǎn)程數(shù)據(jù)庫(kù)中查看未發(fā)現(xiàn)數(shù)據(jù)，因?yàn)槭聞?wù)未提交
test=# select * from test;
id
-------
1111
2222
3333
4444
6666
33333
在本地?cái)?shù)據(jù)庫(kù)中提交遠(yuǎn)程連接數(shù)據(jù)庫(kù)中的事務(wù)
postgres=# select dblink_exec('test_dblink','commit;');
dblink_exec
-------------
COMMIT
(1 row)
再次查看
postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);
id
-------
1111
2222
3333
4444
6666
33333
7777
遠(yuǎn)程數(shù)據(jù)庫(kù)中也存在
test=# select * from test;
id
-------
1111
2222
3333
4444
6666
33333
7777
若換成將commit替換成rollback則插入取消
postgres=# select dblink_exec('test_dblink','begin;');
dblink_exec
-------------
BEGIN
(1 row)
postgres=# select dblink_exec('test_dblink','insert into test values(99999);');
dblink_exec
-------------
INSERT 0 1
postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);
id
-------
1111
2222
3333
4444
6666
33333
7777
99999
執(zhí)行回滾操作
postgres=# select dblink_exec('test_dblink','rollback;');
dblink_exec
-------------
ROLLBACK
(1 row)
經(jīng)查看回滾之后，不記錄之前插入的數(shù)據(jù)
postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);
id
-------
1111
2222
3333
4444
6666
33333
7777
方法C：通過(guò)postgres_fdw實(shí)現(xiàn)跨庫(kù)訪問(wèn)
環(huán)境：本地：192.168.0.14，遠(yuǎn)程：192.168.0.17，PG：9.3.9兩臺(tái)機(jī)器的測(cè)試用戶及數(shù)據(jù)庫(kù)均為：test,test
1.在本地?cái)?shù)據(jù)庫(kù)中創(chuàng)建postgres_fdw extension.
[postgres@minion1 bin]$ ./psql test test
psql (9.3.9)
Type "help" for help.
test=# \c test test
You are now connected to database "test" as user "test".
test=# create extension postgres_fdw ;
CREATE EXTENSION
2.在遠(yuǎn)程數(shù)據(jù)庫(kù)上生成測(cè)試數(shù)據(jù) :
[postgres@minion4 bin]$ ./psql test test
psql (9.3.9)
Type "help" for help.
test=# CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
CREATE TYPE
test=# \dT
List of data types
Schema | Name | Description
--------+-----------+-------------
public | user_enum |
(1 row)
test=# select oid from pg_type where typname='user_enum';
oid
-------
16902
(1 row)
test=# CREATE SCHEMA test;
CREATE SCHEMA
test=# CREATE TABLE test.test1 (
test(# c1 int NOT NULL,
test(# c2 int NOT NULL,
test(# c3 text,
test(# c4 timestamptz,
test(# c5 timestamp,
test(# c6 varchar(10),
test(# c7 char(10),
test(# c8 user_enum,
test(# CONSTRAINT t1_pkey PRIMARY KEY (c1)
test(# );
CREATE TABLE
test=# CREATE TABLE test.test2 (
test(# c1 int NOT NULL,
test(# c2 text,
test(# CONSTRAINT t2_pkey PRIMARY KEY (c1)
test(# );
CREATE TABLE
test=# INSERT INTO test.test1
test-# SELECT id,
test-# id % 10,
test-# to_char(id, 'FM00000'),
test-# '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval,
test-# '1970-01-01'::timestamp + ((id % 100) || ' days')::interval,
test-# id % 10,
test-# id % 10,
test-# 'foo'::user_enum
test-# FROM generate_series(1, 1000) id;
INSERT 0 1000
test=# INSERT INTO test.test2
test-# SELECT id,
test-# 'AAA' || to_char(id, 'FM000')
test-# FROM generate_series(1, 100) id;
INSERT 0 100
test=# analyze test.test1;
ANALYZE
test=# analyze test.test2;
ANALYZE
3.在本地?cái)?shù)據(jù)庫(kù)中創(chuàng)建server
test=# CREATE SERVER s1 FOREIGN DATA WRAPPER postgres_fdw;
CREATE SERVER
test=# select * from pg_foreign_server ;
srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
---------+----------+--------+---------+------------+--------+------------
s1 | 17444 | 17449 | | | |
(1 row)
test=# alter server s1 options ( add hostaddr '192.168.0.17', add port '5432', add dbname 'test');
ALTER SERVER
4.SERVER賦權(quán) :
test=# grant usage on foreign server s1 to test;
GRANT
test=# select * from pg_foreign_server ;
srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
---------+----------+--------+---------+------------+---------------+--------------------------------
---------------
s1 | 17444 | 17449 | | | {test=U/test} | {hostaddr=192.168.0.17,port=543
2,dbname=test}
(1 row)
5.在本地?cái)?shù)據(jù)庫(kù)中創(chuàng)建user mapping :
test=# create user mapping for test server s1 options(user 'test',password 'test');
CREATE USER MAPPING
6.在本地?cái)?shù)據(jù)庫(kù)中創(chuàng)建foreign table
test=# CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
CREATE TYPE
test=# \dT
List of data types
Schema | Name | Description
--------+-----------+-------------
public | user_enum |
(1 row)
test=# select oid from pg_type where typname='user_enum';
oid
-------
17453
(1 row)
test=# CREATE FOREIGN TABLE ft1 (
test(# c0 int,
test(# c1 int NOT NULL,
test(# c2 int NOT NULL,
test(# c3 text,
test(# c4 timestamptz,
test(# c5 timestamp,
test(# c6 varchar(10),
test(# c7 char(10),
test(# c8 user_enum
test(# ) SERVER s1 options(schema_name 'test', table_name 'test1');
CREATE FOREIGN TABLE
test=# select * from ft1 limit 1;
ERROR: column "c0" does not exist
CONTEXT: Remote SQL command: SELECT c0, c1, c2, c3, c4, c5, c6, c7, c8 FROM test.test1
test=# alter foreign table ft1 drop column c0;
ALTER FOREIGN TABLE
test=# select * from ft1 limit 1;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
----+----+-------+------------------------+---------------------+----+------------+-----
1 | 1 | 00001 | 1970-01-02 00:00:00+08 | 1970-01-02 00:00:00 | 1 | 1 | foo
(1 row)
test=# create foreign table ft2 (c2 text,c1 int not null) server s1 options(schema_name 'test',table_name 'test2');
CREATE FOREIGN TABLE
test=# select * from ft2 limit 1;
c2 | c1
--------+----
AAA001 | 1
(1 row)
test=# create foreign table ft3(c2 text,c3 int not null) server s1 options(schema_name 'test',table_name 'test2');
CREATE FOREIGN TABLE
test=# select * from ft3 limit 1;
ERROR: column "c3" does not exist
CONTEXT: Remote SQL command: SELECT c2, c3 FROM test.test2
test=# alter foreign table ft3 alter column c3 options (column_name 'c1');
ALTER FOREIGN TABLE
test=# select * from ft3 limit 1;
c2 | c3
--------+----
AAA001 | 1
(1 row)
test=# create foreign table ft4(c2 text,c3 int options (column_name 'c1') not null) server s1 options(schema_name 'test',table_name 'test2');
CREATE FOREIGN TABLE
test=# select * from ft4 limit 2;
c2 | c3
--------+----
AAA001 | 1
AAA002 | 2
(2 rows)
PostgreSQL跨庫(kù)訪問(wèn)事務(wù)測(cè)試
遠(yuǎn)程機(jī)器創(chuàng)建測(cè)試表
test=# create table test3(id int);
CREATE TABLE
test=# select * from test3;
id
----
(0 rows)
本地機(jī)器測(cè)試
創(chuàng)建對(duì)應(yīng)的外部表
test=# create foreign table ft_test3(id int) server s1 options(schema_name 'test',table_name 'test3');
CREATE FOREIGN TABLE
test=# select * from ft_test3 ;
id
----
(0 rows)
本地機(jī)器事務(wù)測(cè)試（不提交）
test=# begin;
BEGIN
test=# insert into ft_test3 values (100);
INSERT 0 1
test=# insert into ft_test3 values (200);
INSERT 0 1
test=# insert into ft_test3 values (300);
INSERT 0 1
test=# select * from ft_test3 ;
id
-----
100
200
300
(3 rows)
test=# rollback;
ROLLBACK
test=# select * from ft_test3 ;
id
----
(0 rows)
本地機(jī)器事務(wù)測(cè)試（提交）
test=# begin;
BEGIN
test=# insert into ft_test3 values (1000);
INSERT 0 1
test=# insert into ft_test3 values (2000);
INSERT 0 1
test=# insert into ft_test3 values (3000);
INSERT 0 1
test=# end;
COMMIT
test=# select * from ft_test3 ;
id
------
1000
2000
3000
(3 rows)
test=# rollback;
NOTICE: there is no transaction in progress
ROLLBACK
您可能感興趣的文章:- python連接PostgreSQL數(shù)據(jù)庫(kù)的過(guò)程詳解
- docker環(huán)境下數(shù)據(jù)庫(kù)的備份(postgresql, mysql) 實(shí)例代碼
- PHP實(shí)現(xiàn)基于PDO擴(kuò)展連接PostgreSQL對(duì)象關(guān)系數(shù)據(jù)庫(kù)示例
- C# 操作PostgreSQL 數(shù)據(jù)庫(kù)的示例代碼
- 在Ubuntu中安裝Postgresql數(shù)據(jù)庫(kù)的步驟詳解
- Python連接PostgreSQL數(shù)據(jù)庫(kù)的方法
- PostgreSQL將數(shù)據(jù)加載到buffer cache中操作方法
