部署13版本postgreSql
1.去官方文档里找到自己想要安装的版本PostgreSQL: Linux downloads (Red Hat family)
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql13-server
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13
数据库文件初始化
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
2.查看13的状态
systemctl status postgresql-13
3.生成数据库文件默认地址
cd /var/lib/pgsql/13
4.安装完成后默认会创建一个postgres用户,可以通过命令修改用户密码
vim /var/lib/pgsql/13/data/pg_hba.conf
修改
# "local" is for Unix domain socket connections only
local all all trust #原来为peer
# IPv4 local connections:
host all all 0.0.0.0/0 md5 # 新增
host all all 127.0.0.1/32 md5 # 修改
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
5.然后重启服务
sudo systemctl restart postgresql-13.service
6.设置密码,并创建ssgt_spatial库(下文导入要用到)
psql -U postgres 登录postgres用户,上面改成了trust,不需要密码就可登录
ALTER USER postgres WITH PASSWORD '2bM3f*H19s#'; 登录状态下修改密码
CREATE DATABASE ssgt_spatial; 创建ssgt_spatial库
\q 退出
7.然后重新编辑
vim /var/lib/pgsql/13/data/pg_hba.conf
# "local" is for Unix domain socket connections only
local all all md5 # 原来为trust
# IPv4 local connections:
host all all 0.0.0.0/0 md5 # 新增
host all all 127.0.0.1/32 md5 # 修改
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all md5
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
8.修改默认端口,开启远程访问
vim /var/lib/pgsql/13/data/postgresql.conf
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
port = 15432 # (change requires restart)
9.重启服务
sudo systemctl restart postgresql-13.service
10.防火墙配置
sudo firewall-cmd --zone=public --add-port=5432/tcp --permanent
sudo firewall-cmd --reload
sudo firewall-cmd --zone=public --list-ports
11.yum 安装 postgis
yum install epel-release
yum list postgis* 展示所有版本
sudo yum install postgis32_13.x86_64 选择于13对应的版本
12导入与导出数据
导出
E:\Soft\PostgreSQL\14\bin\pg_dump -h localhost -U postgres -d ssgt_spatial -f E:\ssgt_spatial.sql
导入
sudo -u postgres psql -h localhost -U postgres -d ssgt_spatial -f /data/ssgt_spatial.sql
ssgt_spatial 库名
postgres 用户名
localhost
导入时需要先创建ssgt_spatial库(见第6步)