使用yum安装部署postgresql13+postgis3.2
环境:
OS:Centos 7
PGDB:13.8
postgis:3.2
1.创建相应的用户
[root@localhost opt]# groupadd postgres
[root@localhost opt]# useradd -g postgres postgres
[root@localhost opt]# passwd postgres
该步骤可以不用创建用户,yum安装过程会创建该用户,这里方便管理我手工创建用户
2.关闭防火墙
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# systemctl disable firewalld.service
vi /etc/selinux/config
修改如下项目:
SELINUX=disabled
2.安装yum镜像
[root@localhost yum.repos.d]#yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[root@localhost yum.repos.d]# ls -lrt
total 52
-rw-r--r--. 1 root root 616 Oct 23 2020 CentOS-x86_64-kernel.repo
-rw-r--r--. 1 root root 8515 Oct 23 2020 CentOS-Vault.repo
-rw-r--r--. 1 root root 1331 Oct 23 2020 CentOS-Sources.repo
-rw-r--r--. 1 root root 314 Oct 23 2020 CentOS-fasttrack.repo
-rw-r--r--. 1 root root 649 Oct 23 2020 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root 1309 Oct 23 2020 CentOS-CR.repo
-rw-r--r--. 1 root root 1664 Oct 23 2020 CentOS-Base.repo
-rw-r--r--. 1 root root 11855 Oct 16 03:00 pgdg-redhat-all.repo
-rw-r--r--. 1 root root 558 Oct 28 23:26 CentOS-Media.repo
/etc/yum.repos.d目录会多出一个pgdg-redhat-all.repo文件
3. yum list查看postgresql13相应的安装包
[root@localhost yum.repos.d]# yum list postgresql13*
Available Packages
postgresql13.x86_64 13.8-1PGDG.rhel7 pgdg13
postgresql13-contrib.x86_64 13.8-1PGDG.rhel7 pgdg13
postgresql13-devel.x86_64 13.8-1PGDG.rhel7 pgdg13
postgresql13-docs.x86_64 13.8-1PGDG.rhel7 pgdg13
postgresql13-libs.x86_64 13.8-1PGDG.rhel7 pgdg13
postgresql13-llvmjit.x86_64 13.8-1PGDG.rhel7 pgdg13
postgresql13-odbc.x86_64 13.02.0000-1PGDG.rhel7 pgdg13
postgresql13-plperl.x86_64 13.8-1PGDG.rhel7 pgdg13
postgresql13-plpython3.x86_64 13.8-1PGDG.rhel7 pgdg13
postgresql13-pltcl.x86_64 13.8-1PGDG.rhel7 pgdg13
postgresql13-server.x86_64 13.8-1PGDG.rhel7 pgdg13
postgresql13-test.x86_64 13.8-1PGDG.rhel7
4.安装postgresql13
[root@localhost usr]# yum install postgresql13-server
[root@localhost usr]# yum install postgresql13-contrib
默认postgreql 安装在/usr/pgsql-13
数据存储目录:/var/lib/pgsql/版本号/data,
在实际生产中/var可能存在硬盘空间不足的问题,我们一般将数据存储目录放在挂载的硬盘如/data下
5.创建数据存储目录
[root@localhost bin]#mkdir -p /opt/pg13/data
[root@localhost bin]#mkdir -p /opt/pg13/log
[root@localhost bin]#mkdir -p /opt/pg13/archivelog
[root@localhost bin]#chown -R postgres:postgres /opt/pg13
[root@localhost bin]#chmod 0700 /opt/pg13/data
6.初始化数据库
su - postgres
/usr/pgsql-13/bin/initdb -D /opt/pg13/data
7.修改启动参数
su - root
[root@localhost postgresql-13.service.d]# ls -al /usr/lib/systemd/system/postgresql-13.service
-rw-r--r--. 1 root root 1764 Aug 10 06:06 /usr/lib/systemd/system/postgresql-13.service
vi /usr/lib/systemd/system/postgresql-13.service
修改为Environment=PGDATA=/opt/pg13/data/
8.修改配置参数postgresql.conf
su - postgres
vi /opt/pg13/data/postgresql.conf
修改的内容如下:
listen_addresses='*'
port=5432
max_connections=1000
unix_socket_directories = '/tmp'
####内存相关#########
work_mem=10MB
maintenance_work_mem=2GB
max_locks_per_transaction=1024
max_wal_size=64GB
checkpoint_timeout=30min
checkpoint_completion_target=0.9
shared_buffers=RAM*0.25GB #RAM指内存,最大设置为64GB
effective_cache_size=RAM*0.5GB #RAM指内存(free -g)
##归档部分
wal_level=replica
archive_mode=on
archive_command = 'DATE=`date +%Y%m%d`;DIR="/opt/pg13/archivelog/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f'
##archive_command = 'cp %p /opt/pg13/archivelog/%f' ##pa_wal目录的归档日志会拷贝到该目录
######慢查询部分#####################
logging_collector=on
log_directory = '/opt/pg13/log' ##指定具体目录,否则会默认在pgdata目录下创建log目录
log_destination='stderr'
log_min_duration_statement=1000 ##超过1秒的慢查询都会记录
log_filename = 'postgresql-%Y-%m-%d.log'
log_truncate_on_rotation = off ##是否覆盖
log_rotation_age = 1d ##每天生成
log_rotation_size = 10MB ##每个日志大小
#log_statement = all 和 log_min_duration_statement = 5000
#根据需要两者设置其一即可
#如果两个都进行了配置默认所有SQL全部打印，log_min_duration_statement设置失效
##log_statement = all #需设置跟踪所有语句，否则只能跟踪出错信息
log_line_prefix='%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h'
wal_log_hints=on
full_page_writes=on
##时区
log_timezone = 'Asia/Shanghai'
timezone = 'Asia/Shanghai'
wal_keep_size = 1024MB ##为standby从库保留的wal日志大小 wal_keep_size = wal_keep_segments * wal_segment_size
9.修改配置参数pg_hba.conf
su - postgres
vi /opt/pg13/data/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
10.设置postgres用户环境变量
su - postgres
[postgres@localhost ~]$ more .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PGHOME=/usr/pgsql-13
PGDATA=/opt/pg13/data
PATH=$PGHOME/bin:$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export PGHOST=/tmp ##因为参数unix_socket_directories = '/tmp' 设置在tmp目录
11.启动数据库
[root@localhost pgsql-13]#systemctl daemon-reload
[root@localhost pgsql-13]#systemctl start postgresql-13
[root@localhost pgsql-13]#systemctl enable postgresql-13
10.登录并修改postgres账号的密码
[postgres@localhost ~]$ psql -h localhost -U postgres -p5432
psql (13.8)
Type "help" for help.
postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
ALTER ROLE
客户端连接需要关闭服务器防火墙
[root@localhost log]# systemctl stop firewalld.service
12.检查常用的扩展是否已经安装
[postgres@localhost ~]$ psql -h localhost -U postgres -p5432
psql (13.8)
Type "help" for help.
postgres=# create extension postgres_fdw;
CREATE EXTENSION
postgres=# create extension dblink;
CREATE EXTENSION
说明这两个默认扩展是已经安装了的.
13.尝试使用命令行启动关闭数据库
yum安装的默认启动和关闭数据库可以使用systemctl操作的,下面我们尝试使用命令的方式启动和关闭
[root@localhost log]# systemctl stop postgresql-13
方法1:
su - postgres
[postgres@localhost ~]$ pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start
[postgres@localhost ~]$ pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop
方法2:
su - postgres
后台启动
/usr/pgsql-13/bin/postmaster -D /opt/pg13/data/ &
关闭
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop
################################安装postgis扩展#########################
1.安装相关依赖yum
[root@localhost]# yum install epel-release
2.查看postgis个版本
yum list postgis*
3.我们这里安装32版本
这里可以查看pg版本可以安装那个postgis的版本对应关系
https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS
我这里选择安装32-13版本,32代表的是postgis版本,13代表的是pg版本
yum install postgis32_13.x86_64
安装过程可以看到安装很多的依赖包:
Install 1 Package (+81 Dependent packages)
4.查看安装版本信息
[root@localhost yum.repos.d]# rpm -qi postgis32_13
Name : postgis32_13
Version : 3.2.3
Release : 1.rhel7
Architecture: x86_64
Install Date: Mon 31 Oct 2022 03:01:36 AM EDT
Group : Unspecified
Size : 36912353
License : GPLv2+
Signature : DSA/SHA1, Mon 22 Aug 2022 03:50:51 AM EDT, Key ID 1f16d2e1442df0f8
Source RPM : postgis32_13-3.2.3-1.rhel7.src.rpm
Build Date : Mon 22 Aug 2022 03:50:46 AM EDT
Build Host : koji-centos7-x86-64-pgbuild
Relocations : (not relocatable)
Vendor : PostgreSQL Global Development Group
URL : https://www.postgis.net/
Summary : Geographic Information Systems Extensions to PostgreSQL
Description :
PostGIS adds support for geographic objects to the PostgreSQL object-relational
database. In effect, PostGIS "spatially enables" the PostgreSQL server,
allowing it to be used as a backend spatial database for geographic information
systems (GIS), much like ESRI's SDE or Oracle's Spatial extension. PostGIS
follows the OpenGIS "Simple Features Specification for SQL" and has been
certified as compliant with the "Types and Functions" profile.
5.安装扩展
su - postgres
[postgres@localhost ~]$ psql -h localhost -U postgres -p5432
psql (13.8)
Type "help" for help
postgres=# create extension postgis;
CREATE EXTENSION
postgres=# create extension postgis_raster;
CREATE EXTENSION
postgres=# create extension postgis_topology;
CREATE EXTENSION
postgres=# create extension postgis_sfcgal;
CREATE EXTENSION
postgres=# create extension fuzzystrmatch;
CREATE EXTENSION
postgres=# create extension address_standardizer;
CREATE EXTENSION
postgres=# create extension address_standardizer_data_us;
CREATE EXTENSION
postgres=# create extension postgis_tiger_geocoder;
CREATE EXTENSION
6.验证是否安装成功
##查看版本,验证安装是否成功
postgres=# SELECT PostGIS_full_version();
postgis_full_version
---------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------
POSTGIS="3.2.3 2f97b6c" [EXTENSION] PGSQL="130" GEOS="3.10.3-CAPI-1.16.1" SFCGAL="1.3.1" PROJ="7.2.1" GDAL="GDAL 3.4
.3, released 2022/04/22" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RASTER
(1 row)
7.查看当前数据库安装的组件
postgres=# \dx
List of installed extensions
Name | Version | Schema | Descriptio
n
------------------------------+---------+------------+---------------------------------------------------------------
------------------------------------------------------
address_standardizer | 3.2.3 | public | Used to parse an address into constituent elements. Generally
used to support geocoding address normalization step.
address_standardizer_data_us | 3.2.3 | public | Address Standardizer US dataset example
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
fuzzystrmatch | 1.1 | public | determine similarities and distance between strings
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.2.3 | public | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.2.3 | public | PostGIS raster types and functions
postgis_sfcgal | 3.2.3 | public | PostGIS SFCGAL functions
postgis_tiger_geocoder | 3.2.3 | tiger | PostGIS tiger geocoder and reverse geocoder
postgis_topology | 3.2.3 | topology | PostGIS topology spatial types and functions
postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers
(11 rows)
-- The End --
