0223---在华为云服务器上安装 postgresql14.7过程

最新推荐文章于 2023-11-03 11:31:09 发布

gmriwyf

最新推荐文章于 2023-11-03 11:31:09 发布

阅读量414

点赞数

文章标签：运维 postgresql

本文链接：https://blog.csdn.net/gmriwyf/article/details/131619471

版权

在centos7 服务器上安装 postgresql14.7过程

一安装过程

1 从官网确定安装脚本

https://www.postgresql.org ，点击DownLoads
选择 Linux
选择postgresql版本
选择 centos7

2 选择后执行官方提供的脚本

# 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 postgresql14-server postgresql14-contrib

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

3 默认安装位置

pg 系统安装位置

[root@wy pgsql-14]# tree -d -L 3 /usr/pgsql-14/
/usr/pgsql-14/
├── bin
├── doc
│   └── extension
├── lib
│   └── bitcode
└── share
    ├── contrib
    ├── extension
    ├── locale
    │   ├── cs
    │   ├── de
    │   ├── el
    │   ├── es
    │   ├── fr
    │   ├── it
    │   ├── ja
    │   ├── ko
    │   ├── pl
    │   ├── pt_BR
    │   ├── ru
    │   ├── sv
    │   ├── tr
    │   ├── uk
    │   ├── vi
    │   └── zh_CN
    ├── man
    │   ├── man1
    │   ├── man3
    │   └── man7
    ├── timezonesets
    └── tsearch_data

初始化的生成的数据库位置

/var/lib/pgsql/
└── 14
    ├── backups
    └── data
        ├── base
        │   ├── 1
        │   ├── 14485
        │   └── 14486
        ├── global
        ├── log
        ├── pg_commit_ts
        ├── pg_dynshmem
        ├── pg_logical
        │   ├── mappings
        │   └── snapshots
        ├── pg_multixact
        │   ├── members
        │   └── offsets
        ├── pg_notify
        ├── pg_replslot
        ├── pg_serial
        ├── pg_snapshots
        ├── pg_stat
        ├── pg_stat_tmp
        ├── pg_subtrans
        ├── pg_tblspc
        ├── pg_twophase
        ├── pg_wal
        │   └── archive_status
        └── pg_xact

二修改必要的设置

1 检查防火墙的设置

centos7 防火墙放行 TCP 5432端口

--查看服务器当前防火墙的状态。
[root@orc19c ~]# systemctl  status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2023-05-25 09:58:07 CST; 1 months 14 days ago

-- 查看当前防火墙放行了哪些端口
[root@orc19c ~]# firewall-cmd --list-ports
1521/tcp 5500/tcp
--查看防火墙使用的默认区域(zone)
[root@orc19c ~]# firewall-cmd --get-default-zone
public
--把PG的默认使用端口加入到操作系统防火墙默认区域中
[root@orc19c ~]#firewall-cmd --zone=public  --add-port=5432/tcp --permanent
--使防火墙规则生效
[root@orc19c ~]#firewall-cmd --reload
--或

2 设置 postgres 用户密码

[root@wy ~]# su - postgres
-bash-4.2$ psql
postgres=# ALTER USER postgres WITH PASSWORD 'XXXXXX';
# 重启 postgresql 服务
[root@wy ~]# systemctl restart postgresql-14

3 修改配置文件 pg_hba.conf 和 postgresql.conf

修改 /var/lib/pgsql/14/data 目录下的配置文件

vim pg_hba.conf 添加

# IPv4 local connections:
host    all             all             0.0.0.0/0               scram-sha-256

vim postgresql.conf

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'                  # what IP address(es) to listen on;

4 安装 pldebugger 插件

官方网址

https://rhel.pkgs.org/7/postgresql-14-x86_64/pldebugger_14-1.4-1.rhel7.x86_64.rpm.html

由于安装了 postgresql的yum源，可以直接安装pldebugger:

# yum install pldebugger_14

修改 postgresql.conf

shared_preload_libraries = 'plugin_debugger'

5 安装 postgis 拓展

[root@wy extension]# yum search postgis
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
============================================== N/S matched: postgis ==============================================
postgis-docs.x86_64 : Extra documentation for PostGIS
postgis-utils.x86_64 : The utils for PostGIS
postgis24_11-client.x86_64 : Client tools and their libraries of PostGIS
postgis24_11-devel.x86_64 : Development headers and libraries for PostGIS
postgis24_11-docs.x86_64 : Extra documentation for PostGIS
postgis24_11-gui.x86_64 : GUI for PostGIS
postgis24_11-utils.x86_64 : The utils for PostGIS
postgis25_11-client.x86_64 : Client tools and their libraries of PostGIS
postgis25_11-devel.x86_64 : Development headers and libraries for PostGIS
postgis25_11-docs.x86_64 : Extra documentation for PostGIS
postgis25_11-gui.x86_64 : GUI for PostGIS
postgis25_11-utils.x86_64 : The utils for PostGIS
postgis25_12-client.x86_64 : Client tools and their libraries of PostGIS
postgis25_12-devel.x86_64 : Development headers and libraries for PostGIS
postgis25_12-docs.x86_64 : Extra documentation for PostGIS
postgis25_12-gui.x86_64 : GUI for PostGIS
postgis25_12-utils.x86_64 : The utils for PostGIS
postgis30_11-client.x86_64 : Client tools and their libraries of PostGIS
postgis30_11-devel.x86_64 : Development headers and libraries for PostGIS
postgis30_11-docs.x86_64 : Extra documentation for PostGIS
postgis30_11-gui.x86_64 : GUI for PostGIS
postgis30_11-utils.x86_64 : The utils for PostGIS
postgis30_12-client.x86_64 : Client tools and their libraries of PostGIS
postgis30_12-devel.x86_64 : Development headers and libraries for PostGIS
postgis30_12-docs.x86_64 : Extra documentation for PostGIS
postgis30_12-gui.x86_64 : GUI for PostGIS
postgis30_12-utils.x86_64 : The utils for PostGIS
postgis30_13-client.x86_64 : Client tools and their libraries of PostGIS
postgis30_13-devel.x86_64 : Development headers and libraries for PostGIS
postgis30_13-docs.x86_64 : Extra documentation for PostGIS
postgis30_13-gui.x86_64 : GUI for PostGIS
postgis30_13-utils.x86_64 : The utils for PostGIS
postgis31_11-client.x86_64 : Client tools and their libraries of PostGIS
postgis31_11-devel.x86_64 : Development headers and libraries for PostGIS
postgis31_11-docs.x86_64 : Extra documentation for PostGIS
postgis31_11-gui.x86_64 : GUI for PostGIS
postgis31_11-utils.x86_64 : The utils for PostGIS
postgis31_12-client.x86_64 : Client tools and their libraries of PostGIS
postgis31_12-devel.x86_64 : Development headers and libraries for PostGIS
postgis31_12-docs.x86_64 : Extra documentation for PostGIS
postgis31_12-gui.x86_64 : GUI for PostGIS
postgis31_12-utils.x86_64 : The utils for PostGIS
postgis31_13-client.x86_64 : Client tools and their libraries of PostGIS
postgis31_13-devel.x86_64 : Development headers and libraries for PostGIS
postgis31_13-docs.x86_64 : Extra documentation for PostGIS
postgis31_13-gui.x86_64 : GUI for PostGIS
postgis31_13-utils.x86_64 : The utils for PostGIS
postgis31_14-client.x86_64 : Client tools and their libraries of PostGIS
postgis31_14-devel.x86_64 : Development headers and libraries for PostGIS
postgis31_14-docs.x86_64 : Extra documentation for PostGIS
postgis31_14-gui.x86_64 : GUI for PostGIS
postgis31_14-utils.x86_64 : The utils for PostGIS
postgis32_11-client.x86_64 : Client tools and their libraries of PostGIS
postgis32_11-devel.x86_64 : Development headers and libraries for PostGIS
postgis32_11-docs.x86_64 : Extra documentation for PostGIS
postgis32_11-gui.x86_64 : GUI for PostGIS
postgis32_11-utils.x86_64 : The utils for PostGIS
postgis32_12-client.x86_64 : Client tools and their libraries of PostGIS
postgis32_12-devel.x86_64 : Development headers and libraries for PostGIS
postgis32_12-docs.x86_64 : Extra documentation for PostGIS
postgis32_12-gui.x86_64 : GUI for PostGIS
postgis32_12-utils.x86_64 : The utils for PostGIS
postgis32_13-client.x86_64 : Client tools and their libraries of PostGIS
postgis32_13-devel.x86_64 : Development headers and libraries for PostGIS
postgis32_13-docs.x86_64 : Extra documentation for PostGIS
postgis32_13-gui.x86_64 : GUI for PostGIS
postgis32_13-utils.x86_64 : The utils for PostGIS
postgis32_14-client.x86_64 : Client tools and their libraries of PostGIS
postgis32_14-devel.x86_64 : Development headers and libraries for PostGIS
postgis32_14-docs.x86_64 : Extra documentation for PostGIS
postgis32_14-gui.x86_64 : GUI for PostGIS
postgis32_14-utils.x86_64 : The utils for PostGIS
postgis32_15-client.x86_64 : Client tools and their libraries of PostGIS
postgis32_15-devel.x86_64 : Development headers and libraries for PostGIS
postgis32_15-docs.x86_64 : Extra documentation for PostGIS
postgis32_15-gui.x86_64 : GUI for PostGIS
postgis32_15-utils.x86_64 : The utils for PostGIS
postgis33_11-client.x86_64 : Client tools and their libraries of PostGIS
postgis33_11-devel.x86_64 : Development headers and libraries for PostGIS
postgis33_11-docs.x86_64 : Extra documentation for PostGIS
postgis33_11-gui.x86_64 : GUI for PostGIS
postgis33_11-utils.x86_64 : The utils for PostGIS
postgis33_12-client.x86_64 : Client tools and their libraries of PostGIS
postgis33_12-devel.x86_64 : Development headers and libraries for PostGIS
postgis33_12-docs.x86_64 : Extra documentation for PostGIS
postgis33_12-gui.x86_64 : GUI for PostGIS
postgis33_12-utils.x86_64 : The utils for PostGIS
postgis33_13-client.x86_64 : Client tools and their libraries of PostGIS
postgis33_13-devel.x86_64 : Development headers and libraries for PostGIS
postgis33_13-docs.x86_64 : Extra documentation for PostGIS
postgis33_13-gui.x86_64 : GUI for PostGIS
postgis33_13-utils.x86_64 : The utils for PostGIS
postgis33_14-client.x86_64 : Client tools and their libraries of PostGIS
postgis33_14-devel.x86_64 : Development headers and libraries for PostGIS
postgis33_14-docs.x86_64 : Extra documentation for PostGIS
postgis33_14-gui.x86_64 : GUI for PostGIS
postgis33_14-utils.x86_64 : The utils for PostGIS
postgis33_15-client.x86_64 : Client tools and their libraries of PostGIS
postgis33_15-devel.x86_64 : Development headers and libraries for PostGIS
postgis33_15-docs.x86_64 : Extra documentation for PostGIS
postgis33_15-gui.x86_64 : GUI for PostGIS
postgis33_15-utils.x86_64 : The utils for PostGIS
SFCGAL.x86_64 : C++ wrapper library around CGAL for PostGIS
pgrouting_11.x86_64 : Routing functionality for PostGIS
pgrouting_12.x86_64 : Routing functionality for PostGIS
pgrouting_13.x86_64 : Routing functionality for PostGIS
pgrouting_14.x86_64 : Routing functionality for PostGIS
pgrouting_15.x86_64 : Routing functionality for PostGIS
postgis.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis24_11.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis25_11.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis25_12.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis30_11.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis30_12.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis30_13.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis31_11.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis31_12.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis31_13.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis31_14.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis32_11.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis32_12.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis32_13.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis32_14.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis32_15.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis33_11.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis33_12.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis33_13.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis33_14.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis33_15.x86_64 : Geographic Information Systems Extensions to PostgreSQL


# 安装
[root@wy ~]# yum install postgis33_14-client.x86_64  postgis33_14-devel.x86_64 postgis33_14-docs.x86_64 postgis33_14-gui.x86_64 postgis33_14-utils.x86_64

6 pgsql添加postgis拓展

postgres=# CREATE EXTENSION postgis;

postgres=# select name from pg_available_extensions;
postgres=# select * from pg_extension;

7 postgis使用例子

# 二维坐标点组成的多边形 0 0, 60 50，220 50, 220 -40, 50 -40, 0 0 
# 判断一个点是否落在多边形内，不包含边界上的点
SELECT  ST_Contains(
    ST_MakePolygon(
        ST_GeomFromText('LINESTRING ( 0 0,60 50,220 50,220 -40,50 -40,0 0 )')
        ) ,st_point(60,49)
    );

# 判断一个点是否落在多边形内，包含边界上的点
SELECT  ST_Covers(
    ST_MakePolygon(
        ST_GeomFromText('LINESTRING ( 0 0,60 50,220 50,220 -40,50 -40,0 0 )')
        ) ,st_point(60,50)
    );

SELECT  ST_GeomFromText('LINESTRING ( 0 0,60 50,220 50,220 -40,50 -40 ,0 0)');

Text(‘LINESTRING ( 0 0,60 50,220 50,220 -40,50 -40,0 0 )’)
) ,st_point(60,49)
);