1. 簡介
1.1 安裝環境：ContOS6.8, PostGIS9.4, PostpreSQL9.4, QGIS2.17
1.2 說明：
1.2.1 參考自http://www.postgresonline.com/journal/archives/362-An-almost-idiots-guide-to-install-PostgreSQL-9.5,-PostGIS-2.2-and-pgRouting-2.1.0-with-Yum.html#pgRouting
和
http://blog.csdn.net/caoshiying/article/details/51778207
1.2.2 安裝過程中要求安裝依賴時一律是：yes
2. 安裝PostgreSQL
2.1 下載下傳檔案
https://yum.postgresql.org/
2.2 上傳檔案到ContOS（遠端資料庫）
2.3 安裝
1. yum install pgdg-redhat94-9.4-1.noarch.rpm
2. yum install postgresql94-server postgresql94-contrib
2.4 配置
2.4.1 初始化并啟動服務
service postgresql-9.4 initdb
chkconfig postgresql-9.4 on
2.4.2 編輯pg_hba.conf
cd /var/lib/pgsql/9.4/data
vim pg_hba.conf
儲存并退出
1. # TYPE DATABASE USER ADDRESS METHOD
2.
3. # "local" is for Unix domain socket connections only
4. local all all peer
5. # IPv4 local connections:
6. host all all 0.0.0.0/0 MD5
7. # IPv6 local connections:
8. host all all ::1/128 ident
9. # Allow replication connections from localhost, by a user with the
10. # replication privilege.
11. #local replication postgres peer
12. #host replication postgres 127.0.0.1/32 ident
13. #host replication postgres ::1/128 ident
2.4.3 編輯postgresql.conf檔案
vim postgresql.conf
儲存并退出
1. # - Connection Settings -
2.
3. listen_addresses = 'localhost' # what IP address(es) to listen on;
4. # comma-separated list of addresses;
5. # defaults to 'localhost'; use '*' for all
6. # (change requires restart)
7. port = 5432 # (change requires restart)
8. max_connections = 100 # (change requires restart)
2.4.4 編輯環境變量
vim /etc/profile
source /etc/profile
1. HOSTNAME=`/bin/hostname 2>/dev/null`
2. HISTSIZE=1000
3. if [ "$HISTCONTROL" = "ignorespace" ] ; then
4. export HISTCONTROL=ignoreboth
5. else
6. export HISTCONTROL=ignoredups
7. fi
8.
9. export PGHOME=/usr/pgsql-9.4
10. export PGDATA=/var/lib/pgsql/9.4/data
export PATH=$PGHOME/bin:$PATH
3. 安裝PostGIS
首選檢查PostGIS是否存在源清單中：
1. postgres=# \q
2. bash-4.1$ exit
3. exit
4. [[email protected] etc]# yum list | grep -i postgis
5. postgis.x86_64 1.3.6-1.el6.rf rpmforge
6. postgis-utils.x86_64 1.3.6-1.el6.rf rpmforge
7. postgis2_94.x86_64 2.1.8-1.rhel6 pgdg94
8. postgis2_94-client.x86_64 2.1.8-1.rhel6 pgdg94
9. postgis2_94-debuginfo.x86_64 2.1.8-1.rhel6 pgdg94
10. postgis2_94-devel.x86_64 2.1.8-1.rhel6 pgdg94
11. postgis2_94-docs.x86_64 2.1.8-1.rhel6 pgdg94
12. postgis2_94-utils.x86_64 2.1.8-1.rhel6 pgdg94
13. [[email protected] etc]#
嘗試安裝：
1. [[email protected] etc]# yum install postgis2_94
測試建立空間資料庫：
1. -bash-4.1$ psql -c "CREATE DATABASE test01;"
2. CREATE DATABASE
3. -bash-4.1$ psql -d test01 -c "CREATE EXTENSION postgis;"
4. CREATE EXTENSION
5. -bash-4.1$ psql -d test01
6. psql (9.4.8)
可以輸入 "help"來擷取幫助資訊，下面是查詢已安裝的PostGIS的版本
1. test01=# select postgis_full_version();
2. 注意: Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?
3. postgis_
4. full_version
5.
6. --------------------------------------------------------------------------------
7. --------------------------------------------------------------------------------
8. -----
9. POSTGIS="2.1.8 r13780" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March
10. 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6" LIBJSON="UNKNOWN" RA
11. STER
12. (1 行記錄)
13.
14. test01=#
4. 安裝pgRouting二進制檔案
4.1安裝
yum install pgrouting_94
5. 安裝ogrfdw二進制檔案
安裝：
yum install ogr_fdw94
6. 在資料庫中啟用擴充
6.1 修改使用者密碼
1. [[email protected] etc]# cat /etc/passwd | grep -i postgres
2. postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
3. [[email protected] etc]#
如果輸出為空則表示使用者不存在，需要建立使用者。如果已經存在，則需要修改使用者密碼。使用者密碼需要分兩步修改。首先修改系統的postgres使用者的密碼：
1. [[email protected] etc]# passwd postgres
2. Changing password for user postgres.
3. New password:
4. Retype new password:
5. passwd: all authentication tokens updated successfully.
6. [[email protected] etc]#
切換到postgres使用者：
1. [[email protected] etc]# su postgres
2. bash-4.1$
嘗試重新開機或啟動PostgreSQL服務
1. bash-4.1$ pg_ctl restart
2. pg_ctl: PID file "/var/lib/pgsql/9.4/data/postmaster.pid" does not exist
3. Is server running?
4. starting server anyway
5. pg_ctl: could not read file "/var/lib/pgsql/9.4/data/postmaster.opts"
6. bash-4.1$ pg_ctl start
7. server starting
8. bash-4.1$ < 2016-06-28 23:46:04.688 CST >LOG: redirecting log output to logging collector process
9. < 2016-06-28 23:46:04.688 CST >HINT: Future log output will appear in directory "pg_log".
10.
11. bash-4.1$
注意服務啟動成功時指令行沒有傳回使用者的bash，按一下回車即可。
進入psql指令行：
1. bash-4.1$ psql
2. psql (9.4.8)
3. Type "help" for help.
4.
5. postgres=#
修改資料庫的postgres使用者的密碼：
1. alter user postgres with password '最好與系統的使用者密碼相同';
指令中的紅色文字請自行更改為您自己的密碼，然後執行指令，如下所示：
1. postgres=# alter user postgres with password '不告訴你^_^^_^';
2. ALTER ROLE
3. postgres=#
退出測試一下：
1. postgres=# \q
2. bash-4.1$ psql -h 192.168.8.155 -p 5432 -d postgres -U postgres -W
3. Password for user postgres:
4. psql (9.4.8)
5. Type "help" for help.
6.
7. postgres=#
至此可以用工具遠端登入資料庫系統了。
建立一個資料庫并安裝擴充
1. bash-4.1$ pg_ctl restart
2. pg_ctl: PID file "/var/lib/pgsql/9.4/data/postmaster.pid" does not exist
3. Is server running?
4. starting server anyway
5. pg_ctl: could not read file "/var/lib/pgsql/9.4/data/postmaster.opts"
6. bash-4.1$ pg_ctl start
7. server starting
8. bash-4.1$ < 2016-06-28 23:46:04.688 CST >LOG: redirecting log output to logging collector process
9. < 2016-06-28 23:46:04.688 CST >HINT: Future log output will appear in directory "pg_log".
10.
11. bash-4.1$
注意服務啟動成功時指令行沒有傳回使用者的bash，按一下回車即可。
進入psql指令行：
1. su postgres
2. /usr/pgsql-9.4/bin/psql -p 5432.
3. CREATE DATABASE gistest;
4. \connect gistest;
接下來安裝擴充
5. CREATE EXTENSION postgis;
6. CREATE EXTENSION postgis_topology;
7. CREATE EXTENSION ogr_fdw;
7. 導入ShapeFile檔案
PostGIS連接配接資料庫
導入資料
查詢導入資料