目次
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 <- サービス開始
パッケージ更新後のデータベースアップグレード
$ sudo postgresql-setup --upgrade
* Upgrading database. * Upgraded OK. WARNING: The configuration files were replaced by default configuration. WARNING: The previous configuration and data are stored in folder WARNING: /var/lib/pgsql/data-old. WARNING: If you've just upgraded your database from a previous major version of Fedora or RHEL, please run reindexdb against your databases. Core library collation data may have changed and this will invalidate database indexes. For example, in Fedora 28 and RHEL 8 there have been extensive changes in glibc collations to support ISO 14651:2016 (Unicode 9.0.0 data) and your indexes may be affected: https://sourceware.org/ml/libc-announce/2018/msg00002.html * See /var/lib/pgsql/upgrade_postgresql.log for details.
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 ミリ秒で結果なしでうまく帰りました。