1、安装EPEL
http://fedoraproject.org/wiki/EPEL
wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm
2、安装PostgreSQL yum
https://yum.postgresql.org/repopackages.php#pg96
wget https://download.postgresql.org/pub/repos/yum/testing/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
rpm -ivh pgdg-centos10-10-2.noarch.rpm
yum search all postgresql -v
yum search all postgis -v
安装数据库软件
数据库软件
yum install -y postgresql10*
PostGIS空间数据库插件
yum install -y postgis24_10*
道路路由插件
yum install -y pgrouting_10*
可选:
openstreetmap导入pgrouting的工具
yum install -y osm2pgrouting_10*
plpgsql函数调试工具,支持pgadmin调试PLPGSQL函数
yum install -y plpgsql_check_10*
PostgreSQL 图形化监控软件
yum install -y powa_10*
PostgreSQL log分析生成HTML的软件
yum install -y pgbadger
PostgreSQL TOP监控工具
yum install -y pg_top10*
PostgreSQL 虚拟索引插件
yum install -y hypopg_10*
PostgreSQL 分布式插件
yum install -y citus_10*
PostgreSQL 列存储插件
yum install -y cstore_fdw_10*
PostgreSQL pg_pathman高效分区插件
yum install -y pg_pathman10*
PostgreSQL orafce Oracle兼容包
yum install -y orafce10*
PostgreSQL linux cache管理插件
yum install -y pgfincore10*
PostgreSQL BSON类型、memcache接口、plpgsql调试函数、plV8存储过程语言、DML审计日志、ip地址转经纬度、IP地址范围GiST索引接口、数据空间回收接口、R-Tree空间类型、plR存储过程语言
yum install -y pgbson10*
yum install -y pgmemcache-10*
yum install -y pldebugger10*
yum install -y plv8_10*
yum install -y cyanaudit10*
yum install -y geoip10*
yum install -y ip4r10*
yum install -y pg_repack10*
yum install -y pgsphere10*
yum install -y plr10*
查询软件目录在哪里:
rpm -ql postgresql10-server
配置OS用户环境变量
su - postgres
vi ~/.bash_profile
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=54321
export PGDATA=/data01/pg_root$PGPORT
export LANG=en_US.utf8
export PGHOME=/usr/pgsql-10
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'
unalias vi
规划数据库目录
mkdir /data/pg_data -p
chown postgres:postgres /data/pg_data
初始化数据库
su - postgres
initdb -D $PGDATA -U postgres --lc-collate=C --lc-ctype=en_US.utf8 -E UTF8
配置数据库配置文件
su - postgres
cd $PGDATA
1、postgresql.conf
vi postgresql.conf
listen_addresses = '0.0.0.0'
port = 54321 # 监听端口
max_connections = 2000 # 最大允许的连接数
superuser_reserved_connections = 10
unix_socket_directories = '.'
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 60
tcp_keepalives_count = 10
shared_buffers = 256MB # 共享内存,建议设置为系统内存的1/4 .
# vm.nr_hugepages = 102352
# 建议shared buffer设置超过64GB时 使用大页,页大小 /proc/meminfo Hugepagesize
maintenance_work_mem = 64MB # 系统内存超过32G时,建议设置为1GB。超过64GB时,建议设置为2GB。超过128GB时,建议设置为4GB。
work_mem = 64MB # 1/4 主机内存 / 256 (假设256个并发同时使用work_mem)
wal_buffers = 512MB # min( 2047MB, shared_buffers/32 )
dynamic_shared_memory_type = posix
vacuum_cost_delay = 0
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 500
bgwriter_lru_multiplier = 5.0
effective_io_concurrency = 0
max_worker_processes = 128
max_parallel_workers_per_gather = 32 # 建议设置为主机CPU核数的一半。
max_parallel_workers = 32 # 看业务AP和TP的比例,以及AP TP时间交错分配。实际情况调整。例如 主机CPU cores-2
wal_level = replica
fsync = on
synchronous_commit = off
full_page_writes = on # 支持原子写超过BLOCK_SIZE的块设备,在对齐后可以关闭。或者支持cow的文件系统可以关闭。
wal_writer_delay = 10ms
wal_writer_flush_after = 1MB
checkpoint_timeout = 35min
max_wal_size = 32GB # shared_buffers*2
min_wal_size = 8GB # max_wal_size/4
archive_mode = on
archive_command = '/bin/date'
max_wal_senders = 10
max_replication_slots = 10
wal_receiver_status_interval = 1s
max_logical_replication_workers = 4
max_sync_workers_per_subscription = 2
random_page_cost = 1.2
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
effective_cache_size = 10GB # 建议设置为主机内存的5/8。
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_duration_statement = 5s
log_checkpoints = on
log_connections = on # 如果是短连接,并且不需要审计连接日志的话,建议OFF。
log_disconnections = on # 如果是短连接,并且不需要审计连接日志的话,建议OFF。
log_error_verbosity = verbose
log_line_prefix = '%m [%p] '
log_lock_waits = on
log_statement = 'ddl'
log_timezone = 'PRC'
log_autovacuum_min_duration = 0
autovacuum_max_workers = 5
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_freeze_max_age = 1000000000
autovacuum_multixact_freeze_max_age = 1200000000
autovacuum_vacuum_cost_delay = 0
statement_timeout = 0 # 单位ms, s, min, h, d. 表示语句的超时时间,0表示不限制。
lock_timeout = 0 # 单位ms, s, min, h, d. 表示锁等待的超时时间,0表示不限制。
idle_in_transaction_session_timeout = 2h # 单位ms, s, min, h, d. 表示空闲事务的超时时间,0表示不限制。
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 800000000
vacuum_multixact_freeze_min_age = 50000000
vacuum_multixact_freeze_table_age = 800000000
datestyle = 'iso, ymd'
timezone = 'PRC'
lc_messages = 'en_US.UTF8'
lc_monetary = 'en_US.UTF8'
lc_numeric = 'en_US.UTF8'
lc_time = 'en_US.UTF8'
default_text_search_config = 'pg_catalog.simple'
shared_preload_libraries='pg_stat_statements,pg_pathman'
2、pg_hba.conf (数据库ACL访问控制列表,防火墙)
追加如下,表示允许所有用户从任意地方访问任意数据库,这个是偷懒的做法。
host all all 0.0.0.0/0 md5
格式
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
详见pg_hba.conf文件内容说明
启用开机自启动
systemctl enable postgresql-10
systemctl start postgresql-10
重启Server验证
reboot
su - postgres
psql
postgres=# show max_connections ;
max_connections
-----------------
2000
(1 row)
创建数据库用户
su - postgres
createuser -d -l -P -S blueapple
Enter password for new role:
Enter it again:
创建数据库
su - postgres
psql
postgres=# du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
blueapple | Create DB | {}
postgres=# c postgres blueapple
You are now connected to database "postgres" as user "blueapple".
postgres=> create database db1 with owner blueapple;
CREATE DATABASE
创建postgis空间数据库插件
连接到PG集群,在需要使用空间数据的DB中,使用超级用户,创建空间数据库插件。
psql
c db1 postgres
db1=# create extension postgis;
CREATE EXTENSION
db1=# create extension postgis_sfcgal;
CREATE EXTENSION
db1=# create extension postgis_tiger_geocoder cascade;
注意: 正在安装所需的扩展 "fuzzystrmatch"
CREATE EXTENSION
db1=# create extension postgis_topology ;
CREATE EXTENSION
db1=# create extension pgrouting ;
CREATE EXTENSION
postgres=# create extension pg_pathman ;
CREATE EXTENSION
postgres=# create extension orafce ;
CREATE EXTENSION
验证1、空间数据库PostGIS的使用
psql
c db1 blueapple
db1=> select st_geohash(st_setsrid(st_makepoint(120,70),4326),20);
st_geohash
----------------------
ysmq4xj7d9v2fsmq4xj7
(1 row)
![d03a5eaec6f5d25d5f4d1e1479e0b612.png](https://i-blog.csdnimg.cn/blog_migrate/cbf799b59f1b0e64ef2dfc028baa21e3.jpeg)