1 環境描述

• 操作系統:Centos7.6

• postgresql:13.6

  安裝方式：源碼安裝

2 創建用戶

# groupadd -g 2000 pgsql
# useradd -u 2000 -g pgsql pgsql

3 目錄規劃

# mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg13,soft,pg_log}
# chown -R pgsql:pgsql /postgresql
# chmod -R 755 /postgresql

4 安裝

# cd /usr/local/src
# wget http://172.25.24.80/postgresql/postgresql-13.6.tar.gz
# tar zxvf postgresql-13.6.tar.gz
# cd postgresql-13.6
# yum install -y cmake make gcc zlib gcc-c++ perl readline readline-devel zlib zlib-devel perl python36 tcl openssl ncurses-devel openldap pam rlwrap 
# yum -y install perl-devel perl-ExtUtils-Embed
# cd /usr/local/src/postgresql-13.6
# ./configure --prefix=/postgresql/pg13 --without-readline --with-perl --with-python
# make
# make install
# cd contrib/pg_trgm/
# make
# make install
# cd ../btree_gist extension
# make
# make install

5 配置環境變量

# vim /etc/profile
export PGPORT=5432
export PGDATA=/postgresql/pgdata
export PGHOME=/postgresql/pg13
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
alias psql='rlwrap psql'
# source /etc/profile

6 初始化數據庫

# su - pgsql
$ /postgresql/pg13/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /postgresql/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/postgresql/pg13/bin/pg_ctl -D /postgresql/pgdata -l logfile start

7 配置遠程登陸

# cat /postgresql/pgdata/postgresql.conf
listen_addresses = '*'
port = 5432                             
max_connections = 1000                
unix_socket_directories = '/postgresql/pgdata'  # comma-separated list of directories
             
# - Authentication -  
password_encryption = md5
# - Memory -  
shared_buffers = 1024MB    ## 1/4 of the memory  
dynamic_shared_memory_type = posix

# WRITE-AHEAD LOG 
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1024MB

# - Archiving -   
archive_mode = on
archive_command = 'cp %p /postgresql/archive/%f'

# REPLICATION
max_wal_senders = 10 
wal_keep_size = 1000
wal_sender_timeout = 120s
max_slot_wal_keep_size = 10

log_directory = '/postgresql/pg_log'                        
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_truncate_on_rotation = off          
log_rotation_age = 1d                  
log_rotation_size = 0
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
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.english'

# cat /postgresql/pgdata/pg_hba.conf
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               md5
host    all             all             ::1/128                 trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    replication     all             0.0.0.0/32              md5

8 啟動/關閉服務

# su  - gpsql 
$ pg_ctl start -l /postgresql/pg_log/pg.log
$ pg_ctl stop
$ pg_ctl status
或者
$ nohup /postgresql/pg13/bin/postgres -D /postgresql/pgdata > /postgresql/pg13/pglog.out 2>&1 &

說明: postgresql支持三種停止數據庫的模式，這個類似Oracle。

• smart： 等待活動事務提交結束，并等待客戶端主動斷開連接之后關閉數據庫
• fast： 回滾所有活動的事務，并強制斷開客戶端的鏈接之后關閉數據庫，默認為fast
• immediate： 立即終止所有服務器進程，當下一次數據庫啟動的時候首先進入恢復默認。

推薦使用fast默認關閉數據庫。

9 開機自啟動

方式一： 配置/etc/rc.local

# vim /etc/rc.local
su - pgsql -c "pg_ctl start"

方式二： 配置systemctl服務

# vim /etc/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network.target
[Service]
Type=forking
User=pgsql
Group=pgsql
Environment=PGPORT=5432
Environment=PGDATA=/postgresql/pgdata
Environment=PGLOG=/postgresql/pg_log/pg.log
OOMScoreAdjust=-1000
ExecStart=/postgresql/pg13/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -l ${PGLOG} -w -t 300
ExecStop=/postgresql/pg13/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/postgresql/pg13/bin/pg_ctl reload -D ${PGDATA} -l ${PGLOG} -s
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=300
[Install]
WantedBy=multi-user.target

# systemctl enable postgresql.service

10. 登陸及常用命令

$ psql

常用的操作命令
 (1) 列出所有數據庫
 \l 或者\l+
（2）連接到數據庫
 \c 或者 \connect , 如\c dbname
（3）切換新的用戶連接當前的數據庫
 \c - username
 關于表的操作
（4）列出當前數據庫的表
  \dt 或者 \dt+   視圖為\dv
 (5) 顯示表結構
  \d tablename
 (6) 顯示可用的模式
  \dn
（7）設置模式搜索路徑
  set search_path to schema_name;
 (8) 查看數據庫有哪些用戶
  \du