1、centos 7 安装postgresql9.6/10和Postgis

–切换到root用户或者直接再root用户下操作

suroot

–卸载旧版

yum remove postgresql*

–安装打开https://yum.postgresql.org/repopackages.php ，找到自己需要的版本右键复制链接。

yuminstall https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

或者yum installhttps://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

 

–检查可用的安装包

yumlist post*   或者yum list postgresql*  

 

–安装PostgreSql

yuminstall postgresql96.x86_64 postgresql96-server.x86_64 postgresql96-libs.x86_64postgresql96-contrib.x86_64 postgresql96-devel.x86_64

或者

yuminstall postgresql10.x86_64 postgresql10-server.x86_64 postgresql10-libs.x86_64postgresql10-contrib.x86_64  postgresql10-devel.x86_64

 

–安装Postgis

yum install postgis2_96.x86_64

或者

yum install postgis24_10.x86_64

 

–如果安装报错，需安装EPEL的repository 之后在进行安装

yum install https://mirrors.aliyun.com/epel/epel-release-latest-7.noarch.rpm

 

–安装pgRouting  路径分析（功能使用可参考http://blog.csdn.net/u014529917/article/details/72866436）

yum install pgrouting_96

或

yum install pgrouting_10

 

–初始化数据库CentOS7系统

/usr/pgsql-9.6/bin/postgresql96-setupinitdb

或

/usr/pgsql-10/bin/postgresql-10-setupinitdb

 

–启动服务并设置为开机启动

CentOS7 系统

 

systemctlstart postgresql-9.6 #启动服务 或systemctl start postgresql-10

systemctlrestart postgresql-9.6 #重启服务

systemctlstop postgresql-9.6 #停止服务

systemctlenable postgresql-9.6 #开机自动启动 或systemctl enablepostgresql-10

 

–查看数据库状态：systemctl status postgresql-9.6

 

–开放防火墙端口

firewall-cmd–permanent –add-port=5432/tcp

firewall-cmd–permanent –add-port=80/tcp

firewall-cmd–reload

 

–安装pgadmin4

yum install pgadmin3 或yum install pgadmin4-v2

 

注意：pgadmin4安装特别说明：

–第一次启动pgadmin4需要输入邮箱和密码

–启动服务 

/usr/pgadmin4-v2/bin/pgadmin4-v2-web-setup.sh

–接着需要输入邮箱和密码

— Email address:zcclove@126.com

— Password:88888888

— Retypepassword:

— 接着按提示操作即可

— 接着你就可约打开pgadmin4 了如果还是无法打开，那就再启动下服务吧

 

–相关配置修改

可通过pgadmin配置修改认证文件/var/lib/pgsql/data/pg_hba.conf登陆使用密码，把这个配置文件中的认证 METHOD的ident修改为trust，可以实现用账户和密码来访问数据库

可通过pgadmin配置修改认证文件/var/lib/pgsql/data/postgresql.conf修改listen_addresses = ‘localhost’为listen_addresses= ‘*’，允许所有远程访问；

 

–修改完之后重启服务

systemctl restart postgresql-9.6

–pgadmin登录后可以创建数据库、修改用户密码等

//////////////////////////////////////////////

 

–检查postgis安装是否正确，连接数据库执行

select * from pg_available_extensions wherename like ‘postgis%’;

–再pgadmin命令行中，安装PostGis扩展

 

CREATE EXTENSION postgis

CREATE EXTENSION postgis_topology

CREATE EXTENSION ogr_fdw

–验证安装结果

SELECT postgis_full_version();

 

–创建表验证1、创建cities 表；2、给cities表中添加空间字段；3、给表插入数据

CREATE TABLE cities(id varchar(20),namevarchar(50));

 

SELECT AddGeometryColumn (‘cities’,’the_geom’, 4326, ‘POINT’, 2);

 

INSERT INTO cities(id, the_geom, name)VALUES (1,ST_GeomFromText(‘POINT(-0.1257 51.508)’,4326),’London, England’);

INSERT INTO cities (id, the_geom, name)VALUES (2,ST_GeomFromText(‘POINT(-81.233 42.983)’,4326),’London, Ontario’);

INSERT INTO cities (id, the_geom, name)VALUES (3,ST_GeomFromText(‘POINT(27.91162491 -33.01529)’,4326),’EastLondon,SA’);

 

 

 

PostGis的扩展列表

— Enable PostGIS (includes raster)

CREATE EXTENSION postgis;

— Enable Topology

CREATE EXTENSION postgis_topology;

— Enable PostGIS Advanced 3D

— and other geoprocessing algorithms

— sfcgal not available with alldistributions

CREATE EXTENSION postgis_sfcgal;

— fuzzy matching needed for Tiger

CREATE EXTENSION fuzzystrmatch;

— rule based standardizer

CREATE EXTENSION address_standardizer;

转载自：https://blog.csdn.net/zcc0618/article/details/79670329