コンテンツへスキップ

[root@localhost system]# systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since 水 2021-06-23 05:57:55 JST; 1h 9min ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 2260 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 2265 (postmaster)
   CGroup: /system.slice/postgresql-13.service
           tq2265 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/data
           tq2269 postgres: checkpointer
           tq2270 postgres: background writer
           tq2271 postgres: walwriter
           tq2272 postgres: autovacuum launcher
           tq2273 postgres: stats collector
           mq2274 postgres: logical replication launcher

 6月 23 05:57:54 localhost.localdomain systemd[1]: Starting PostgreSQL 13 database server...
 6月 23 05:57:55 localhost.localdomain postmaster[2265]: 2021-06-23 05:57:55.031 JST [2265] LOG:  PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.…しています
 6月 23 05:57:55 localhost.localdomain postmaster[2265]: 2021-06-23 05:57:55.034 JST [2265] LOG:  IPv6アドレス"::1"、ポート5432で待ち受けています
 6月 23 05:57:55 localhost.localdomain postmaster[2265]: 2021-06-23 05:57:55.034 JST [2265] LOG:  IPv4アドレス"127.0.0.1"、ポート5432で待ち受けています
 6月 23 05:57:55 localhost.localdomain postmaster[2265]: 2021-06-23 05:57:55.035 JST [2265] LOG:  Unixソケット"/var/run/postgresql/.s.PGSQL.5432"で待ち受けています
 6月 23 05:57:55 localhost.localdomain postmaster[2265]: 2021-06-23 05:57:55.037 JST [2265] LOG:  Unixソケット"/tmp/.s.PGSQL.5432"で待ち受けています
 6月 23 05:57:55 localhost.localdomain postmaster[2265]: 2021-06-23 05:57:55.041 JST [2268] LOG:  データベースシステムは 2021-06-23 05:57:44 JST にシャットダウンしました
 6月 23 05:57:55 localhost.localdomain postmaster[2265]: 2021-06-23 05:57:55.045 JST [2265] LOG:  データベースシステムの接続受け付け準備が整いました
 6月 23 05:57:55 localhost.localdomain systemd[1]: Started PostgreSQL 13 database server.
Hint: Some lines were ellipsized, use -l to show in full.
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  It is recommended to use systemd
# "dropin" feature;  i.e. create file with suffix .conf under
# /etc/systemd/system/postgresql-13.service.d directory overriding the
# unit's defaults. You can also use "systemctl edit postgresql-13"
# Look at systemd.unit(5) manual page for more info.

# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.

# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-13-setup.
[Unit]
Description=PostgreSQL 13 database server
Documentation=https://www.postgresql.org/docs/13/static/
After=syslog.target
After=network.target

[Service]
Type=notify

User=postgres
Group=postgres

# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.

# Location of database directory
#Environment=PGDATA=/var/lib/pgsql/13/data/
Environment=PGDATA=/var/lib/pgsql/data

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT


# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0

[Install]
WantedBy=multi-user.target

 

キャッシュヒット率、デッドロック発生回数等の確認

psql -U postgres -x -c "select * from pg_stat_database where datnme = 'dvdrental';"

PS E:\> psql -U postgres -x -c "select * from pg_stat_database where datname = 'dvdrental';"
-[ RECORD 1 ]---------+------------------------------
datid                 | 16384
datname               | dvdrental
numbackends           | 0
xact_commit           | 4131
xact_rollback         | 1
blks_read             | 2212583
blks_hit              | 2307441
tup_returned          | 560370952
tup_fetched           | 119685
tup_inserted          | 677037
tup_updated           | 40
tup_deleted           | 37
conflicts             | 0
temp_files            | 0
temp_bytes            | 0
deadlocks             | 0
checksum_failures     |
checksum_last_failure |
blk_read_time         | 0
blk_write_time        | 0
stats_reset           | 2019-09-20 10:16:28.019498+09

・キャッシュヒット率

psql -U postgres -x -c "select (blks_hit * 100.0)/(blks_hit + blks_read) from pg_stat_database where datname = 'dvdrental';"

PS E:\> psql -U postgres -x -c "select (blks_hit * 100.0)/(blks_hit + blks_read) from pg_stat_database where datname = 'dvdrental';"
-[ RECORD 1 ]-----------------
?column? | 62.0830075708206989

・テーブルに対する処理

psql -U postgres -d dvdrental -x -c "select * from pg_stat_user_tables where relname = 'testtable';"

PS E:\> psql -U postgres -d dvdrental -x -c "select * from pg_stat_user_tables where relname = 'testtable';"
-[ RECORD 1 ]-------+------------------------------
relid               | 16391
schemaname          | public
relname             | testtable
seq_scan            | 2293
seq_tup_read        | 492528000
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 993000
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 993000
n_dead_tup          | 0
n_mod_since_analyze | 83000
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    | 2019-09-20 10:50:22.847133+09
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 18

・動作中のバックエンドプロセス情報

psql -U postgres -d dvdrental -x -c "select * from pg_stat_activity;"

PS E:\> psql -U postgres -d dvdrental -x -c "select * from pg_stat_activity;"
-[ RECORD 1 ]----+--------------------------------
datid            |
datname          |
pid              | 2816
usesysid         | 10
usename          | postgres
application_name |
client_addr      |
client_hostname  |
client_port      |
backend_start    | 2019-09-20 10:15:48.140548+09
xact_start       |
query_start      |
state_change     |
wait_event_type  | Activity
wait_event       | LogicalLauncherMain
state            |
backend_xid      |
backend_xmin     |
query            |
backend_type     | logical replication launcher
-[ RECORD 2 ]----+--------------------------------
datid            |
datname          |
pid              | 5456
usesysid         |
usename          |
application_name |
client_addr      |
client_hostname  |
client_port      |
backend_start    | 2019-09-20 10:15:48.335029+09
xact_start       |
query_start      |
state_change     |
wait_event_type  | Activity
wait_event       | AutoVacuumMain
state            |
backend_xid      |
backend_xmin     |
query            |
backend_type     | autovacuum launcher
-[ RECORD 3 ]----+--------------------------------
datid            | 16384
datname          | dvdrental
pid              | 9096
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | ::1
client_hostname  |
client_port      | 53631
backend_start    | 2019-09-23 05:50:09.886686+09
xact_start       | 2019-09-23 05:50:09.942008+09
query_start      | 2019-09-23 05:50:09.942008+09
state_change     | 2019-09-23 05:50:09.942016+09
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 10652
query            | select * from pg_stat_activity;
backend_type     | client backend
-[ RECORD 4 ]----+--------------------------------
datid            | 16384
datname          | dvdrental
pid              | 4392
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | ::1
client_hostname  |
client_port      | 53634
backend_start    | 2019-09-23 05:50:09.889032+09
xact_start       | 2019-09-23 05:50:09.94383+09
query_start      | 2019-09-23 05:50:09.94383+09
state_change     | 2019-09-23 05:50:09.943841+09
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 10652
query            | select count(*) from testtable;
backend_type     | client backend
-[ RECORD 5 ]----+--------------------------------
datid            |
datname          |
pid              | 4628
usesysid         |
usename          |
application_name |
client_addr      |
client_hostname  |
client_port      |
backend_start    | 2019-09-20 10:15:47.657203+09
xact_start       |
query_start      |
state_change     |
wait_event_type  | Activity
wait_event       | BgWriterMain
state            |
backend_xid      |
backend_xmin     |
query            |
backend_type     | background writer
-[ RECORD 6 ]----+--------------------------------
datid            |
datname          |
pid              | 8744
usesysid         |
usename          |
application_name |
client_addr      |
client_hostname  |
client_port      |
backend_start    | 2019-09-20 10:15:48.357302+09
xact_start       |
query_start      |
state_change     |
wait_event_type  | Activity
wait_event       | CheckpointerMain
state            |
backend_xid      |
backend_xmin     |
query            |
backend_type     | checkpointer
-[ RECORD 7 ]----+--------------------------------
datid            |
datname          |
pid              | 7876
usesysid         |
usename          |
application_name |
client_addr      |
client_hostname  |
client_port      |
backend_start    | 2019-09-20 10:15:47.294516+09
xact_start       |
query_start      |
state_change     |
wait_event_type  | Activity
wait_event       | WalWriterMain
state            |
backend_xid      |
backend_xmin     |
query            |
backend_type     | walwriter

 

 

pg_controlファイル:リカバリ時に参照されるファイル。

pg_control -D <データベースクラスタディレクトリ>

データベースクラスタディレクトリのglobalディレクトリ配下に格納されたデータファイルを読むコマンド。

PS E:\my-document> pg_controldata -D $p.dirdbcluster
pg_controlバージョン番号:                    1201
カタログバージョン番号:                      201909212
データベースシステム識別子:                  6858058649328852080
データベースクラスタの状態:                  運用中
pg_control最終更新:                          2019/09/02 5:11:46
最終チェックポイント位置:                    0/131C0460
最終チェックポイントのREDO位置:              0/131C0460
最終チェックポイントのREDO WALファイル:      000000020000000000000013
最終チェックポイントの時系列ID:              2
最終チェックポイントのPrevTimeLineID:        2
最終チェックポイントのfull_page_writes:      オン
最終チェックポイントのNextXID:               0:10705
最終チェックポイントのNextOID:               24576
最終チェックポイントのNextMultiXactId:       1
最終チェックポイントのNextMultiOffset:       0
最終チェックポイントのoldestXID:             479
最終チェックポイントのoldestXIDのDB:         1
最終チェックポイントのoldestActiveXID:       0
最終チェックポイントのoldestMultiXid:        1
最終チェックポイントのoldestMultiのDB:       1
最終チェックポイントのoldestCommitTsXid:     0
最終チェックポイントのnewestCommitTsXid:     0
最終チェックポイント時刻:                    2019/09/02 5:11:46
UNLOGGEDリレーションの偽のLSNカウンタ:       0/3E8
最小リカバリ終了位置:                        0/0
最小リカバリ終了位置のタイムライン:          0
バックアップ開始位置:                        0/C000060
バックアップ終了位置:                        0/0
必要なバックアップ最終レコード:              いいえ
wal_levelの設定:                             replica
wal_log_hintsの設定:                         オフ
max_connectionsの設定:                       100
max_worker_processesの設定:                  8
max_wal_sendersの設定:                       10
max_prepared_xactsの設定:                    0
max_locks_per_xactの設定:                    64
track_commit_timestampの設定:                オフ
最大データアラインメント:                    8
データベースのブロックサイズ:                8192
大きなリレーションのセグメント毎のブロック数:131072
WALのブロックサイズ:                         8192
WALセグメント当たりのバイト数:               16777216
識別子の最大長:                              64
インデックス内の最大列数:                    32
TOASTチャンクの最大サイズ:                   1996
ラージオブジェクトチャンクのサイズ:          2048
日付/時刻型の格納方式:                       64ビット整数
Float4引数の渡し方:                          値渡し
Float8引数の渡し方:                          値渡し
データベージチェックサムのバージョン:        0
認証用の疑似nonce:                           bb1d86aa4d7223431facbc8144ccccc452c0355ce5f9f5ead161b8ad4d9d8303

 

ログ出力設定

postgresql.confの以下パラメータを変更。

logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_min_messages = warning

遅いSQL文(スロークエリ)をログに出力する

デフォルトでは無効になっている以下のパラメータを設定する。

log_min_duration_statement = 1000

1000ミリ秒以上かかったSQLをログに出力する。

特定のユーザが実行したSQL文をログに出力する

log_statementパラメータで制御する。

postgres=# ALTER ROLE postgres SET log_statement = 'all';

よく使うコマンド

  • createdb:データベースを作成する
  • createuser:ユーザを作成する
  • dropdb:データベースを削除する
  • dropuser:ユーザを削除する
  • initdb:データベースクラスタを初期化する
  • pg_controldata <dataディレクトリ>:データベースクラスタの様々な情報を表示する
  • pg_ctl:postmasterを起動・停止する
  • pg_dump:一つのデータベースをバックアップする
  • pg_dumpall:全てのデータベースをバックアップする
  • pg_restore:pg_dumpでダンプしたデータを復元する
  • psql:対話的にSQLを実行する

psql -l:データベース一覧を表示する

  • vacuumdb:vacuumを実行する

ユーザーコマンド

  • ユーザ一覧

testdb=> select * from pg_user;

  • ユーザの追加

$ createuser --interactive testuser

全ての権限を持たないユーザーを作成する。

Shall the new role be a superuser? (y/n) n

Shall the new role be allowed to create databases? (y/n)n

Shall the new role be allowed to create more new roles?(y/n)n

  • ユーザの削除

testdb=> drop user testuser;

  • サンプルコード
# 作成途中

param(
    [parameter(mandatory=$false)][switch]$dbStart,
    [parameter(mandatory=$false)][switch]$dbStop,
    [parameter(mandatory=$false)][switch]$backupStart,
    [parameter(mandatory=$false)][switch]$backupStop,
    [parameter(mandatory=$false)][string]$recovery,
    [parameter(mandatory=$false)][switch]$archiveCleanup,
    [parameter(mandatory=$false)][int]$logCleanup = 30,
    [parameter(mandatory=$false)][switch]$dbStatus,
    [parameter(mandatory=$false)][string]$dbDir
)

. E:\my-document\Programing\powershell\logger.ps1
$log = New-object MyLogger

if($dbDir){
    $dirDbCluster = $dbDir
}

$log.debug("DB管理者:[$dbAdmin]")
$log.debug("DBユーザ:[$dbUser]")
$log.debug("DBクラスターディレクトリ:[$dirDbCluster]")

function isDbRunning([string]$dirDbCluster){
    pg_ctl status -D $dirDbCluster|out-null
    return $?
}

function isBackupRunning([string]$dirDbCluster){
    
}
function dbStartStop([string]$dbAdmin,$dirDbCluster,$dbInit) {
    $argument = ("-U", $dbAdmin, "-D", $dirDbCluster, $dbinit)
    Start-Process -FilePath pg_ctl.exe -ArgumentList $argument -WindowStyle Minimized
    if($?){
        $message = ("Success {0} database.[{1}]" -f $dbInit, $dirDbCluster)
        $log.info($message)
        $retVal = $returnSuccess
    } else {
        $log.error("Error {0} database.[{1}]" -f $dbInit, $dirDbCluster)
        $retVal = $returnErrorValue
    }
    return $retVal
}
function execSql([string]$dbAdmin,$sql){
    #psql -U $dbAdmin -c $sql | Out-Null
}

if($dbStatus){
    if(isDbRunning($dirDbCluster)){
        $log.debug("Database[$dirDbCluster] is running.")
    } else {
        $log.debug("Database[$dirDbCluster] is not running.")
    }
    exit $returnSuccessValue
} elseif($dbStart){
    if(isDbRunning($dirDbCluster)){
        $message = ("Database[{0}] already running" -f $dirDbCluster)
        $log.error($message)
        $retVal = $returnErrorValue      
    } else {
        $retVal = dbStartStop $dbAdmin $dirDbCluster "start"
    }
    exit $retVal
} elseif($dbStop){
    if(isDbRunning($dirDbCluster)){
        $retVal = dbStartStop $dbAdmin $dirDbCluster "stop"
    } else {
        $log.error("Database[$dirDbCluster] is not running.")
        $retVal = $returnErrorValue
    }
    exit $retVal
} elseif($backupStart){
    $sql = "SELECT pg_start_backup(now($dirDbCluster)::text)"
} elseif ($backupStop) {
    $sql = "SELECT pg_stop_backup(now($dirDbCluster)::text)"
} elseif ($recovery) {
    $log.debug("recovery mode")
    $sql = ""
} elseif ($archiveCleanup) {
    $log.debug("Cleanup Archive")
    $sql = ""
} else {
    write-host("usage:")
    exit $returnErrorValue
}