PostgreSQL 商用版本EPAS(阿里云ppas) - 测试环境部署(EPAS 安装、配置、管理、Oracle DBLINK、外表)

简介:
+关注继续查看

标签

PostgreSQL , Enterprisedb , EPAS , ppas , 测试


背景

PostgreSQL 商用版本EPAS( 阿里云ppas ),如何自己部署一套PPAS数据库在客户端进行测试?

部署PPAS数据库

配置操作系统

(为了更好的性能、稳定性):

可以参考以下文章中的OS配置部分(主要包括sysctl.conf, limit等配置):

《PostgreSQL 10 on ECS 实施 流复制备库镜像+自动快照备份+自动备份验证+自动清理备份与归档》

《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户)》

《PostgreSQL on ECS多云盘的部署、快照备份和恢复》

《PostgreSQL on Linux 最佳部署手册》

下载PPAS安装包

如果你需要自己挑选工具或组件进行下载,请打开以下链接进行选择。

https://www.enterprisedb.com/advanced-downloads

本文选择,傻瓜式打包下载,请打开以下链接,选择对应的产品下载:

https://www.enterprisedb.com/software-downloads-postgres

选择版本:

pic

点击下载,会给你一个提示,仅用于60天的试用:

pic

然后你需要输入一个EDB注册的账号(免费注册链接 ):

pic

开始下载,下载页面会附上EDB的文档等链接,需要的话你可以点进去看一下。

pic

下载完成后,将安装包拷贝到服务器(建议CentOS 7.4 x64)。

安装PPAS软件和初始化数据库

1、解压安装包

[root@digoal ~]# mkdir edb     
[root@digoal ~]# mv edb-as10-server-10.1.5-1-linux-x64.tar.gz  edb/     
[root@digoal ~]# cd edb     
[root@digoal edb]# ll     
total 126224     
-rw-r--r-- 1 root root 129249660 Jan 19 10:17 edb-as10-server-10.1.5-1-linux-x64.tar.gz     
[root@digoal edb]# tar -zxvf edb-as10-server-10.1.5-1-linux-x64.tar.gz      
edb-as10-server-10.1.5-1-linux-x64/     
edb-as10-server-10.1.5-1-linux-x64/README_FIRST_Linux64.txt     
edb-as10-server-10.1.5-1-linux-x64/edb-as10-server-10.1.5-1-linux-x64.run     
[root@digoal edb]# ll     
total 126228     
drwxrwxr-x 2  501  501      4096 Nov  8 12:05 edb-as10-server-10.1.5-1-linux-x64     
-rw-r--r-- 1 root root 129249660 Jan 19 10:17 edb-as10-server-10.1.5-1-linux-x64.tar.gz     
[root@digoal edb]# cd edb-as10-server-10.1.5-1-linux-x64     
[root@digoal edb-as10-server-10.1.5-1-linux-x64]# ll     
total 127604     
-rwxr-xr-x 1 501 501 130656365 Nov  8 12:05 edb-as10-server-10.1.5-1-linux-x64.run     
-rw-rw-r-- 1 501 501      5569 Nov  8 12:05 README_FIRST_Linux64.txt     

2、执行安装程序

[root@digoal edb-as10-server-10.1.5-1-linux-x64]# ./edb-as10-server-10.1.5-1-linux-x64.run      
     

3、安装过程,会遇到一些需要输入的地方,根据实际情况输入

----------------------------------------------------------------------------     
Welcome to the EDB Postgres Advanced Server Setup Wizard.     
     
----------------------------------------------------------------------------     
Please read the following License Agreement. You must accept the terms of this      
agreement before continuing with the installation.     

回车,看完它的license

Press [Enter] to continue:     

选择y,同意它的license

Do you accept this license? [y/n]: y     

然后又会提示你输入EDB的账号密码

User Authentication     
     
This installation requires a registration with EnterpriseDB.com. Please enter      
your credentials below. If you do not have an account, Please create one now on      
https://www.enterprisedb.com/user-login-registration     
     
     
     
Email []: xxxxxx     
     
Password : xxxxxx    

输入安装路径,不输入的话,会使用这里的默认路径

----------------------------------------------------------------------------     
Please specify the directory where EDB Postgres Advanced Server will be      
installed.     
     
Installation Directory [/opt/edb/as10]:      

选择安装组件,并确认

----------------------------------------------------------------------------     
Select the components you want to install; clear the components you do not want      
to install. Click Next when you are ready to continue.     
     
EDB Postgres Advanced Server [Y/n] :Y     
     
pgAdmin 4 [Y/n] :n     
     
StackBuilder Plus [Y/n] :n     
     
Command Line Tools [Y/n] :Y     
     
Is the selection above correct? [Y/n]: Y     

输入你的数据目录,REDO(wal)目录。建议两者使用不同的块设备分区,并确保使用足够大的空间。wal的目录建议IOPS极好。

----------------------------------------------------------------------------     
Additional Directories     
     
Please select a directory under which to store your data.     
     
Data Directory [/opt/edb/as10/data]: /data01/edb/as10/data      # 不建议使用默认配置     
     
Please select a directory under which to store your Write-Ahead Logs.     
     
Write-Ahead Log (WAL) Directory [/opt/edb/as10/data/pg_wal]: /data02/edb/as10/pg_wal      # 不建议使用默认配置       
     
----------------------------------------------------------------------------     

选择你需要兼容Oracle还是PostgreSQL,选择Oracle的话,会安装Oracle的一些兼容类型、包、视图、函数等,同时数据类型的显示会兼容Oracle的风格。

如果不需要Oracle的兼容性,建议选择兼容PostgreSQL。更方便的逃离Oracle。

----------------------------------------------------------------------------     
Advanced Server Dialect     
     
EDB Postgres Advanced Server can be configured in one of two "Dialects" - 1) Compatible with Oracle or 2) Compatible with Postgres.     
     
If you select Compatible with Oracle, Advanced Server will be configured with appropriate data type conversions, time and date formats, Oracle-styled operators,      
dictionary views and more. This makes it easier to migrate or write new applications that are more compatible with the Oracle database.     
     
If you select Compatible with Postgres, Advanced Server will be configured with standard PostgeSQL data types, time/date formats and operators.     
     
Advanced Server Dialect     
     
[1] Compatible with Oracle     
[2] Compatible with Postgres     
Please choose an option [1] :      # 根据实际需要选择     

输入数据库初始超级用户enterprisedb的密码,

----------------------------------------------------------------------------     
Please provide a password for the database superuser (enterprisedb). A locked      
Unix user account (enterprisedb) will be created if not present.     
     
Password : IlovePG     
Retype Password : IlovePG     

输入数据库的监听端口号:

Please select the port number the server should listen on.     
     
Port [5444]: 1921     

输入数据库字符集:

...     
[764] zh_CN.utf8     
...     
     
Please choose an option [1] : 764     

是否安装示范表和存储过程,不需要。

Install sample tables and procedures. [y/N]: N     

EDB具备动态优化的功能,这里会询问你的服务器是不是数据库独占的

----------------------------------------------------------------------------     
Dynatune Dynamic Tuning:     
Server Utilization     
     
Please select the type of server to determine the amount of system resources      
that may be utilized:     
     
     
     
[1] Development (e.g. a developer's laptop)     
[2] General Purpose (e.g. a web or application server)     
[3] Dedicated (a server running only Advanced Server)     
Please choose an option [2] : 2     

这里会询问你的数据库用于什么业务场景,oltp, htap, olap 三个选项。

----------------------------------------------------------------------------     
Dynatune Dynamic Tuning:     
Workload Profile     
     
Please select the type of workload this server will be used for:     
     
     
     
[1] Transaction Processing (OLTP systems)     
[2] General Purpose (OLTP and reporting workloads)     
[3] Reporting (Complex queries or OLAP workloads)     
Please choose an option [1] :      

准备安装,再次确认你的配置是否正确:

----------------------------------------------------------------------------     
Pre Installation Summary     
     
The following settings will be used for the installation::     
     
Installation Directory: /opt/edb/as10     
Server Installation Directory: /opt/edb/as10     
Data Directory: /data01/edb/as10/data     
WAL Directory: /data02/edb/as10/pg_wal     
Database Port: 1921     
Database Superuser: enterprisedb     
Operating System Account: enterprisedb     
Database Service: edb-as-10     
Command Line Tools Installation Directory: /opt/edb/as10     

回车继续

Press [Enter] to continue:     
     
----------------------------------------------------------------------------     
Setup is now ready to begin installing EDB Postgres Advanced Server on your      
computer.     

确认开始安装

Do you want to continue? [Y/n]: Y     
     
----------------------------------------------------------------------------     
Please wait while Setup installs EDB Postgres Advanced Server on your computer.     
     
 Installing EDB Postgres Advanced Server      
 0% ______________ 50% ______________ 100%     
 #########################################     
     
----------------------------------------------------------------------------     
Setup has finished installing EDB Postgres Advanced Server on your computer.     

4、安装结束后,会新建一个操作系统用户enterprisedb,同时新增一个随操作系统自动启动数据库的服务。

安装Oracle OCI

1、安装Oracle OCI。这样才可以在EDB数据库中建立ORACLE的DBLINK。

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

选择下载包含OCI的包( 需要输入Oracle的账号密码。 可以免费注册。 )

pic

pic

将安装包上传到服务器,解压,放到EDB的PGHOME目录,并建立软链。详情参考

https://www.enterprisedb.com/docs/en/10.0/Ora_Compat_Dev_Guide/Database_Compatibility_for_Oracle_Developers_Guide.1.124.html#

操作如下

[root@digoal ~]# mkdir oci     
[root@digoal ~]# mv instantclient-basic-linux.x64-12.2.0.1.0.zip oci/     
[root@digoal ~]# cd oci     
[root@digoal oci]# ll     
total 67356     
-rw-r--r-- 1 root root 68965195 Jan 19 11:00 instantclient-basic-linux.x64-12.2.0.1.0.zip     
[root@digoal oci]# unzip instantclient-basic-linux.x64-12.2.0.1.0.zip      
Archive:  instantclient-basic-linux.x64-12.2.0.1.0.zip     
  inflating: instantclient_12_2/adrci       
  inflating: instantclient_12_2/BASIC_README       
  inflating: instantclient_12_2/genezi       
  inflating: instantclient_12_2/libclntshcore.so.12.1       
  inflating: instantclient_12_2/libclntsh.so.12.1       
  inflating: instantclient_12_2/libipc1.so       
  inflating: instantclient_12_2/libmql1.so       
  inflating: instantclient_12_2/libnnz12.so       
  inflating: instantclient_12_2/libocci.so.12.1       
  inflating: instantclient_12_2/libociei.so       
  inflating: instantclient_12_2/libocijdbc12.so       
  inflating: instantclient_12_2/libons.so       
  inflating: instantclient_12_2/liboramysql12.so       
  inflating: instantclient_12_2/ojdbc8.jar       
  inflating: instantclient_12_2/uidrvci       
  inflating: instantclient_12_2/xstreams.jar       
[root@digoal oci]# ll     
total 67360     
drwxr-xr-x 2 root root     4096 Jan 19 11:02 instantclient_12_2     
-rw-r--r-- 1 root root 68965195 Jan 19 11:00 instantclient-basic-linux.x64-12.2.0.1.0.zip     
[root@digoal oci]# cd instantclient     
-bash: cd: instantclient: No such file or directory     
[root@digoal oci]# cd instantclient_12_2/     
[root@digoal instantclient_12_2]# ll     
total 216696     
-rwxrwxr-x 1 root root     44220 Jan 26  2017 adrci     
-rw-rw-r-- 1 root root       363 Jan 26  2017 BASIC_README     
-rwxrwxr-x 1 root root     57272 Jan 26  2017 genezi     
-rwxrwxr-x 1 root root   8033199 Jan 26  2017 libclntshcore.so.12.1     
-rwxrwxr-x 1 root root  71638263 Jan 26  2017 libclntsh.so.12.1     
-r-xr-xr-x 1 root root   2981501 Jan 26  2017 libipc1.so     
-r-xr-xr-x 1 root root    539065 Jan 26  2017 libmql1.so     
-r-xr-xr-x 1 root root   6568149 Jan 26  2017 libnnz12.so     
-rwxrwxr-x 1 root root   2218687 Jan 26  2017 libocci.so.12.1     
-rwxrwxr-x 1 root root 124771800 Jan 26  2017 libociei.so     
-r-xr-xr-x 1 root root    158543 Jan 26  2017 libocijdbc12.so     
-r-xr-xr-x 1 root root    380996 Jan 26  2017 libons.so     
-rwxrwxr-x 1 root root    116563 Jan 26  2017 liboramysql12.so     
-r--r--r-- 1 root root   4036257 Jan 26  2017 ojdbc8.jar     
-rwxrwxr-x 1 root root    240476 Jan 26  2017 uidrvci     
-rw-rw-r-- 1 root root     74230 Jan 26  2017 xstreams.jar     
     
[root@digoal instantclient_12_2]# cp libclntsh.so.12.1 /opt/edb/as10/lib/     
[root@digoal instantclient_12_2]# cd /opt/edb/as10/lib     
[root@digoal lib]# ln -s libclntsh.so.12.1 libclntsh.so     

配置enterprisedb用户环境变量

[root@digoal edb-as10-server-10.1.5-1-linux-x64]# vi /opt/edb/as10/.bash_profile     
     
export PS1="$USER@`/bin/hostname -s`-> "     
export PGPORT=1921     
export PGDATA=/data01/edb/as10/data     
export LANG=en_US.utf8     
export PGHOME=/opt/edb/as10     
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH     
export DATE=`date +"%Y%m%d%H%M"`     
export PATH=$PGHOME/bin:$PATH:.     
export MANPATH=$PGHOME/share/man:$MANPATH     
export PGHOST=$PGDATA     
export PGUSER=enterprisedb     
export PGDATABASE=postgres     
alias rm='rm -i'     
alias ll='ls -lh'     
unalias vi     

修改数据库参数配置,让数据库运行更爽一点

连接到enterprisedb用户

[root@digoal edb-as10-server-10.1.5-1-linux-x64]# su - enterprisedb     
cd $PGDATA     
     
vi postgresql.conf     
     
     
listen_addresses = '0.0.0.0'     
port = 1921     
max_connections = 2000     
unix_socket_directories = '.'     
tcp_keepalives_idle = 60     
tcp_keepalives_interval = 10     
tcp_keepalives_count = 10     
shared_buffers = 128MB     
work_mem = 4MB     
maintenance_work_mem = 64MB     
dynamic_shared_memory_type = posix     
shared_preload_libraries = 'pg_stat_statements,auto_explain,$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq'     
vacuum_cost_delay = 0     
bgwriter_delay = 10ms     
bgwriter_lru_maxpages = 500     
effective_io_concurrency = 0     
max_worker_processes = 128     
max_parallel_workers_per_gather = 8     
max_parallel_workers = 32     
wal_level = replica     
synchronous_commit = off     
wal_buffers = 64MB     
wal_writer_delay = 10ms     
checkpoint_timeout = 30min     
max_wal_size = 16GB     
min_wal_size = 8GB     
checkpoint_completion_target = 0.1     
archive_mode = on     
archive_command = '/bin/date'     
random_page_cost = 1.1     
log_destination = 'csvlog'     
logging_collector = on     
log_truncate_on_rotation = on     
log_checkpoints = on     
log_connections = on     
log_disconnections = on     
log_error_verbosity = verbose        
log_line_prefix = '%t '     
log_lock_waits = on     
log_statement = 'ddl'     
log_timezone = 'PRC'     
autovacuum = on     
log_autovacuum_min_duration = 0     
autovacuum_max_workers = 5     
autovacuum_freeze_max_age = 800000000     
autovacuum_multixact_freeze_max_age = 900000000     
autovacuum_vacuum_cost_delay = 0     
vacuum_freeze_min_age = 50000000     
vacuum_freeze_table_age = 750000000     
vacuum_multixact_freeze_min_age = 5000000     
vacuum_multixact_freeze_table_age = 750000000     
datestyle = 'redwood,show_time'     
timezone = 'PRC'     
lc_messages = 'en_US.utf8'     
lc_monetary = 'en_US.utf8'     
lc_numeric = 'en_US.utf8'     
lc_time = 'en_US.utf8'     
default_text_search_config = 'pg_catalog.simple'     
edb_redwood_date = on     
edb_redwood_greatest_least = on     
edb_redwood_strings = on     
db_dialect = 'redwood'                   
edb_dynatune = 66     
edb_dynatune_profile = oltp     
timed_statistics = off     

修改数据库防火墙,允许外部连接:ACL

vi pg_hba.conf     
     
# TYPE  DATABASE        USER            ADDRESS                 METHOD     
     
# "local" is for Unix domain socket connections only     
local   all             all                                     trust     
# IPv4 local connections:     
host    all             all             127.0.0.1/32            md5     
# IPv6 local connections:     
host    all             all             ::1/128                 md5     
# Allow replication connections from localhost, by a user with the     
# replication privilege.     
local   replication     all                                     md5     
host    replication     all             127.0.0.1/32            md5     
host    replication     all             ::1/128                 md5     
host all all 0.0.0.0/0 md5     

重启数据库,

pg_ctl restart -m fast     

简单使用举例

1、创建用户,

create role digoal login encrypted password 'pwd';     

2、创建数据库,

create database db1 with template template1;     
     
grant all on database db1 to digoal;     

3、连接数据库,

psql -h xxx.xxx.xxx.xxx -p 1921 -U digoal db1     

4、图形化客户端,pgadmin是一个非常好用的PG图形化管理工具

https://www.pgadmin.org/download/

5、创建oracle DBLINK

使用SQL创建

Command:     CREATE DATABASE LINK     
Description: create a new database link     
Syntax:     
CREATE [PUBLIC] DATABASE LINK name     
    CONNECT TO username     
    IDENTIFIED BY password     
    USING [ "oci" | "libpq" ] 'connection_string'     

或者可以使用UDF接口创建

https://www.enterprisedb.com/docs/en/10.0/Ora_Compat_Dev_Guide/Database_Compatibility_for_Oracle_Developers_Guide.1.124.html#

                                                       List of functions     
   Schema   |                Name                | Result data type  |              Argument data types               |  Type       
------------+------------------------------------+-------------------+------------------------------------------------+--------     
 pg_catalog | dblink_ora_call                    | SETOF record      | text, text, numeric                            | normal     
 pg_catalog | dblink_ora_connect                 | text              | text                                           | normal     
 pg_catalog | dblink_ora_connect                 | text              | text, boolean                                  | normal     
 pg_catalog | dblink_ora_connect                 | text              | text, text, text, text, text, integer          | normal     
 pg_catalog | dblink_ora_connect                 | text              | text, text, text, text, text, integer, boolean | normal     
 pg_catalog | dblink_ora_copy                    | bigint            | text, text, text, text                         | normal     
 pg_catalog | dblink_ora_copy                    | bigint            | text, text, text, text, boolean                | normal     
 pg_catalog | dblink_ora_copy                    | bigint            | text, text, text, text, boolean, integer       | normal     
 pg_catalog | dblink_ora_disconnect              | text              | text                                           | normal     
 pg_catalog | dblink_ora_exec                    | void              | text, text                                     | normal     
 pg_catalog | dblink_ora_record                  | SETOF record      | text, text                                     | normal     
 pg_catalog | dblink_ora_status                  | text              | text                                           | normal     

6、例子

基于OCI的DBLINK,访问ORACLE

CREATE DATABASE LINK chicago  
  CONNECT TO admin IDENTIFIED BY 'mypassword'   
  USING oci '//127.0.0.1/acctg';  

基于libpq的DBLINK,访问PostgreSQL系列产品

CREATE DATABASE LINK boston  
  CONNECT TO admin IDENTIFIED BY 'mypassword'   
  USING libpq 'host=127.0.0.1 dbname=sales';  

基于postgres_fdw的DBLINK,访问PostgreSQL系列产品

CREATE DATABASE LINK bedford  
  CONNECT TO admin IDENTIFIED BY 'mypassword'   
  USING postgres_fdw 'host=127.0.0.1 port=5444 dbname=marketing';  

7、创建公共DBLINK

CREATE PUBLIC DATABASE LINK oralink CONNECT TO edb IDENTIFIED BY 'password' USING '//127.0.0.1:1521/xe';  

8、创建私有DBLINK

CREATE DATABASE LINK edblink CONNECT TO enterprisedb IDENTIFIED BY 'password' USING libpq 'host=localhost port=5443 dbname=edb';  
  
CREATE DATABASE LINK fdwlink CONNECT TO enterprisedb IDENTIFIED BY 'password' USING postgres_fdw 'host=192.168.2.22 port=5444 dbname=edb';  

9、查询已有的DBLINK(私有DBLINK查不到具体的内容,隐藏私密性)

SELECT lnkname, lnkuser, lnkconnstr FROM pg_catalog.edb_dblink;  
   
 lnkname |   lnkuser    |             lnkconnstr  
---------+--------------+-------------------------------------  
 oralink | edb          | //127.0.0.1:1521/xe  
 edblink | enterprisedb | host=localhost port=5443 dbname=edb  
(2 rows)  
  
SELECT lnkname, lnkuser, lnkconnstr FROM pg_catalog.edb_dblink;  
   
 lnkname |   lnkuser    |               lnkconnstr  
---------+--------------+----------------------------------------  
 oralink | edb          | //127.0.0.1:1521/xe  
 fdwlink | enterprisedb |  
(2 rows)  

10、基于DBLINK创建外部表

CREATE FOREIGN TABLE emp_ora (  
    empno           NUMERIC(4),  
    ename           VARCHAR(10),  
    job             VARCHAR(9),  
    mgr             NUMERIC(4),  
    hiredate        TIMESTAMP WITHOUT TIME ZONE,  
    sal             NUMERIC(7,2),  
    comm            NUMERIC(7,2),  
    deptno          NUMERIC(2)  
)  
  SERVER oralink  
  OPTIONS (table_name 'emp', schema_name 'edb'  
);  

查询外表

SELECT * FROM emp_ora;  
   
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno  
-------+--------+-----------+------+--------------------+---------+---------+--------  
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20  
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30  
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30  
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20  
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30  
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30  
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10  
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20  
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10  
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30  
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20  
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30  
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20  
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10  
(14 rows)  

查询远程表,用法一样,类似匿名

SELECT * FROM emp@oralink;  
   
 empno | ename  |    job    | mgr  |      hiredate      | sal  | comm | deptno  
-------+--------+-----------+------+--------------------+------+------+--------  
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800 |      |     20  
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600 |  300 |     30  
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250 |  500 |     30  
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975 |      |     20  
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250 | 1400 |     30  
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850 |      |     30  
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450 |      |     10  
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000 |      |     20  
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000 |      |     10  
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500 |    0 |     30  
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100 |      |     20  
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950 |      |     30  
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000 |      |     20  
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300 |      |     10  
(14 rows)  

11、查询远程数据(自动PUSHDOWN)

SELECT * FROM emp@oralink;  
   
 empno | ename  |    job    | mgr  |      hiredate      | sal  | comm | deptno  
-------+--------+-----------+------+--------------------+------+------+--------  
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800 |      |     20  
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600 |  300 |     30  
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250 |  500 |     30  
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975 |      |     20  
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250 | 1400 |     30  
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850 |      |     30  
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450 |      |     10  
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000 |      |     20  
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000 |      |     10  
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500 |    0 |     30  
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100 |      |     20  
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950 |      |     30  
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000 |      |     20  
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300 |      |     10  
(14 rows)  
  
SELECT d.deptno, d.dname, e.empno, e.ename, e.job, e.sal, e.comm FROM emp@oralink e, dept@fdwlink d WHERE e.deptno = d.deptno ORDER BY 1, 3;  
   
 deptno |   dname    | empno | ename  |    job    | sal  | comm  
--------+------------+-------+--------+-----------+------+------  
     10 | ACCOUNTING |  7782 | CLARK  | MANAGER   | 2450 |  
     10 | ACCOUNTING |  7839 | KING   | PRESIDENT | 5000 |  
     10 | ACCOUNTING |  7934 | MILLER | CLERK     | 1300 |  
     20 | RESEARCH   |  7369 | SMITH  | CLERK     |  800 |  
     20 | RESEARCH   |  7566 | JONES  | MANAGER   | 2975 |  
     20 | RESEARCH   |  7788 | SCOTT  | ANALYST   | 3000 |  
     20 | RESEARCH   |  7876 | ADAMS  | CLERK     | 1100 |  
     20 | RESEARCH   |  7902 | FORD   | ANALYST   | 3000 |  
     30 | SALES      |  7499 | ALLEN  | SALESMAN  | 1600 |  300  
     30 | SALES      |  7521 | WARD   | SALESMAN  | 1250 |  500  
     30 | SALES      |  7654 | MARTIN | SALESMAN  | 1250 | 1400  
     30 | SALES      |  7698 | BLAKE  | MANAGER   | 2850 |  
     30 | SALES      |  7844 | TURNER | SALESMAN  | 1500 |    0  
     30 | SALES      |  7900 | JAMES  | CLERK     |  950 |  
(14 rows)  
  
EXPLAIN (verbose,costs off) SELECT d.deptno, d.dname, e.empno, e.ename FROM dept@oralink d LEFT OUTER JOIN emp@oralink e ON d.deptno = e.deptno ORDER BY 1, 3;  
   
                                        QUERY PLAN                                                                                          
--------------------------------------------------------------------------------  
 Foreign Scan  
   Output: d.deptno, d.dname, e.empno, e.ename  
   Relations: (_dblink_dept_1 d) LEFT JOIN (_dblink_emp_2 e)  
   Remote Query: SELECT r1.deptno, r1.dname, r2.empno, r2.ename FROM (dept r1 LEFT JOIN emp r2 ON ((r1.deptno = r2.deptno))) ORDER BY r1.deptno ASC NULLS LAST, r2.empno ASC NULLS LAST  
(4 rows)  

其他资料

《PostgreSQL 商用版本EPAS(阿里云ppas) 自定义custom plan次数》

《PostgreSQL 模糊查询 与 正则匹配 性能差异与SQL优化建议》

《PostgreSQL 商用版本EPAS(阿里云ppas) SQL防火墙使用(白名单管理、防SQL注入、防DDL等)》

《PostgreSQL 商用版本EPAS(阿里云ppas) NUMA 架构spin锁等待优化》

《PostgreSQL 商用版本EPAS(阿里云ppas) 自动(postgresql.conf)参数计算与适配功能》

《PostgreSQL 商用版本EPAS(阿里云ppas) 索引推荐功能使用》

《PostgreSQL 商用版本EPAS(阿里云ppas) HTAP功能之资源隔离管理 - CPU与刷脏资源组管理》

参考

《PostgreSQL 10 on ECS 实施 流复制备库镜像+自动快照备份+自动备份验证+自动清理备份与归档》

《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户)》

《PostgreSQL on ECS多云盘的部署、快照备份和恢复》

《PostgreSQL on Linux 最佳部署手册》

https://www.enterprisedb.com/docs/en/10.0/Ora_Reference_Guide_v10/Database_Compatibility_for_Oracle_Developers_Reference_Guide.1.035.html

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
1天前
|
存储 Dragonfly 弹性计算
阿里云4核8G配置云服务器不同实例最新收费标准价格及选择参考
阿里云服务器4核8G配置有多款实例规格可选,不同实例规格云服务器收费标准是不同的,目前4核8G配置收费标准最低的是经济型 e实例,收费标准为216.0/月,如果通过目前阿里云官网的活动购买4核8G配置云服务器,有经济型e、通用算力型u1、计算型c7和计算型c8y实例可选,最低价格为1684.68元1年。本文为大家介绍阿里云4核8G配置云服务器的最新收费标准与活动价格和选择参考。
阿里云4核8G配置云服务器不同实例最新收费标准价格及选择参考
|
12天前
|
XML Java Linux
Linux的CentOS7中安装maven3.6并配置阿里云远程仓库
Linux的CentOS7中安装maven3.6并配置阿里云远程仓库
34 0
Linux的CentOS7中安装maven3.6并配置阿里云远程仓库
|
21天前
|
存储 固态存储 安全
阿里云2核4G配置云服务器可选实例规格、收费标准及最新活动价格参考
2核4G的云服务器是很多普通企业级用户选择最多的配置,这个配置可选的实例规格有20多种,不同实例规格的云服务器收费标准是不一样的,如果你想买2核4G配置的阿里云服务器,目前2核2G4M轻量应用服务器年付低至298.97元,此外,计算型c7实例2核4G配置活动价格为2129.41元1年;通用算力型u1实例2核4G配置活动价格为1591.66元1年;计算型c8y实例2核4G配置活动价格为1545.63元1年。本文主要介绍2核4G配置的可选实例规格、收费标准及最新活动价格,以供参考。
95 0
阿里云2核4G配置云服务器可选实例规格、收费标准及最新活动价格参考
|
1月前
|
弹性计算 固态存储 数据可视化
2023阿里云ECS服务器配置价格表_租用费用
2023阿里云ECS服务器配置价格表_租用费用,轻量应用服务器和云服务器ECS优惠价格表,阿里云轻量应用服务器2核2G3M带宽轻量服务器一年108元,2核4G4M带宽轻量服务器一年297.98元12个月,云服务器ECS包括通用算力型u1、ECS计算型c7、通用型g7和内存型r7均有活动
|
1月前
阿里云最新产品手册——云基础产品与基础设施——计算——弹性裸金属服务器——配置
阿里云最新产品手册——云基础产品与基础设施——计算——弹性裸金属服务器——配置自制脑图
62 2
|
1月前
|
弹性计算 固态存储 数据可视化
2023年企业如何选择阿里云服务器配置,阿里云企业级服务器多少钱1年?
阿里云在国内云计算领域的地位是毋庸置疑的,市场占有率早已超过了半壁江山,目前无论个人站长还是企业,都将阿里云作为了上云的地域选择,那么作为企业用户应该如何选择阿里云服务器呢?购买什么配置的阿里云服务器是适合自己的呢?2023年企业如何选择阿里云服务器配置,阿里云企业级服务器多少钱1年?下面我们就来说说企业如何选择阿里云服务器配置:
2023年企业如何选择阿里云服务器配置,阿里云企业级服务器多少钱1年?
|
1月前
|
弹性计算 固态存储 数据可视化
服务器有什么作用?2023阿里云ECS云服务器、GPU云服务器和轻量服务器配置和精准报价哪里看?
2023年阿里云服务器有什么作用?阿里云服务器主要是搭建网站、APP、小程序用的。2023阿里云ECS云服务器、GPU云服务器和轻量服务器配置和精准报价哪里看?2023年阿里云服务器租用费用,轻量应用服务器和云服务器ECS优惠价格表,阿里云轻量应用服务器2核2G3M带宽轻量服务器一年108元,2核4G4M带宽轻量服务器一年297.98元12个月,云服务器ECS包括通用算力型u1、ECS计算型c7、通用型g7和内存型r7均有活动,分享云服务器详细价格表如下:
|
1月前
|
弹性计算 固态存储 数据可视化
阿里云轻量应用服务器配置报价表2核2G3M和2核4G4M带宽
阿里云轻量应用服务器配置报价表2核2G3M和2核4G4M带宽
|
1月前
|
弹性计算 固态存储 数据可视化
2023年阿里云ECS云服务器多少钱?阿里云ECS云服务器配置价格表
2023年阿里云ECS云服务器多少钱?阿里云ECS云服务器配置价格表,包括轻量应用服务器和云服务器ECS,云服务器ECS包括通用算力型u1、ECS计算型c7、通用型g7和内存型r7,CPU内存配置可选2核2G、2核4G、2核8G、2核16G、4核4G、4核8G、4核16G、4核32G、8核8G、8核16G、8核32G、8核64G等配置,如下表:
|
1月前
|
弹性计算 固态存储 数据可视化
阿里云轻量应用服务器多少钱1年?2023年阿里云轻量应用服务器配置价格表
阿里云轻量应用服务器多少钱1年?2023年阿里云轻量应用服务器配置价格表。阿里云2核2G3M带宽轻量服务器一年108元,2核4G4M带宽轻量服务器一年297.98元12个月。2023年阿里云服务器租用费用,轻量应用服务器和云服务器ECS优惠价格表,阿里云轻量应用服务器2核2G3M带宽轻量服务器一年108元,2核4G4M带宽轻量服务器一年297.98元12个月,云服务器ECS包括通用算力型u1、ECS计算型c7、通用型g7和内存型r7均有活动,分享云服务器详细价格表如下:
相关产品
云数据库 Redis 版
云数据库 MongoDB 版
云数据库 RDS
推荐文章
更多
推荐镜像
更多