一、安裝PostgreSQL
1、安裝PostgreSQL
解壓PostgreSQL軟體包
tar -zxvf postgresql.tar.gz配置並安裝PostgreSQL
[postgres@localhost setup]$ tar -zxvf postgresql-10.23.tar.gz
進入解壓後的目錄，按照PostgreSQL的官方文件進行配置和安裝。這通常涉及到建立資料目錄、配置postgresql.conf和pg_hba.conf等檔案。
[postgres@localhost setup]$ cd postgresql-10.23
[postgres@localhost postgresql-10.23]$ ./configure --prefix=/home/postgres/postgresql --with-libxml
[postgres@localhost postgresql-10.23]$ make
[postgres@localhost postgresql-10.23]$ make install
設定環境變數
[postgres@localhost postgresql-10.23]$ vim ~/.bash_profile
export PGDATA=/home/postgres/postgresql/data
export PGHOME=/home/postgres/postgresql
export PATH=$PGHOME/bin:$PATH
[postgres@localhost postgresql-10.23]$ source ~/.bash_profile
初始化資料庫
使用initdb命令初始化資料庫目錄。
[postgres@localhost bin]$ pwd
/home/postgres/postgresql/bin
[postgres@localhost bin]$ initdb -D /home/postgres/postgresql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /home/postgres/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Shanghai
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /home/postgres/postgresql/data -l logfile start
[postgres@localhost bin]$
2、配置遠端登入
1.編輯 pg_hba.conf 檔案
[postgres@localhost postgresql-12.2]$ vi /home/postgres/postgresql/data/pg_hba.conf
#zkm 2024-12-03
host all all 0.0.0.0/0 md5
將 host all all 0.0.0.0/0 md5 新增到檔案中，代表所有的使用者透過任意 ip 都可以透過md5（密碼）的方式登陸PostgreSQL。
如下圖所示：
host all all 0.0.0.0/0 md5
2.編輯 postgresql.conf 檔案
[postgres@localhost postgresql-12.2]$ vi /home/postgres/postgresql/data/postgresql.conf
[postgres@localhost postgresql-12.2]$ cat /home/postgres/postgresql/data/postgresql.conf|grep listen_addresses
#listen_addresses = 'localhost' # what IP address(es) to listen on;
[postgres@localhost postgresql-12.2]$ vi /home/postgres/postgresql/data/postgresql.conf
[postgres@localhost postgresql-12.2]$ cat /home/postgres/postgresql/data/postgresql.conf|grep listen_addresses
listen_addresses = '*' # what IP address(es) to listen on;
[postgres@localhost postgresql-12.2]$
增加sudo 許可權
[root@localhost yum.repos.d]# whereis sudoers
sudoers: /etc/sudoers
[root@localhost yum.repos.d]# chmod -v u+w /etc/sudoers
mode of '/etc/sudoers' changed from 0440 (r--r-----) to 0640 (rw-r-----)
[root@localhost yum.repos.d]# echo -e "#zkm 2024-11-23\npostgres ALL=(ALL) ALL" >> /etc/sudoers
[root@localhost yum.repos.d]# chmod -v u-w /etc/sudoers
mode of '/etc/sudoers' changed from 0640 (rw-r-----) to 0440 (r--r-----)
[root@localhost yum.repos.d]#
8.配置系統服務
1.建立postgresql.service檔案
[postgres@localhost bin]$ sudo vi /usr/lib/systemd/system/postgresql.service
[sudo] password for postgres:
[postgres@localhost bin]$ sudo cat /usr/lib/systemd/system/postgresql.service | grep -v ^# |grep -v ^$
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=7001
Environment=PGDATA=/home/postgres/postgresql/data
OOMScoreAdjust=-1000
ExecStart=/home/postgres/postgresql/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/home/postgres/postgresql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/home/postgres/postgresql/bin/pg_ctl reload -D ${PGDATA} -s
TimeoutSec=300
[Install]
WantedBy=multi-user.target
[postgres@localhost bin]$
2.控制命令
sudo systemctl daemon-reload # 啟用服務控制守護
sudo systemctl start postgresql # 啟動
sudo systemctl stop postgresql # 停止
sudo systemctl restart postgresql # 重啟
sudo systemctl enable postgresql # 開機自啟
sudo systemctl status postgresql # 檢視狀態
9.設定資料庫使用者密碼
# 直接用postgres超級使用者登入，預設不需要密碼，psql直接回車就以postgres使用者進入了postgres資料庫
[postgres@localhost tmp]$ psql -p 7001 -U postgres
psql (12.0, server 12.2)
Type "help" for help.
postgres=#
# 修改超級使用者密碼為：Postgres!2024
postgres=# ALTER USER postgres with encrypted password 'xxxxxx';
ALTER ROLE
postgres=#
\q
[postgres@localhost bin]$
10.遠端連線測試
透過 Navicat 連線訪問
192.168.0.3 7001
postgres
xxxxxx
成功連線如下
執行psql，預設進入postgres使用者的postgres資料庫
使用\du檢視使用者
使用\l檢視使用者的資料庫列表（以下庫為PostgreSQL預設裝上，不建議刪除）
使用\c檢視路徑
使用\d檢視資料表，沒有資料表顯示為（沒有找到任何關係）
利用`\h`顯示命令的幫助，如`\h create database`，顯示建立資料庫命令的引數幫助
二、postgis安裝
Postgis 安裝依賴
Proj4 （ 4.9.0以上版本） 4.9.1
GEOS （ 3.5 以上版本） 3.8.0
LibXML2 （2.5以上版本）2.9.10
JSON-C （0.9以上版本）0.13.1
Gdal （1.8以上版本）2.2.3
[postgres@localhost setup]$ cd proj-4.9.1
[postgres@localhost proj-4.9.1]$ ./configure -prefix=/home/postgres/postgresql/plugin/proj
[postgres@localhost proj-4.9.1]$ make
[postgres@localhost proj-4.9.1]$ make install
GEOS （ 3.5 以上版本） 3.8.0
[postgres@localhost setup]$ tar -zxvf libxml2-2.9.10.tar.gz
[postgres@localhost setup]$ cd libxml2-2.9.10
[postgres@localhost libxml2-2.9.10]$ ./configure -prefix=/home/postgres/postgresql/plugin/libxml2
[postgres@localhost libxml2-2.9.10]$ make
[postgres@localhost libxml2-2.9.10]$ make install
JSON-C （0.9以上版本）0.13.1
[postgres@localhost setup]$ tar -zxvf json-c-0.13.1.tar.gz
[postgres@localhost setup]$ cd json-c-0.13.1
[postgres@localhost json-c-0.13.1]$ ./configure -prefix=/home/postgres/postgresql/plugin/json-c
[postgres@localhost json-c-0.13.1]$ make
[postgres@localhost json-c-0.13.1]$ make install
[postgres@localhost setup]$ tar -zxvf gdal-2.2.3.tar.gz
[postgres@localhost setup]$ cd gdal-2.2.3
[postgres@localhost gdal-2.2.3]$ ./configure -prefix=/home/postgres/postgresql/plugin/gdal --with-proj=/home/postgres/postgresql/plugin/proj --with-libjson-c=/home/postgres/postgresql/plugin/json-c --with-geos=/home/postgres/postgresql/plugin/geos/bin/geos-config
[postgres@localhost gdal-2.2.3]$ make
[postgres@localhost gdal-2.2.3]$ make install
gdal 錯誤資訊1：
解決方案：複製json-c原始碼檔案到安裝目錄下
[postgres@localhost setup]$ tar -zxvf postgis-2.5.0.tar.gz
[postgres@localhost setup]$ cd postgis-2.5.0
[postgres@localhost postgis-2.5.0]$ ./configure -prefix=/home/postgres/postgresql/plugin/postgis --with-pgconfig=/home/postgres/postgresql/bin/pg_config --with-libjson-c=/home/postgres/postgresql/plugin/json-c --with-geosconfig=/home/postgres/postgresql/plugin/geos/bin/geos-config --with-projdir=/home/postgres/postgresql/plugin/proj --with-gdalconfig=/home/postgres/postgresql/plugin/gdal/bin/gdal-config
[postgres@localhost postgis-2.5.0]$ make
cat address_standardizer.control.in \
| sed -e 's|@EXTVERSION@|2.5.0|g' \
> address_standardizer.control
cat address_standardizer_data_us.control.in \
| sed -e 's|@EXTVERSION@|2.5.0|g' \
> address_standardizer_data_us.control
make[2]: Leaving directory '/home/postgres/setup/postgis-2.5.0/extensions/address_standardizer'
make[1]: Leaving directory '/home/postgres/setup/postgis-2.5.0/extensions'
PostGIS was built successfully. Ready to install.
[postgres@localhost postgis-2.5.0]$
[postgres@localhost postgis-2.5.0]$ make install
/usr/bin/install -c -m 644 .//postgis_topology.control '/home/postgres/postgresql/share/extension/'
/usr/bin/install -c -m 644 postgis_topology.control sql/postgis_topology--2.5.0.sql sql/postgis_topology--unpackaged--2.5.0.sql '/home/postgres/postgresql/share/extension/'
make[2]: Leaving directory '/home/postgres/setup/postgis-2.5.0/extensions/postgis_topology'
---- Making install in address_standardizer
make[2]: Entering directory '/home/postgres/setup/postgis-2.5.0/extensions/address_standardizer'
/usr/bin/mkdir -p '/home/postgres/postgresql/lib'
/usr/bin/mkdir -p '/home/postgres/postgresql/share/extension'
/usr/bin/mkdir -p '/home/postgres/postgresql/share/extension'
/usr/bin/mkdir -p '/home/postgres/postgresql/share/doc/extension'
/usr/bin/install -c -m 755 address_standardizer.so '/home/postgres/postgresql/lib/address_standardizer.so'
/usr/bin/install -c -m 644 .//address_standardizer.control '/home/postgres/postgresql/share/extension/'
/usr/bin/install -c -m 644 .//sql/address_standardizer.sql .//sql/address_standardizer_data_us.sql .//sql/address_standardizer--1.0--2.5.0.sql .//sql/address_standardizer--2.5.0--2.5.0next.sql .//sql/address_standardizer--2.5.0next--2.5.0.sql .//sql/address_standardizer--2.5.0.sql .//sql/address_standardizer_data_us--2.5.0--2.5.0next.sql .//sql/address_standardizer_data_us--2.5.0next--2.5.0.sql .//sql/address_standardizer_data_us--2.5.0.sql address_standardizer.control address_standardizer_data_us.control '/home/postgres/postgresql/share/extension/'
/usr/bin/install -c -m 644 .//README.address_standardizer '/home/postgres/postgresql/share/doc/extension/'
make[2]: Leaving directory '/home/postgres/setup/postgis-2.5.0/extensions/address_standardizer'
make[1]: Leaving directory '/home/postgres/setup/postgis-2.5.0/extensions'
[postgres@localhost postgis-2.5.0]$
8.檢查postgis元件是否安裝
ls /home/postgres/postgresql/share/extension/postgis*
9.使用超級使用者建立擴充套件,修改超級使用者密碼為：Postgres!2024
[postgres@localhost tmp]$ psql -p 7001 -U postgres
psql (12.0, server 12.2)
Type "help" for help.
postgres=# CREATE EXTENSION POSTGIS;
CREATE EXTENSION POSTGIS_TOPOLOGY;
CREATE EXTENSION FUZZYSTRMATCH;
CREATE EXTENSION POSTGIS_TIGER_GEOCODER;
檢查postgis安裝是否正確，連線資料庫執行,用sql語句查詢是否啟用成功：
SELECT * FROM PG_AVAILABLE_EXTENSIONS WHERE NAME LIKE 'postgis%'
用sql語句查詢版本號：
SELECT POSTGIS_VERSION();
SELECT ST_SETSRID(ST_POINT(-108,30.741),4326);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT('POINT(-106.51 29.741)',4326))
錯誤資訊2：
postgres-# create extension postgis;
ERROR: syntax error at or near "create"
LINE 2: create extension postgis;
^
postgres=# CREATE EXTENSION POSTGIS;
ERROR: could not load library "/home/postgres/postgresql/lib/rtpostgis-2.5.so": libgdal.so.20: cannot open shared object file: No such file or directory
postgres=# \q
解決方案：
cp /home/postgres/postgresql/plugin/gdal/lib/libgdal.so.20 /home/postgres/postgresql/lib
[postgres@localhost postgis-2.5.0]$ sudo systemctl restart postgresql
[sudo] password for postgres:
[postgres@localhost postgis-2.5.0]$ psql -p 7001 -U postgres
psql (10.23)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
大小寫區別
postgres=# CREATE EXTENSION POSTGIS;
CREATE EXTENSION
postgres=#