postgis安装_安装epel源在线yum安装PostgreSQL

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)