要求

保留原环境原数据

安装依赖

1
2
3
mkdir rpmpackage
yum install --downloadonly --downloaddir=rpmpackage/ gcc readline-devel zlib-devel openssl-devel libxml2-devel systemd-devel bison flex make libicu-devel perl net-tools tree wget curl vim nano
yum install rpmpackage/*

安装postgresql 10.5

安装

1
2
3
4
5
6
tar zxvf postgresql-10.5.tar.gz
cd postgresql-10.5
mkdir -p /opt/pgsql10
./configure --prefix=/opt/pgsql10 --with-openssl --with-libxml --with-systemd
make -j 4
make install

设置环境变量

1
2
3
4
5
6
vim /etc/profile.d/pgsql10.sh
export PGHOME=/opt/pgsql10
export PATH=$PGHOME/bin:$PATH
export PGDATA=/opt/pgsql10/data

source /etc/profile.d/pgsql10.sh

创建用户

1
2
3
useradd -m postgres
mkdir -p /opt/pgsql10/data
chown -R postgres:postgres /opt/pgsql10/data

初始化数据库

1
2
su - postgres
initdb -D /opt/pgsql10/data/

修改配置

1
2
3
4
5
6
7
vim /opt/pgsql10/data/postgresql.conf
listen_addresses = '*'

vim /opt/pgsql10/data/pg_hba.conf
host    all             all             0.0.0.0/0               trust

exit    退出postgres用户

设置系统服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
vim /usr/lib/systemd/system/postgresql10.service
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
User=postgres
Group=postgres
ExecStart=/opt/pgsql10/bin/postgres -D /opt/pgsql10/data
[Install]
WantedBy=multi-user.target

systemctl daemon-reload
systemctl enable postgresql10
systemctl start postgresql10

测试

1
2
su - postgres
psql -C "SELECT version();"

安装postgresql 18.3

安装

1
2
3
4
5
6
tar zxvf postgresql-18.3.tar.gz
cd postgresql-18.3
mkdir -p /opt/pgsql18
./configure --prefix=/opt/pgsql18 --with-openssl --with-libxml --with-systemd
make -j 4
make install

设置环境变量

1
2
3
4
5
6
7
8
vim /etc/profile.d/pgsql18.sh
export PGHOME=/opt/pgsql18
export PATH=$PGHOME/bin:$PATH
export PGDATA=/opt/pgsql18/data

注释/etc/profile.d/pgsql10.sh

source /etc/profile.d/pgsql18.sh

初始化数据库

1
2
3
4
5
mkdir -p /opt/pgsql18/data
chown -R postgres:postgres /opt/pgsql18/data/

su - postgres
initdb -D /opt/pgsql18/data/

配置

1
2
3
4
5
6
7
8
9
vim /opt/pgsql18/data/postgresql.conf
listen_addresses = '*'
port = 54321

vim /opt/pgsql18/data/pg_hba.conf
host    all             all             192.168.184.0/24        scram-sha-256        允许单个网段访问
host    all             all             192.168.184.1/32       md5      允许单个ip访问

exit    退出postgres用户

设置系统服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
vim /usr/lib/systemd/system/postgresql18.service
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
User=postgres
Group=postgres
ExecStart=/opt/pgsql18/bin/postgres -D /opt/pgsql18/data
[Install]
WantedBy=multi-user.target

systemctl daemon-reload
systemctl enable postgresql18
systemctl start postgresql18

设置用户远程登陆密码

1
2
3
su - postgres
psql -p 54321
ALTER USER postgres WITH PASSWORD '2008.com';

导入导出数据

1
2
pg_dumpall -U postgres -p 54321 > pgsqlall.sql
psql -h localhost -p 54321 -U postgres -f pgsqlall.sql postgres