背景
萬事開頭難，搭建好一套學習、開發PostgreSQL的環境，是重中之重，
因為其他平臺(Ubuntu, CentOS, MAC)的用戶大多數都具備了自行安裝資料庫的能力，在這里我只寫一個面向Windows用戶的學習環境搭建檔案，
分為三個部分，用戶可以自由選擇，
如果你想深入的學習PostgreSQL，建議搭建PostgreSQL on Linux的環境，如果你只是想將資料庫使用在日常的應用開發作業中，有也不需要PG的其他附加插件的功能，那么你可以選擇PostgreSQL on Win的環境搭建，
如果你不想搭建本地的PostgreSQL，那么你可以使用云資料庫服務，比如阿里云RDS for PostgreSQL，
本章大綱
一、PostgreSQL on Win環境搭建
1 環境要求
2 下載PostgreSQL安裝包
3 解壓PostgreSQL安裝包
4 下載pgadmin安裝包(可選)
5 安裝pgadmin(可選)
6 規劃資料檔案目錄
7 初始化資料庫集群
8 配置postgresql.conf
9 配置pg_hba.conf(可選)
10 啟動、停止資料庫集群
11 如何自動啟動資料庫集群
12 使用psql 命令列連接資料庫
13 新增用戶
14 使用psql幫助
15 使用psql語法補齊
16 使用psql sql語法幫助
17 查看當前配置
18 設定會話引數
19 在psql中切換到另一個用戶或資料庫
20 使用pgadmin4連接資料庫
21 檔案
二、PostgreSQL on Linux(虛擬機)環境搭建
1 環境要求
2 下載Linux鏡像
3 安裝VMware Workstation(試用版本)
4 安裝securecrt(試用版本)
5 安裝Linux虛擬機
6 配置Linux虛擬機網路
7 securecrt終端連接Linux
8 配置linux
9 配置yum倉庫(可選)
10 創建普通用戶
11 規劃資料庫存盤目錄
12 下載PostgreSQL原始碼
13 安裝PostgreSQL
14 配置linux用戶環境變數
15 初始化資料庫集群
16 配置資料庫
17 啟動資料庫集群
18 連接資料庫
19 安裝pgadmin(可選)
20 配置pgadmin(可選)
21 使用pgadmin連接資料庫(可選)
三、云資料庫RDS for PostgreSQL
1 購買云資料庫
2 設定并記住RDS for PostgreSQL資料庫根用戶名和密碼
3 配置網路
4 配置白名單
5 本地安裝pgadmin(可選)
6 本地配置pgadmin(可選)
7 使用pgadmin連接RDS PostgreSQL資料庫(可選)
一、PostgreSQL on Win環境搭建
1 環境要求
Win 7 x64, 8GB以上記憶體, 4核以上, SSD硬碟(推薦)，100GB以上剩余空間, 可以訪問公網(10MB/s以上網路帶寬)
2 下載PostgreSQL安裝包
https://www.postgresql.org/download/windows/
建議下載高級安裝包，不需要安裝，直接使用，
下載win x64的版本(建議下載最新版本)
http://www.enterprisedb.com/products/pgbindownload.do
例如
https://get.enterprisedb.com/postgresql/postgresql-9.6.2-3-windows-x64-binaries.zip
3 解壓PostgreSQL安裝包
postgresql-9.6.2-3-windows-x64-binaries.zip
例如解壓到d:\pgsql
bin: 二進制檔案
doc: 檔案
include: 頭檔案
lib: 動態庫
pgAdmin 4: 圖形化管理工具
share: 擴展庫
StackBuilder: 打包庫
symbols: 符號表
4 下載pgadmin安裝包(可選)
如果PostgreSQL包中沒有包含pgAdmin，建議自行下載一個
建議下載pgadmin4(pgadmin3不再維護)
https://www.pgadmin.org/index.php
https://www.postgresql.org/ftp/pgadmin3/pgadmin4/v1.3/windows/
5 安裝pgadmin(可選)
6 規劃資料檔案目錄
例如將D盤的pgdata作為資料庫目錄，
新建d:\pgdata空目錄，
7 初始化資料庫集群
以管理員身份打開cmd.exe
>d: >cd pgsql >cd bin >initdb.exe -D d:\pgdata -E UTF8 --locale=C -U postgres 初始化時，指定資料庫檔案目錄，字符集，本地化，資料庫超級用戶名
8 配置postgresql.conf
資料庫組態檔名字postgresql.conf，這個檔案在資料檔案目錄D:\pgdata中，
將以下內容追加到postgresql.conf檔案末尾
listen_addresses = '0.0.0.0' port = 1921 max_connections = 200 tcp_keepalives_idle = 60 tcp_keepalives_interval = 10 tcp_keepalives_count = 6 shared_buffers = 512MB maintenance_work_mem = 64MB dynamic_shared_memory_type = windows vacuum_cost_delay = 0 bgwriter_delay = 10ms bgwriter_lru_maxpages = 1000 bgwriter_lru_multiplier = 5.0 bgwriter_flush_after = 0 old_snapshot_threshold = -1 wal_level = minimal max_wal_senders = 0 # 如果max_wal_senders大于0，需要設定wal_level至少為replica級別， PG 10開始，max_wal_senders默認值為10，以前的版本默認為0， synchronous_commit = off full_page_writes = on wal_buffers = 64MB wal_writer_delay = 10ms wal_writer_flush_after = 4MB checkpoint_timeout = 35min max_wal_size = 2GB min_wal_size = 80MB checkpoint_completion_target = 0.1 checkpoint_flush_after = 0 random_page_cost = 1.5 log_destination = 'csvlog' logging_collector = on log_directory = 'pg_log' log_truncate_on_rotation = on log_checkpoints = on log_connections = on log_disconnections = on log_error_verbosity = verbose log_temp_files = 8192 log_timezone = 'Asia/Hong_Kong' autovacuum = on log_autovacuum_min_duration = 0 autovacuum_naptime = 20s autovacuum_vacuum_scale_factor = 0.05 autovacuum_freeze_max_age = 1500000000 autovacuum_multixact_freeze_max_age = 1600000000 autovacuum_vacuum_cost_delay = 0 vacuum_freeze_table_age = 1400000000 vacuum_multixact_freeze_table_age = 1500000000 datestyle = 'iso, mdy' timezone = 'Asia/Hong_Kong' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' default_text_search_config = 'pg_catalog.english'
9 配置pg_hba.conf(可選)
資料庫防火墻檔案名字pg_hba.conf，這個檔案在資料檔案目錄D:\pgdata中，
將以下內容追加到檔案末尾，表示允許網路用戶使用用戶密碼連接你的postgresql資料庫.
host all all 0.0.0.0/0 md5
10 啟動、停止資料庫集群
使用命令列啟動資料庫集群
>d: >cd pgsql >cd bin D:\pgsql\bin>pg_ctl.exe start -D d:\pgdata 正在啟動服務器行程 D:\pgsql\bin>LOG: 00000: redirecting log output to logging collector process HINT: Future log output will appear in directory "pg_log". LOCATION: SysLogger_Start, syslogger.c:622
使用命令列停止資料庫集群
D:\pgsql\bin>pg_ctl.exe stop -m fast -D "d:\pgdata" 等待服務器行程關閉 .... 完成 服務器行程已經關閉
11 如何自動啟動資料庫集群
配置windows自動啟動服務.
12 使用psql 命令列連接資料庫
psql -h IP地址 -p 埠 -U 用戶名 資料庫名
D:\pgsql\bin>psql -h 127.0.0.1 -p 1921 -U postgres postgres psql (9.6.2) 輸入 "help" 來獲取幫助資訊. postgres=# \dt
13 新增用戶
新建用戶屬于資料庫操作，先使用psql和超級用戶postgres連接到資料庫，
新增一個普通用戶
postgres=# create role digoal login encrypted password 'pwd_digoal'; CREATE ROLE
新增一個超級用戶
postgres=# create role dba_digoal login superuser encrypted password 'dba_pwd_digoal'; CREATE ROLE
新增一個流復制用戶
postgres=# create role digoal_rep replication login encrypted password 'pwd'; CREATE ROLE
你還可以將一個用戶在不同角色之間切換
例如將digoal設定為超級用戶
postgres=# alter role digoal superuser; ALTER ROLE
查看已有用戶
postgres=# \du+ 角色串列 角色名稱 | 屬性 | 成員屬于 | 描述 ------------+--------------------------------------------+----------+------ dba_digoal | 超級用戶 | {} | digoal | 超級用戶 | {} | digoal_rep | 復制 | {} | postgres | 超級用戶, 建立角色, 建立 DB, 復制, 繞過RLS | {} |
14 使用psql幫助
psql有很多快捷的命令，使用\?就可以查看，
postgres=# \? 一般性 \copyright 顯示PostgreSQL的使用和發行許可條款 \errverbose 以最冗長的形式顯示最近的錯誤訊息 \g [檔案] or; 執行查詢 (并把結果寫入檔案或 |管道) \gexec 執行策略，然后執行其結果中的每個值 \gset [PREFIX] 執行查詢并把結果存到psql變數中 \q 退出 psql \crosstabview [COLUMNS] 執行查詢并且以交叉表顯示結果 \watch [SEC] 每隔SEC秒執行一次查詢 幫助 \? [commands] 顯示反斜線命令的幫助 ......
15 使用psql語法補齊
如果你編譯PostgreSQL使用了補齊選項，那么在psql中按TAB鍵，可以自動補齊命令，
16 使用psql sql語法幫助
如果你忘記了某個SQL的語法，使用\h 命令即可列印命令的幫助
例如
postgres=# \h create table 命令： CREATE TABLE 描述： 建立新的資料表 語法： CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI STS ] 表名 ( [ { 列名稱 資料_型別 [ COLLATE 校對規則 ] [ 列約束 [ ... ] ] | 表約束 | LIKE 源表 [ like選項 ... ] } [, ... ] ] ) ......
17 查看當前配置
show 引數名
postgres=# show client_encoding; client_encoding ----------------- GBK (1 行記錄)
查看pg_settings
postgres=# select * from pg_settings;
18 設定會話引數
set 引數名=值;
postgres=# set client_encoding='sql_ascii'; SET
19 在psql中切換到另一個用戶或資料庫
\c 切換到其他用戶或資料庫
postgres=# \c template1 digoal 您現在已經連接到資料庫 "template1",用戶 "digoal".
20 使用pgadmin4連接資料庫
pgAdmin4被安裝在這個目錄
d:\pgsql\pgAdmin 4\bin
雙擊pgAdmin4.exe打開pgadmin4(有點耗時，自動啟動HTTPD服務)
點擊server，右鍵，創建server.
配置server別名，連接資料庫的 IP，埠，用戶，密碼，資料庫名
21 檔案
PostgreSQL的安裝包中包含了pgadmin, PostgreSQL的檔案，找到對應的doc目錄，打開index.html，
二、PostgreSQL on Linux(虛擬機)環境搭建
1 環境要求
Win 7 x64, 8GB以上記憶體, 4核以上, SSD硬碟(推薦)，100GB以上剩余空間, 可以訪問公網(10MB/s以上網路帶寬)
2 下載Linux鏡像
http://isoredirect.centos.org/centos/6/isos/x86_64/
http://mirrors.163.com/centos/6.9/isos/x86_64/CentOS-6.9-x86_64-minimal.iso
3 安裝VMware Workstation(試用版本)
http://www.vmware.com/cn/products/workstation/workstation-evaluation.html
4 安裝securecrt(試用版本)
securecrt可以用來連接Linux終端，方便使用
https://www.vandyke.com/products/securecrt/windows.html
5 安裝Linux虛擬機
打開vmware, 創建虛擬機, 選擇CentOS 6 x64版本.
1. 配置建議：
4G記憶體，40G磁盤，2核以上，NAT網路模式，
2. 安裝建議：
minimal最小化安裝，
3. root密碼：
記住你設定的root密碼，
4. Linux安裝配置建議
配置主機名，配置網路（根據你的vmware NAT網路進行配置），關閉selinux，關閉防火墻或開放ssh埠(測驗環境)，
6 配置Linux虛擬機網路
vmware視窗連接linux
例子，192.168.150 請參考你的vmware NAT網路修改一下，
配置網關
vi /etc/sysconfig/network NETWORKING=yes HOSTNAME=digoal01 GATEWAY=192.168.150.2
配置IP
cat /etc/sysconfig/network-scripts/ifcfg-eth0 DEVICE=eth0 TYPE=Ethernet UUID=d28f566a-b0b9-4bde-95e7-20488af19eb6 ONBOOT=yes NM_CONTROLLED=yes BOOTPROTO=static HWADDR=00:0C:29:5D:6D:9C IPADDR=192.168.150.133 PREFIX=24 GATEWAY=192.168.150.2 DNS1=192.168.150.2 DEFROUTE=yes IPV4_FAILURE_FATAL=yes IPV6INIT=no NAME="System eth0"
配置DNS
cat /etc/resolv.conf nameserver 192.168.150.2
重啟網路服務
service network restart
7 securecrt終端連接Linux
添加一個session，連接到Linux虛擬機，
8 配置linux
1. /etc/sysctl.conf
vi /etc/sysctl.conf 追加到檔案末尾 kernel.shmall = 4294967296 kernel.shmmax=135497418752 kernel.shmmni = 4096 kernel.sem = 50100 64128000 50100 1280 fs.file-max = 7672460 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65000 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 4194304 net.ipv4.tcp_max_syn_backlog = 4096 net.core.netdev_max_backlog = 10000 net.ipv4.netfilter.ip_conntrack_max = 655360 net.ipv4.tcp_timestamps = 0 net.ipv4.tcp_tw_recycle=1 net.ipv4.tcp_timestamps=1 net.ipv4.tcp_keepalive_time = 72 net.ipv4.tcp_keepalive_probes = 9 net.ipv4.tcp_keepalive_intvl = 7 vm.zone_reclaim_mode=0 vm.dirty_background_bytes = 40960000 vm.dirty_ratio = 80 vm.dirty_expire_centisecs = 6000 vm.dirty_writeback_centisecs = 50 vm.swappiness=0 vm.overcommit_memory = 0 vm.overcommit_ratio = 90
生效
sysctl -p
2. /etc/security/limits.conf
vi /etc/security/limits.conf * soft nofile 131072 * hard nofile 131072 * soft nproc 131072 * hard nproc 131072 * soft core unlimited * hard core unlimited * soft memlock 500000000 * hard memlock 500000000
3. /etc/security/limits.d/*
rm -f /etc/security/limits.d/*
4. 關閉selinux
# vi /etc/sysconfig/selinux SELINUX=disabled SELINUXTYPE=targeted
5. 配置OS防火墻
（建議按業務場景設定，我這里先清掉）
iptables -F
配置范例
# 私有網段 -A INPUT -s 192.168.0.0/16 -j ACCEPT -A INPUT -s 10.0.0.0/8 -j ACCEPT -A INPUT -s 172.16.0.0/16 -j ACCEPT
重啟linux，
reboot
9 配置yum倉庫(可選)
在linux虛擬機中，找一個有足夠空間的磁區，下載ISO鏡像
wget http://mirrors.163.com/centos/6.9/isos/x86_64/CentOS-6.9-x86_64-bin-DVD1.iso wget http://mirrors.163.com/centos/6.9/isos/x86_64/CentOS-6.9-x86_64-bin-DVD2.iso
新建ISO掛載點目錄
mkdir /mnt/cdrom1 mkdir /mnt/cdrom2
掛載ISO
mount -o loop,defaults,ro /u01/CentOS-6.8-x86_64-bin-DVD1.iso /mnt/cdrom1 mount -o loop,defaults,ro /u01/CentOS-6.8-x86_64-bin-DVD2.iso /mnt/cdrom2
備份并洗掉原有的YUM組態檔
mkdir /tmp/yum.bak cd /etc/yum.repos.d/ mv * /tmp/yum.bak/
新增YUM組態檔
cd /etc/yum.repos.d/ vi local.repo [local-yum] name=Local Repository baseurl=file:///mnt/cdrom1 enabled=1 gpgcheck=0
重繪YUM快取
yum clean all
測驗
yum list yum install createrepo -- 方便后面測驗
修改YUM配置，修改路徑為上層目錄
cd /etc/yum.repos.d/ vi local.repo [local-yum] name=Local Repository baseurl=file:///mnt/ enabled=1 gpgcheck=0
創建YUM索引
cd /mnt/ createrepo .
重繪YUM快取，測驗
yum clean all yum list yum install vim
10 創建普通用戶
useradd digoal
11 規劃資料庫存盤目錄
假設/home磁區有足夠的空間, /home/digoal/pgdata規劃為資料檔案目錄
Filesystem Size Used Avail Use% Mounted on /dev/sda3 14G 5.7G 7.2G 45% /
12 下載PostgreSQL原始碼
https://www.postgresql.org/ftp/source/
su - digoal wget https://ftp.postgresql.org/pub/source/v9.6.2/postgresql-9.6.2.tar.bz2
13 安裝PostgreSQL
安裝依賴包
root用戶下，使用yum 安裝依賴包 yum -y install coreutils glib2 lrzsz mpstat dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make smartmontools flex bison perl-devel perl-Ext Utils* openldap-devel jadetex openjade bzip2
編譯安裝PostgreSQL
digoal用戶下，編譯安裝PostgreSQL tar -jxvf postgresql-9.6.2.tar.bz2 cd postgresql-9.6.2 ./configure --prefix=/home/digoal/pgsql9.6 make world -j 8 make install-world
14 配置linux用戶環境變數
digoal用戶下，配置環境變數
su - digoal vi ~/.bash_profile 追加 export PS1="$USER@`/bin/hostname -s`-> " export PGPORT=1921 export PGDATA=/home/digoal/pgdata export LANG=en_US.utf8 export PGHOME=/home/digoal/pgsql9.6 export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH export PATH=$PGHOME/bin:$PATH:. export DATE=`date +"%Y%m%d%H%M"` 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
重新登錄digoal用戶，配置生效
exit su - digoal
15 初始化資料庫集群
initdb -D $PGDATA -E UTF8 --locale=C -U postgres
16 配置資料庫
組態檔在$PGDATA目錄中
1. 配置postgresql.conf
追加 listen_addresses = '0.0.0.0' port = 1921 max_connections = 200 unix_socket_directories = '.' tcp_keepalives_idle = 60 tcp_keepalives_interval = 10 tcp_keepalives_count = 10 shared_buffers = 512MB dynamic_shared_memory_type = posix vacuum_cost_delay = 0 bgwriter_delay = 10ms bgwriter_lru_maxpages = 1000 bgwriter_lru_multiplier = 10.0 bgwriter_flush_after = 0 old_snapshot_threshold = -1 backend_flush_after = 0 wal_level = replica synchronous_commit = off full_page_writes = on wal_buffers = 16MB wal_writer_delay = 10ms wal_writer_flush_after = 0 checkpoint_timeout = 30min max_wal_size = 2GB min_wal_size = 128MB checkpoint_completion_target = 0.05 checkpoint_flush_after = 0 random_page_cost = 1.3 log_destination = 'csvlog' logging_collector = on log_truncate_on_rotation = on log_checkpoints = on log_connections = on log_disconnections = on log_error_verbosity = verbose autovacuum = on log_autovacuum_min_duration = 0 autovacuum_naptime = 20s autovacuum_vacuum_scale_factor = 0.05 autovacuum_freeze_max_age = 1500000000 autovacuum_multixact_freeze_max_age = 1600000000 autovacuum_vacuum_cost_delay = 0 vacuum_freeze_table_age = 1400000000 vacuum_multixact_freeze_table_age = 1500000000 datestyle = 'iso, mdy' timezone = 'PRC' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' default_text_search_config = 'pg_catalog.english' shared_preload_libraries='pg_stat_statements'
2. 配置pg_hba.conf
追加 host all all 0.0.0.0/0 md5
17 啟動資料庫集群
su - digoal pg_ctl start
18 連接資料庫
su - digoal psql psql (9.6.2) Type "help" for help. postgres=#
19 安裝pgadmin(可選)
在windows 機器上，安裝pgadmin
https://www.pgadmin.org/download/windows4.php
20 配置pgadmin(可選)
參考章節1
21 使用pgadmin連接資料庫(可選)
參考章節1
三、云資料庫RDS for PostgreSQL
1 購買云資料庫
https://www.aliyun.com/product/rds/postgresql
2 設定并記住RDS for PostgreSQL資料庫根用戶名和密碼
在RDS 控制臺操作，
3 配置網路
在RDS 控制臺操作，配置連接資料庫的URL和埠，
4 配置白名單
在RDS 控制臺操作，配置來源IP的白名單，如果來源IP為動態IP，白名單設定為0.0.0.0，
(資料庫開放公網連接有風險，請謹慎設定，本文僅為測驗環境，)
5 本地安裝pgadmin(可選)
在windows 機器上，安裝pgadmin
https://www.pgadmin.org/download/windows4.php
6 本地配置pgadmin(可選)
參考章節1
7 使用pgadmin連接RDS PostgreSQL資料庫(可選)
參考章節1
作者丨digoal
本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/How-to-build-an-environment-to-learn-and-develop-PostgreSQL.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/519196.html
標籤：其他