wiki:jazz/PostgreSQL

Version 10 (modified by jazz, 7 years ago) (diff)

--

PostgreSQL

  1. PostgreSQL
    1. 修改管理者密碼
    2. 資料庫列表
    3. 新增使用者
    4. 新增資料庫
    5. 允許使用者可以管理指定資料庫
    6. 新增使用者為管理者
    7. 查詢目前連線數
    8. 修改同時連線數上限
    9. 查詢同時連線數上限
    10. 效能調校
    11. 資料庫備份

修改管理者密碼

~$ 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 (略)
-----  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"

資料庫備份

  • 一般慣用 pg_dump 來進行備份
  • 差別在於備份的格式，例如 -Fd 等同於 --format=d 是將備份資料存放於子目錄下，-f 路徑 指定存放路徑 
    pg_dump -Fd -f 存放路徑 資料庫名稱
  • 而 -Fc 等同於 --format=c 則是採用 PostgreSQL 自訂的格式 
    pg_dump --verbose --format=c --dbname=資料庫名稱 -f 存放路徑