PostGIS mysql_fdw操作日志（留观）
#####Linux终端操作命令记录，留做自己后面研究，绿色部分为成功部分
错误: 服务器"mysql_server" 不存在
postgres=# create user mapping for postgres server mysql_server1 options(username 'foo1',password 'bar1');
CREATE USER MAPPING
postgres=# grant usage on foreign server mysql_server1 to postgres;
GRANT
postgres=# create foreign table warehouse(warehouse_id int,warehouse_name text) server mysql_server1 options (dbname 'testdb',table_name 'warehouse');
CREATE FOREIGN TABLE
postgres=# insert into warehouse values (1,'test');
错误: failed to connect to MySQL: Access denied for user 'foo1'@'localhost' (using password: YES)
postgres=# select * from warehouse;
错误: failed to connect to MySQL: Access denied for user 'foo1'@'localhost' (using password: YES)
postgres=# \q
-bash-4.2$ exit;
登出
[root@localhost ~]# mysql -uroot -p;
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.5.62 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| skytf |
| test |
| testfdw |
+--------------------+
6 rows in set (0.00 sec)
mysql> use testfdw;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_testfdw |
+-------------------+
| testtbfdw |
+-------------------+
1 row in set (0.00 sec)
mysql> select user.host from user
-> ;
ERROR 1146 (42S02): Table 'testfdw.user' doesn't exist
mysql> select user,host from mysql.user;
+------------+-----------------------+
| user | host |
+------------+-----------------------+
| fdwgzc | % |
| usr_kenyon | % |
| root | 127.0.0.1 |
| root | ::1 |
| gzc | localhost |
| root | localhost |
| usrabc | localhost |
| root | localhost.localdomain |
+------------+-----------------------+
8 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| skytf |
| test |
| testfdw |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| warehouse |
+----------------+
1 row in set (0.00 sec)
mysql> select * from warehouse;
Empty set (0.00 sec)
mysql> exit;
Bye
[root@localhost ~]# su - progres;
su: user progres does not exist
[root@localhost ~]# su - postgres;
上一次登录：四 7月 18 10:09:17 CST 2019pts/0 上
-bash-4.2$ psql;
psql (9.4.23)
输入 "help" 来获取帮助信息.
postgres=# \l
资料库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | test | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/test +
| | | | | test=CTc/test
testsde | sde | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =CTc/sde +
| | | | | sde=CTc/sde
(5 行记录)
postgres=# select * from warehouse;
错误: failed to connect to MySQL: Access denied for user 'foo1'@'localhost' (using password: YES)
postgres=# \q
-bash-4.2$ exit;
登出
[root@localhost ~]# mysql -ufoo1 -p;
Enter password:
ERROR 1045 (28000): Access denied for user 'foo1'@'localhost' (using password: YES)
[root@localhost ~]# mysql -uroot -p;
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 59
Server version: 5.5.62 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select host,user from mysql.user;
+-----------------------+------------+
| host | user |
+-----------------------+------------+
| % | fdwgzc |
| % | usr_kenyon |
| 127.0.0.1 | root |
| ::1 | root |
| localhost | gzc |
| localhost | root |
| localhost | usrabc |
| localhost.localdomain | root |
+-----------------------+------------+
8 rows in set (0.00 sec)
mysql> set password for 'foo1'@'localhost' = password('123456');
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> exit;
Bye
[root@localhost ~]# su - postgres;
上一次登录：四 7月 18 13:32:32 CST 2019pts/0 上
-bash-4.2$ psql -U username -d dbname;
psql: 致命错误: 角色 "username" 不存在
-bash-4.2$ psql -U test -d testdb;
psql (9.4.23)
输入 "help" 来获取帮助信息.
testdb=> create server mysql_server2 foreign data wrapper mysql_fdw options (host '127.0.0.1',port '3306');
错误: 访问外部数据封装器 mysql_fdw 的权限不足
testdb=> \c - postgres
您现在已经连线到数据库 "testdb",用户 "postgres".
testdb=# create server mysql_server2 foreign data wrapper mysql_fdw options (host '127.0.0.1',port '3306');
CREATE SERVER
testdb=# \c foo1
致命错误: 数据库 "foo1" 不存在
保留上一次连线
testdb=# \c foo1
致命错误: 数据库 "foo1" 不存在
保留上一次连线
testdb=# \c - foo1
致命错误: 角色 "foo1" 不存在
保留上一次连线
testdb=# create user mapping for test server mysql_server2 options (username 'foo',password 'bar');
CREATE USER MAPPING
testdb=# grant usage on foreign server mysql_server2 to test;
GRANT
testdb=# \c testdb test;
您现在已经连线到数据库 "testdb",用户 "test".
testdb=> create foreign table warehouse(warehouse_id int ,warehouse_name text) server mysql_server2 options (dbname 'test' ,table_name 'warehouse');
CREATE FOREIGN TABLE
testdb=> insert into warehouse values (1,'22');
错误: failed to connect to MySQL: Access denied for user 'foo'@'localhost' (using password: YES)
testdb=> create foreign table warehouse(warehouse_id int ,warehouse_name text) server mysql_server2 options (dbname 'testdb' ,table_name 'warehouse');
错误: 关系 "warehouse" 已经存在
testdb=> \c - postgres;
您现在已经连线到数据库 "testdb",用户 "postgres".
testdb=# create extension mysql_fdw;
错误: 扩展 "mysql_fdw" 已经存在
testdb=# create server myserver foreign data wrapper mysql_fdw options (host '10.19.147.172' , port '3306');
CREATE SERVER
testdb=# create user mapping for test server myserver options (username 'root',password '123456');
CREATE USER MAPPING
testdb=# grant usage on foreign server myserver to test;
GRANT
testdb=# \c testdb test;
您现在已经连线到数据库 "testdb",用户 "test".
testdb=> create foreign table warehouse(id int ,name text) server myserver options (dbname 'test',table_name 'warehouse');
错误: 关系 "warehouse" 已经存在
testdb=> create foreign table warehouse2(id int ,name text) server myserver options (dbname 'test',table_name 'warehouse');
CREATE FOREIGN TABLE
testdb=> create foreign table warehouse2(id int ,name text) server myserver options (dbname 'test',table_name 'warehouse2');
错误: 关系 "warehouse2" 已经存在
testdb=> create foreign table warehouse3(id int ,name text) server myserver options (dbname 'test',table_name 'warehouse3');
CREATE FOREIGN TABLE
testdb=> insert into warehouse3 values (5,'55');
错误: first column of remote table must be unique for INSERT/UPDATE/DELETE operation
testdb=> insert into warehouse3 values (55555,'55');
错误: first column of remote table must be unique for INSERT/UPDATE/DELETE operation
testdb=> insert into warehouse3 values (55555,'55');
INSERT 0 1
testdb=> \q
-bash-4.2$ exit;
登出
[root@localhost ~]# mysql -uroot -p;
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.5.62 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from test;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| skytf |
| test |
| testfdw |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| warehouse |
+----------------+
1 row in set (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| skytf |
| test |
| testfdw |
+--------------------+
6 rows in set (0.00 sec)
mysql> use testfdw;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_testfdw |
+-------------------+
| testtbfdw |
+-------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# su - postgres
上一次登录：四 7月 18 13:38:55 CST 2019pts/0 上
-bash-4.2$ psql
psql (9.4.23)
输入 "help" 来获取帮助信息.
postgres=# \det
引用表列表
架构模式 | 资料表 | 服务器
----------+---------------+-----------------------
public | example | mysql_svr2
public | pg_mysql_tbl1 | mysql_svr
public | pgtbfdw | fdw_svr
public | test1 | server_remote_rudy_01
public | test2 | server_remote_rudy_02
public | warehouse | mysql_server1
public | warehouse2 | mysql_server2
(7 行记录)
postgres=# \c - test;
您现在已经连线到数据库 "postgres",用户 "test".
postgres=> \det
引用表列表
架构模式 | 资料表 | 服务器
----------+---------------+-----------------------
public | example | mysql_svr2
public | pg_mysql_tbl1 | mysql_svr
public | pgtbfdw | fdw_svr
public | test1 | server_remote_rudy_01
public | test2 | server_remote_rudy_02
public | warehouse | mysql_server1
public | warehouse2 | mysql_server2
(7 行记录)
postgres=> select * from warehouse3;
错误: 关系 "warehouse3" 不存在
第1行select * from warehouse3;
^
postgres=> \c testdb test;
您现在已经连线到数据库 "testdb",用户 "test".
testdb=> \det;
引用表列表
架构模式 | 资料表 | 服务器
----------+------------+---------------
public | warehouse | mysql_server2
public | warehouse2 | myserver
public | warehouse3 | myserver
(3 行记录)
testdb=> select * from warehouse3;
id | name
-------+------
55555 | 55
(1 行记录)
testdb=> \q
-bash-4.2$ quit;
-bash: quit: 未找到命令
-bash-4.2$ exit;
登出
[root@localhost ~]# mysql -uroot -p;
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.5.62 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from warehouse3;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| skytf |
| test |
| testfdw |
+--------------------+
6 rows in set (0.00 sec)
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_BUFFER_PAGE |
| INNODB_TRX |
| INNODB_BUFFER_POOL_STATS |
| INNODB_LOCK_WAITS |
| INNODB_CMPMEM |
| INNODB_CMP |
| INNODB_LOCKS |
| INNODB_CMPMEM_RESET |
| INNODB_CMP_RESET |
| INNODB_BUFFER_PAGE_LRU |
+---------------------------------------+
40 rows in set (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| warehouse |
+----------------+
1 row in set (0.00 sec)
mysql> select * from warehouse3;
ERROR 1146 (42S02): Table 'test.warehouse3' doesn't exist
mysql> use test;
Database changed
mysql> select * from warehouse3;
ERROR 1146 (42S02): Table 'test.warehouse3' doesn't exist
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| warehouse |
+----------------+
1 row in set (0.01 sec)
mysql> insert into warehouse3 values (123,'123123');
ERROR 1146 (42S02): Table 'test.warehouse3' doesn't exist
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into warehouse3 values (123,'123123');
ERROR 1146 (42S02): Table 'mysql.warehouse3' doesn't exist
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| skytf |
| test |
| testfdw |
+--------------------+
6 rows in set (0.00 sec)
mysql> use performance_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------------------------+
| Tables_in_performance_schema |
+----------------------------------------------+
| cond_instances |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name |
| file_instances |
| file_summary_by_event_name |
| file_summary_by_instance |
| mutex_instances |
| performance_timers |
| rwlock_instances |
| setup_consumers |
| setup_instruments |
| setup_timers |
| threads |
+----------------------------------------------+
17 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| skytf |
| test |
| testfdw |
+--------------------+
6 rows in set (0.00 sec)
mysql> use skytf;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_skytf |
+-----------------+
| test_1 |
+-----------------+
1 row in set (0.00 sec)
mysql> use testfdw;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_testfdw |
+-------------------+
| testtbfdw |
+-------------------+
1 row in set (0.00 sec)
mysql> quit;
Bye
[root@localhost ~]# su - postgres;
上一次登录：四 7月 18 14:10:03 CST 2019pts/0 上
-bash-4.2$ psql
psql (9.4.23)
输入 "help" 来获取帮助信息.
postgres=# \c - test
您现在已经连线到数据库 "postgres",用户 "test".
postgres=> select * from warehouse3;
错误: 关系 "warehouse3" 不存在
第1行select * from warehouse3;
^
postgres=> \c testdb;
您现在已经连线到数据库 "testdb",用户 "test".
testdb=> select * from warehouse;
错误: failed to connect to MySQL: Access denied for user 'foo'@'localhost' (using password: YES)
testdb=> select * from warehouse3;
id | name
-------+------
55555 | 55
(1 行记录)
testdb=> insert into warehouse3 values (666,'666');
INSERT 0 1
testdb=> select * from warehouse3;
id | name
-------+------
666 | 666
55555 | 55
(2 行记录)
testdb=> \q
-bash-4.2$ exit;
登出
[root@localhost ~]# service mysql restart;
Redirecting to /bin/systemctl restart mysql.service
[root@localhost ~]# mysql -uroot -p;
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.62 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| skytf |
| test |
| testfdw |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| warehouse |
+----------------+
1 row in set (0.00 sec)
mysql> select * from warehouse3;
ERROR 1146 (42S02): Table 'test.warehouse3' doesn't exist
mysql> select id from test.warehouse3;
ERROR 1146 (42S02): Table 'test.warehouse3' doesn't exist
mysql> exit;
Bye
[root@localhost ~]# su - postgres
上一次登录：四 7月 18 14:25:23 CST 2019pts/0 上
-bash-4.2$ psql
psql (9.4.23)
输入 "help" 来获取帮助信息.
postgres=# \c testdb test;
您现在已经连线到数据库 "testdb",用户 "test".
testdb=> select * from warehouse3;
id | name
-------+-------
22 | 32323
666 | 666
55555 | 55
(3 行记录)
testdb=> \q
-bash-4.2$ exit;
登出
[root@localhost ~]# su - postgres
上一次登录：四 7月 18 14:30:00 CST 2019pts/0 上
-bash-4.2$ psql
psql (9.4.23)
输入 "help" 来获取帮助信息.
postgres=# create server lf_test_server foreign data wrapper mysql_fdw options (host '10.19.147.197' ,port '3306');
CREATE SERVER
postgres=# create user mapping for test server lf_test_server options (username 'root',password '123456');
CREATE USER MAPPING
postgres=# grant usage on foreign server lf_test_server to test;
GRANT
postgres=# \c testdb test;
您现在已经连线到数据库 "testdb",用户 "test".
testdb=> create foreign table USER_ROLE (ROLE_ID int,ROLE_NAME text ,ROLE_TXT text,MENU_ID text) server lf_test_server options (dbname 'bkrq',table_name 'USER_ROLE');
错误: 服务器"lf_test_server" 不存在
testdb=> create foreign table USER_ROLE (ROLE_ID int,ROLE_NAME text ,ROLE_TXT text,MENU_ID text) server lf_test_server options (dbname 'bkrq',table_name 'USER_ROLE');
错误: 服务器"lf_test_server" 不存在
testdb=> CREATE FOREIGN TABLE warehouse(
testdb(> warehouse_id int,
testdb(> warehouse_name text,
testdb(> warehouse_created datetime)
testdb-> SERVER mysql_server
testdb-> OPTIONS (dbname 'bkrq', table_name 'user_role');
testdb-> create foreign table user_role(role_id int ,role_name text,role_txt text,menu_id text) server lf_test_server options (dbname 'bkrq',table_name 'user_role');
错误: 语法错误 在 "create" 或附近的
第6行create foreign table user_role(role_id int ,role_name text,r...
^
testdb=> CREATE FOREIGN TABLE user_role(
role_id int,
role_name text,
role_txt text,menu_id text)
SERVER lf_test_server options (dbname 'bkrq',table_name 'user_role');
错误: 服务器"lf_test_server" 不存在
testdb=> create server lf_test_server foreign data wrapper mysql_fdw options (host '10.19.147.197' ,port '3306');
错误: 访问外部数据封装器 mysql_fdw 的权限不足
testdb=> \c postgres postgres;
您现在已经连线到数据库 "postgres",用户 "postgres".
postgres=# create server lf_test_server foreign data wrapper mysql_fdw options (host '10.19.147.197' ,port '3306');
错误: 服务器 "lf_test_server" 已经存在
postgres=# create foreign table USER_ROLE (ROLE_ID int,ROLE_NAME text ,ROLE_TXT text,MENU_ID text) server lf_test_server options (dbname 'bkrq',table_name 'USER_ROLE');
CREATE FOREIGN TABLE
postgres=# \c testdb test;
您现在已经连线到数据库 "testdb",用户 "test".
testdb=> select * from user_role;
错误: 关系 "user_role" 不存在
第1行select * from user_role;
^
testdb=> create user mapping for test server lf_test_server options (username 'root',password '123456');
错误: 服务器"lf_test_server" 不存在
testdb=> create server lf_test_server foreign data wrapper mysql_fdw options (host '10.19.147.197',port '3306');
错误: 访问外部数据封装器 mysql_fdw 的权限不足
testdb=> \c postgres postgres;
您现在已经连线到数据库 "postgres",用户 "postgres".
postgres=# create user mapping for test server lf_test_server options (username 'root',password '123456');
错误: 对于服务器lf_test_server,用户映射"test"已存在
postgres=# \c testdb test;
您现在已经连线到数据库 "testdb",用户 "test".
testdb=> \c postgres postgres;
您现在已经连线到数据库 "postgres",用户 "postgres".
postgres=# grant usage on foreign server lf_test_server to test;
GRANT
postgres=# \c testdb tes;
致命错误: 角色 "tes" 不存在
保留上一次连线
postgres=# \c testdb test;
您现在已经连线到数据库 "testdb",用户 "test".
testdb=> create foreign table USER_ROLE (ROLE_ID int,ROLE_NAME text ,ROLE_TXT text,MENU_ID text) server lf_test_server options (dbname 'bkrq',table_name 'USER_ROLE');
错误: 服务器"lf_test_server" 不存在
testdb=> quit;
错误: 语法错误 在 "quit" 或附近的
第1行quit;
^
testdb=> \q
-bash-4.2$ quit;
-bash: quit: 未找到命令
-bash-4.2$ exit;
登出
[root@localhost ~]# su - postgres;
上一次登录：四 7月 18 14:31:17 CST 2019pts/0 上
-bash-4.2$ psql;
psql (9.4.23)
输入 "help" 来获取帮助信息.
postgres=# psql -U postgres -d bkrq;
错误: 语法错误 在 "psql" 或附近的
第1行psql -U postgres -d bkrq;
^
postgres=# \c bkrq;
您现在已经连线到数据库 "bkrq",用户 "postgres".
bkrq=# create extension mysql_fdw;
CREATE EXTENSION
bkrq=# create server bkrq_server foreign data wrapper mysql_fdw options (host '10.19.147.197' , port '3306');
CREATE SERVER
bkrq=# \c - test
您现在已经连线到数据库 "bkrq",用户 "test".
bkrq=> \c - postgres;
您现在已经连线到数据库 "bkrq",用户 "postgres".
bkrq=# create user mapping for test server bkrq_server options (username 'root',password '123456');
CREATE USER MAPPING
bkrq=# grant usage on foreign server bkrq_server to test;
GRANT
bkrq=# \c bkrq test;
您现在已经连线到数据库 "bkrq",用户 "test".
bkrq=> create foreign table user_role(role_id int,role_name text ,role_txt text,menu_id text) server bkrq_server options(dbname 'bkrq',table_name 'user_role');
CREATE FOREIGN TABLE
bkrq=> select * from user_role;
错误: failed to prepare the MySQL query:
Table 'bkrq.user_role' doesn't exist
bkrq=> select * from user_role;
错误: failed to prepare the MySQL query:
Table 'bkrq.user_role' doesn't exist
bkrq=> insert into user_role values (1,'2','3','4');
错误: failed to execute the MySQL query:
Table 'bkrq.user_role' doesn't exist
bkrq=> select * from USER_ROLE;
错误: failed to prepare the MySQL query:
Table 'bkrq.user_role' doesn't exist
bkrq=> \c - test;
您现在已经连线到数据库 "bkrq",用户 "test".
bkrq=> \c - postgres;
您现在已经连线到数据库 "bkrq",用户 "postgres".
bkrq=# select * from user_role;
错误: 没有找到对于"postgres"的用户映射
bkrq=# create user mapping for postgres server bkrq_server options (username 'root',password '123456');
CREATE USER MAPPING
bkrq=# grant usage on foreign server bkrq_server to postgres;
GRANT
bkrq=# \c bkrq postgres;
您现在已经连线到数据库 "bkrq",用户 "postgres".
bkrq=# select * from user_role;
错误: failed to prepare the MySQL query:
Table 'bkrq.user_role' doesn't exist
bkrq=# create foreign table user_role(role_id int,role_name text ,role_txt text,menu_id text) server bkrq_server options(dbname 'bkrq',table_name 'user_role');
错误: 关系 "user_role" 已经存在
bkrq=# drop foreign table user_role;
DROP FOREIGN TABLE
bkrq=# create foreign table user_role(role_id int,role_name text ,role_txt text,menu_id text) server bkrq_server options(dbname 'bkrq',table_name 'user_role');
CREATE FOREIGN TABLE
bkrq=# select * from user_role;
错误: failed to prepare the MySQL query:
Table 'bkrq.user_role' doesn't exist
bkrq=# \dev
无效的命令 \dev，用 \? 显示说明。
bkrq=# \det
引用表列表
架构模式 | 资料表 | 服务器
----------+-----------+-------------
public | user_role | bkrq_server
(1 行记录)
bkrq=# \des
外部服务器列表
名称 | 拥有者 | 外部数据封装器
-------------+----------+----------------
bkrq_server | postgres | mysql_fdw
(1 行记录)
bkrq=# \deu
列出用户映射
服务器 | 用户名:
-------------+----------
bkrq_server | postgres
bkrq_server | test
(2 行记录)
bkrq=# \dx
已安装扩展列表
名称 | 版本 | 架构模式 | 描述
-----------+------+------------+--------------------------------------------------
mysql_fdw | 1.1 | public | Foreign data wrapper for querying a MySQL server
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 行记录)
bkrq=# select * from pg_foreign_table;
ftrelid | ftserver | ftoptions
---------+----------+------------------------------------
32951 | 32945 | {dbname=bkrq,table_name=user_role}
(1 行记录)
bkrq=# \c bkrq test;
您现在已经连线到数据库 "bkrq",用户 "test".
bkrq=> select * from user_role;
错误: 对关系 user_role 权限不够
bkrq=> grant usage on foreign server bkrq_server to test;
警告: 没有为"bkrq_server"授予权限
GRANT
bkrq=> \c - postgres;
您现在已经连线到数据库 "bkrq",用户 "postgres".
bkrq=# grant usage on foreign server bkrq_server to test;
GRANT
bkrq=# \c -test;
致命错误: 数据库 "-test" 不存在
保留上一次连线
bkrq=# select * from user_role;
错误: failed to prepare the MySQL query:
Table 'bkrq.user_role' doesn't exist
bkrq=# select * from USER_ROLE;
错误: failed to prepare the MySQL query:
Table 'bkrq.user_role' doesn't exist
bkrq=# select * from USER_ROLE;
错误: failed to prepare the MySQL query:
Table 'bkrq.user_role' doesn't exist
bkrq=# \q
-bash-4.2$ psql
psql (9.4.23)
输入 "help" 来获取帮助信息.
postgres=# create extension mysql_fdw;
错误: 扩展 "mysql_fdw" 已经存在
postgres=# create server bkrq_svr foreign data wrapper mysql_fdw options (host '10.19.147.197',port '3306');
CREATE SERVER
postgres=# create user mapping for test server bkrq_svr options (username 'root',password '123456');
CREATE USER MAPPING
postgres=# grant usage on foreign server bkrq_svr to test;
GRANT
postgres=# \c bkrq test;
您现在已经连线到数据库 "bkrq",用户 "test".
bkrq=> create foreign table USER_ROLE(ROLE_ID int,ROLE_NAME text,ROLE_TXT text,MENU_ID text) server bkrq_svr options (dbname 'bkrq',table_name 'USER_ROLE');
错误: 关系 "user_role" 已经存在
bkrq=> select * from USER_ROLE;
错误: 对关系 user_role 权限不够
bkrq=> drop foreign table user_role;
错误: 必须是关系 user_role 的属主
bkrq=> \c - postgres;
您现在已经连线到数据库 "bkrq",用户 "postgres".
bkrq=# drop foreign table user_role;
DROP FOREIGN TABLE
bkrq=# \c test;
致命错误: 数据库 "test" 不存在
保留上一次连线
bkrq=# \c - test;
您现在已经连线到数据库 "bkrq",用户 "test".
bkrq=> create foreign table USER_ROLE(ROLE_ID int,ROLE_NAME text,ROLE_TXT text,MENU_ID text) server bkrq_svr options (dbname 'bkrq',table_name 'USER_ROLE');
错误: 服务器"bkrq_svr" 不存在
bkrq=> \des
外部服务器列表
名称 | 拥有者 | 外部数据封装器
-------------+----------+----------------
bkrq_server | postgres | mysql_fdw
(1 行记录)
bkrq=> \c - postgres;
您现在已经连线到数据库 "bkrq",用户 "postgres".
bkrq=# create server bkrq_svr foreign data wrapper mysql_fdw options (host '10.19.147.197', port '3306');
CREATE SERVER
bkrq=# create user mapping for test server bkrq_svr options(username 'root',password '123456');
CREATE USER MAPPING
bkrq=# grant usage on foreign server myserver to test;
错误: 服务器"myserver" 不存在
bkrq=# grant usage on foreign server bkrq_svr to test;
GRANT
bkrq=# \c bkrq test;
您现在已经连线到数据库 "bkrq",用户 "test".
bkrq=> CREATE FOREIGN TABLE USER_ROLE(ROLE_ID int, ROLE_NAME text, ROLE_TXT text, MENU_ID datetime) SERVER bkrq_svr OPTIONS (dbname 'bkrq', table_name 'USER_ROLE');
错误: 类型 "datetime" 不存在
第1行...LE_ID int, ROLE_NAME text, ROLE_TXT text, MENU_ID datetime) ...
^
bkrq=> CREATE FOREIGN TABLE USER_ROLE(ROLE_ID int, ROLE_NAME text, ROLE_TXT text, MENU_ID text) SERVER bkrq_svr OPTIONS (dbname 'bkrq', table_name 'USER_ROLE');
CREATE FOREIGN TABLE
bkrq=> select * from user_role;
role_id | role_name | role_txt |
menu_id
---------+------------+------------------+------------------------------------------------------------
------------------------------------------------------------------------------------------------------
---------------------------------------------------------
1 | 管理员 | 拥有所有操作权限 | 1,1-2,1-1,1-5,1-7,2,2-1,2-3,2-4,2-5,2-6,3,3-1,3-2,3-3,3-4,3
-5,4,5,5-1,5-5,5-6,5-7,7,7-2,7-1,8,8-1,8-2,8-3,8-4,8-5,8-6,8-7,8-8,8-9,8-9-1,8-9-2,8-9-3,8-9-4,8-9-5,8
-10,8-11,8-11-1,8-11-2,8-12,8-13,8-14,8-15,10,10-1,10-2|
2 | 市燃热办 | xiugai | 1,1-1,1-2,1-5,1-7,2,2-1,2-3,2-4,2-5,2-6,3,3-1,3-2,3-3,3-4,3
-5,4,5,5-1,5-5,5-6,5-7,7,7-2,7-1,8,8-11,8-11-1,8-11-2,8-1,8-3,8-4,8-5,8-6,8-7,8-8,8-9,8-9-1,8-9-2,8-9-
3,8-9-4,8-9-5,8-10,8-12,8-13,8-14,8-15,8-2|
3 | 区县燃热办 | 测试人员访问222 | 1-1,1-5,1-7,2,2-1,2-3,2-4,2-5,2-6,3-1,3-5,4,5-5,5-6,5-7,8,8
-11,8-11-1,8-11-2,8-1,8-3,8-4,8-5,8-6,8-7,8-8,8-9,8-9-1,8-9-2,8-9-3,8-9-4,8-9-5,8-10,8-12,8-13,8-14,8-
15,8-2|1,3,5
4 | 企业 | | 8-11,8-11-1,8-11-2,8-1,8-3,8-4,8-5,8-6,8-7,8-8,8-9,8-9-1,8-
9-2,8-9-3,8-9-4,8-9-5,8-10,8-12,8-13,8-15,8-2|8
(4 行记录)
bkrq=> insert into user_role values (888,'test','testtxt','textmenu');
INSERT 0 1
bkrq=>
