一、环境依赖
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