SRE文档

深浅模式

一、环境依赖 ​

centos7_x86

pgsql_12.2

源码方式安装

https://www.enterprisedb.com/download-postgresql-binaries

二、安装 ​

2.1下载 ​

#wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.gz
#wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.gz

2.2环境依赖 ​

#yum install -y wget gcc gcc-c++ llvm5.0 llvm5.0-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python-devel
#yum install -y wget gcc gcc-c++ llvm5.0 llvm5.0-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python-devel

2.3安装 ​

bash
#解压
#tar zxvf postgresql-12.2.tar.gz && cd postgresql-12.2
[root@test postgresql-12.2]#./configure --prefix=/data/apps/pgsql/12 --enable-nls --with-python --with-tcl --with-gssapi --with-icu --with-openssl --with-pam --with-ldap --with-systemd --with-libxml --with-libxslt

[root@test postgresql-12.2]#make && make install 
（ gmake world && gmake install-world）

 #创建目录和用户,以及配置环境变量
[root@test postgresql-12.2]#groupadd postgres
#useradd -s /sbin/nolog -M redis
[root@test postgresql-12.2]#useradd -g postgres postgres

## 密码设置在引号内输入自己的密码
[root@test postgresql-12.2]#echo "postgres" | passwd --stdin postgres

[root@test postgresql-12.2]#mkdir -p /data/pgdata/{data,archivedir}
[root@test postgresql-12.2]#chown -R postgres:postgres /data/apps/pgsql/12
[root@test postgresql-12.2]#chown postgres:postgres /data/pgdata -R

[root@test postgresql-12.2]#echo "export PATH=/data/apps/pgsql/12/bin:$PATH" >/etc/profile.d/pgsql.sh
[root@test postgresql-12.2]#source /etc/profile.d/pgsql.sh

#以下的操作均在postgres用户下进行
[root@test postgresql-12.2]#su - postgres

# 配置环境变量
$ vi ~/.bash_profile
export PGPORT=5532
export PGUSER=postgres
export PGHOME=/data/apps/pgsql/12
export PGDATA=/data/pgdata/data
export PATH=$PGHOME/bin:$PATH



##数据库初始化
[postgres@test ~]$ initdb -D /data/pgdata/data -U postgres --locale=en_US.UTF8 -E UTF8
[postgres@test ~]$ cp /data/pgdata/data/{pg_hba.conf,pg_hba.conf.bak}
[postgres@test ~]$ cp /data/pgdata/data/{postgresql.conf,postgresql.conf.bak}

#手工启停数据库
查看数据库运行状态
$ pg_ctl -D /opt/data5555 status
启动数据库
$ pg_ctl -D /opt/data5555 start &
停止数据库
$ pg_ctl -D /opt/data5555 stop
#解压
#tar zxvf postgresql-12.2.tar.gz && cd postgresql-12.2
[root@test postgresql-12.2]#./configure --prefix=/data/apps/pgsql/12 --enable-nls --with-python --with-tcl --with-gssapi --with-icu --with-openssl --with-pam --with-ldap --with-systemd --with-libxml --with-libxslt

[root@test postgresql-12.2]#make && make install 
（ gmake world && gmake install-world）

 #创建目录和用户,以及配置环境变量
[root@test postgresql-12.2]#groupadd postgres
#useradd -s /sbin/nolog -M redis
[root@test postgresql-12.2]#useradd -g postgres postgres

## 密码设置在引号内输入自己的密码
[root@test postgresql-12.2]#echo "postgres" | passwd --stdin postgres

[root@test postgresql-12.2]#mkdir -p /data/pgdata/{data,archivedir}
[root@test postgresql-12.2]#chown -R postgres:postgres /data/apps/pgsql/12
[root@test postgresql-12.2]#chown postgres:postgres /data/pgdata -R

[root@test postgresql-12.2]#echo "export PATH=/data/apps/pgsql/12/bin:$PATH" >/etc/profile.d/pgsql.sh
[root@test postgresql-12.2]#source /etc/profile.d/pgsql.sh

#以下的操作均在postgres用户下进行
[root@test postgresql-12.2]#su - postgres

# 配置环境变量
$ vi ~/.bash_profile
export PGPORT=5532
export PGUSER=postgres
export PGHOME=/data/apps/pgsql/12
export PGDATA=/data/pgdata/data
export PATH=$PGHOME/bin:$PATH



##数据库初始化
[postgres@test ~]$ initdb -D /data/pgdata/data -U postgres --locale=en_US.UTF8 -E UTF8
[postgres@test ~]$ cp /data/pgdata/data/{pg_hba.conf,pg_hba.conf.bak}
[postgres@test ~]$ cp /data/pgdata/data/{postgresql.conf,postgresql.conf.bak}

#手工启停数据库
查看数据库运行状态
$ pg_ctl -D /opt/data5555 status
启动数据库
$ pg_ctl -D /opt/data5555 start &
停止数据库
$ pg_ctl -D /opt/data5555 stop
[postgres@redis ~]$ cat /data/pgdata/data/postgresql.conf
listen_addresses = '*' 
port = 5532 
max_connections = 3000 
superuser_reserved_connections = 10 
full_page_writes = on 
wal_log_hints = off 
max_wal_senders = 50 
hot_standby = on 
log_destination = 'csvlog' 
logging_collector = on 
log_directory = 'log' 
log_filename = 'postgresql-%Y-%m-%d_%H%M%S' 
log_rotation_age = 1d 
log_rotation_size = 10MB 
log_statement = 'mod' 
log_timezone = 'PRC' 
timezone = 'PRC' 
unix_socket_directories = '/tmp' 
shared_buffers = 512MB 
temp_buffers = 16MB 
work_mem = 3048MB 
effective_cache_size = 2GB 
maintenance_work_mem = 128MB 
#max_stack_depth = 2MB 
dynamic_shared_memory_type = posix 
## PITR full_page_writes = on 
wal_buffers = 16MB 
wal_writer_delay = 200ms 
commit_delay = 0 
commit_siblings = 5 
wal_level = replica 
archive_mode = on 
archive_command = 'test ! -f /data/pgdata/archivedir/%f && cp %p /data/pgdata/archivedir/%f' 
archive_timeout = 60s
[postgres@redis ~]$ cat /data/pgdata/data/postgresql.conf
listen_addresses = '*' 
port = 5532 
max_connections = 3000 
superuser_reserved_connections = 10 
full_page_writes = on 
wal_log_hints = off 
max_wal_senders = 50 
hot_standby = on 
log_destination = 'csvlog' 
logging_collector = on 
log_directory = 'log' 
log_filename = 'postgresql-%Y-%m-%d_%H%M%S' 
log_rotation_age = 1d 
log_rotation_size = 10MB 
log_statement = 'mod' 
log_timezone = 'PRC' 
timezone = 'PRC' 
unix_socket_directories = '/tmp' 
shared_buffers = 512MB 
temp_buffers = 16MB 
work_mem = 3048MB 
effective_cache_size = 2GB 
maintenance_work_mem = 128MB 
#max_stack_depth = 2MB 
dynamic_shared_memory_type = posix 
## PITR full_page_writes = on 
wal_buffers = 16MB 
wal_writer_delay = 200ms 
commit_delay = 0 
commit_siblings = 5 
wal_level = replica 
archive_mode = on 
archive_command = 'test ! -f /data/pgdata/archivedir/%f && cp %p /data/pgdata/archivedir/%f' 
archive_timeout = 60s
[postgres@test~]$ grep -v "#" /data/pgdata/data/pg_hba.conf

local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 trust
host    all             all             0.0.0.0/0               md5
host    all             all             172.31.0.0/32           md5

# 启动
 [postgres@test~]$ pg_ctl -D /data/pgdata/data -l /data/pgdata/data/serverlog start
 [postgres@test~]$ pg_ctl -D /data/pgdata/data -l /data/pgdata/data/serverlog stop
[postgres@test~]$ grep -v "#" /data/pgdata/data/pg_hba.conf

local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 trust
host    all             all             0.0.0.0/0               md5
host    all             all             172.31.0.0/32           md5

# 启动
 [postgres@test~]$ pg_ctl -D /data/pgdata/data -l /data/pgdata/data/serverlog start
 [postgres@test~]$ pg_ctl -D /data/pgdata/data -l /data/pgdata/data/serverlog stop

2.4启动脚本 ​

bash
cp /opt/postgresql-12.2/contrib/start-scripts/linux /etc/init.d/postgresql
#调整配置，主要是脚本中三个变量的值
prefix="/home/postgresql/dbhome"
PGDATA="/home/postgresql/data"
PGUSER=postgre
    prefix 是软件的安装路径
    PGDATA 是数据存放路径
    PGUSER 是启动postgresql服务的用户


[postgres@redis ~]$ cat /etc/init.d/postgresql 
#! /bin/bash
# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS
# chkconfig --add postgresql
#
# Installation prefix
prefix=/data/apps/pgsql/12

# Data directory
PGDATA="/data/pgdata/data"

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

# 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=/data/apps/pgsql/12/bin:/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
cp /opt/postgresql-12.2/contrib/start-scripts/linux /etc/init.d/postgresql
#调整配置，主要是脚本中三个变量的值
prefix="/home/postgresql/dbhome"
PGDATA="/home/postgresql/data"
PGUSER=postgre
    prefix 是软件的安装路径
    PGDATA 是数据存放路径
    PGUSER 是启动postgresql服务的用户


[postgres@redis ~]$ cat /etc/init.d/postgresql 
#! /bin/bash
# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS
# chkconfig --add postgresql
#
# Installation prefix
prefix=/data/apps/pgsql/12

# Data directory
PGDATA="/data/pgdata/data"

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

# 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=/data/apps/pgsql/12/bin:/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

登陆

psql -U postgres -h 127.0.0.1 -p 5532
psql -U postgres -h 127.0.0.1 -p 5532

systemd启动

[root@localhost postgresql-12.2]# vi /usr/lib/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking

User=postgres
Group=postgres

# Port number for server to listen on
Environment=PGPORT=5532

# Location of database directory
Environment=PGDATA=/data/pgdata/data

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000

#ExecStartPre=/usr/local/pgsql/bin/postgresql-check-db-dir ${PGDATA}
ExecStart=/data/apps/pgsql/12/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/data/apps/pgsql/12/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/data/apps/pgsql/12/bin/pg_ctl reload -D ${PGDATA} -s

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target
[root@localhost postgresql-12.2]# vi /usr/lib/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking

User=postgres
Group=postgres

# Port number for server to listen on
Environment=PGPORT=5532

# Location of database directory
Environment=PGDATA=/data/pgdata/data

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000

#ExecStartPre=/usr/local/pgsql/bin/postgresql-check-db-dir ${PGDATA}
ExecStart=/data/apps/pgsql/12/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/data/apps/pgsql/12/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/data/apps/pgsql/12/bin/pg_ctl reload -D ${PGDATA} -s

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target
[root@localhost postgresql-12.2]# systemctl  start  postgresql.service
[root@localhost postgresql-12.2]# systemctl  stop  postgresql.service
systemctl enable postgresql.service
systemctl disable postgresql.service
systemctl   status  postgresql.service
systemctl   is-active postgresql.service
[root@localhost postgresql-12.2]# systemctl  start  postgresql.service
[root@localhost postgresql-12.2]# systemctl  stop  postgresql.service
systemctl enable postgresql.service
systemctl disable postgresql.service
systemctl   status  postgresql.service
systemctl   is-active postgresql.service

三、client端 ​

3.1安装客户端包 ​

bash
yum install postgresql12
yum install postgresql12

四、yum安装 ​

参考官方文档，https://www.postgresql.org/download/linux/redhat/

centos7

4.1Install the repository RPM ​

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

4.2 install ​

yum install postgresql12 postgresql12-server
yum install postgresql12 postgresql12-server

4.3 initialize the database ​

/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12
systemctl start postgresql-12
/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12
systemctl start postgresql-12

4.4 数据目录 ​

  • 其数据目录是/var/lib/pgsql/12/data
  • 其二进制目录是/usr/pgsql-12/bin/
  • /var/lib/pgsql/12/pgstartup.log

4.5 pg_ctl,启停pg的服务 ​

bash
关闭服务
[root@db 12]# su postgres -c '/usr/pgsql-12/bin/pg_ctl stop -D /var/lib/pgsql/12/data/ -l zsdpglog'
启动服务

[root@db 12]# su postgres -c '/usr/pgsql-12/bin/pg_ctl start -D /var/lib/pgsql/12/data/ -l zsdpglog'

使用postgres,启动pg的服务

[postgres@db bin]$ ./postgres -D /var/lib/pgsql/12/data/ > /home/postgres/zsdstartupPg.log 2>&1 &
关闭服务
[root@db 12]# su postgres -c '/usr/pgsql-12/bin/pg_ctl stop -D /var/lib/pgsql/12/data/ -l zsdpglog'
启动服务

[root@db 12]# su postgres -c '/usr/pgsql-12/bin/pg_ctl start -D /var/lib/pgsql/12/data/ -l zsdpglog'

使用postgres,启动pg的服务

[postgres@db bin]$ ./postgres -D /var/lib/pgsql/12/data/ > /home/postgres/zsdstartupPg.log 2>&1 &

4.6 centos8_yum ​

1.下载并安装PostgreSQL官方yum源配置文件
dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. 禁用系统内置yum源的PostgreSQL安装模块
CentOS8的内置yum源中已经提供PostgreSQL安装模块（但比官方提供的版本要旧），而在执行安装命令时，内置yum源的优先级高于其他yum源，因此要禁用内置yum源的PostgreSQL安装模块

dnf module list postgresql

dnf config-manager --disable pgdg11

dnf config-manager --disable pgdg10

dnf config-manager --disable pgdg96

dnf config-manager --disable pgdg95

dnf module disable postgresql


3. 安装PostgreSQL12的客户端和服务器端程序
dnf install postgresql12
dnf install postgresql12-server
dnf install postgresql12-contrib


注意：程序安装目录是"/usr/pgsql-12"，程序运行目录是"/var/run/postgresql"，程序运行用户和组是"postgres:postgres"，"postgres"用户和组安装时默认创建


设置数据库实例的数据存储目录
数据库实例的默认数据存储目录是"/var/lib/pgsql/12/data/"。"/var"是一个系统目录，不宜存放大量业务数据。因此需要在初始化数据库实例之前设置数据存储目录

1）创建数据存储目录

mkdir /data/pgsql12-data


2）设置数据存储目录的所有者用户和组为"postgres:postgres"，"postgres"用户和组在安装PostgreSQL12时已创建

chown postgres:postgres /data/pgsql12-data


3）修改PostgreSQL12开机启动服务配置文件，设置为新的数据存储目录

vim /usr/lib/systemd/system/postgresql-12.service
1.下载并安装PostgreSQL官方yum源配置文件
dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. 禁用系统内置yum源的PostgreSQL安装模块
CentOS8的内置yum源中已经提供PostgreSQL安装模块（但比官方提供的版本要旧），而在执行安装命令时，内置yum源的优先级高于其他yum源，因此要禁用内置yum源的PostgreSQL安装模块

dnf module list postgresql

dnf config-manager --disable pgdg11

dnf config-manager --disable pgdg10

dnf config-manager --disable pgdg96

dnf config-manager --disable pgdg95

dnf module disable postgresql


3. 安装PostgreSQL12的客户端和服务器端程序
dnf install postgresql12
dnf install postgresql12-server
dnf install postgresql12-contrib


注意：程序安装目录是"/usr/pgsql-12"，程序运行目录是"/var/run/postgresql"，程序运行用户和组是"postgres:postgres"，"postgres"用户和组安装时默认创建


设置数据库实例的数据存储目录
数据库实例的默认数据存储目录是"/var/lib/pgsql/12/data/"。"/var"是一个系统目录，不宜存放大量业务数据。因此需要在初始化数据库实例之前设置数据存储目录

1）创建数据存储目录

mkdir /data/pgsql12-data


2）设置数据存储目录的所有者用户和组为"postgres:postgres"，"postgres"用户和组在安装PostgreSQL12时已创建

chown postgres:postgres /data/pgsql12-data


3）修改PostgreSQL12开机启动服务配置文件，设置为新的数据存储目录

vim /usr/lib/systemd/system/postgresql-12.service

*　修改配置文件中的"Environment"参数项并保存

[Unit]
Description=PostgreSQL 12 database server
Documentation=https://www.postgresql.org/docs/12/static/
After=syslog.target
After=network.target

[Service]
Type=notify

User=postgres
Group=postgres

# Location of database directory
#Environment=PGDATA=/var/lib/pgsql/12/data/
Environment=PGDATA=/data/pgsql12-data/

# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-12/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT


# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0

[Install]
WantedBy=multi-user.target
[Unit]
Description=PostgreSQL 12 database server
Documentation=https://www.postgresql.org/docs/12/static/
After=syslog.target
After=network.target

[Service]
Type=notify

User=postgres
Group=postgres

# Location of database directory
#Environment=PGDATA=/var/lib/pgsql/12/data/
Environment=PGDATA=/data/pgsql12-data/

# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-12/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT


# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0

[Install]
WantedBy=multi-user.target

2.2 初始化数据库实例 ​

进入程序安装目录下的"bin"目录下，执行"postgresql-12-setup initdb"命令

cd /usr/pgsql-12/bin

./postgresql-12-setup initdb

或者
postgres /usr/pgsql-12/bin/pg_ctl -D /data/pgsql12-data init
cd /usr/pgsql-12/bin

./postgresql-12-setup initdb

或者
postgres /usr/pgsql-12/bin/pg_ctl -D /data/pgsql12-data init

2.3 启动数据库实例服务，并设置为开机自动启动 ​

systemctl enable postgresql-12.service

systemctl start postgresql-12.service
systemctl enable postgresql-12.service

systemctl start postgresql-12.service

2.4 设置数据库实例超级管理员账户"postgres"的口令 ​

PostgreSQL12安装完成后"postgres"的默认口令为空，为空时无法使用该用户登录数据库

passwd postgres

su postgres

bash-4.4$ psql
psql (12.3)
Type "help" for help.

postgres=# alter user postgres with password 'gis';
ALTER ROLE
postgres=# \q
bash-4.4$ exit
PostgreSQL12安装完成后"postgres"的默认口令为空，为空时无法使用该用户登录数据库

passwd postgres

su postgres

bash-4.4$ psql
psql (12.3)
Type "help" for help.

postgres=# alter user postgres with password 'gis';
ALTER ROLE
postgres=# \q
bash-4.4$ exit

五、源码安装 ​

bash
 ./configure --prefix=/data/apps/pgsql/version --enable-nls --without-python --with-tcl --with-gssapi --with-icu --with-openssl --with-pam  --with-ldap --with-systemd --with-libxml --with-lz4 --with-libxslt --with-perl --enable-thread-safety --with-wal-blocksize=8 --with-blocksize=8 --with-segsize=10 --with-pgport=15432 --enable-dtrace --enable-depend --enable-cassert --enable-debug
  
 gmake world && gmake install-world
 ./configure --prefix=/data/apps/pgsql/version --enable-nls --without-python --with-tcl --with-gssapi --with-icu --with-openssl --with-pam  --with-ldap --with-systemd --with-libxml --with-lz4 --with-libxslt --with-perl --enable-thread-safety --with-wal-blocksize=8 --with-blocksize=8 --with-segsize=10 --with-pgport=15432 --enable-dtrace --enable-depend --enable-cassert --enable-debug
  
 gmake world && gmake install-world

六、扩展安装 ​

下载文件并解压
# su - postgres
$ cd /soft
$ wget https://ftp.postgresql.org/pub/source/v12.1/postgresql-12.1.tar.gz
$ tar -zxvf postgresql-12.1.tar.gz


编译
$ cd postgresql-12.1/
$ ./configure --prefix=/opt/pg12 --with-pgport=5555

建议修改一下端口，不采用默认5432端口

安装
使用gmake 或者gmake world
$ gmake world
当看到最后一行显示为：
PostgreSQL, contrib, and documentation successfully made. Ready to install.
说明已经编译成功
 
使用gmake install或者gmake install-world 进行安装
$ gmake install-world //包含扩展包和文档
当看到最后一行显示为：
PostgreSQL, contrib, and documentation installation complete.
说明已经安装成功

查看版本
$ /opt/pg12/bin/postgres --version
postgres (PostgreSQL) 12.1
下载文件并解压
# su - postgres
$ cd /soft
$ wget https://ftp.postgresql.org/pub/source/v12.1/postgresql-12.1.tar.gz
$ tar -zxvf postgresql-12.1.tar.gz


编译
$ cd postgresql-12.1/
$ ./configure --prefix=/opt/pg12 --with-pgport=5555

建议修改一下端口，不采用默认5432端口

安装
使用gmake 或者gmake world
$ gmake world
当看到最后一行显示为：
PostgreSQL, contrib, and documentation successfully made. Ready to install.
说明已经编译成功
 
使用gmake install或者gmake install-world 进行安装
$ gmake install-world //包含扩展包和文档
当看到最后一行显示为：
PostgreSQL, contrib, and documentation installation complete.
说明已经安装成功

查看版本
$ /opt/pg12/bin/postgres --version
postgres (PostgreSQL) 12.1