濮阳杆衣贸易有限公司

主頁(yè) > 知識(shí)庫(kù) > PostgreSQL數(shù)據(jù)庫(kù)中跨庫(kù)訪問(wèn)解決方案

PostgreSQL數(shù)據(jù)庫(kù)中跨庫(kù)訪問(wèn)解決方案
熱門標(biāo)簽：智能外呼系統(tǒng)官網(wǎng) 青白江400企業(yè)電話申請(qǐng) 小裙科技電銷機(jī)器人怎樣 外呼線路資源屬于電信業(yè)務(wù)嗎 crm外呼系統(tǒng)聯(lián)系方式 呼和浩特外呼系統(tǒng)原理是什么 長(zhǎng)沙電銷外呼防封卡是什么 內(nèi)蒙古營(yíng)銷智能外呼系統(tǒng)哪個(gè)好 河南電話外呼系統(tǒng)招商

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ù)器上開(kāi)始一個(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中操作方法

標(biāo)簽：黃石 白山 池州 舟山 楚雄 呼倫貝爾 菏澤 安順

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《PostgreSQL數(shù)據(jù)庫(kù)中跨庫(kù)訪問(wèn)解決方案》，本文關(guān)鍵詞  PostgreSQL,數(shù)據(jù)庫(kù),中跨,庫(kù),；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《PostgreSQL數(shù)據(jù)庫(kù)中跨庫(kù)訪問(wèn)解決方案》相關(guān)的同類信息！
  • 本頁(yè)收集關(guān)于PostgreSQL數(shù)據(jù)庫(kù)中跨庫(kù)訪問(wèn)解決方案的相關(guān)信息資訊供網(wǎng)民參考！
    • 推薦文章
    靖西县| 杭锦旗| 崇文区| 利辛县| 万年县| 莎车县| 丹巴县| 宜都市| 施甸县| 韩城市| 台山市| 丹东市| 华亭县| 开阳县| 德钦县| 连云港市| 甘孜| 青田县| 德保县| 贺兰县| 惠水县| 玉树县| 攀枝花市| 鄯善县| 五寨县| 安国市| 南汇区| 开鲁县| 合江县| 彭泽县| 昔阳县| 改则县| 汝南县| 太仓市| 离岛区| 远安县| 抚宁县| 任丘市| 平湖市| 宜章县| 松滋市|