PostgreSQL
管理者
修改管理者密碼
~$ sudo su - postgres
~$ psql
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q
新增使用者為管理者
~$ sudo su - postgres
-bash-4.2$ psql -d postgres ### 使用 postgres 資料庫
postgres=# ALTER USER 使用者名稱 WITH SUPERUSER;
使用者
新增使用者
~$ sudo su - postgres
-bash-4.2$ createuser 使用者名稱
查詢使用者
postgres-# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication | {}
資料庫
資料庫列表
~$ sudo su - postgres
~$ psql
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 |
template0 | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
新增資料庫
~$ sudo su - postgres
-bash-4.2$ createdb 資料庫名稱
允許使用者可以管理指定資料庫
~$ sudo su - postgres
-bash-4.2$ psql -d postgres ### 使用 postgres 資料庫
postgres=# ALTER USER 使用者名稱 WITH ENCRYPTED PASSWORD '密碼';
postgres=# GRANT ALL PRIVILEGES ON DATABASE 資料庫名稱 TO 使用者名稱;
資料庫備份
- 一般慣用 pg_dump 來進行備份
- 差別在於備份的格式，例如 -Fd 等同於 --format=d 是將備份資料存放於子目錄下，-f 路徑 指定存放路徑
pg_dump -Fd -f 存放路徑 資料庫名稱
- 而 -Fc 等同於 --format=c 則是採用 PostgreSQL 自訂的格式
pg_dump --verbose --format=c --dbname=資料庫名稱 -f 存放路徑
- 支援格式請參閱 man page 內容，需要注意的是 pg_restore 只支援 c, d, t 三種
-F format
--format=format
Selects the format of the output. format can be one of the following:
p
plain
Output a plain-text SQL script file (the default).
c
custom
Output a custom-format archive suitable for input into pg_restore.
Together with the directory output format, this is the most flexible
output format in that it allows manual selection and reordering of
archived items during restore. This format is also compressed by
default.
d
directory
Output a directory-format archive suitable for input into
pg_restore. This will create a directory with one file for each
table and blob being dumped, plus a so-called Table of Contents file
describing the dumped objects in a machine-readable format that
pg_restore can read. A directory format archive can be manipulated
with standard Unix tools; for example, files in an uncompressed
archive can be compressed with the gzip tool. This format is
compressed by default and also supports parallel dumps.
t
tar
Output a tar-format archive suitable for input into pg_restore. The
tar format is compatible with the directory format: extracting a
tar-format archive produces a valid directory-format archive.
However, the tar format does not support compression. Also, when
using tar format the relative order of table data items cannot be
changed during restore.
資料庫還原
- 一般慣用 pg_restore 來進行備份，須注意格式只支援 c, d, t 三種。
pg_restore --verbose --clean --if-exist --format=格式 --dbname=資料庫名稱 備份檔案名稱
- 使用 c 格式，偶爾會遇到版本號碼不相容的問題
-bash-4.2$ pg_restore --verbose --clean --if-exist --format=c --dbname=資料庫名稱 /tmp/20180410.backup
pg_restore: [archiver] unsupported version (1.13) in file header
系統效能
查詢目前連線數
- 查連線總數
postgres=# SELECT count(*) FROM pg_stat_activity;
count
-------
52
(1 row)
- 依資料庫查詢各別連線數
postgres=# SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;
修改同時連線數上限
查詢同時連線數上限
效能調校
- https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
- [工具] https://github.com/jfcoz/postgresqltuner
- 相依 DBD::Pg 與 DBI 兩個 Perl 模組
# Missing Perl module 'DBD::Pg'. Please install it
yum install -y perl-DBD-Pg
# Missing Perl module 'DBI'. Please install it
yum install -y perl-DBI
# Please install theses Perl modules
- 範例輸出
~$ ./postgresqltuner.pl
postgresqltuner.pl version 1.0.0
Connecting to /var/run/postgresql:5432 database template1 with user postgres...
[OK] User used for report have super rights
===== OS information =====
[INFO] OS: \SKernel \r on an \m
[INFO] OS total memory: 11.73 GB
[BAD] Memory overcommitment is allowed on the system. This can lead to OOM Killer killing some PostgreSQL process, which will cause a PostgreSQL server restart (crash recovery)
[INFO] sysctl vm.overcommit_ratio=50
[BAD] vm.overcommit_memory is too small, you will not be able to use more than 50*RAM+SWAP for applications
[INFO] Running in VMware hypervisor
[INFO] Currently used I/O scheduler(s) : cfq
[BAD] CFQ scheduler is bad on virtual machines (hypervisor and/or storage is already dooing I/O scheduling)
===== General instance informations =====
----- Version -----
[WARN] You are using version 9.4.11 which is not the latest version
----- Uptime -----
[INFO] Service uptime : 10m 42s
[WARN] Uptime is less than 1 day. postgresqltuner.pl result may not be accurate
----- Databases -----
[INFO] Database count (except templates): 7
[INFO] Database list (except templates): postgres (略)
----- Extensions -----
[INFO] Number of activated extensions : 1
[INFO] Activated extensions : plpgsql
[WARN] Extensions pg_stat_statements is disabled
----- Users -----
[OK] No user account will expire in less than 7 days
[WARN] some users account have the username as password : (略)
[OK] Password encryption is enabled
----- Connection information -----
[INFO] max_connections: 300
[INFO] current used connections: 48 (16.00%)
[INFO] 3 are reserved for super user (1.00%)
[INFO] Average connection age : 06m 05s
[WARN] Average connection age is less than 10 minutes. Use a connection pooler to limit new connection/seconds
----- Memory usage -----
[INFO] configured work_mem: 4.00 MB
[INFO] Using an average ratio of work_mem buffers by connection of 150% (use --wmp to change it)
[INFO] total work_mem (per connection): 6.00 MB
[INFO] shared_buffers: 128.00 MB
[INFO] Track activity reserved size : 311.00 KB
[WARN] maintenance_work_mem is less or equal default value. Increase it to reduce maintenance tasks time
[INFO] Max memory usage :
shared_buffers (128.00 MB)
+ max_connections * work_mem * average_work_mem_buffers_per_connection (300 * 4.00 MB * 150 / 100 = 1.76 GB)
+ autovacuum_max_workers * maintenance_work_mem (3 * 64.00 MB = 192.00 MB)
+ track activity size (311.00 KB)
= 2.07 GB
[INFO] effective_cache_size: 4.00 GB
[INFO] Size of all databases : 24.46 GB
[INFO] PostgreSQL maximum memory usage: 17.65% of system RAM
[WARN] Max possible memory usage for PostgreSQL is less than 60% of system total RAM. On a dedicated host you can increase PostgreSQL buffers to optimize performances.
[INFO] max memory+effective_cache_size is 51.74% of total RAM
[WARN] Increase shared_buffers and/or effective_cache_size to use more memory
----- Logs -----
[OK] log_hostname is off : no reverse DNS lookup latency
[WARN] log of long queries is desactivated. It will be more difficult to optimize query performances
[OK] log_statement=none
----- Two phase commit -----
[OK] Currently no two phase commit transactions
----- Autovacuum -----
[OK] autovacuum is activated.
[INFO] autovacuum_max_workers: 3
----- Checkpoint -----
[WARN] checkpoint_completion_target(0.5) is low
----- Disk access -----
[OK] fsync is on
[OK] synchronize_seqscans is on
----- WAL -----
----- Planner -----
[OK] costs settings are defaults
[OK] all plan features are enabled
===== Database information for database template1 =====
----- Database size -----
[INFO] Database template1 total size : 7.12 MB
[INFO] Database template1 tables size : 4.50 MB (63.16%)
[INFO] Database template1 indexes size : 2.62 MB (36.84%)
----- Tablespace location -----
[OK] No tablespace in PGDATA
----- Shared buffer hit rate -----
[INFO] shared_buffer_heap_hit_rate: 98.12%
[INFO] shared_buffer_toast_hit_rate: 0.00%
[INFO] shared_buffer_tidx_hit_rate: 0.00%
[INFO] shared_buffer_idx_hit_rate: 98.77%
[OK] Shared buffer idx hit rate is very good
----- Indexes -----
[OK] No invalid indexes
[OK] No unused indexes
----- Procedures -----
[OK] No procedures with default costs
===== Configuration advices =====
----- checkpoint -----
[MEDIUM] Your checkpoint completion target is too low. Put something nearest from 0.8/0.9 to balance your writes better during the checkpoint interval
----- extension -----
[LOW] Enable pg_stat_statements to collect statistics on all queries (not only queries longer than log_min_duration_statement in logs)
----- sysctl -----
[URGENT] set vm.overcommit_memory=2 in /etc/sysctl.conf and run sysctl -p to reload it. This will disable memory overcommitment and avoid postgresql killed by OOM killer.
----- system -----
[URGENT] Configure your system to use noop or deadline io scheduler when on virtual machines :
echo deadline > /sys/block/sdX/queue/scheduler
update your kernel parameters line with elevator=deadline to keep this parameter at next reboot
----- version -----
[LOW] Upgrade to last version
- 這裡提到的 update your kernel parameters line with elevator=deadline to keep this parameter at next reboot 必須改在 /etc/default/grub 的 GRUB_CMDLINE_LINUX
GRUB_CMDLINE_LINUX="rd.lvm.lv=centos/root rd.lvm.lv=centos/swap rhgb quiet elevator=deadline"
