EnterpriseDB: 交互式命令行安装

关于 EnterpriseDB

EnterpriseDB,简称EDB,是开源数据库PostgreSQL的企业版,在PostgreSQL基础上对企业级特性进行了增强,例如增加了企业级工具、高可用性增强等,同时高度兼容Oracle。

EDB 主要包括以下特点:

  • EDB Advanced Server将PostgreSQL的所有特性与额外的企业级功能结合起来,以增强企业级应用的性能和安全性需求。
  • EDB高级服务器特性提高了DBA和开发人员的工作效率。
  • EDB Advanced Server 高度兼容 Oracle语法 ,包括PL/SQL、内置包和许多DBA工具,以加速和简化迁移。

下载EDB安装介质

EDB 提供两类数据库可供下载:

  • OPTION A - EDB POSTGRES ADVANCED SERVER
  • OPTION B - POSTGRESQL

我们选择 OPTION A 下载对应 Linux 平台安装介质,目前 Linux 平台交互式安装支持的最新版本为 10.5,RPM 安装支持 11.1 ,我们选择交互式安装方式。

https://www.enterprisedb.com/edb-postgres-advanced-server-10512-linux-x86-64

下载的介质为 edb-as10-server-10.5.12-1-linux-x64.run。

交互式安装EDB

EDB封装了安装脚本,安装过程非常简单,交互式安装EDB过程如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
[root@pghost6 soft_bak]# ./edb-as10-server-10.5.12-1-linux-x64.run --mode text
----------------------------------------------------------------------------
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.

Press [Enter] to continue:
Limited Use Software License Agreement
Version 2.9

IMPORTANT - READ CAREFULLY
...后面省略大量关于许可协议的描述,一路回车

Press [Enter] to continue:

是否同意许可协议,选择同意。
Do you accept this license? [y/n]: y

----------------------------------------------------------------------------
Please specify the directory where EDB Postgres Advanced Server will be
installed.

选择安装目录,即 PGHOME
Installation Directory [/opt/edb/as10]: /opt/edb/


----------------------------------------------------------------------------
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] :y

Command Line Tools [Y/n] :y

Is the selection above correct? [Y/n]: y

----------------------------------------------------------------------------

选择数据目录和WAL日志目录,即 PGDATA
Additional Directories

Please select a directory under which to store your data.

Data Directory [/opt/edb/data]: /opt/edb/data

Please select a directory under which to store your Write-Ahead Logs.

Write-Ahead Log (WAL) Directory [/opt/edb/data/pg_wal]: /opt/edb/data/pg_wal

----------------------------------------------------------------------------
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

选择兼容Oracle还是兼容PostgreSQL,我们选择兼容Oracle,关于Oracle兼容性本文末尾有详细说明。

[1] Compatible with Oracle
[2] Compatible with Postgres
Please choose an option [1] : 1

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

安装过程中会创建系统用户 enterprisedb,设置密码

Password :
Retype Password :
----------------------------------------------------------------------------
Additional Configuration

Please select the port number the server should listen on.

设置EDB端口

Port [5444]: 5444

Select the locale to be used by the new database cluster.

Locale

...省略
[232] en_US
[233] en_US.iso88591
[234] en_US.iso885915
[235] en_US.utf8
[236] en_ZA
[237] en_ZA.iso88591
...省略

设置字符集

Please choose an option [1] : 235

Would you like to install sample tables and procedures?

Install sample tables and procedures. [Y/n]: y


----------------------------------------------------------------------------
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 developers 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

----------------------------------------------------------------------------
Dynatune Dynamic Tuning:
Workload Profile

Please select the type of workload this server will be used for:


设置OLTP或OLAP
[1] Transaction Processing (OLTP systems)
[2] General Purpose (OLTP and reporting workloads)
[3] Reporting (Complex queries or OLAP workloads)
Please choose an option [1] : 1

----------------------------------------------------------------------------
Update Notification Service


Install Update Notification Service [Y/n]: n


The Update Notification Service informs, downloads and installs whenever
security patches and other updates are available for your EDB Postgres Advanced
Server installation.


EDB安装选项汇总
----------------------------------------------------------------------------
Pre Installation Summary

The following settings will be used for the installation::

Installation Directory: /opt/edb
Server Installation Directory: /opt/edb
Data Directory: /opt/edb/data
WAL Directory: /opt/edb/data/pg_wal
Database Port: 5444
Database Superuser: enterprisedb
Operating System Account: enterprisedb
Database Service: edb-as-10
Command Line Tools Installation Directory: /opt/edb
StackBuilderPlus Installation Directory: /opt/edb/stackbuilderplus

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.

安装完成,安装文件如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[root@pghost6 ~]# ll /opt/edb
total 8124
drwxr-xr-x 2 root daemon 4096 Jan 15 10:34 bin
drwx------ 21 enterprisedb enterprisedb 4096 Jan 15 10:35 data
drwxr-xr-x 4 root daemon 38 Jan 15 10:34 doc
-rw-r--r-- 1 root daemon 72341 Aug 17 14:09 edb-as10-server_commandlinetools_3rd_party_licenses.txt
-r--r--r-- 1 root daemon 15216 Aug 17 14:09 edb-as10-server_license.txt
drwxr-xr-x 3 root daemon 23 Jan 15 10:34 etc
drwxr-xr-x 6 root daemon 4096 Jan 15 10:34 include
-rw-r--r-- 1 root root 556 Jan 15 10:35 installation_summary.log
drwxr-xr-x 5 root daemon 55 Jan 15 10:34 installer
drwxr-xr-x 4 root daemon 8192 Jan 15 10:34 lib
-rwxr-xr-x 1 root daemon 915 Jan 15 10:35 pgplus_env.sh
drwxr-xr-x 5 root daemon 73 Jan 15 10:34 scripts
drwxr-xr-x 10 root daemon 4096 Jan 15 10:34 share
drwxr-xr-x 7 root daemon 136 Jan 15 10:34 stackbuilderplus
-rwx------ 1 root daemon 8118363 Jan 15 10:35 uninstall-edb-as10-server
-rw------- 1 root root 68383 Jan 15 10:35 uninstall-edb-as10-server.dat

目录的权限为root,权限需要调整(安装过程中默认创建了 enterprisedb 操作系统用户)

1
[root@pghost6 ~]# chown -R enterprisedb:enterprisedb /opt/edb

设置环境变量

安装完成后自动生成环境变量文件 /opt/edb/pgplus_env.sh, 脚本内容如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# EnterpriseDB shell environment loader
#
# Instructions:
# This file contains additions to the user environment
# that make accessing EDB Postgres Advanced Server
# executables easier.
#
# To load the environment for a single user:
# cp pgplus_env.sh /home/<username>
# chown <username> /home/<username>/pgplus_env.sh
# vi /home/<username>/.bash_profile
# At the bottom, add the line:
# . /home/<username>/pgplus_env.sh
# ( Note the '.' followed by a space )

# To load the environment for all users:
# cp pgplus_env.sh /etc
# vi /etc/profile
# At the bottom, add the line:
# . /etc/pgplus_env.sh
# ( Note the '.' followed by a space )

# Environment

export PATH=/opt/edb/bin:$PATH
export EDBHOME=/opt/edb
export PGDATA=/opt/edb/data
export PGDATABASE=edb
# export PGUSER=enterprisedb
export PGPORT=5444
export PGLOCALEDIR=/opt/edb/share/locale

将整个文件内容或尾部的环境变量加到系统用户 enterprisedb 环境变量文件 .bash_profile中,如下:

1
2
3
4
[root@pghost6 ~]# su - enterprisedb
Last login: Tue Jan 15 11:00:16 CST 2019 on pts/1

-bash-4.2$ cp /opt/edb/pgplus_env.sh /opt/edb/.bash_profile

登录EDB验证

登录 EDB 数据库,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-bash-4.2$ psql -h 127.0.0.1 -p 5444
Password:
psql.bin (10.5.12)
Type "help" for help.

edb=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU | Access privileges
-----------+--------------+----------+------------+------------+-----+-------------------------------
edb | enterprisedb | UTF8 | en_US.utf8 | en_US.utf8 | |
postgres | enterprisedb | UTF8 | en_US.utf8 | en_US.utf8 | |
template0 | enterprisedb | UTF8 | en_US.utf8 | en_US.utf8 | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
template1 | enterprisedb | UTF8 | en_US.utf8 | en_US.utf8 | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
(4 rows)

edb=# SELECT version();
version
--------------------------------------------------------------------------------------------------------------
EnterpriseDB 10.5.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)

EDB的启动与关闭

EDB 的启停有多种方式,这里主要介绍 pg_ctlsystemctl 方式(RHEL7或CentOS7以上系统)。

pg_ctl 启停

pg_ctl 的启停兼容 PostgreSQL 的语法,不需要ROOT用户执行,关闭数据库如下:

1
2
3
-bash-4.2$ pg_ctl stop -m fast 
waiting for server to shut down.... done
server stopped

启动数据库,如下:

1
2
3
4
5
6
7
8
-bash-4.2$ pg_ctl start
waiting for server to start....2019-01-15 11:05:59 CST LOG: listening on IPv4 address "0.0.0.0", port 5444
2019-01-15 11:05:59 CST LOG: listening on IPv6 address "::", port 5444
2019-01-15 11:05:59 CST LOG: listening on Unix socket "/tmp/.s.PGSQL.5444"
2019-01-15 11:05:59 CST LOG: redirecting log output to logging collector process
2019-01-15 11:05:59 CST HINT: Future log output will appear in directory "log".
done
server started

systemctl 启停

如果是 CentOS7 或 RHEL7 以上操作系统,通过 systemctl启动或关闭EDB,如下:

1
2
[root@pghost6 ~]# systemctl stop edb-as-10.service 
[root@pghost6 ~]# systemctl start edb-as-10.service

EDB与Oracle的兼容性

以下是EDB官网对Oracle兼容性的描述,支持大部分Oracle语法,如下:

Installing Advanced Server in Compatible with Oracle mode provides the following functionality:

  • Data dictionary views and data type conversions compatible with Oracle databases.
  • Date values displayed in a format compatible with Oracle syntax.
  • Oracle-styled concatenation rules (if you concatenate a string value with a NULL value, the returned value is the value of the string).
  • Schemas (dbo and sys) compatible with Oracle databases added to the SEARCH_PATH.
  • Support for the following Oracle built-in packages:
Package Functionality Compatible with Oracle Databases
dbms_alert Provides the ability to register for, send and receive alerts.
dbms_aq Provides queueing functionality for Advanced Server.
dbms_aqadm Provides supporting functionality for dbms_aq.
dbms_crypto Provides a way to encrypt or decrypt RAW, BLOB or CLOB data.
dbms_job Implements job-scheduling functionality.
dbms_lob Provides the ability to manage large objects.
dbms_lock Provides support for the DBMS_LOCK.SLEEP procedure.
dbms_mview Provides a way to manage and refresh materialized views.
dbms_output Provides the ability to display a message on the client.
dbms_pipe Provides the ability to send a message from one session and read it in another session.
dbms_profiler Collects and stores performance data about PL/pgSQL and SPL statements.
dbms_random Provides a way to generate random numbers.
dbms_rls Implements row level security.
dbms_scheduler Provides a way to create and manage Oracle-style jobs.
dbms_session A partial implementation that provides support for DBMS_SESSION.SET_ROLE.
dbms_sql Implements use of Dynamic SQL
dbms_utility Provides a collection of misc functions and procedures.
utl_encode Provides a way to encode or decode data.
utl_file Provides a way for a function, procedure or anonymous block to interact with files stored in the server’s file system.
utl_http Provides a way to use HTTP or HTTPS to retrieve information found at a URL.
utl_mail Provides a simplified interface for sending email and attachments.
utl_raw Provides a way to manipulate or retrieve the length of raw data types.
utl_smtp Implements smtp email functions.
utl_url Provides a way to escape illegal and reserved characters in a URL.

总结

以上是EDB交互式安装整体过程,EDB也支持无人值守安装,详见 EnterpriseDB: 无人值守安装

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%