postgresql11.2 postgis2.5部署 -m6米乐安卓版下载

软件版本：

postgresql-11.2
gdal-2.2.3
proj-4.8.0
geos-3.5.0
json-c-0.13.1
protobuf-2.6.1
protobuf-c-1.2.1
postgis-2.5.0

用户配置

创建安装用户
groupadd dba -g 1000 
useradd postgres -g 1000 -u 1000

上传对应iso镜像，构建本地yum源

[root@ysl ~]# ls
centos-7-x86_64-everything-2009.iso  rpmbuild  temp
[root@ysl ~]# mkdir /mnt/cdrom
[root@ysl ~]# mount -o loop /root/centos-7-x86_64-everything-2009.iso /mnt/cdrom
[root@ysl ~]# cd /mnt/cdrom/
[root@ysl cdrom]# ls
centos_buildtag  gpl       liveos    rpm-gpg-key-centos-7
efi              images    packages  rpm-gpg-key-centos-testing-7
eula             isolinux  repodata  trans.tbl
#备份原来repo，并创建新的repo
[root@ysl ~]# cd /etc/yum.repos.d/
[root@ysl yum.repos.d]# mv centos-base.repo centos-base.repo_bak
[root@ysl yum.repos.d]# mv epel.repo epel.repo_bak
[root@ysl yum.repos.d]#vim myiso.repo
[root@ysl yum.repos.d]# cat myiso.repo
[base]
name=centos7ios
baseurl=file:///mnt/cdrom
enabled=1
gpgcheck=0
gpgkey=file:///mnt/cdrom/rpm-gpg-key-centos-7
[root@ysl ~]# yum clean all
[root@ysl ~]# yum list

安装依赖

yum install -y  openssl openssl-devel readline readline-devel zlib-devel gcc gcc-c   libxml2 libxml2-devel flex automake libtool

切换到postgres用户

su - postgres

获取并解压包

wget https://ftp.postgresql.org/pub/source/v11.2/postgresql-11.2.tar.gz  
tar -xf postgresql-11.2.tar.gz  

进入目录

cd postgresql-11.2

编译环境检查

./configure --prefix=/home/postgres/soft --with-openssl --with-pgport=6000
(最后出现makefile)

编译安装

make world -j24
make install-world -j24

初始化数据库数据目录到/home/postgres/data

/home/postgres/soft/bin/initdb -d /home/postgres/data -e utf8

启动数据库

/home/postgres/soft/bin/pg_ctl start -d /home/postgres/data -l /tmp/logfile

配置环境变量.bashrc

vi .bashrc
export pgport=6000
export pguser=postgres
export pghome=/home/postgres/soft
export pgdata=/home/postgres/data
export path=${pghome}/bin:${path}
ld_library_path=$pghome/lib:/usr/local/lib:/usr/local/lib64:/usr/lib64:$ld_library_path

–读取环境变量

source .bashrc

数据库配置

su - postgres

mkdir -p /home/postgres/archive/wal

cluster_name='enmo_6001'
listen_addresses='0.0.0.0'
port=6000
wal_log_hints=on
logging_collector=on
logging_collector=on
log_filename='pg_log_%u.log'
log_file_mode=0600
log_truncate_on_rotation=on
log_rotation_age=1d
wal_keep_segments=100
archive_mode = on
archive_command = 'cp %p /home/postgres/archive/wal/%f'
wal_level = 'logical'
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
client_encoding='utf8'
lc_messages = 'en_us.utf-8'
lc_monetary = 'en_us.utf-8'
lc_numeric = 'en_us.utf-8'
lc_time = 'en_us.utf-8

修改参数，其余参数根据环境进行调整。

重启数据库生效

pg_ctl restart -d $pgdata -l /tmp/logfile

mkdir  /home/postgres/postgis
cd /home/postgres/postgis

1.安装gdal

 wget http://download.osgeo.org/gdal/2.2.3/gdal-2.2.3.tar.gz
 tar xf gdal-2.2.3.tar.gz 
 cd gdal-2.2.3 
 ./configure 
 make -j 24
 make install -j 24

2.安装proj

 wget http://download.osgeo.org/proj/proj-4.8.0.tar.gz
 tar xf proj-4.8.0.tar.gz
 cd  proj-4.8.0
  ./configure 
 export pkg_config_path=/usr/local/lib/pkgconfig
 export ld_library_path=/usr/local/lib
 make -j 24
 make install -j 24

3.安装geos

 wget http://download.osgeo.org/geos/geos-3.5.0.tar.bz2
 tar jxvf geos-3.5.0.tar.bz2
  cd geos-3.5.0
 ./autogen.sh
 ./configure
 make -j 24
 make install -j 24

4.安装json-c

 wget https://s3.amazonaws.com/json-c_releases/releases/json-c-0.13.1.tar.gz
 tar -xf json-c-0.13.1.tar.gz
 ./autogen.sh
 ./configure
 make -j 24
 make install -j 24

5.安装protobuf-c

wget https://github.com/google/protobuf/releases/download/v2.6.1/protobuf-2.6.1.tar.gz
tar xvf protobuf-2.6.1.tar.gz
cd protobuf-2.6.1
./configure                                                              
make -j 24                                                                    
make check                                                               
make install -j 24
wget https://github.com/protobuf-c/protobuf-c/releases/download/v1.2.1/protobuf-c-1.2.1.tar.gz
tar xvf protobuf-c-1.2.1.tar.gz
cd protobuf-c-1.2.1
export pkg_config_path=/usr/local/lib/pkgconfig  // 指定protobuf.pc文件所在
./configure
make -j 24
make install -j 24

6.配置ldconfig

将数据库软件、gdal、proj、geos的lib目录添加到ldconfig。

cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
/usr/local/gdal/lib/
/usr/local/proj/lib/
/usr/local/geos/lib/
/home/postgres/soft/lib/
/usr/local/lib
#保存退出
ldconfig 

7.安装postgis(./configure对应目录需校对)

 wget http://download.osgeo.org/postgis/source/postgis-2.5.0.tar.gz
 tar -xf postgis-2.5.0.tar.gz
 cd postgis-2.5.0/
 ./configure -prefix=/usr/local/postgis --with-geosconfig=/usr/local/bin/geos-config --with-projdir=/usr/local/proj --with-gdalconfig=/usr/local/bin/gdal-config  --with-pgconfig=/home/postgres/soft/bin/pg_config 
make -j 24
make install -j 24

8.检查postgis组件是否安装

ls /home/postgres/soft/share/extension/postgis*

9.使用超级用户创建扩展

[postgres@ysl ~]$psql -upostgres postgres
create extension postgis;
create extension postgis_topology;
create extension fuzzystrmatch;
create extension postgis_tiger_geocoder;

  postgres=# select postgis_full_version();

postgres=# select st_setsrid(st_point(-87.71,43.741),4326),st_geomfromtext('point(-87.71 43.741)',4326);

1.模板及相关的函数，类型，操作符创建

# 创建无空间特性数据库
[postgres@ysl ~]$ createdb template_postgis
# 创建相关空间数据库相关的函数，类型，操作符等
[postgres@ysl ~]$ psql -f /home/postgres/soft/share/contrib/postgis-2.5/postgis.sql -d template_postgis
[postgres@ysl ~]$ psql -f /home/postgres/soft/share/contrib/postgis-2.5/rtpostgis.sql -d template_postgis
# 验证空间数据库版本
[postgres@ysl ~]$ psql template_postgis
psql (11.2)
type "help" for help.
template_postgis=# select postgis_full_version();
                                                                               postgis_full_v
ersion
---------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
 postgis="2.5.0 r16836" pgsql="110" geos="3.5.0-capi-1.9.0 r4084" proj="rel. 4.8.0, 6 march 2
012" gdal="gdal 2.2.3, released 2017/11/20 gdal_data not found" libxml="2.9.1" raster
(1 row)
template_postgis=# \d
               list of relations
 schema |       name        | type  |  owner
-------- ------------------- ------- ----------
 public | geography_columns | view  | postgres
 public | geometry_columns  | view  | postgres
 public | raster_columns    | view  | postgres
 public | raster_overviews  | view  | postgres
 public | spatial_ref_sys   | table | postgres
(5 rows)

2.根据空间数据库模板创建新的空间数据库

[postgres@ysl ~]$ createdb -t template_postgis new_database
template_postgis=# \l
                                     list of databases
       name       |  owner   | encoding |   collate   |    ctype    |   access privileges
------------------ ---------- ---------- ------------- ------------- -----------------------
 new_database     | postgres | utf8     | en_us.utf-8 | en_us.utf-8 |
 postgres         | postgres | utf8     | en_us.utf-8 | en_us.utf-8 |
 template0        | postgres | utf8     | en_us.utf-8 | en_us.utf-8 | =c/postgres           
                  |          |          |             |             | postgres=ctc/postgres
 template1        | postgres | utf8     | en_us.utf-8 | en_us.utf-8 | =c/postgres           
                  |          |          |             |             | postgres=ctc/postgres
 template_postgis | postgres | utf8     | en_us.utf-8 | en_us.utf-8 |
(5 rows)

3.测试

测试点(0, 0)是否在指定的多边形内。

new_database=# select st_within(st_geomfromtext('point(0 0)', 4326), st_geomfromtext('polygon((1 1, 1 -1, -1 -1, -1 1, 1 1))', 4326)) ;
 st_within
-----------
 t
(1 row)