= PostgreSQL = [[PageOutline]] * psql 指令說明 - https://www.postgresql.org/docs/current/static/app-psql.html == 修改管理者密碼 == {{{ ~$ sudo su - postgres ~$ psql postgres=# \password postgres Enter new password: Enter it again: postgres=# \q }}} == 資料庫列表 == {{{ ~$ 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$ createuser 使用者名稱 }}} == 新增資料庫 == {{{ ~$ 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 PRIVILEDGES ON DATABASE 資料庫名稱 TO 使用者名稱; }}} == 新增使用者為管理者 == {{{ ~$ sudo su - postgres -bash-4.2$ psql -d postgres ### 使用 postgres 資料庫 postgres=# ALTER USER 使用者名稱 WITH SUPERUSER; }}} == 查詢目前連線數 == * 查連線總數 {{{ postgres=# SELECT count(*) FROM pg_stat_activity; count ------- 52 (1 row) }}} * 依資料庫查詢各別連線數 {{{ postgres=# SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname; }}} == 修改同時連線數上限 == * 修改 postgresql.conf {{{ $ sudo grep max_connections /var/lib/postgresql/postgresql.conf max_connections = 300 # (change requires restart) # Note: Increasing max_connections costs ~400 bytes of shared memory per # max_locks_per_transaction * (max_connections + max_prepared_transactions) }}} == 查詢同時連線數上限 == * 使用 `SHOW` 指令 {{{ postgres=# SHOW max_connections; }}} * 或從 `pg_settings` 資料表查詢 {{{ postgres=# SELECT * FROM pg_settings WHERE name = 'max_connections'; }}} == 效能調校 == * 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 ssp_jenkins ssp_dev ssp_stage ads_test ads_dev ads_beta ----- 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 : ssp_jenkins,ssp_dev,ssp_stage,cmax_dev,cmax_stage,ads_test,ads_dev,ads_beta [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 }}}