postgreSQL部署

postgreSQL部署

一、包安装

下载rpm和deb包。

https://www.postgresql.org/

二、编译安装

文档：https://www.postgresql.org/docs/current/installation.html

下载：https://www.postgresql.org/ftp/source/

2.1 安装依赖

yum install -y gcc make readline-devel zlib-devel

2.2 编译参数

./configure --prefix=/apps/pgsql
make -j 2
make install-world
#安装帮助文档

2.3 创建用户

useradd postgres -s /bin/bash -m -d /home/postgres
#创建用户

echo -e '12345\n12345' | passwd postgres
#修改密码

echo pgsql:12345 | chpasswd
#修改密码方法二

2.4 环境变量

cat << EOF > /etc/profile.d/pgsql.sh
#postgreSQL
export PGHOME=/apps/pgsql
export PATH=\$PGHOME/bin:\$PATH
export PGDATA=/data/pgsql-data
export PGUSER=postgres
export MANPATH=/apps/pgsql/share/man:\$MANPATH
EOF

. /etc/profile.d/pgsql.sh

2.5 创建数据库文件路径

mkdir /data/pgsql-data -p

chown -R postgres. /data/pgsql-data /apps/pgsql

三、初始化

3.1 切换用户

su postgres

3.1 初始化数据

#方法一：
initdb -D /var/lib/postgresql/data
initdb -d ${PGDATA}
#初始化数据

#方法二：
pg_createcluster 14 main --start
#cluster初始化

3.2 启动

pg_ctl -D /var/lib/postgresql/data -l logfile start
pg_ctl -D ${PGDATA} -l logfile start
#启动数据库
#默认端口5432

3.3 登陆

psql
#客户端工具

3.4 开机启动

#方法一：
cat << EOF >> /etc/rc.local
su - postgres -c "/apps/pgsql/bin/pg_ctl -l logfile start"
EOF

chmod +x /etc/rc.local

#方法二：
cp contrib/start-scripts/linux /etc/init.d/postgresql
chmod +x /etc/init.d/postgresql
chkconfig --add postgresql

#修改脚本内容：
prefix=/apps/pgsql
PGDATA=/data/pgsql-data

service postgresql start
#启动服务

#service文件
cat << EOF > /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=5432

# Location of database directory
Environment=PGDATA=/data/pgsql-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

ExecStart=/apps/pgsql/bin/pg_ctl start -D \${PGDATA} -s -o "-p \${PGPORT}" -w -t 300
ExecStop=/apps/pgsql/bin/pg_ctl stop -D \${PGDATA} -s -m fast
ExecReload=/apps/pgsql/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

EOF
#创建service文件

systemctl daemon-reload
#重新加载service

systemctl enable --now postgresql
#启动服务