Fedoraでは、以下のようにパッケージをインストールする。(Fedora 10, 18, 29 にて確認)
$ sudo dnf install postgresql postgresql-server postgresql-contrib pgadmin3
$ 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 はデフォルト値で省略できる。
サービスを起動する前にデータベースクラスタ(インスタンスで管理されるデータベース集合)を初期化する必要があります。
$ 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. [失敗]
$ sudo postgresql-setup initdb Initializing database ... OK
$ 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 を確認。
$ sudo chkconfig postgresql on $ sudo chkconfig --list postgresql postgresql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
$ sudo systemctl start postgresql.service
$ sudo service postgresql start
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
サービスを再起動する。
$ sudo systemctl restart postgresql.service
$ 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
$ 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.
/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 ミリ秒で結果なしでうまく帰りました。