PostGreSQL|PostGIS部署

部署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步）