麒麟v10 PostgreSQL 数据迁移

要求

保留原环境原数据

安装依赖

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

安装

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

设置环境变量

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

创建用户

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

初始化数据库

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

修改配置

vim /opt/pgsql10/data/postgresql.conf
listen_addresses = '*'

vim /opt/pgsql10/data/pg_hba.conf    # 测试环境用trust
host    all             all             0.0.0.0/0               trust

exit    退出postgres用户

设置系统服务

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

测试

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

安装postgresql 18.3

安装

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

设置环境变量

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

初始化数据库

mkdir -p /opt/pgsql18/data
chown -R postgres:postgres /opt/pgsql18/data/

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

配置

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用户

设置系统服务

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

设置用户远程登陆密码

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

导入导出数据

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