Centos7安装PostgreSQL 12.4 + postgis安装

迷南。 2022-12-04 09:29 302阅读 0赞

环境：Centos7.8-Mini + PostgreSQL 12.4 +

\# 关闭防火墙  
systemctl  stop firewalld  
systemctl  disable firewalld

vim /etc/selinux/config  
SELINUX=disabled

\# 立即生效  
setenforce 0

方法1）yum源安装PostgreSQL

进入PostgreSQL官网：  
https://www.postgresql.org/download/linux/redhat/

根据自己的PostgreSQL版本信息和环境信息完成配置后，就会给出基于yum源的安装方式的安装操作：

Select version:   
12

Select platform:   
Red Hat Enterprise, CentOS, Scientific or Oracle version 7  
Select architecture:   
x86\_64  
Copy, paste and run the relevant parts of the setup script:  
\# Install the repository RPM:  
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86\_64/pgdg-redhat-repo-latest.noarch.rpm

\# Install PostgreSQL:  
sudo yum install -y postgresql12-server

\# Optionally initialize the database and enable automatic start:  
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb  
sudo systemctl enable postgresql-12  
sudo systemctl start postgresql-12

注意：yum仓库地址可能会发生变化和调整，早期我安装时生成的地址：

yum install -y https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86\_64/pgdg-redhat-repo-latest.noarch.rpm 后来不存在了，对应目录下只有rpm安装包，没有repo rpm安装包了。

官网重新配置后，发现地址发生了变化，更新为：

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86\_64/pgdg-redhat-repo-latest.noarch.rpm。所以每次安装时，还是直接去官网重新配置和生成吧。

安装PostgreSQL

\# 安装PostgreSQL yum仓库  
yum install -y https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86\_64/pgdg-redhat-repo-latest.noarch.rpm （该地址已经不可用了）

\# 安装PostgreSQL yum仓库  
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86\_64/pgdg-redhat-repo-latest.noarch.rpm

\#查看yum源的所有PostgreSQL所有版本  
cat /etc/yum.repos.d/pgdg-redhat-all.repo |grep PostgreSQL  
yum search postgresql

yum search postgresql |grep postgresql12                    
postgresql12.x86\_64 : PostgreSQL client programs and libraries  
postgresql12-contrib.x86\_64 : Contributed source and binaries distributed with  
postgresql12-devel.x86\_64 : PostgreSQL development header files and libraries  
postgresql12-docs.x86\_64 : Extra documentation for PostgreSQL  
postgresql12-libs.x86\_64 : The shared libraries required for any PostgreSQL  
postgresql12-llvmjit.x86\_64 : Just-in-time compilation support for PostgreSQL  
postgresql12-odbc.x86\_64 : PostgreSQL ODBC driver  
postgresql12-odbc-debuginfo.x86\_64 : Debug information for package  
                                   : postgresql12-odbc  
postgresql12-plperl.x86\_64 : The Perl procedural language for PostgreSQL  
postgresql12-plpython.x86\_64 : The Python procedural language for PostgreSQL  
postgresql12-plpython3.x86\_64 : The Python3 procedural language for PostgreSQL  
postgresql12-pltcl.x86\_64 : The Tcl procedural language for PostgreSQL  
postgresql12-server.x86\_64 : The programs needed to create and run a PostgreSQL  
postgresql12-tcl.x86\_64 : A Tcl client library for PostgreSQL  
postgresql12-test.x86\_64 : The test suite distributed with PostgreSQL

\# 通过yum安装 PostgreSQL Client packages & PostgreSQL Server packages  
yum -y install postgresql12  
yum install -y postgresql12-server  
默认安装路径为：/usr/pgsql-12 目录

\# contrib 是一些第三方组织贡献出来的一些工具，在日常维护中也很有用，如果需要的话，也可以安装上  
yum install -y postgresql12-contrib

方法2）rpm包方式安装PostgreSQL（不推荐该方式，因为后续PostGIS安装如果不通过yum方式安装，其他依赖软件（PostgreSQL，GEOS，SFCGAL，GDAL，PROJ，protobuf-c，json-c）和依赖组件、有版本要求，安装依赖包会非常麻烦。如果只是安装PostgreSQL不安装PostGIS，那么该方式还是可以的）  
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86\_64/ 下面不再提供repos，但提供了rpm包，也可以通过rpm安装包进行PostgreSQL的安装，下面目录中寻找自己对应OS和对应PG的版本rpm包

\# 下载rpm 12.4安装包  
mkdir -p /opt/postgres-12.4-down  
cd $\_  
wget https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86\_64/postgresql12-libs-12.4-1PGDG.rhel7.x86\_64.rpm  
wget https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86\_64/postgresql12-12.4-1PGDG.rhel7.x86\_64.rpm  
wget https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86\_64/postgresql12-server-12.4-1PGDG.rhel7.x86\_64.rpm  
\# contrib 是一些第三方组织贡献出来的一些工具，在日常维护中也很有用，如果需要的话，也可以安装上  
wget https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86\_64/postgresql12-contrib-12.4-1PGDG.rhel7.x86\_64.rpm

如果要下载其他OS版本对应的包或者其他版本PostgreSQL的安装包，可以切换对应的目录寻找。PostGIS也同步提供了，如果要安装对应版本的PostGIS、也可以同步下载

\# yum localinstall 本地目录安装方式进行安装（如果有其他额外的组件包依赖、该方式安装也会自动安装依赖包解决依赖问题）  
yum localinstall -y \*.rpm

当然，还有一种方式，也可以用传统的 yum install 方式安装  
\# 先安装相应依赖，否则后面安装会提示依赖组件不存在  
\# yum install -y libicu systemd-sysv

安装rpm包（注意：按照这个先后顺序安装，卸载就反序卸载）  
rpm -ivh postgresql12-libs-12.4-1PGDG.rhel7.x86\_64.rpm  
rpm -ivh postgresql12-12.4-1PGDG.rhel7.x86\_64.rpm  
rpm -ivh postgresql12-server-12.4-1PGDG.rhel7.x86\_64.rpm  
rpm -ivh postgresql12-contrib-12.4-1PGDG.rhel7.x86\_64.rpm

如果要卸载，无需卸载的跳过  
rpm -e postgresql12-contrib-12.4-1PGDG.rhel7.x86\_64  
rpm -e postgresql12-server-12.4-1PGDG.rhel7.x86\_64  
rpm -e postgresql12-12.4-1PGDG.rhel7.x86\_64  
rpm -e postgresql12-libs-12.4-1PGDG.rhel7.x86\_64  
rm -rf /usr/pgsql-12/  
rm -rf /var/lib/pgsql/

到此为止，PostgreSQL软件包安装完成。

软件安装后，接下来就是初始化DB、设置自启动和数据库配置修改等操作了

\# 初始化数据库  
/usr/pgsql-12/bin/postgresql-12-setup initdb

\# 启动服务&设置开机自启动  
systemctl start postgresql-12  
systemctl enable postgresql-12

\# 查看数据库服务状态  
systemctl status postgresql-12

\[root@localhost postgres-12.4-down\]\# systemctl status postgresql-12  
● postgresql-12.service - PostgreSQL 12 database server  
   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)  
   Active: active (running) since Sat 2021-09-11 23:59:01 CST; 6min ago  
     Docs: https://www.postgresql.org/docs/12/static/  
 Main PID: 2964 (postmaster)  
   CGroup: /system.slice/postgresql-12.service  
           ├─2964 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/  
           ├─2966 postgres: logger     
           ├─2968 postgres: checkpointer     
           ├─2969 postgres: background writer     
           ├─2970 postgres: walwriter     
           ├─2971 postgres: autovacuum launcher     
           ├─2972 postgres: stats collector     
           └─2973 postgres: logical replication launcher

Sep 11 23:59:01 localhost.localdomain systemd\[1\]: Starting PostgreSQL 12 database server...  
Sep 11 23:59:01 localhost.localdomain postmaster\[2964\]: 2021-09-11 23:59:01.454 CST \[2964\] LOG:  starting PostgreSQL 12.4 on x86\_64-pc-linux-gnu, compiled by g...), 64-bit  
Sep 11 23:59:01 localhost.localdomain postmaster\[2964\]: 2021-09-11 23:59:01.459 CST \[2964\] LOG:  listening on IPv6 address "::1", port 5432  
Sep 11 23:59:01 localhost.localdomain postmaster\[2964\]: 2021-09-11 23:59:01.459 CST \[2964\] LOG:  listening on IPv4 address "127.0.0.1", port 5432  
Sep 11 23:59:01 localhost.localdomain postmaster\[2964\]: 2021-09-11 23:59:01.461 CST \[2964\] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"  
Sep 11 23:59:01 localhost.localdomain postmaster\[2964\]: 2021-09-11 23:59:01.468 CST \[2964\] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"  
Sep 11 23:59:01 localhost.localdomain postmaster\[2964\]: 2021-09-11 23:59:01.483 CST \[2964\] LOG:  redirecting log output to logging collector process  
Sep 11 23:59:01 localhost.localdomain postmaster\[2964\]: 2021-09-11 23:59:01.483 CST \[2964\] HINT:  Future log output will appear in directory "log".  
Sep 11 23:59:01 localhost.localdomain systemd\[1\]: Started PostgreSQL 12 database server.  
Hint: Some lines were ellipsized, use -l to show in full.  
\[root@localhost postgres-12.4-down\]\#

查看service文件信息，可以看到两个目录配置（程序目录和数据存储目录）

\[Unit\]  
Description=PostgreSQL 12 database server  
Documentation=https://www.postgresql.org/docs/12/static/  
After=syslog.target  
After=network.target

\[Service\]  
Type=notify

User=postgres  
Group=postgres

\# Note: avoid inserting whitespace in these Environment= lines, or you may  
\# break postgresql-setup.

\# Location of database directory  
Environment=PGDATA=/var/lib/pgsql/12/data/

\# Where to send early-startup messages from the server (before the logging  
\# options of postgresql.conf take effect)  
\# This is normally controlled by the global default set by systemd  
\# StandardOutput=syslog

\# Disable OOM kill on the postmaster  
OOMScoreAdjust=-1000  
Environment=PG\_OOM\_ADJUST\_FILE=/proc/self/oom\_score\_adj  
Environment=PG\_OOM\_ADJUST\_VALUE=0

ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir $\{PGDATA\}  
ExecStart=/usr/pgsql-12/bin/postmaster -D $\{PGDATA\}  
ExecReload=/bin/kill -HUP $MAINPID  
KillMode=mixed  
KillSignal=SIGINT

\# Do not set any timeout value, so that systemd will not kill postmaster  
\# during crash recovery.  
TimeoutSec=0

\# 修改数据库配置  
vim /var/lib/pgsql/12/data/postgresql.conf

listen\_addresses = '\*'  
port = 5432

vim /var/lib/pgsql/12/data/pg\_hba.conf  
末尾添加下面类容，不限制任何主机并允许远程登录：  
host    all             all             0.0.0.0/0               md5

\# 修改后重启数据库  
systemctl  restart postgresql-12

\# 查看服务监听状态  
netstat -an |grep 5432

PostgreSQL 安装后会默认创建一个 postgres 用户  
\# 切换到 postgres 用户  
su - postgres

\\q  
exit

查看当前连接用户  
select \* from current\_user;  
select user;  
\\du;

执行结果如下：  
postgres=\# select \* from current\_user;  
 current\_user   
\--------------  
 postgres  
(1 row)

postgres=\# select user;  
   user     
\----------  
 postgres  
(1 row)

postgres=\# \\du;  
                                   List of roles  
 Role name |                         Attributes                         | Member of   
\-----------+------------------------------------------------------------+-----------  
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | \{\}

postgres=\#

常用操作命令（PostgreSQL常用命令可参考：[PostgreSQL常用操作命令\_sunny05296的博客-CSDN博客\_postgres操作命令][PostgreSQL_sunny05296_-CSDN_postgres]）

创建数据库新用户：  
postgres=\# CREATE USER test WITH PASSWORD '1q2w3e';  
注意：语句要以分号结尾，密码要用单引号括起来。

创建用户数据库  
postgres=\# CREATE DATABASE testdb01 OWNER test;

将数据库的所有权限赋予用户  
postgres=\# GRANT ALL PRIVILEGES ON DATABASE testdb01 TO test;

将数据库 testdb01 权限授权于 test 用户，但此时用户还是没有已存在的、非自己创建的表的读写权限，如果要把库中的其他用户创建的存量表授权给 test 用户读写，仍需要继续授权表操作：  
注意：该GRANT授权语句必须在所要操作的数据库里执行 GRANT ALL PRIVILEGES ON all tables in schema public TO test;

例如：

postgres=\# \\c testdb01  
You are now connected to database "testdb01" as user "postgres".  
testdb01=\# GRANT ALL PRIVILEGES ON all tables in schema public TO test;  
GRANT  
testdb01=\#

如果是对某个单表单独授权，则执行：

GRANT SELECT ON TABLE test01 TO test;

获取当前db中所有的表信息:  
select \* from pg\_tables;

查看用户的所有表（用户自定义的表，如果未经特殊处理，默认都是放在名为public的schema下）  
postgres=\# select tablename from pg\_tables where schemaname='public';

创建表：  
postgres=\# create table test01(   
id integer not null, name character(255) not null,  
price decimal(8,2) not null,  
primary key(id)  
);

插入数据  
postgres=\# insert into test01(id,name,price) values (1,'a',11.5),(2,'b',20.3);

查看表结构  
\\d test01;

查看表的数据  
select \* from test01;

退出重启数据库后，以用户test登录报错：

\-bash-4.2$ psql -U test testdb01  
psql: error: could not connect to server: FATAL:  Peer authentication failed for user "test"

以postgres用户可以正常登录：  
\-bash-4.2$ psql -U postgres testdb01  
psql (12.4)  
Type "help" for help.  
testdb01=\#

报错原因：  
psql的连接建立于Unix Socket上默认使用peer authentication，所以必须要用和数据库用户相同的系统用户进行登录。  
还有一种方法，将peer authentiction 改为 md5，并给数据库设置密码。修改配置文件/var/lib/pgsql/12/data/pg\_hba.conf，将  
local   all             all                                     peer  
local   replication     all                                     peer  
两行配置的peer改成md5，修改后的内容如下：

vim /var/lib/pgsql/12/data/pg\_hba.conf

\# TYPE  DATABASE        USER            ADDRESS                 METHOD

\# "local" is for Unix domain socket connections only  
\#local   all             all                                     peer  
local   all             all                                     md5  
\# IPv4 local connections:  
host    all             all             127.0.0.1/32            ident  
\# IPv6 local connections:  
host    all             all             ::1/128                 ident  
\# Allow replication connections from localhost, by a user with the  
\# replication privilege.  
\#local   replication     all                                     peer  
local   replication     all                                     md5  
host    replication     all             127.0.0.1/32            ident  
host    replication     all             ::1/128                 ident  
host    all             all             0.0.0.0/0               md5

重启数据库  
systemctl  restart postgresql-12  
su - postgres  
\-bash-4.2$ psql -U test testdb01  
Password for user test:   
psql (12.4)  
Type "help" for help.

testdb01=>   
登录成功

安装postgis

先介绍一下ArcGIS和PostGIS概念：  
安装PostgreSQL数据库，创建用户sde，数据库test，架构名sde。ArcGIS就能连接了，但如果要使用，还要安装PostGIS插件，然后执行create extension postgis；这样该库就有了一个空间类型postgis，就可以成功创建要素类了。  
有了空间类型postgis以后，再执行create enterprise geodatabase工具来创建Geodatabase模型。完成Geodatabase模型创建后，该库就有了两种空间类型，一种是ArcGIS的st\_geometry，一种是PostGIS的geometry。而ArcGIS是可以兼容PostGIS类型的，所以创建要素类时，可以选择使用哪种类型，default就是ArcGIS的st\_geometry，而pg\_geometry是PostGIS的geometry。

安装postgis  
1.安装工具包  
yum install -y wget net-tools epel-release

2.安装postgis  
yum install -y postgis30\_12 postgis30\_12-client

3.安装拓展工具  
yum install -y ogr\_fdw12  
yum install -y pgrouting\_12

注意：安装是注意版本要和PostgreSQL的版本一致，postgis和拓展工具的版本也要保持一致

4.创建数据库spatial\_testdb  
\# create database spatial\_testdb OWNER postgres;  
也可以不单独创建，直接使用已有的数据库 testdb01

5.进入指定的数据库安装postgis扩展插件（开启postgis插件）  
注意：需要使用 postgres 用户  
psql -U postgres testdb01  
create extension postgis;  
create extension postgis\_topology;  
下面如果没有需求，也可以先不开启  
\#\#create extension ogr\_fdw;  
\#\#create extension fuzzystrmatch;  
\#\#create extension address\_standardizer;  
\#\#create extension address\_standardizer\_data\_us;  
\#\#create extension postgis\_tiger\_geocoder;

6.验证是否安装成功  
SELECT postgis\_full\_version();

testdb01=\# SELECT postgis\_full\_version();  
                                                                           postgis\_full\_version                                                                              
\---------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 POSTGIS="3.0.2 2fb2a18" \[EXTENSION\] PGSQL="120" GEOS="3.8.1-CAPI-1.13.3" PROJ="7.1.1" LIBXML="2.9.1" LIBJSON="0.11" LIBPROTOBUF="1.0.2" WAGYU="0.4.3 (Internal)" TOPOLOGY  
(1 row)

testdb01=\#

SELECT ST\_SetSRID(ST\_Point(-87.71, 43.741), 4326), ST\_GeomFromText('POINT(-87.71 43.741)', 4326)

8.创建空间数据表  
CREATE TABLE geom\_test01(id integer not null, name varchar(255), primary key(id));  
SELECT AddGeometryColumn('geom\_test01', 'zone\_geom', 4326, 'POINT', 2);  
INSERT INTO geom\_test01(id, zone\_geom, name) VALUES (1, ST\_GeomFromText('POINT(-0.1250 52.500)',4326), 'test');  
INSERT INTO geom\_test01(id, zone\_geom, name) VALUES (2, ST\_GeomFromText('POINT(27.91162480 -33.01532)', 4326),'test');

SELECT \* FROM geom\_test01;  
SELECT id, ST\_AsText(zone\_geom), ST\_AsEwkt(zone\_geom), ST\_X(zone\_geom), ST\_Y(zone\_geom) FROM geom\_test01;

testdb01=\#

CREATE TABLE geom\_test02(  
id integer not null,  
zone\_geom geometry(point, 4326),  
name varchar(255),  
primary key(id)  
);

INSERT INTO geom\_test02(id, zone\_geom, name) values (1, st\_geomfromtext('point(27.91162480 -33.01532)', 4326), 'aaa');  
SELECT \* FROM geom\_test02;  
SELECT id, ST\_AsText(zone\_geom), ST\_AsEwkt(zone\_geom), ST\_X(zone\_geom), ST\_Y(zone\_geom) FROM geom\_test02;

testdb01=\# SELECT \* FROM geom\_test02;  
 id |                     zone\_geom                      | name   
\----+----------------------------------------------------+------  
  1 | 0101000020E6100000F8382E3E60E93B40C47C7901F68140C0 | aaa  
(1 row)

testdb01=\# SELECT id, ST\_AsText(zone\_geom), ST\_AsEwkt(zone\_geom), ST\_X(zone\_geom), ST\_Y(zone\_geom) FROM geom\_test02;  
 id |          st\_astext          |               st\_asewkt               |    st\_x    |   st\_y      
\----+-----------------------------+---------------------------------------+------------+-----------  
  1 | POINT(27.9116248 -33.01532) | SRID=4326;POINT(27.9116248 -33.01532) | 27.9116248 | -33.01532  
(1 row)

[PostgreSQL_sunny05296_-CSDN_postgres]: https://blog.csdn.net/sunny05296/article/details/108466453