トップページ > コンテンツ > データベース関連メモ > PostgreSQL

用語説明

WAL	PostgreSQLの全ての更新ログ
LSN	WALを識別する番号。xlogid+xrecoff
	タイムラインIDはPoint In Time Recovery（PITR)を実現するために実装された仕組みで、PITRが実行されるたびに値が1ずつ大きくなり
CHECKPOINT	WALの内容のうち、ディスクに書き込みを終えたポイント。復旧時はCHEKPOINT以前のWALは無視して復旧できる
OID	オブジェクトID
XID	トランザクションID

主なフォルダや設定ファイル構成例

ディレクトリ					説明
/var	/lib	/pgsql	/data	postgresql.conf	postgresの設定ファイル。showコマンドで設定値を見ることができる。setコマンドで設定値を設定することもできる(一部パラメータのみ)
				pg_hba.conf	認証情報を設定するファイル
				recovery.conf	リカバリを行う際に作成するファイル
				pg_log	ログファイルの格納場所。.historyはどのようにタイムラインが移動したかの記録
				pg_xlog	WALファイルの格納場所。
				base	データベース本体の在処。ディレクトリ名称はデータベースのOID
archives					WALアーカイブの格納場所。pgsql.confのarchive_commandで指定

※バージョンとかで微妙にパスは異なる。/etc/postgres/バージョン/main/か/var/lib/下を見る感じが多い。

コマンド類

データベースクラスタ作成

データベースクラスタ構築(パッケージからインストール。systemd系)	postgresql-setup --initdb	$PGDATA(/var/lib/pgsql/data)が生成される。ロケールは無効、文字コードはutf8を使うことが多いかも
データベースクラスタ構築(ソースコードからinstall)	initdb --encoding=UTF8 --no-locale データベースクラスタを作成する場所
データベースクラスタ構築(Ubuntuの場合)	pg_createcluster --start Postgreバージョン クラスタ名	クラスタ一覧を見る時はpg_lsclusters。削除する場合はpg_dropcluster --stop Postgreバージョンクラスタ名

起動停止系

起動	pg_ctl start
停止	pg_ctl stop
利用開始	psql -U ユーザ名	複数のデータベースクラスタがある場合は--portでポート番号を切り替える
利用終了	¥q;
sqlファイルを指定して実行	¥i 実行したいsql; または psql -f

ユーザ操作

ユーザの作成	¥create user ユーザ名;
ユーザの切替	¥connect - ユーザ名;
ロール一覧の表示	¥du;
作成したロールの確認	select * from pg_pgroles;

スキーマ・データベース操作

スキーマを指定	set search_path="hogehoge"
データベースを作成	create database データベース名 encoding '文字エンコード名';	create databaseのラッパーのcreatedbを使うこともある
データベースを選択	¥c データベース名;
データベースを確認	psql -l または ¥l;

テーブル操作

テーブル作成	create table( カラム名 データ型, カラム名 データ型 );
テーブル構造を見る	¥d;
テーブル一覧を見る	¥dt;
データ検索	select 探したいもの from テーブル名　条件等;	文字列は''で囲うと良い。また、テーブル名は大文字を含む場合は""で囲う必要がある。
テーブルへの権限付与	grant アクセスの種類(select等) on テーブル名 to ユーザ名
テーブルから権限剥奪	revoke アクセスの種類(select等) on テーブル名 from ユーザ名

データのインポート・エクスポート

CSVからデータ投入	¥copy テーブル名(カラム1,カラム2,...) from 'csvファイル名' with csv
データをCSVに出力	¥copy テーブル名 出力ファイル名.csv csv

トランザクション処理

トランザクションの開始	begin;
トランザクションのコミット	commit;
トランザクション処理をキャンセルして終了	rolllback;またはabort;
行ロック(排他ロック)	SQL文 for update;
行ロック(共有ロック)	SQL文 for share;
テーブルロック	lock table テーブル名;

レプリケーション関連

概要	実行サーバ	コマンド
プライマリとスタンバイの識別		ps auxでwal senderが動いているのがプライマリ。wal receiverが動いてるのがスタンバイ
接続中のスタンバイの一覧とその状況を確認	プライマリ側	psql -x -c "select * from pg_stat_replication"
	スタンバイ側	psql -x -c "select * from pg_stat_wal_receiver"
FOさせる	プライマリ側(擬似故障)	pg_ctl -m immediate stop
	スタンバイ側	pg_ctl promote

WAL関連

WALのlocation確認	プライマリー側	psql -x -c "SELECT * FROM pg_current_xlog_location()"
	スタンバイ側	psql -x -c "SELECT * FROM pg_last_xlog_receive_location()"
現在のWALファイル名確認		psql -x -c "SELECT pg_walfile_name(pg_current_wal_lsn());"
現在のLSN確認		psql -x -c "SELECT pg_current_wal_lsn();"
WALの中身確認		pg_waldump

運用メンテナンス

やりたいこと	コマンド	備考
文字コードを修正	postgresql.confのlc_messageの記述を変更することで文字コードを修正できる。
不要領域の回収	vacuum
実行時間を調べる	explain,explain analyze等

バックアップ・リストア

バックアップの種類	コマンド例		備考
論理バックアップ(SQLファイル保存)	pgdump データベース名 > バックアップ名
論理リストア	psql データベース名 < dumpファイル
物理オンラインバックアップ	pg_basebackup -D 出力先 -F tar -z		稼働中状態の時に予めtar形式でバックアップを取っておく例
物理オンラインリストア	1.データベースクラスタの削除	rm -r /var/lib/pgsql/data/*
	2.basebackupした内容を展開 (2行のうちどちらかを実行で/var/lib/pgsql/data/相当を復元)	tar xvfz tarファイル	予めtarでバックアップしたものを展開する場合(/var/lib/pgsql/data/相当が保存できる)
		pg_basebackup -h プライマリサーバのIPアドレス -p ポート番号 -D データの場所 -U レプリケーション用ユーザ	稼働系からデータを持ってくる場合の例
	3.basebackup時点の古いWALは削除	rm -rf /var/lib/pgsql/data/pg_wal/*
	4,最新のWALファイルコピー	cp -p 最新のアーカイブディレクトリ /var/lib/postgresql/pg_xlog
	5.recovery.confの作成	vi /var/lib/pgsql/data/recovery.conf	restore_commandでWALファイルを指定
	6.DB再開	pg_ctl start
物理オフラインバックアップ	tar zcf バックアップ名.tar.gz /var/lib/pgsql
物理オフラインリストア	cd / tar zxf バックアップファイル

※pg_basebackupの主なオプションついてはこちら参照。

pg_control ファイルの内容を表示する	pg_controldata -D /var/lib/pgsql/data	以下のような情報が閲覧できる。 ・WALの開始時点(CHECK POINT) ・タイムラインID
旧プライマリサーバと新プライマリサーバの差分を同期	pg_rewind	旧プライマリサーバが新スタンバイサーバとして追従する場合に使用する。Postgresql9.5以降。

その他

やりたいこと	コマンド	備考
ヘルプを開く	¥h; postgres=#¥?