EnterpriseDB: 使用 edbstart and edbstop 启停数据库

EDB 封装了 edbstartedbstop 命令行工具用于启动和关闭 Linux 平台上多个 EDB实例,对于主机上部署了多个EDB实例的场景降低了维护工作量,RPM 安装方式不支持使用这两个脚本工具。

演示这两个工具之前,我们首先创建两个EDB数据库实例。

安装 EDB

计划创建两个EDB实例,软件目录为 /opt/edb,端口分别为 5444、5445,数据目录分别为 /edbdata/5444/data, /edbdata/5445/data

创建系统用户

创建操作系统用户,如下:

1
2
3
# groupadd enterprisedb
# useradd -g enterprisedb enterprisedb
# passwd enterprisedb

创建数据目录

创建两个数据目录,如下:

1
2
3
[root@pghost6 edb]# mkdir -p /edbdata/5444
[root@pghost6 edb]# mkdir -p /edbdata/5445
chown -R enterprisedb:enterprisedb /edbdata

定义安装配置文件

创建配置文件如下:

1
touch /opt/edb/config_edb.conf

给配置文件 /opt/edb/config_edb.conf 加入以下内容:

1
2
3
4
5
6
mode=unattended
superpassword=xxxxxx
prefix=/opt/edb
datadir=/edbdata/5444/data
xlogdir=/edbdata/5444/data/pg_wal
serverport=5444

安装 EDB

执行安装脚本,如下:

1
/opt/soft_bak/edb-as10-server-10.5.12-1-linux-x64.run --optionfile /opt/edb/config_edb.conf

设置环境变量

安装完成后自动生成环境变量文件 /opt/edb/pgplus_env.sh, 将脚本最后部分的环境变量加到系统用户 enterprisedb 环境变量文件 .bash_profile 中,如下:

1
2
3
4
5
6
7
export PATH=/opt/edb/bin:$PATH
export EDBHOME=/opt/edb
export PGDATA=/edbdata/5444/data
export PGDATABASE=edb
# export PGUSER=enterprisedb
export PGPORT=5444
export PGLOCALEDIR=/opt/edb/share/locale

创建另一个EDB实例

使用 initdb 创建另外一个EDB实例,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-bash-4.2$ /opt/edb/bin/initdb -D /edbdata/5445/data -E UTF8 --locale=C -U enterprisedb -W
The files belonging to this database system will be owned by user "enterprisedb".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password:
Enter it again:

creating directory /edbdata/5445/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
...省略

修改此实例 postgresql.conf 的端口号等相关参数,之后启动这个实例,如下:

1
-bash-4.2$ pg_ctl start -D /edbdata/5445/data

查看进程,已经有了两个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
-bash-4.2$ ps -ef | grep post
root 1232 1 0 2018 ? 00:00:19 /usr/libexec/postfix/master -w
postfix 1240 1232 0 2018 ? 00:00:04 qmgr -l -t unix -u
postfix 29277 1232 0 10:52 ? 00:00:00 pickup -l -t unix -u
enterpr+ 29530 1 0 10:55 ? 00:00:00 /opt/edb/bin/edb-postgres -D /edbdata/5444/data
enterpr+ 29531 29530 0 10:55 ? 00:00:00 postgres: logger process
enterpr+ 29533 29530 0 10:55 ? 00:00:00 postgres: checkpointer process
enterpr+ 29534 29530 0 10:55 ? 00:00:00 postgres: writer process
enterpr+ 29535 29530 0 10:55 ? 00:00:00 postgres: wal writer process
enterpr+ 29536 29530 0 10:55 ? 00:00:00 postgres: autovacuum launcher process
enterpr+ 29537 29530 0 10:55 ? 00:00:00 postgres: stats collector process
enterpr+ 29538 29530 0 10:55 ? 00:00:00 postgres: bgworker: dbms_aq launcher
enterpr+ 29539 29530 0 10:55 ? 00:00:00 postgres: bgworker: logical replication launcher
enterpr+ 32025 1 2 11:34 pts/1 00:00:00 /opt/edb/bin/edb-postgres -D /edbdata/5445/data
enterpr+ 32027 32025 0 11:34 ? 00:00:00 postgres: checkpointer process
enterpr+ 32028 32025 0 11:34 ? 00:00:00 postgres: writer process
enterpr+ 32029 32025 0 11:34 ? 00:00:00 postgres: wal writer process
enterpr+ 32030 32025 0 11:34 ? 00:00:00 postgres: autovacuum launcher process
enterpr+ 32031 32025 0 11:34 ? 00:00:00 postgres: stats collector process
enterpr+ 32032 32025 0 11:34 ? 00:00:00 postgres: bgworker: dbms_aq launcher
enterpr+ 32033 32025 0 11:34 ? 00:00:00 postgres: bgworker: logical replication launcher
enterpr+ 32043 32025 0 11:34 ? 00:00:00 postgres: bgworker: dbms_aq worker[postgres] idle
enterpr+ 32045 32025 0 11:34 ? 00:00:00 postgres: bgworker: dbms_aq worker[edb] idle
enterpr+ 32047 29021 0 11:34 pts/1 00:00:00 grep --color=auto post

关于 edbtab

配置文件 /etc/edbtab 用来定义主机上的多个 EDB 实例, edbstartedbstop 脚本会读取 /etc/edbtab 文件内容并进行EDB实例的启停控制。

edbtab 文件位于软件安装目录 /opt/edb/scripts/server/autostart/,将此文件复制到 ‘/etc/‘ 目录,如下:

1
2
3
4
5
6
7
8
9
-bash-4.2$ ll /opt/edb/scripts/server/autostart
total 16
-rwxr-xr-x 1 enterprisedb enterprisedb 1344 Aug 17 14:09 edb_autostart
drwxrwxr-x 3 enterprisedb enterprisedb 18 Jan 17 12:00 edbdata
-rwxr-xr-x 1 enterprisedb enterprisedb 1481 Aug 17 14:09 edbstart
-rwxr-xr-x 1 enterprisedb enterprisedb 1463 Aug 17 14:09 edbstop
-rw-r--r-- 1 enterprisedb enterprisedb 369 Jan 17 11:43 edbtab

[root@pghost6 autostart]# cp /opt/edb/scripts/server/autostart/edbtab /etc/

edbtab 文件包含 $EDB_HOME、$PGDATA、<N|Y>三个字段,用冒号分隔,每一行表示一个EDB数据库实例,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# This file is used to automatically start EnterpriseDB clusters
# at system boot time
#
# Format of entries:
#
# $EDB_HOME:$PGDATA:<N|Y>
#
# Example of usage:
#
# /opt/edb/as10:/opt/edb/as10/data:Y
#
# The 'Y' or 'N' indicate whether the cluster should or should not be
# started upon system boot/shutdown
#
/opt/edb:/edbdata/5444/data:Y
/opt/edb:/edbdata/5445/data:Y

这个文件很容易理解,文件的最后两行设置了EDB的两个实例。

关于 edbstart 脚本

edbstartedbstop 文件也位于软件安装目录 /opt/edb/scripts/server/autostart/

edbstart 脚本内容如下:

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
#!/bin/sh

# EnterpriseDB cluster startup script
#
# This script will start all clusters listed the /etc/edbtab file
# that have a 'Y' indicating that the cluster should be started at
# system boot time.
# This script is meant to be run at boot time
# (called by /etc/init.d/edb_autostart) but can be run manually.
# If you choose to run this script manually, it will attemp to start all
# clusters with a 'Y' unless you provide a data directory as an argument:
# i.e ./edbstart /opt/edb/as9.6/data, in which case, only that
# cluster will be started.

startAll ()
{
# Retrieve all entries from edbtab
for ENTRY in `cat /etc/edbtab | grep -v '^[ \t]*\#' | egrep -v ':[nN][ \t]*'`
do
EDB_HOME=`echo $ENTRY | awk -F: '{print $1}'`
PGDATA=`echo $ENTRY | awk -F: '{print $2}'`
EDBLOG=$PGDATA/log/log.txt
printf "Starting ${EDB_HOME} (${PGDATA})...\n"
$EDB_HOME/bin/pg_ctl -D $PGDATA -W start >> $EDBLOG 2>&1
done
}

startOne ()
{
PGDATA=$1
# Retrieve entry from edbtab
ENTRY=`cat /etc/edbtab | grep "${PGDATA}"`
if [ -z $ENTRY ]; then
printf "Entry could not be found!"
exit 1
fi

# Extract components
EDB_HOME=`echo $ENTRY | awk -F: '{print $1}'`
EDBLOG=$PGDATA/log/log.txt

printf "Starting ${EDB_HOME} (${PGDATA})...\n"
$EDB_HOME/bin/pg_ctl -D $PGDATA -w start >> $EDBLOG 2>&1
}

main ()
{
if [ -z "$1" ]; then
startAll
else
startOne $1
fi
}
main $*

关于 edbstop 脚本

edbstop 脚本内容如下:

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
#!/bin/sh

# EnterpriseDB cluster shutdown script
#
# This script will stop clusters listed the /etc/edbtab file
# that have a 'Y' indicating that the cluster should be stopped at
# system shutdown.
# This script is meant to be run at system shutdown
# (called by /etc/init.d/edb_autostart) but can be run manually.
# If you choose to run this script manually, it will attemp to stop all
# clusters with a 'Y' unless you provide a data directory as an argument:
# i.e ./edbstop /opt/edb/as9.6/data, in which case, only that
# cluster will be stopped.

stopAll ()
{
# Retrieve all entries from edbtab
for ENTRY in `cat /etc/edbtab | grep -v '^[ \t]*\#' | egrep -v ':[nN][ \t]*'`
do
EDB_HOME=`echo $ENTRY | awk -F: '{print $1}'`
PGDATA=`echo $ENTRY | awk -F: '{print $2}'`
printf "Stopping ${EDB_HOME} (${PGDATA})...\n"
$EDB_HOME/bin/pg_ctl -D $PGDATA -w -m immediate stop
done
}

stopOne ()
{
PGDATA=$1

# Retrieve entry from edbtab
ENTRY=`cat /etc/edbtab | grep "${PGDATA}"`
if [ -z $ENTRY ]; then
printf "Entry could not be found!\n"
exit 1
fi

# Extract components
EDB_HOME=`echo $ENTRY | awk -F: '{print $1}'`
PGDATA=`echo $ENTRY | awk -F: '{print $2}'`

printf "Stopping ${EDB_HOME} (${PGDATA})...\n"
$EDB_HOME/bin/pg_ctl -D $PGDATA -w -m immediate stop
}

main ()
{
if [ -z "$1" ]; then
stopAll
else
stopOne $1
fi
}

main $*

验证

为了使用方便,将 edbstartedbstop 复制到 $EDBHOME/bin 目录下,如下:

1
2
-bash-4.2$ cp /opt/edb/scripts/server/autostart/edbstart $EDBHOME/bin
-bash-4.2$ cp /opt/edb/scripts/server/autostart/edbstop $EDBHOME/bin

启停主机上所有实例

两个脚本不接任何参数表示启动或停止主机上的所有实例。

关闭主机上所有EDB实例,如下:

1
2
3
4
5
6
7
-bash-4.2$ edbstop
Stopping /opt/edb (/edbdata/5444/data)...
waiting for server to shut down.... done
server stopped
Stopping /opt/edb (/edbdata/5445/data)...
waiting for server to shut down.... done
server stopped

启动主机上所有EDB实例,如下:

1
2
3
-bash-4.2$ edbstart
Starting /opt/edb (/edbdata/5444/data)...
Starting /opt/edb (/edbdata/5445/data)...

启停主机上指定实例

也可以关闭或启动指定实例,指定数据目录即可。

关闭主机上的 /edbdata/5444/data 实例,如下:

1
2
3
4
-bash-4.2$ edbstop /edbdata/5444/data
Stopping /opt/edb (/edbdata/5444/data)...
waiting for server to shut down.... done
server stopped

启动主机上的 /edbdata/5444/data 实例,如下:

1
2
-bash-4.2$ edbstart /edbdata/5444/data
Starting /opt/edb (/edbdata/5444/data)...

参考

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

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

PostgreSQL实战
感谢支持!
0%