postgreSQL-12编译安装

PostgreSql官网 (opens new window)

官方源码下载 (opens new window)

编译安装postgresql-12

下载postgresql-12.10.tar.gz (opens new window)

• 安装编译工具

yum install -y make gcc gcc-c++ perl readline readline readline-devel ncurses-devel zlib zlib-devel

• 安装服务

cd /opt/soft
wget https://ftp.postgresql.org/pub/source/v12.10/postgresql-12.10.tar.gz

#解压
tar xf postgresql-12.10.tar.gz
cd postgresql-12.10/

#生成
./configure --prefix=/opt/postgresql

#编译
make -j $(nproc) #linux系统 nproc 命令，多核编译
make install
echo  $?

#添加用户授权，不建议修改登录用户名
useradd postgres
chown -R postgres:postgres /opt/postgresql

#添加环境变量
echo 'export PATH=/opt/postgresql/bin:$PATH' >> /etc/profile
source /etc/profile

• configure参数说明

    --prefix=prefix 安装到prefix指向的目录；默认为/usr/local/pgsql
    --bindir=dir 安装应用程序到dir；默认为prefix/bin
    --with-docdir=dir 安装文档到dir；默认为prefix/doc
    --with-pgport=port 设置默认的服务器端网络连接服务TCP端口号
    --with-tcl 为服务端提供Tcl存储过程支持
    --with-perl 为服务端提供Perl存储过程支持
    --with-python 为服务端提供Python存储过程支持
  

初始化，启停pgSql

#切换到普通用户
su - postgres

#初始化
/opt/postgresql/bin/initdb -D /opt/postgresql/data -U postgres

#启动数据库
pg_ctl -D /opt/postgresql/data start

#重新启动数据库
pg_ctl -D /opt/postgresql/data restart

#查看数据库状态
pg_ctl -D /opt/postgresql/data status

#停止数据库服务
pg_ctl -D /opt/postgresql/data stop

• initdb初始化参数说明

    -D 指定数据库数据位置。必传。也可以设置环境变量PGDATA来替换-D选项。
    -U 选择数据库superuser的用户名。这默认为运行initdb的用户的名称，系统超级管理用户
    -W 对于新的超级用户提示输入口令
    -E 指定数据库编码，一般为UTF8，默认
  

psql 不指定参数，登录用户==当前用户名，登录库==当前用户名，

编译postgres_fdw

不需要用到，可以省略

cd /opt/soft/postgresql-12.10/contrib

#编译
make -j $(nproc)
make install

#重新启动数据库
pg_ctl -D /opt/postgresql/data restart

#登录数据库，创建插件
postgres=# CREATE EXTENSION postgres_fdw; 
CREATE EXTENSION

添加到systemd管理

• service 服务启动

cat >/etc/init.d/postgresql<<'EOF'
#!/bin/bash

start() {
    echo "Starting postgresql..."
    # 启动命令
su - postgres -c "
    pg_ctl -D /opt/postgresql/data start "
}
stop() {
    echo "Stopping postgresql..."
    # 停止命令
su - postgres -c "
    pg_ctl -D /opt/postgresql/data stop"
}
restart() {
    echo "Restarting postgresql..."
    # 重启命令
su - postgres -c "
    pg_ctl -D /opt/postgresql/data restart"
}
status() {
    echo "Checking postgresql status..."
    # 查看状态命令
su - postgres -c "
    pg_ctl -D /opt/postgresql/data status"
}

case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    restart)
        restart
        ;;
    status)
        status
        ;;
    *)
        echo "Usage: $0 {start|stop|restart|status}"
        exit 1
        ;;
esac

exit 0
EOF

chmod +x /etc/init.d/postgresql

• 启动服务

service postgresql start

• systemd 服务启动（Centos系统）

• vi /usr/lib/systemd/system/postgresql-12.service

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

[Service]
#Type=forking
Type=simple

User=postgres
Group=postgres

# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.

# Location of database directory
Environment=PGDATA=/opt/postgresql/data/


OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

#源码安装没有这个脚本postgresql-12-check-db-dir
ExecStartPre=/opt/postgresql/bin/postgresql-12-check-db-dir ${PGDATA}
ExecStart=/opt/postgresql/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

• 源码安装，默认没有这个脚本postgresql-12-check-db-dir

vi /opt/postgresql/bin/postgresql-12-check-db-dir

#!/bin/sh

# This script verifies that the postgresql data directory has been correctly
# initialized.  We do not want to automatically initdb it, because that has
# a risk of catastrophic failure (ie, overwriting a valuable database) in
# corner cases, such as a remotely mounted database on a volume that's a
# bit slow to mount.  But we can at least emit a message advising newbies
# what to do.

PGDATA="$1"

if [ -z "$PGDATA" ]
then
    echo "Usage: $0 database-path"
    exit 1
fi

# PGVERSION is the full package version, e.g., 12.0
# Note: the specfile inserts the correct value during package build
PGVERSION=12
# PGMAJORVERSION is major version, e.g., 9.6 (this should match PG_VERSION)
PGMAJORVERSION=`echo "$PGVERSION" | sed 's/^\([0-9]*\.[0-9]*\).*$/\1/'`
# PREVMAJORVERSION is the previous major version, e.g., 9.5, for upgrades
# Note: the specfile inserts the correct value during package build
PREVMAJORVERSION=11
# PGDOCDIR is the directory containing the package's documentation
# Note: the specfile inserts the correct value during package build
PGDOCDIR=/usr/share/doc/postgresql12-12.7

# Check for the PGDATA structure
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base" ]
then
    # Check version of existing PGDATA
    if [ x`cat "$PGDATA/PG_VERSION"` = x"$PGMAJORVERSION" ]
    then
        : A-OK
    elif [ x`cat "$PGDATA/PG_VERSION"` = x"$PREVMAJORVERSION" ]
    then
        echo $"An old version of the database format was found."
        echo $"Use \"postgresql-setup upgrade\" to upgrade to version $PGMAJORVERSION."
        echo $"See $PGDOCDIR/README.rpm-dist for more information."
        exit 1
    else
        echo $"An old version of the database format was found."
        echo $"You need to dump and reload before using PostgreSQL $PGMAJORVERSION."
        echo $"See $PGDOCDIR/README.rpm-dist for more information."
        exit 1
    fi
else
    # No existing PGDATA! Warn the user to initdb it.
    echo $"\"$PGDATA\" is missing or empty."
    echo $"Use \"/usr/pgsql-12/bin/postgresql-12-setup initdb\" to initialize the database cluster."
    echo $"See $PGDOCDIR/README.rpm-dist for more information."
    exit 1
fi

exit 0

• 启动

chmod +x /opt/postgresql/bin/postgresql-12-check-db-dir
systemctl daemon-reload
systemctl start postgresql-12.service