PostgreSQLonECSSLA流複製備庫+秒級快照+PITR+自動清理
標籤
PostgreSQL , ECS , 阿里雲 , 部署 , 物理映象 , 流複製 , 快照備份 , 備份驗證 , 自動清理
背景
介紹在阿里雲ECS環境中，實現一個非常簡單，但是可用性和可靠性滿足一般企業要求的PostgreSQL環境。
包括：
1、自動啟動資料庫
2、包括一個物理流複製備庫
3、包括自動的秒級快照備份
4、包括自動備份集有效性驗證
5、包括自動清理N天以前的備份集、歸檔檔案
6、監控請自建
部署環境介紹
1、ECS
111.111.111.199 (主)
111.111.111.223 (備)
2、雲盤
400 GB
3、軟體
OS: CentOS 7.4 x64
PostgreSQL: 10.1
ZFS: 0.7.3
部署流程
雙機
1、安裝軟體
yum -y install coreutils glib2 lrzsz dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc gcc-c++ make smartmontools flex bison perl-devel perl-ExtUtils* openldap-devel jadetex openjade bzip2
2、修改核心配置
vi /etc/sysctl.conf
# add by digoal.zhou
fs.aio-max-nr = 1048576
fs.file-max = 76724600
kernel.sem = 4096 2147483647 2147483646 512000
kernel.shmall = 107374182
kernel.shmmax = 274877906944
kernel.shmmni = 819200
net.core.netdev_max_backlog = 10000
net.core.rmem_default = 262144
# The default setting of the socket receive buffer in bytes.
net.core.rmem_max = 4194304
# The maximum receive socket buffer size in bytes
net.core.wmem_default = 262144
# The default setting (in bytes) of the socket send buffer.
net.core.wmem_max = 4194304
# The maximum send socket buffer size in bytes.
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_keepalive_intvl = 20
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_mem = 8388608 12582912 16777216
net.ipv4.tcp_fin_timeout = 5
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_timestamps = 1
net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_max_tw_buckets = 262144
net.ipv4.tcp_rmem = 8192 87380 16777216
net.ipv4.tcp_wmem = 8192 65536 16777216
net.nf_conntrack_max = 1200000
net.netfilter.nf_conntrack_max = 1200000
vm.dirty_background_bytes = 204800000
vm.dirty_expire_centisecs = 3000
vm.dirty_ratio = 90
vm.dirty_writeback_centisecs = 50
vm.swappiness = 0
vm.mmap_min_addr = 65536
vm.overcommit_memory = 0
vm.overcommit_ratio = 90
vm.swappiness = 0
vm.zone_reclaim_mode = 0
net.ipv4.ip_local_port_range = 40000 65535
fs.nr_open=20480000
3、修改資源限制
# vi /etc/security/limits.conf
* soft nofile 1024000
* hard nofile 1024000
* soft nproc unlimited
* hard nproc unlimited
* soft core unlimited
* hard core unlimited
* soft memlock unlimited
* hard memlock unlimited
4、關閉透明大頁
chmod +x /etc/rc.d/rc.local
vi /etc/rc.local
# 追加
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
5、分配磁碟
parted -s /dev/vdb mklabel gpt
parted -s /dev/vdb mkpart primary 1MiB 100%
主機
1、建立檔案系統
mkfs.ext4 /dev/vdb1 -m 0 -O extent,uninit_bg -E lazy_itable_init=1 -T largefile -L data01
2、載入檔案系統
mkdir /data01
vi /etc/fstab
LABEL=data01 /data01 ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback 0 0
mount -a
備機
1、安裝ZFS
yum install -y http://download.zfsonlinux.org/epel/zfs-release.el7_4.noarch.rpm
yum install -y kernel-devel-3.10.0-693.2.2.el7.x86_64
yum install -y zfs
2、手動載入zfs模組
/usr/sbin/modprobe zfs
3、建立zpool和zfs for 資料目錄，歸檔目錄
zpool create -o ashift=12 zp1 vdb1
zfs set compression=lz4 zp1
zfs set canmount=off zp1
zfs set atime=off zp1
zfs create -o mountpoint=/data01 zp1/data01
zfs create -o mountpoint=/pg_arch zp1/pg_arch
4、自動載入zpool
vi /etc/rc.local
/usr/sbin/modprobe zfs
/usr/sbin/zpool import zp1
雙機
1、安裝PostgreSQL與EPEL軟體 yum 倉庫
yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
2、安裝資料庫軟體與周邊軟體
yum install -y postgresql10* postgis24_10* pgrouting_10* osm2pgrouting_10* plpgsql_check_10* pgbadger pg_top10* hypopg_10* citus_10* cstore_fdw_10* pg_pathman10* orafce10* pgfincore10* pgbson10* pgmemcache-10* pldebugger10* plv8_10* geoip10* ip4r10* pg_repack10* pgsphere10* plr10*
3、修改環境變數
su - postgres
vi ~/.bash_profile
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=3433
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
主機
1、建立資料目錄，修改許可權
cd /data01
mkdir pg_root3433
mkdir -p pg_arch/pg3433
chown postgres:postgres /data01/*
ln -s /data01/pg_arch /pg_arch
備機
1、建立資料目錄，修改許可權
cd /data01
mkdir pg_root3433
chown postgres:postgres /data01/*
cd /pg_arch
mkdir pg3433
chown postgres:postgres /pg_arch/pg3433
主機
1、初始化資料庫叢集
su - postgres
initdb -D $PGDATA -U postgres --locale=en_US.UTF8 -E UTF8
2、修改資料庫引數配置
cd $PGDATA
vi postgresql.auto.conf
listen_addresses = `0.0.0.0`
port = 3433 # 監聽埠
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 = 16GB # 共享記憶體，建議設定為系統記憶體的1/4 .
maintenance_work_mem = 512MB # 系統記憶體超過32G時，建議設定為1GB。超過64GB時，建議設定為2GB。超過128GB時，建議設定為4GB。
work_mem = 64MB # 1/4 主機記憶體 / 256 (假設256個併發同時使用work_mem)
wal_buffers = 128MB # 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 = 16 # 建議設定為主機CPU核數的一半。
max_parallel_workers = 16 # 看業務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 = 30min
max_wal_size = 32GB # shared_buffers*2
min_wal_size = 8GB # max_wal_size/4
archive_mode = always
archive_command = `/bin/date`
hot_standby = on
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 = 32GB # 建議設定為主機記憶體的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、修改備庫將用到的自動流複製引數檔案
cp $PGHOME/share/recovery.conf.sample ./
mv recovery.conf.sample recovery.done
vi recovery.done
recovery_target_timeline = `latest`
standby_mode = on
primary_conninfo = `host=111.111.111.223 port=3433 user=rep password=xxxxxxxxxx`
restore_command = `BASEDIR="/pg_arch/pg3433" ; find $BASEDIR -name %f -exec cp {} %p \;`
3、修改資料庫認證許可權訪問控制ACL, pg_hba.conf
vi pg_hba.conf
host all all 0.0.0.0/0 md5
host replication rep 0.0.0.0/0 md5
4、啟動資料庫
pg_ctl start
5、建立流複製，資料庫角色
psql -n
create role rep replication login encrypted password `xxxxxxxxxx`;
備機
1、建立備庫
su - postgres
pg_basebackup -D $PGDATA -F p -h 111.111.111.199 -p 3433 -U rep
2、修改資料庫配置
cd $PGDATA
vi postgresql.auto.conf
archive_command = `DT="/pg_arch/pg3433/`date +%F`" ; test ! -d $DT && mkdir -p $DT ; test ! -f $DT/%f && cp %p $DT/%f`
3、配置備庫的自動同步引數檔案
mv recovery.done recovery.conf
vi recovery.conf
primary_conninfo = `host=111.111.111.199 port=3433 user=rep password=xxxxxxxxxx`
5、修改資料目錄許可權
su - root
chmod 700 /data01/pg_root3433
雙機
1、系統啟動時，自動啟動資料庫
vi /etc/rc.local
# 追加
su - postgres -c "pg_ctl start"
2、重啟ECS驗證
reboot
su - postgres
psql
postgres=# show max_connections ;
max_connections
-----------------
2000
(1 row)
備機
1、配置自動備份（快照備份）
su - root
mkdir script
vi /root/script/zfs_snap.sh
#!/bin/bash
date +%F%T
# 自動建立、清理快照
/sbin/zfs snapshot zp1/data01@`date +%Y%m%d`
/sbin/zfs destroy zp1/data01@`date +%Y%m%d -d `-10 day``
/sbin/zfs list -t snapshot
# 自動清理歸檔
rm -rf /pg_arch/pg3433/`date +%F -d `-11 day``
date +%F%T
2、設定指令碼許可權
chmod 500 /root/script/zfs_snap.sh
3、設定自動備份任務，每天備份一次
# crontab -e
1 8 * * * /root/script/zfs_snap.sh >> /root/zfs_snap.log 2>&1
自動恢復驗證
備機
1、建立備份驗證用到的配置檔案，修改埠號，關閉歸檔。
su - root
mkdir /root/test_recovery
vi /root/test_recovery/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = `0.0.0.0`
port = 11111 # 監聽埠
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 = 16GB # 共享記憶體，建議設定為系統記憶體的1/4 .
maintenance_work_mem = 512MB # 系統記憶體超過32G時，建議設定為1GB。超過64GB時，建議設定為2GB。超過128GB時，建議設定為4GB。
work_mem = 64MB # 1/4 主機記憶體 / 256 (假設256個併發同時使用work_mem)
wal_buffers = 128MB # 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 = 16 # 建議設定為主機CPU核數的一半。
max_parallel_workers = 16 # 看業務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 = 30min
max_wal_size = 32GB # shared_buffers*2
min_wal_size = 8GB # max_wal_size/4
archive_mode = off
archive_command = `DT="/pg_arch/pg3433/`date +%F`" ; test ! -d $DT && mkdir -p $DT ; test ! -f $DT/%f && cp %p $DT/%f`
hot_standby = on
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 = 32GB # 建議設定為主機記憶體的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、建立自動恢復指令碼，根據昨日快照克隆，並使用克隆恢復，判斷是否可正常恢復
vi /root/test_recovery/recovery_test.sh
#!/bin/bash
date +%F%T
. /var/lib/pgsql/.bash_profile
DATE=`date -d `-1 day` +%Y%m%d`
zfs clone -o mountpoint=/test_recovery zp1/data01@$DATE zp1/test_recovery
rm -f /test_recovery/pg_root3433/postgresql.auto.conf
rm -f /test_recovery/pg_root3433/postmaster.pid
rm -f /test_recovery/pg_root3433/pg_wal/*
cp -p /root/test_recovery/postgresql.auto.conf /test_recovery/pg_root3433/postgresql.auto.conf
su - postgres -c "pg_ctl start -D /test_recovery/pg_root3433"
for ((i=1;i<180;i++)) do
echo $i
sleep 20
psql -h 127.0.0.1 -p 11111 postgres postgres -c "select now(),* from pg_database;"
ret=$?
if [ $ret -eq 0 ]; then
break
fi
done
su - postgres -c "pg_ctl stop -w -t 6000 -m immediate -D /test_recovery/pg_root3433"
sleep 60
zfs destroy zp1/test_recovery
date +%F%T
3、配置指令碼許可權
chmod 500 /root/test_recovery/recovery_test.sh
4、測試自動備份集恢復指令碼
/root/test_recovery/recovery_test.sh
2017-11-2923:23:15
/var/lib/pgsql/.bash_profile: line 24: unalias: vi: not found
rm: cannot remove ‘/test_recovery/pg_root3433/pg_wal/archive_status’: Is a directory
/var/lib/pgsql/.bash_profile: line 24: unalias: vi: not found
waiting for server to start....2017-11-29 23:23:15.879 CST [11051] LOG: 00000: listening on IPv4 address "0.0.0.0", port 11111
2017-11-29 23:23:15.879 CST [11051] LOCATION: StreamServerPort, pqcomm.c:593
2017-11-29 23:23:15.898 CST [11051] LOG: 00000: listening on Unix socket "./.s.PGSQL.11111"
2017-11-29 23:23:15.898 CST [11051] LOCATION: StreamServerPort, pqcomm.c:587
2017-11-29 23:23:16.422 CST [11051] LOG: 00000: redirecting log output to logging collector process
2017-11-29 23:23:16.422 CST [11051] HINT: Future log output will appear in directory "log".
2017-11-29 23:23:16.422 CST [11051] LOCATION: SysLogger_Start, syslogger.c:634
done
server started
1
now | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
-------------------------------+-----------+--------+----------+------------+------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------
2017-11-29 23:23:36.572934+08 | postgres | 10 | 6 | en_US.UTF8 | en_US.UTF8 | f | t | -1 | 13805 | 548 | 1 | 1663 |
2017-11-29 23:23:36.572934+08 | template1 | 10 | 6 | en_US.UTF8 | en_US.UTF8 | t | t | -1 | 13805 | 548 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
2017-11-29 23:23:36.572934+08 | template0 | 10 | 6 | en_US.UTF8 | en_US.UTF8 | t | f | -1 | 13805 | 548 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
(3 rows)
/var/lib/pgsql/.bash_profile: line 24: unalias: vi: not found
waiting for server to shut down.... done
server stopped
其他
1、檢視已有備份集
# zfs list -t snapshot
NAME USED AVAIL REFER MOUNTPOINT
zp1/data01@20171129 0B - 12.6M -
zp1/data01@20171128 0B - 12.6M -
2、主動刪除備份集
# zfs destroy zp1/data01@20171128
3、根據已有快照克隆zfs
# zfs clone -o mountpoint=/test_recovery zp1/data01@20171128 zp1/test_recovery
4、刪除克隆ZFS
# zfs destroy zp1/test_recovery
5、檢查主備時間延遲
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 11697
usesysid | 16384
usename | rep
application_name | walreceiver
client_addr | 111.111.111.223
client_hostname |
client_port | 53622
backend_start | 2017-11-29 22:46:17.806483+08
backend_xmin |
state | streaming
sent_lsn | 0/E06B9A30
write_lsn | 0/E06B9A30
flush_lsn | 0/E06B9A30
replay_lsn | 0/E06B9A30
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
6、檢查主備LSN延遲
postgres=# select *, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), sent_lsn)) as sent_delay, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), replay_lsn)) as replay_delay from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 11697
usesysid | 16384
usename | rep
application_name | walreceiver
client_addr | 111.111.111.223
client_hostname |
client_port | 53622
backend_start | 2017-11-29 22:46:17.806483+08
backend_xmin |
state | streaming
sent_lsn | 1/39D5C000
write_lsn | 1/39000000
flush_lsn | 1/39000000
replay_lsn | 1/37A58718
write_lag | 00:00:00.192577
flush_lag | 00:00:00.192577
replay_lag | 00:00:00.502927
sync_priority | 0
sync_state | async
sent_delay | 9650 kB
replay_delay | 44 MB
7、建立資料庫
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ] ]
8、建立資料庫模板
Command: ALTER DATABASE
Description: change a database
Syntax:
ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:
ALLOW_CONNECTIONS allowconn
CONNECTION LIMIT connlimit
IS_TEMPLATE istemplate
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL
postgres=# alter database postgres is_template true;
ALTER DATABASE
9、從模板克隆資料庫
create database newdb with template template_db;
8、建立使用者
Command: CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD `password`
| VALID UNTIL `timestamp`
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
9、建立schema
Command: CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
where role_specification can be:
user_name
| CURRENT_USER
| SESSION_USER
10、配置使用者許可權
Command: GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
......
11、配置庫級別引數
Command: ALTER DATABASE
Description: change a database
Syntax:
ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:
ALLOW_CONNECTIONS allowconn
CONNECTION LIMIT connlimit
IS_TEMPLATE istemplate
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL
12、配置使用者級別引數
Command: ALTER ROLE
Description: change a database role
Syntax:
ALTER ROLE role_specification [ WITH ] option [ ... ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD `password`
| VALID UNTIL `timestamp`
ALTER ROLE name RENAME TO new_name
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL
where role_specification can be:
role_name
| CURRENT_USER
| SESSION_USER
13、配置函式級別引數
Command: ALTER FUNCTION
Description: change the definition of a function
Syntax:
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
action [ ... ] [ RESTRICT ]
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
RENAME TO new_name
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
SET SCHEMA new_schema
ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
DEPENDS ON EXTENSION extension_name
where action is one of:
CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
PARALLEL { UNSAFE | RESTRICTED | SAFE }
COST execution_cost
ROWS result_rows
SET configuration_parameter { TO | = } { value | DEFAULT }
SET configuration_parameter FROM CURRENT
RESET configuration_parameter
RESET ALL
14、配置表級別引數
Command: ALTER TABLE
Description: change the definition of a table
Syntax:
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
SET TABLESPACE new_tablespace [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] name
ATTACH PARTITION partition_name FOR VALUES partition_bound_spec
ALTER TABLE [ IF EXISTS ] name
DETACH PARTITION partition_name
where action is one of:
ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
ALTER [ COLUMN ] column_name SET STATISTICS integer
ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint [ NOT VALID ]
ADD table_constraint_using_index
ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
VALIDATE CONSTRAINT constraint_name
DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE REPLICA TRIGGER trigger_name
ENABLE ALWAYS TRIGGER trigger_name
DISABLE RULE rewrite_rule_name
ENABLE RULE rewrite_rule_name
ENABLE REPLICA RULE rewrite_rule_name
ENABLE ALWAYS RULE rewrite_rule_name
DISABLE ROW LEVEL SECURITY
ENABLE ROW LEVEL SECURITY
FORCE ROW LEVEL SECURITY
NO FORCE ROW LEVEL SECURITY
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITH OIDS
SET WITHOUT OIDS
SET TABLESPACE new_tablespace
SET { LOGGED | UNLOGGED }
SET ( storage_parameter = value [, ... ] )
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
OF type_name
NOT OF
OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
and table_constraint_using_index is:
[ CONSTRAINT constraint_name ]
{ UNIQUE | PRIMARY KEY } USING INDEX index_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
15、配置認證ACL
vi $PGDATA/pg_hba.conf
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all super_user 0.0.0.0/0 reject
host all all 0.0.0.0/0 md5
pg_ctl reload
16、使用psql幫助
postgres=# h SQL命令,支援TAB補齊
17、使用psql本地簡化命令
postgres=# ?
General
copyright show PostgreSQL usage and distribution terms
crosstabview [COLUMNS] execute query and display results in crosstab
errverbose show most recent error message at maximum verbosity
g [FILE] or ; execute query (and send results to file or |pipe)
gexec execute query, then execute each value in its result
gset [PREFIX] execute query and store results in psql variables
gx [FILE] as g, but forces expanded output mode
q quit psql
watch [SEC] execute query every SEC seconds
Help
? [commands] show help on backslash commands
? options show help on psql command-line options
? variables show help on special variables
h [NAME] help on syntax of SQL commands, * for all commands
Query Buffer
e [FILE] [LINE] edit the query buffer (or file) with external editor
ef [FUNCNAME [LINE]] edit function definition with external editor
ev [VIEWNAME [LINE]] edit view definition with external editor
p show the contents of the query buffer
reset (clear) the query buffer
s [FILE] display history or save it to file
w FILE write query buffer to file
Input/Output
copy ... perform SQL COPY with data stream to the client host
echo [STRING] write string to standard output
i FILE execute commands from file
ir FILE as i, but relative to location of current script
o [FILE] send all query results to file or |pipe
qecho [STRING] write string to query output stream (see o)
Conditional
if EXPR begin conditional block
elif EXPR alternative within current conditional block
else final alternative within current conditional block
endif end conditional block
Informational
(options: S = show system objects, + = additional detail)
d[S+] list tables, views, and sequences
d[S+] NAME describe table, view, sequence, or index
da[S] [PATTERN] list aggregates
dA[+] [PATTERN] list access methods
db[+] [PATTERN] list tablespaces
dc[S+] [PATTERN] list conversions
dC[+] [PATTERN] list casts
dd[S] [PATTERN] show object descriptions not displayed elsewhere
dD[S+] [PATTERN] list domains
ddp [PATTERN] list default privileges
dE[S+] [PATTERN] list foreign tables
det[+] [PATTERN] list foreign tables
des[+] [PATTERN] list foreign servers
deu[+] [PATTERN] list user mappings
dew[+] [PATTERN] list foreign-data wrappers
df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions
dF[+] [PATTERN] list text search configurations
dFd[+] [PATTERN] list text search dictionaries
dFp[+] [PATTERN] list text search parsers
dFt[+] [PATTERN] list text search templates
dg[S+] [PATTERN] list roles
di[S+] [PATTERN] list indexes
dl list large objects, same as lo_list
dL[S+] [PATTERN] list procedural languages
dm[S+] [PATTERN] list materialized views
dn[S+] [PATTERN] list schemas
do[S] [PATTERN] list operators
dO[S+] [PATTERN] list collations
dp [PATTERN] list table, view, and sequence access privileges
drds [PATRN1 [PATRN2]] list per-database role settings
dRp[+] [PATTERN] list replication publications
dRs[+] [PATTERN] list replication subscriptions
ds[S+] [PATTERN] list sequences
dt[S+] [PATTERN] list tables
dT[S+] [PATTERN] list data types
du[S+] [PATTERN] list roles
dv[S+] [PATTERN] list views
dx[+] [PATTERN] list extensions
dy [PATTERN] list event triggers
l[+] [PATTERN] list databases
sf[+] FUNCNAME show a function`s definition
sv[+] VIEWNAME show a view`s definition
z [PATTERN] same as dp
Formatting
a toggle between unaligned and aligned output mode
C [STRING] set table title, or unset if none
f [STRING] show or set field separator for unaligned query output
H toggle HTML output mode (currently off)
pset [NAME [VALUE]] set table output option
(NAME := {border|columns|expanded|fieldsep|fieldsep_zero|
footer|format|linestyle|null|numericlocale|pager|
pager_min_lines|recordsep|recordsep_zero|tableattr|title|
tuples_only|unicode_border_linestyle|
unicode_column_linestyle|unicode_header_linestyle})
[on|off] show only rows (currently off)
T [STRING] set HTML <table> tag attributes, or unset if none
x [on|off|auto] toggle expanded output (currently off)
Connection
c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently "postgres")
conninfo display information about current connection
encoding [ENCODING] show or set client encoding
password [USERNAME] securely change the password for a user
Operating System
cd [DIR] change the current working directory
setenv NAME [VALUE] set or unset environment variable
iming [on|off] toggle timing of commands (currently off)
! [COMMAND] execute command in shell or start interactive shell
Variables
prompt [TEXT] NAME prompt user to set internal variable
set [NAME [VALUE]] set internal variable, or list all if no parameters
unset NAME unset (delete) internal variable
Large Objects
lo_export LOBOID FILE
lo_import FILE [COMMENT]
lo_list
lo_unlink LOBOID large object operations
18、使用pg_stat_statements檢視TOP SQL
create extension pg_stat_statements;
select query,calls,total_time,total_time/calls from pg_stat_statements order by total_time desc limit 10;
19、檢視膨脹物件
[《PostgreSQL、Greenplum 日常監控 和 維護任務 - 最佳實踐》](../201709/20170913_01.md)
20、檢視鎖等待
[《PostgreSQL 鎖等待監控 珍藏級SQL - 誰堵塞了誰》](../201705/20170521_01.md)
21、檢視錯誤日誌
cd $PGDATA/log
see it
22、檢視慢SQL、長事務、長2PC事務
select * from pg_stat_activity where now()-xact_start > interval `10s` or now()-query_start > interval `10s`;
select * from pg_prepared_xacts ;
23、檢視活躍連線
select count(*) from pg_stat_activity where query <>`IDLE`;
24、檢視有多少資料庫
select * from pg_database;
25、檢視有多少schema
select * from pg_namespace ;
26、檢視schema下的物件
d schema_name.*
27、修改預設schema搜尋路徑
set search_path=.............;
更多請參考PostgreSQL手冊。
參考文件
《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(適合新使用者)》
《PostgreSQL、Greenplum 日常監控 和 維護任務 – 最佳實踐》
《PostgreSQL 鎖等待監控 珍藏級SQL – 誰堵塞了誰》
《PostgreSQL monitor – customize nagios script》
《PostgreSQL monitor – check_postgres usage – 2》
《PostgreSQL monitor – check_postgres usage – 1》
《PostgreSQL monitor – nagios client installation》
《PostgreSQL monitor – nagios server installation》
《PostgreSQL 最佳實踐 – 塊級增量備份(ZFS篇)雙機HA與塊級備份部署》
《PostgreSQL 最佳實踐 – 塊級增量備份(ZFS篇)單個資料庫採用多個zfs卷(如表空間)時如何一致性備份》
《PostgreSQL 最佳實踐 – 塊級增量備份(ZFS篇)備份集自動校驗》
《PostgreSQL 最佳實踐 – 塊級增量備份(ZFS篇)方案與實戰》
《zfs 快照增量恢復 vs PostgreSQL xlog增量恢復 速度》
《zfs 快照增量大小 vs PostgreSQL產生的XLOG大小》
《fast & safe upgrade to PostgreSQL 9.4 use pg_upgrade & zfs》
相關文章
- [zt] 高階複製、流複製（Streams）、備庫區別
- 【PG流複製】Postgresql流複製主備切換SQL
- pg流複製備份
- Oracle 11.2.0.4 awr過期快照無法自動清理Oracle
- 物理備庫互轉快照備庫
- Docker 搭建KingbaseES主備流複製Docker
- PostgreSQL 13 同步流複製+延遲備庫（#2.5）-202104SQL
- Python 自動備份SVN版本庫並複製到遠端主機指令碼Python指令碼
- oracle 流複製Oracle
- shell 備份檔案指令碼+自動清理指令碼
- Mysql 5.6庫級表級複製的搭建MySql
- PostgreSQL 13 非同步流複製+延遲備庫（#2.2）-202103SQL非同步
- GitLab的自動備份、清理備份與恢復Gitlab
- DM7資料複製之資料庫級複製資料庫
- 10 管理物理和快照備庫
- snapshot standby快照備庫角色
- xtrabackup 不用lock tables來複製備庫
- 【BASIS】HANA備份指令碼，Catalog自動清理指令碼
- Postgres 流複製配置
- 手動複製資料庫資料庫
- 【PG流複製】Postgresql流複製部署過程及效能測試SQL
- Postgresql 9.6 搭建 非同步流複製 和 同步流複製 詳細教程SQL非同步
- 2. PostgreSQL 流複製SQL
- Oracle流複製技術Oracle
- pbootcms模板自動清理runtime快取，自動清理快取boot快取
- Redis主備複製Redis
- 資料庫自動備份資料庫
- PostgreSQL 13 級聯流複製部署（#2.7）-202105SQL
- 貝克曼庫爾特釋出Biomek NGeniuS全自動文庫製備系統
- Facebook如何實現PB級別資料庫自動化備份資料庫
- 主庫reset master清理binlog日誌到主從複製錯誤AST
- Centos-Mysql複製備份還原資料庫CentOSMySql資料庫
- oracle sqldeveloper選擇性複製備份資料庫OracleSQLDeveloper資料庫
- Hazel for Mac自動化清理Mac
- Mysql資料庫自動備份MySql資料庫
- 自動備份Oracle資料庫Oracle資料庫
- Data Guard新特性：快照備用資料庫資料庫
- Oracle備份與恢復系列（四）複製資料庫 續 手工複製資料庫的最簡操作Oracle資料庫