安裝epel源在線yum安裝PostgreSQL
1、安裝EPEL
http://fedoraproject.org/wiki/EPEL
<code>wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm /<code>
2、安裝PostgreSQL yum
https://yum.postgresql.org/repopackages.php#pg96
<code>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 /<code>
安裝數據庫軟件
<code>數據庫軟件
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*/<code>
查詢軟件目錄在哪裡:
<code>rpm -ql postgresql10-server /<code>
配置OS用戶環境變量
<code>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 /<code>
規劃數據庫目錄
<code>mkdir /data/pg_data -p
chown postgres:postgres /data/pg_data /<code>
初始化數據庫
<code>su - postgres
initdb -D $PGDATA -U postgres --lc-collate=C --lc-ctype=en_US.utf8 -E UTF8 /<code>
配置數據庫配置文件
<code>su - postgres
cd $PGDATA /<code>
1、postgresql.conf
<code>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' /<code>
2、pg_hba.conf (數據庫ACL訪問控制列表,防火牆)
<code>追加如下，表示允許所有用戶從任意地方訪問任意數據庫，這個是偷懶的做法。
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文件內容說明/<code>
啟用開機自啟動
<code>systemctl enable postgresql-10
systemctl start postgresql-10/<code>
重啟Server驗證
<code>reboot
su - postgres
psql
postgres=# show max_connections ;
max_connections
-----------------
2000
(1 row) /<code>
創建數據庫用戶
<code>su - postgres
createuser -d -l -P -S blueapple
Enter password for new role:
Enter it again: /<code>
創建數據庫
<code>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 /<code>
創建postgis空間數據庫插件
連接到PG集群，在需要使用空間數據的DB中，使用超級用戶，創建空間數據庫插件。
<code>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/<code>
驗證1、空間數據庫PostGIS的使用
<code>psql
\\c db1 blueapple
db1=> select st_geohash(st_setsrid(st_makepoint(120,70),4326),20);
st_geohash
----------------------
ysmq4xj7d9v2fsmq4xj7
(1 row)/<code>
分享到:
閱讀更多 blueapple 的文章
