Linux环境下PG 14的源码安装部署测试详细笔记

以下内容是参考网络内容，并稍做修改的测试笔记

--关闭selinux，打开防火墙端口

setenforce 0

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux

firewall-cmd --zone=public --add-port=5432/tcp --permanent

firewall-cmd --reload

firewall-cmd --zone=public --list-all

-- 安装依赖包

yum install -y cmake make gcc zlib zlib-devel gcc-c++ perl perl-ExtUtils-Embed readline readline-devel \

python python-devel tcl openssl ncurses-devel openldap pam flex

-- 创建用户

useradd postgres

echo "redhat" | passwd --stdin postgres

-- 创建目录

mkdir -p /usr/local/pgsql14.2/{pgmain,pgdata,archive,scripts,backup,soft}

chown -R pgsql:pgsql /usr/local/pgsql14.2

chmod -R 775 /usr/local/pgsql14.2

-- 生成软链接，方便后续升级替换

ln -sf /usr/local/pgsql14.2 /usr/local/pgsql

chown postgres:postgres /usr/local/pgsql

-- 编译

su - postgres

cd /usr/local/pgsql14.2/soft

-- 下载源码包

wget https://ftp.postgresql.org/pub/source/v14.2/postgresql-14.2.tar.gz --no-check-certificate

tar zxvf postgresql-14.2.tar.gz

cd postgresql-14.2

./configure --prefix=/usr/local/pgsql14.2/pgmain --with-perl --with-python

make -j 16 && make install

--设置postgres用户环境变量

cat >>  ~/.bash_profile <<"EOF"

export PGPORT=5432

export PGDATA=/usr/local/pgsql/pgdata

export PGHOME=/usr/local/pgsql/pgmain

export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

export PATH=$PGHOME/bin:$PATH:.

export PGHOST=$PGDATA

export PGUSER=postgres

export PGDATABASE=postgres

EOF

source  ~/.bash_profile

--初始化

/usr/local/pgsql/pgmain/bin/initdb -D /usr/local/pgsql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres

Success. You can now start the database server using:

    /usr/local/pgsql/pgmain/bin/pg_ctl -D /usr/local/pgsql/pgdata -l logfile start

-- 修改PG数据库参数文件

cat >> /usr/local/pgsql/pgdata/postgresql.conf <<"EOF"

listen_addresses = '*'

port=5432

unix_socket_directories='/usr/local/pgsql/pgdata'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%a.log'

log_truncate_on_rotation = on

EOF

cat   > /usr/local/pgsql/pgdata/pg_hba.conf << EOF

# TYPE  DATABASE    USER    ADDRESS       METHOD

host      all       all    0.0.0.0/0        md5

local      all       all                  peer

EOF

pg_hba.conf修改后，使用pg_ctl reload重新读取pg_hba.conf文件

另外一种加载配置文件的方法是以超级用户登录到任何一个数据库后执行：

命令：select pg_reload_conf();

--启动数据库

[postgres@test pgsql]$ pg_ctl start

waiting for server to start....2022-06-01 15:28:32.686 CST [32022] LOG:  redirecting log output to logging collector process

2022-06-01 15:28:32.686 CST [32022] HINT:  Future log output will appear in directory "pg_log".

 done

server started

--设置开机自动启动（ROOT）

cat > /etc/systemd/system/POSTGRES.service <<"EOF"

[Unit]

Description=PostgreSQL database server

Documentation=man:postgres(1)

After=network.target

[Service]

Type=forking

User=postgres

Group=postgres

Environment=PGPORT=5432

Environment=PGDATA=/usr/local/pgsql/pgdata

OOMScoreAdjust=-1000

ExecStart=/usr/local/pgsql/pgmain/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300

ExecStop=/usr/local/pgsql/pgmain/bin/pg_ctl stop -D ${PGDATA} -s -m fast

ExecReload=/usr/local/pgsql/pgmain/bin/pg_ctl reload -D ${PGDATA} -s

KillMode=mixed

KillSignal=SIGINT

TimeoutSec=0

[Install]

WantedBy=multi-user.target

EOF

systemctl daemon-reload

systemctl enable POSTGRES

systemctl start POSTGRES

systemctl status POSTGRES

su - postgres

[postgres@test ~]$ psql

postgres=# \password postgres

or:

postgres=# alter user postgres with password 'redhat';

-- 安装插件

cd /usr/local/pgsql/soft/postgresql-14.2/contrib

make

sudo make install

[postgres@test pgsql]$ psql

psql (14.2)

Type "help" for help.

postgres=# create extension pageinspect;

CREATE EXTENSION

postgres=# create extension pg_stat_statements;

postgres=# select * from pg_extension ;

  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig |extcondition

-------+--------------------+----------+--------------+----------------+------------+-----------+--------------

 13878 | plpgsql            |       10 |           11 | f              | 1.0        |           |

 16384 | pageinspect        |       10 |         2200 | t              | 1.9        |           |

 16426 | pg_stat_statements |       10 |         2200 | t              | 1.9        |           |

(3 rows)

postgres=# select * from pg_available_extensions order by name;

[root@test src]# ps -ef | grep postgres

postgres 16996     1  0 17:04 ?        00:00:00 /usr/local/pgsql14.2/pgmain/bin/postgres -D /usr/local/pgsql/pgdata -p 5432

postgres 16997 16996  0 17:04 ?        00:00:00 postgres: logger

postgres 16999 16996  0 17:04 ?        00:00:00 postgres: checkpointer

postgres 17000 16996  0 17:04 ?        00:00:00 postgres: background writer

postgres 17001 16996  0 17:04 ?        00:00:00 postgres: walwriter

postgres 17002 16996  0 17:04 ?        00:00:00 postgres: autovacuum launcher

postgres 17003 16996  0 17:04 ?        00:00:00 postgres: stats collector

postgres 17004 16996  0 17:04 ?        00:00:00 postgres: logical replication launcher

postgres 17012  4853  0 17:04 pts/0    00:00:00 psql -h 192.168.2.208 -p 5432 postgres postgres

postgres 17014 16996  0 17:04 ?        00:00:00 postgres: postgres postgres 192.168.2.208(39992) idle

postgres 17124 17086  0 17:04 pts/2    00:00:00 psql

postgres 17125 16996  0 17:04 ?        00:00:00 postgres: postgres postgres [local] idle

postgres 17187 16996  0 17:09 ?        00:00:00 postgres: postgres postgres 192.168.2.177(49947) idle

postgres 17188 16996  0 17:09 ?        00:00:00 postgres: postgres postgres 192.168.2.177(49949) idle

postgres 17189 16996  0 17:09 ?        00:00:00 postgres: postgres postgres 192.168.2.177(49950) idle

[postgres@test pgsql]$ lsof -i:5432

COMMAND   PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME

postgres 5025 postgres    6u  IPv4  52945      0t0  TCP *:postgres (LISTEN)

postgres 5025 postgres    7u  IPv6  52946      0t0  TCP *:postgres (LISTEN)