ユーザ用ツール

サイト用ツール


サイドバー

Top

検索タグ

変更履歴

ページ日付
Raspberry Pi で SD カードをオーバークロック🔥💥する方法🤤 2021/09/25 04:03
SD メモリーカード 2021/09/25 02:54
⚖法律関連 2021/09/24 14:15
Raspberry Pi (ラズベリー パイ) 2021/09/24 12:22
DietPi 2021/09/24 12:19
DokuWiki 2021/09/24 11:52
WordPress 2021/09/24 10:33
Linux 関連 2021/09/24 10:31
電子回路 / 電子工作 2021/09/24 09:44
トランジスタ (Transistor) 2021/09/22 23:28
情報源 (データソース) 2021/09/22 07:30
真空管 (Vacuum tube) 2021/09/21 22:24
Objection 2021/09/21 09:54

メニュー


linux:postgresql

PostgreSQL

インストール

Fedoraでは、以下のようにパッケージをインストールする。(Fedora 10, 18, 29 にて確認)

$ sudo dnf install postgresql postgresql-server postgresql-contrib pgadmin3

クラスタの初期化

Fedora の場合

$ sudo postgresql-setup --initdb --unit=postgresql
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

※ –unit=postgresql はデフォルト値で省略できる。

CentOS 6 の場合

サービスを起動する前にデータベースクラスタ(インスタンスで管理されるデータベース集合)を初期化する必要があります。

$ sudo su - postgres
パスワード:
$ initdb
データベースシステム内のファイルの所有者は"postgres"ユーザでした。
このユーザがサーバプロセスを所有しなければなりません。

データベースクラスタはロケール"ja_JP.utf8"で初期化されます。
したがってデフォルトのデータベース符号化方式はUTF8に設定されました。
initdb: ロケール"ja_JP.utf8"用の適切なテキスト検索設定が見つかりません
デフォルトのテキスト検索設定はsimpleに設定されました。

ディレクトリ/var/lib/pgsql/dataを作成しています ... ok
サブディレクトリを作成しています ... ok
デフォルトのmax_connectionsを選択しています ... 100
デフォルトの shared_buffers を選択しています ... 32MB
設定ファイルを作成しています ... ok
/var/lib/pgsql/data/base/1にtemplate1データベースを作成しています ... ok
pg_authidを初期化しています ... ok
依存関係を初期化しています ... ok
システムビューを作成しています ... ok
システムオブジェクトの定義をロードしています ... ok
照合順序を作成しています ... ok
変換を作成しています ... ok
ディレクトリを作成しています ... ok
組み込みオブジェクトに権限を設定しています ... ok
情報スキーマを作成しています ... ok
PL/pgSQL サーバサイド言語をロードしています ... ok
template1データベースをバキュームしています ... ok
template1からtemplate0へコピーしています ... ok
template1からpostgresへコピーしています ... ok

警告: ローカル接続向けに"trust"認証が有効です。
pg_hba.confを編集する、もしくは、次回initdbを実行する時に-Aオプショ
ン、または、--auth-localおよび--auth-hostを使用することで変更するこ
とができます。

成功しました。以下を使用してデータベースサーバを起動することができます。

    postmaster -D /var/lib/pgsql/data
または
    pg_ctl -D /var/lib/pgsql/data -l logfile start

初期化せずにサービスを開始しようとすると、「“service postgresql initdb”を使用して、最初にクラスタを初期化してください。」と言われる。

$ sudo service postgresql start

/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
                                                           [失敗]

CentOS 7 の場合

$ sudo postgresql-setup initdb
Initializing database ... OK

サービスの自動起動設定

Fedora の場合

$ sudo systemctl enable postgresql.service
ln -s '/usr/lib/systemd/system/postgresql.service' '/etc/systemd/system/multi-user.target.wants/postgresql.service'
$ systemctl status postgresql.service
postgresql.service - PostgreSQL database server
	  Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
	  Active: inactive (dead) since 火 2013-03-26 02:42:49 JST; 19s ago
	 Process: 18564 ExecStop=/usr/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
	Main PID: 17775 (code=exited, status=0/SUCCESS)
	  CGroup: name=systemd:/system/postgresql.service

※自動起動は Loaded: 行の最後の enabled を確認。

CentOS の場合

$ sudo chkconfig postgresql on
$ sudo chkconfig --list postgresql
postgresql      0:off   1:off   2:on    3:on    4:on    5:on    6:off

サービスの起動

Fedora の場合

$ sudo systemctl start postgresql.service

CentOS の場合

$ sudo service postgresql start

認証方法の設定

DB に格納された暗号化パスワードで認証

postgres のパスワードを設定する。

$ sudo passwd postgres
ユーザー postgres のパスワードを変更。
新しいパスワード:
新しいパスワードを再入力してください:
passwd: すべての認証トークンが正しく更新できました。
$ su - postgres
パスワード:
$ psql -U postgres
psql (10.6)
"help" でヘルプを表示します。

postgres=# ALTER USER postgres ENCRYPTED PASSWORD 'password';
ALTER ROLE
postgres=# \q
$ exit

/var/lib/pgsql/data/pg_hba.confを修正する。

$ sudo vi /var/lib/pgsql/data/pg_hba.conf
# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

ローカル接続のみパスワードなしでログイン

/var/lib/pgsql/data/pg_hba.confを修正する。

$ sudo vi /var/lib/pgsql/data/pg_hba.conf
# "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

サービスを再起動する。

Fedora の場合

$ sudo systemctl restart postgresql.service

CentOS の場合

$ sudo service postgresql restart
postgresql サービスを停止中:                               [  OK  ]
postgresql サービスを開始中:                               [  OK  ]

データベース操作

データベース作成

$ createdb -E EUC_JP [-U username] databasename   # EUC_JP encoding
$ createdb -E UNICODE [-U username] databasename  # UTF-8 encoding

※-U を指定しない場合はログイン中のユーザーになる。
※ログイン中のユーザーに権限がない場合は -U に権限のあるユーザー名を指定する必要がある。 例:

$ createdb -E UNICODE -U postgres hogehoge_db

データベース削除

$ dropdb [-U username] databasename

SQL実行

$ psql -d database_name -U postgres
Welcome to psql 8.1.23, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

database_name=# select * from pg_tables;
...
database_name=#\q

ユーザー操作

ユーザー作成

$ createuser -a -d -U postgres -P username
新しいロールのためのパスワード:
もう一度入力してください:

ユーザー削除

$ dropuser -U postgres username

パスワード変更

$ psql -U postgres
psql (8.4.11)
"help" でヘルプを表示します.

postgres=#ALTER USER postgres ENCRYPTED PASSWORD 'new password';
postgres=#\q

バックアップ/リストア

$ pg_dump database options > dumpfile.sql

options
-a データのみをダンプ
-b ラージオブジェクトもダンプする。(注: デフォルトではダンプされません)
-d INSERTコマンドにてダンプ(通常はCOPYコマンド)
-D カラム名を含めたINSERTコマンドにてダンプ
-s テーブルのみダンプ
-t 特定のテーブルのみダンプ
-F アウトプットファイルの形式を指定(cがカスタム、tがtar、pがテキスト)
-v 冗長モードを指定(verbose - ダンプファイルに詳細なオブジェクトコメント、開始時刻、終了時刻、進行状況を出力、標準エラーにメッセージ出力)

バックアップ(プレーンテキストの場合)

$ pg_dump databasename -h xxx.xxx.xxx.xxx -U username -v > dumpfile.sql

バックアップ(カスタムアーカイブ形式の場合)

$ pg_dump databasename -h xxx.xxx.xxx.xxx -U username -v -b -Fc > dumpfile.dmp

リストア(プレーンテキストの場合)

$ psql -d databasename -U username -f dumpfile.sql

リストア(カスタムアーカイブ形式の場合)

$ pg_restore -d databasename -U username dumpfile.dmp

データベース再構築

# pg_dump databasename -h xxx.xxx.xxx.xxx -U username -v -b -Fc > dumpfile.dmp  <- まずはフルバックアップ
# dropdb -U username databasename                        <- DBをきれいに消す
# createdb -E EUC_JP -U username databasename            <- DBを新規作成
# pg_restore -d databasename -U username dumpfile.dmp    <- DBを復元する

# su postgres
# createlang plpgsql databasename
# exit

データベース修復/最適化

VACUUMによって破損したデータベースを修復できる。

VACUUM FULL FREEZE VERBOSE ANALYZE
# /etc/rc.d/init.d/postgresql stop    <- サービス停止
# su postgres                         <- postgresユーザに移行
$ postgres -O -P -D /var/lib/pgsql/data databasename <- PostgreSQLをスタンドアロンで起動
 LOG:  database system was shut down at 2008-11-30 01:03:16 JST
 LOG:  checkpoint record is at 0/529CDFD8
 LOG:  redo record is at 0/529CDFD8; undo record is at 0/0; shutdown TRUE
 LOG:  next transaction id: 2879007; next oid: 4195414
 LOG:  database system is ready
  
 POSTGRES backend interactive interface
 $Revision: 1.307.2.2 $ $Date: 2005/06/02 21:04:30 $
 
backend> REINDEX DATABASE databasename FORCE; <- データベースのインデックス再構築
Ctrl+D                                        <- backend終了
# /etc/rc.d/init.d/postgresql start    <- サービス開始

RedHatにおけるpostgresql.confの場所

/var/lib/pgsql/data/postgresql.conf

コンフィギュレーション チューニング

postgresql.confの内容を変更してサービス再起動。

データベースサーバで使用される共有メモリバッファのページ数を設定する。

shared_buffers = 3072

セッションがソート処理およびハッシュテーブルに使用するメモリの最大量をKByte単位で設定する。

sort_mem = 4096

データベース解析 チューニング

データベースの不要領域の回収。データベースの解析オプションを持つ。

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

FULL
 領域の回収だけでなく、より時間のかかる、テーブルの排他的ロックを行なう、 “完全な”vacuum を選択します。

FREEZE
 積極的にタプルの“凍結”を選択します。

VERBOSE
 各テーブルについてvacuumの詳細な活動報告を出力します。

ANALYZE
 オプティマイザが使用する統計情報を更新し、問い合わせを実行する最も効率的な方法を決定できるようにします。

table
 掃除の対象とするテーブルの名前です。デフォルトは全テーブルです。

column
 解析の対象とする列名です。デフォルトは全列です。

破損したインデックスの再構築。

REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]

TABLE
 指定したテーブルの全インデックスを再作成します。

DATABASE
 指定したデータベースの全てのシステムインデックスを再作成します。(ユーザテーブルのインデックスは含まれません。)

INDEX
 指定したインデックスを再作成します。

name
 インデックスの再作成を行なうテーブル/データベース/インデックスの名前を指定します。

FORCE
 強制的にシステムインデックスの再構築を行ないます。このキーワードがなければ、REINDEX は無効とされていないシステムインデックスを処理しません。FORCE を REINDEX INDEX や ユーザインデックスに対する再作成に付与することは意味がありません。

実行例

VACUUM FULL
>クエリーは、147104 ミリ秒で結果なしでうまく帰りました。
REINDEX TABLE tablename
>クエリーは、22064 ミリ秒で結果なしでうまく帰りました。

参考文献

linux/postgresql.txt · 最終更新: 2019/05/18 02:23 (外部編集)