PostgreSQL的设置开机自启动

2020-3-11 15:24| 发布者: xtyly| 查看: 240| 评论: 0

摘要: 概述为保证服务可靠性，数据库一般都要求在服务器启动时自动运行，pg也需要一定设置才能实现此功能，下面就检查测试介绍下postgresql（edb）实现开机自启动的两种简单方法测试环境系统$ cat /etc/redhat-release Ce ...

概述

为保证服务可靠性，数据库一般都要求在服务器启动时自动运行，pg也需要一定设置才能实现此功能，下面就检查测试介绍下postgresql（edb）实现开机自启动的两种简单方法

测试环境

系统

[ppas@stephen ~]$ cat /etc/redhat-release 
CentOS Linux release 7.7.1908 (Core)

数据库版本

[ppas@stephen ~]$ psql --version
psql (EnterpriseDB) 9.5.0.5

systemcetl 服务实现

使用systemctl 服务控制实现开机自启动

在/etc/systemd/system/ 文件夹下，创建一个 .server 文件，文件内容如下，主要分为[unit]，[service]，[install] 三个小节

[Unit]
Description=postgresql project     #描述
After=pgserver.service            #在此服务启动后才启动，这里可以不写

[Service]
Type=forking      #服务的类型，常用的有 simple（默认类型） 和 forking。默认的 simple 类型可以适应于绝大多数的场景，因此一般可以忽略这个参数的配置。而如果服务程序启动后会通过 fork 系统调用创建子进程，然后关闭应用程序本身进程的情况，则应该将 Type 的值设置为 forking，否则 systemd 将不会跟踪子进程的行为，而认为服务已经退出。 pg需要通过fork来创建一些子进程，所以这里选择forKing
User=ppas      
Group=ppas
ExecStart=/opt/PostgresPlus/9.5AS/bin/pg_ctl start -D /opt/PostgresPlus/9.5AS/data     # 启动命令
ExecReload=/opt/PostgresPlus/9.5AS/bin/pg_ctl restart -D /opt/PostgresPlus/9.5AS/data  # 重新加载
ExecStop=/opt/PostgresPlus/9.5AS/bin/pg_ctl stop -D /opt/PostgresPlus/9.5AS/data  # 停止，以上三命令都需要绝对路径
PrivateTmp=true  #是否给服务分配独立的临时空间（true/false），要给的

[Install]
WantedBy=multi-user.target   #和前面的 Wants 作用相似，只是后面列出的不是服务所依赖的模块，而是依赖当前服务的模块。“WantedBy=multi-user.target” 表明当系统以多用户方式（默认的运行级别）启动时，这个服务需要被自动运行。当然还需要 systemctl enable 激活这个服务以后自动运行才会生效

更新设置

[root@stephen system]# systemctl daemon-reload

启动服务

[root@stephen system]# systemctl start pgserver.service
[root@stephen system]# 

成功后可以看到服务已经启动进程
[ppas@stephen ~]$ ps -ef|grep postgres
ppas 31028 1 0 14:42 ? 00:00:00 /opt/PostgresPlus/9.5AS/bin/edb-postgres -D /opt/PostgresPlus/9.5AS/data
ppas 31029 31028 0 14:42 ? 00:00:00 postgres: logger process   
ppas 31031 31028 0 14:42 ? 00:00:00 postgres: checkpointer process   
ppas 31032 31028 0 14:42 ? 00:00:00 postgres: writer process   
ppas 31033 31028 0 14:42 ? 00:00:00 postgres: wal writer process   
ppas 31034 31028 0 14:42 ? 00:00:00 postgres: autovacuum launcher process   
ppas 31035 31028 0 14:42 ? 00:00:00 postgres: stats collector process   
ppas 31104 30951 0 14:50 pts/1 00:00:00 vim postgresql.conf
ppas 31194 31152 0 14:51 pts/2 00:00:00 grep --color=auto postgres
[ppas@stephen ~]$ 

[ppas@stephen ~]$ psql -h 127.0.0.1 -Uppas -d kmtest -p5432
Password for user ppas: 
psql.bin (9.5.0.5)
Type "help" for help.

kmtest=#                           

这里在测试过程中，发现一个小问题，使用systemctl启动的数据库，并没有在/tmp 下面创建socket文件，在本地登录时会失败，必须要使用 -h  指定好host才能正常登录，这点倒是问题不大

配置开机自启动

[root@stephen system]# systemctl enable pgserver.service
Created symlink from /etc/systemd/system/multi-user.target.wants/pgserver.service to /etc/systemd/system/pgserver.service.
[root@stephen system]#

测试重启，已经开机自启动了

[root@stephen system]# reboot
Connection closing...Socket close.

Connection closed by foreign host.

Disconnected from remote host(2020_pgtest) at 15:09:17.

Type `help' to learn how to use Xshell prompt.
[c:\~]$ 

[root@stephen ~]# ps -ef|grep postgres
ppas 687 1 0 15:09 ? 00:00:00 /opt/PostgresPlus/9.5AS/bin/edb-postgres -D /opt/PostgresPlus/9.5AS/data
ppas 710 687 0 15:09 ? 00:00:00 postgres: logger process   
ppas 721 687 0 15:09 ? 00:00:00 postgres: checkpointer process   
ppas 722 687 0 15:09 ? 00:00:00 postgres: writer process   
ppas 723 687 0 15:09 ? 00:00:00 postgres: wal writer process   
ppas 724 687 0 15:09 ? 00:00:00 postgres: autovacuum launcher process   
ppas 725 687 0 15:09 ? 00:00:00 postgres: stats collector process   
root 1197 1153 0 15:10 pts/0 00:00:00 grep --color=auto postgres

自带脚本实现

另外，如果不想用这种服务，或者centos6的，可以试试使用pg源码自带的启动脚本来实现自启动功能
在源码包的/contrib/start-scripts/ 中的Linux脚本（具体脚本内容可见文件结尾附件）

主要需要修改一下以下参数

# Installation prefix
prefix=/opt/PostgresPlus/9.5AS

# Data directory
PGDATA="/opt/PostgresPlus/9.5AS/data"

# Who to run the postmaster as, usually "postgres". (NOT "root")
PGUSER=ppas

# Where to keep a log file
PGLOG="$PGDATA/serverlog"

DAEMON="$prefix/bin/postmaster"    #这个参数注意下，我安装的edb是叫edb-postmaster ，一般的pg都只是叫postmaster

复制文件+赋权

[root@stephen ~]# cp linux /etc/init.d/ppas
[root@stephen ~]# chmod a+x /etc/init.d/ppas

测试使用服务启动数据库

[root@stephen init.d]# service ppas start
Restarting PostgreSQL: ok

[root@stephen init.d]# service ppas stop
Stopping PostgreSQL: ok

设置开机自启动

[root@stephen init.d]# chkconfig --add ppas
[root@stephen init.d]# chkconfig --list ppas

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

ppas 0:off    1:off    2:on    3:on    4:on    5:on    6:off

使用这种方法也能配置pg服务的开机自启动

附录

pg源码中自带的启动脚本：

#! /bin/sh

# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS

# This is an example of a start/stop script for SysV-style init, such
# as is used on Linux systems. You should edit some of the variables
# and maybe the 'echo' commands.
#
# Place this file at /etc/init.d/postgresql (or
# /etc/rc.d/init.d/postgresql) and make symlinks to
# /etc/rc.d/rc0.d/K02postgresql
# /etc/rc.d/rc1.d/K02postgresql
# /etc/rc.d/rc2.d/K02postgresql
# /etc/rc.d/rc3.d/S98postgresql
# /etc/rc.d/rc4.d/S98postgresql
# /etc/rc.d/rc5.d/S98postgresql
# Or, if you have chkconfig, simply:
# chkconfig --add postgresql
#
# Proper init scripts on Linux systems normally require setting lock
# and pid files under /var/run as well as reacting to network
# settings, so you should treat this with care.

# Original author: Ryan Kirkpatrick <pgsql@rkirkpat.net>

# contrib/start-scripts/linux

## EDIT FROM HERE

# Installation prefix
prefix=/opt/PostgresPlus/9.5AS

# Data directory
PGDATA="/opt/PostgresPlus/9.5AS/data"

# Who to run the postmaster as, usually "postgres". (NOT "root")
PGUSER=ppas

# Where to keep a log file
PGLOG="$PGDATA/serverlog"

# It's often a good idea to protect the postmaster from being killed by the
# OOM killer (which will tend to preferentially kill the postmaster because
# of the way it accounts for shared memory). To do that, uncomment these
# three lines:
#PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
#PG_MASTER_OOM_SCORE_ADJ=-1000
#PG_CHILD_OOM_SCORE_ADJ=0
# Older Linux kernels may not have /proc/self/oom_score_adj, but instead
# /proc/self/oom_adj, which works similarly except for having a different
# range of scores. For such a system, uncomment these three lines instead:
#PG_OOM_ADJUST_FILE=/proc/self/oom_adj
#PG_MASTER_OOM_SCORE_ADJ=-17
#PG_CHILD_OOM_SCORE_ADJ=0

## STOP EDITING HERE

# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# What to use to start up the postmaster. (If you want the script to wait
# until the server has started, you could use "pg_ctl start" here.)
DAEMON="$prefix/bin/postmaster"

# What to use to shut down the postmaster
PGCTL="$prefix/bin/pg_ctl"

set -e

# Only start if we can find the postmaster.
test -x $DAEMON ||
{
 echo "$DAEMON not found"
 if [ "$1" = "stop" ]
 then exit 0
 else exit 5
 fi
}

# If we want to tell child processes to adjust their OOM scores, set up the
# necessary environment variables. Can't just export them through the "su".
if [ -e "$PG_OOM_ADJUST_FILE" -a -n "$PG_CHILD_OOM_SCORE_ADJ" ]
then
 DAEMON_ENV="PG_OOM_ADJUST_FILE=$PG_OOM_ADJUST_FILE PG_OOM_ADJUST_VALUE=$PG_CHILD_OOM_SCORE_ADJ"
fi


# Parse command line parameters.
case $1 in
  start)
 echo -n "Starting PostgreSQL: "
 test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"
 su - $PGUSER -c "$DAEMON_ENV $DAEMON -D '$PGDATA' >>$PGLOG 2>&1 &"
 echo "ok"
 ;;
  stop)
 echo -n "Stopping PostgreSQL: "
 su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s"
 echo "ok"
 ;;
  restart)
 echo -n "Restarting PostgreSQL: "
 su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s"
 test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"
 su - $PGUSER -c "$DAEMON_ENV $DAEMON -D '$PGDATA' >>$PGLOG 2>&1 &"
 echo "ok"
 ;;
  reload)
 echo -n "Reload PostgreSQL: "
 su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s"
 echo "ok"
 ;;
  status)
 su - $PGUSER -c "$PGCTL status -D '$PGDATA'"
 ;;
  *)
 # Print help
 echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
 exit 1
 ;;
esac

exit 0