postgresユーザーのパスワードを忘れた場合の手順

ここではpostgres ユーザのパスワードがわからなくなった場合に、再設定する方法を紹介します。

ざっくり手順としては、①pg_hba.confを編集しpostgresユーザーでパスなしでログインできるようにする、②パスワードを再設定する、③pg_hba.confを元に戻すという流れになります。

1. pg_hba.confの変更

pg_hba.confを変更しパスワードなしでログインできるように変更します。

pg_hba.confに「# IPv4 local connections:」の先頭に次の1行を加えて下さい。最後の「trust」はパスワードなしで接続可という意味なので一時的なものということをお忘れなく。

host    all             postgres        127.0.0.1/32            trust

青い行の箇所のようになればOKです。

2. pg_hba.confの設定を適用する

pg_hba.confの設定を適用するためにpg_ctl reloadを実行。次のコマンドはPostgreSQL12の例です（わからなかったらコンピュータごと再起動でも可）。

# PostgreSQL12の場合のreload例
pg_ctl -D "C:\Program Files\PostgreSQL\data" start

3. パスワード設定（ALTER分の実行）

パスワードを変更するALTER分を実行します。

--postgresユーザーのパスワードを'新しいパスワード'へ変更する
ALTER ROLE postgres with PASSWORD '新しいパスワード';

４. pg_hba.confを元に戻し再度reloadを実行

1で加えた1行を削除し、2のreloadを実行でOKです。

手順は以上です。

万が一の場合の手順として捉え頂き、pg_hba.confの変更は戻すことを忘れないでください。

AUTOVACUUMとは

AUTOVACUUMとは、一定時間おきに自動でVACUUMとANALYZEの処理を行うものです。

自動処理ですが、処理対象となるテーブルは、挿入・更新・削除が一定の値を超えたものです。

またAUTOVACUUMでは、テーブルの排他ロックは取得しません。その他、自動VACUUMをする設定にしてあっても、大量のデータ更新や削除の際には、手動バキュームをおすすめします。

AUTOVACUUMの設定

AUTOVACUUMの設定は、postgresql.confの「autovacumm」で設定します。

#はコメントアウトの意味ですが、デフォルトではONとなっているのでこのままで有効です。

# autovacuum = on

システムカタログ（PostgreSQL独自のもの）

データベース内のオブジェクトに関する情報を管理しているものには、システムカタログと情報スキーマがあります。システムカタログとは内部的な情報を格納するテーブルやビューです。

また、システムカタログはPostgreSQL独自のものですが、情報スキーマは標準SQLの機能になり、PostgreSQL以外でも、（対応しているRDBMSの場合）同じSQLで情報を取得できます。

-- テーブル情報の取得
select * from pg_class;

-- ロール情報の取得
select * from pg_roles;

-- 関数情報の取得
select * from pg_proc;

-- ロール情報の取得
select * from pg_role;

-- パラメータ情報の取得
select * from pg_settings;

-- 実行中SQLの取得
select * from pg_stat_activity;

-- テーブルへのアクセス情報の取得
select * from pg_stat_all_tables;

情報スキーマ（information_schema）

情報スキーマは標準SQLの機能です。

PostgreSQL以外でも、（対応しているRDBMSの場合）同じSQLで情報を取得できます。

-- スキーマ一覧
select * from information_schema.schemata;

-- テーブル一覧
select * from information_schema.tables;

-- ビュー一覧
select * from information_schema.views;

-- トリガー一覧
select * from information_schema.triggers;

postmaster.pidがありませんの対処方法

pg_ctlの実行時に、「postmaster.pidがありません　サーバーが動作していますか？」

と表示された時の対処方法です。

現象：pg_ctlのstart、stopをすると上記のエラーが表示される。

結論：pg_ctlのrestartをし直せばうまくいきました。

-- pg_ctlのrestart構文
pg_ctl -D "dataフォルダまでのパス" restart

-- 実行例
pg_ctl -D "C:\Program Files\PostgreSQL\data" restart

ユーザーとロールの違い

まずPostgreSQLでは、ユーザーとロールの概念はありますが、

内部的には全く同じものとして実装されています。

※ 標準的なSQLでは、ユーザーはその名の通りユーザー、ロールは権限の集まりのような位置づけで、

ユーザーには権限を個別に付与したり、ロールでまとめて付与したりすることが可能

CREATE USERとCREATE ROLEの違い

では、本題の「CREATE USERとCREATE ROLEの違い」についてですが、違いは１点だけです。

CREATE USERとCREATE ROLEですが、

内部的にはCREATE USERを実行するとCREATE ROLEを呼び出すようになっています。

しかしCREATE ROLE実行時の「LOGIN」オプションの値が、

CREATE USERの場合、LOGIN（ログイン可能）、

CREATE ROLEの場合、NOLOGIN（ログイン不可）になっている違いがあります。

これがCREATE USERとCREATE ROLEの唯一の違いとなっています。

（全く同じものなことが逆に混乱する原因な気がしますが、そこんとこどうなんでしょうね）

はじめに

PostgreSQL 9、10、11、12のバージョンごと新機能と変更点をまとめました。

PostgreSQL 12の新機能

パーティショニングの強化

11に引き続いて機能が改善・強化されました。

ホットスタンバイ

jsonから抽出する方法にJSON Pathが利用可能に。

前からJSONの要素を抽出する機能はあったが、独自の関数・演算子を使用。

recovery.confがpostgresql.confへ統合

「recovery.conf」ファイルが廃止。スタンバイ関連のパラメータはpostgresql.confへ設定。

with oidsが廃止

with oidsがあるテーブルはそのままアップグレードできない点に注意。

PostgreSQL11の新機能

PostgreSQL11は機能追加というより、既存の機能強化がメインの印象。

パーティショニングの強化

デフォルトパーティションが追加（10では振り分け先がない時にエラーが発生）。

パーティショニングのキーを更新時、あるべきテーブルに自動でデータが移動されるように。

※ 上の機能自体は10で追加されたが11で強化。

 パラレルクエリの強化

SELECT文以外でもパラレルクエリが対応（CREATE INDEX、CREATE TABLEなど）。

PostgreSQL 10 の新機能

ロジカルレプリケーション

PostgreSQL9では、データベース全体をレプリケーションしていたが、

特定のテーブルだけをレプリケーションすることが可能になりました。

 宣言的パーティショニング

CREATE TABLE文のみでパーティションが構築可能に

PostgreSQL 9.1～9.6 の新機能

PostgreSQL 9.1~9.3

PostgreSQL 9.1 ～ 9.3 での主な変更点は次のとおりです。

出典：まとめ読み！ PostgreSQL最近のアップデート

2013年06月28日 18時00分 公開　[正野裕大（SRA OSS, Inc. Japan），＠IT]

https://www.atmarkit.co.jp/ait/articles/1306/07/news007.html

PostgreSQL 9.4

・マテリアライズドビューの改善

・Logical Decoding

・Background Worker

・JSONB（バイナリ型JSONのサポート）

・postgresql.confへのSQLアクセス

・GIN Indexesのコンパクト化、高速化

・WALのパフォーマンス改善

・Streaming Replicationへのdelayオプション実装

PostgreSQL 9.5、9.6

追記予定

PostgreSQL 9.0 の新機能

64ｂｉｔ版　Windowsのサポート

64bit版のWindowsがサポートされました。

32bitはメモリが最大4GBまでのため、大容量メモリのパワーが発揮できるようになります。

ホットスタンバイ

ホットスタンバイとは複数のサーバーを用意しておき、システムの障害発生時に本系から待機系へ

瞬時に切り替える方式で障害発生時のダウンタイムを最小限に留める手法です。

この待機サーバーを参照用として活用できるようになり、参照の負荷分散が可能になりました。

待機サーバで集計処理、pg_dump などの重いクエリを実行するとよさげです。

ストリーミングレプリケーション

ストリーミングレプリケーションとは、稼働サーバの変更情報を待機サーバに転送する機能です。

待機サーバは定期的にデータ更新の差分を受け取り、データベースを複製することができます。

各バージョンのサポート期限（EOL）のまとめ

各バージョンのサポート期限（EOL＝End Of Life）をまとめた記事はこちらです。

https://postgresweb.com/post-5471

プライマリーキーとユニークキーの違い

結論

最初に結論です。

プライマリーキー（PRIMARY KEY）とユニークキー（UNIQUE KEY）の違いは、

null値を保存できるかそうでないかの違いです。ご参考まで。

PRIMARY KEY … null値は保存不可 

UNIQUE  KEY …  null値は保存可

KEYの宣言方法

参考までにプライマリーキーとユニークキーの書き方は次のように書きます。

CREATE TABLE テーブル名(・・・ , 
    CONSTRAINT 主キー名 PRIMARY KEY (列名),
    UNIQUE(列名)
);

pg_dumpの解説です

はじめに、PostgreSQLのpg_dumpとは、バックアップを取得するコマンドなのですが、

オプションが死ぬほど多い、サイトによっても書き方が違う等々、正直私には難しいものでした。

それでもいろいろ調べ、試行錯誤しながら理解したことを残したいと思います。

全くわからない人向けに書きますのでご理解頂ければ幸いです。

pg_dumpとは

まず、pg_dumpとはデータベースのバックアップを行うPostgreSQLのコマンドです。

pg_dumpを実行するためには、次の例のように「pg_dump」を先頭に記述し、

その後にこのような記述の形になります（サイトによっていろいろな表記がありますが）。

pg_dump XXXX XXX                 # いろいろな書き方あり（後述）
pg_dump --file=test.sql testdb   # 例えばこんな感じ

そもそも「pg_dump」って何？どこの誰よ？というと…

次の場所にあるのがpg_dumpの正体です。

・Windowsの場合

「C:\Program Files\PostgreSQL\XX\bin\pg_dump.exe」　※XXはバージョン

PostgreSQLのインストール先を特に変更していなければこのパスになるはずです。

※ 画面はPostgreSQL12の場合。pg_dump.exeと同じフォルダにあります。

・Linux（Ubuntu Server）の場合

「/usr/bin/pg_dump」にあります。

pg_dumpとpg_dumpallの違い

pg_dumpを探した時、pg_dumpallという似た名前があることに気づいたでしょうか。

pg_dumpもpg_dumpallも同じバックアップを取る時に使うものですが、目的が若干異なります。

・pg_dump    … 一つのデータベースのバックアップ取得する

・pg_dumpall … すべてのデータベースのバックアップやユーザー（＝ロールという）を取得する

pg_dumpは内部的にpg_dumpを複数回行っている形になります。

そのためpg_dumpのほうに焦点をあてて説明します。

補足．pg_dumpが認識されない時

pg_dumpを実行しようとした時、

「pg_dumpは、内部コマンドまたは外部コマンド、操作可能な…認識されません」

というメッセージが表示されるケースがあります。

コマンドの打ち間違いでない限り、理由はこのどちらかだと思います。

① 環境変数（Path）に設定されていない

② pg_dumpのある場所で実行していない

対処方法はこちらで紹介していますので、困った場合は参考にしてみて下さい。

ｰｰUについて

pg_dumpのコマンド解説（-Uについて）

ここからはpg_dumpのコマンドを1つ1つ解説します。

pg_dumpのコマンドの基本形は次のような形になります。

# pg_dumpのコマンドの基本形
pg_dump -U ユーザー名 --format=出力形式 --file=出力先 バックアップを取るDB名

# 実際のコマンド例
pg_dump -U postgres --format=p --file=c:\backup\test.sql testdb

（他のサイトと書き方が違くない？とか思った人がいるかもしれないですが、

少しずつ解説していきますのでお付き合いください。。）

実際のコマンド例を基にします。

「-U postgres」の箇所は、postgresユーザーで実行することを示していますが、

すでにpostgresユーザーでログインしている時は不要な（＝省略可能な）箇所です。

# 実際のコマンド例
pg_dump -U postgres --format=p --file=c:\backup\test.sql testdb

 # すでにログインしているときには-Uが不要
pg_dump --format=p --file=c:\backup\test.sql testdb

ｰｰformatについて

formatで指定できる4パターンについて

次に「--format=p」の箇所についてです。formatで指定できるのは4パターンです。

① --format=p：SQL文のテキストファイルで出力される（データも含みます）。※ p=plain

② --format=c：カスタム形式（＝簡単にいうと圧縮されたバックアップファイルのこと）

③ --format=t：tar形式

④ --format=d：ディレクトリ形式（テーブル単位でのカスタム形式）

※ ①がデフォルト値（formatを指定しない場合は=pと同じ意味になります。）

※ ④のみ--jobsというオプションを使うと並列バックアップが可能

①をスクリプト形式というのに対し、②～④の形式をバイナリ形式と呼びます。

補足ですが、①はデフォルト値のため「--fomat=p」は省略できます。

②は圧縮されたバックアップファイルのため、

他の①、③と比べてもファイルのサイズがかなり小さくなります。

③のtar形式とは、ざっくりいうと複数のファイルが1つになったもののことです。

tar自体はPostgreSQL独自ではないので調べてみて下さい。

④はフォルダが作成されその中にファイルが複数出力される形になります。

※いずれの方法も後半で実行結果を紹介しています。

# ①--format=p : SQL文のテキストファイルで出力（次の2行は同じ意味）
pg_dump -U postgres --format=p --file=c:\backup\test1.sql testdb1
pg_dump -U postgres --file=c:\backup\test1.sql testdb1   # format=pは省略可

# ②--format=c : カスタム形式のファイルで出力
pg_dump -U postgres --format=c --file=C:\backup\test2.custom testdb2

# ③--format=t : tar形式
pg_dump -U postgres --format=t --file=C:\backup\test3.tar testdb3

# ④--format=d : ディレクトリ形式
pg_dump -U postgres --format=d --file=C:\backup\test4 testdb4

--formatは書き換え可能

さらに「--format=」は「-F」に書き換えが可能です。ｰFはハイフンが一つのため注意。

それぞれ書き換えると次のようになります。

① --format=p   =   -Fp

② --format=c   =   -Fc

③ --format=t   =   -Ft

④ --format=d   =  -Fd

# --format=p : SQL文のテキストファイルで出力（次の3行は同じ意味）
pg_dump -U postgres --format=p --file=c:\backup\test1.sql testdb1
pg_dump -U postgres -Fp --file=c:\backup\test1.sql testdb1
pg_dump -U postgres --file=c:\backup\test1.sql testdb1   # format=pは省略可

# --format=c : カスタム形式のファイルで出力（次の2行は同じ意味）
pg_dump -U postgres --format=c --file=C:\backup\test2.custom testdb2
pg_dump -U postgres -Fc --file=C:\backup\test2.custom testdb2

# --format=t : tar形式（次の2行は同じ意味）
pg_dump -U postgres --format=t --file=C:\backup\test3.tar testdb3
pg_dump -U postgres -Ft --file=C:\backup\test3.tar testdb3

# --format=d : ディレクトリ形式（次の2行は同じ意味）
pg_dump -U postgres --format=d --file=C:\backup\test4 testdb4
pg_dump -U postgres -Fd --file=C:\backup\test4 testdb4

4パターンの出力方法の使い分け

CPUのリソースを必要としますが、圧縮された形式で取得したい、早くバックアップを

終わらせたいといった場合には、②カスタム形式または④ディレクトリ形式を選択。

特に速さ重視なら並列バックアップ可能なディレクトリ形式のほうがよいと思います。

CPUリソースを使いたくない時は、①テキスト形式または③tar形式を選択します。

一般的におすすめは②カスタム形式です。

理由は、リストア時に（テーブル単位等での展開ができる等の）柔軟性があること、

必要であればテキスト形式へ変換可能なことが挙げられています。

--fileについて

指定するファイル名

「--file=c:\backup\test.sql」の箇所ですが、フルパス、ファイル名どちらでもOKです。

フルパスでなくファイル名だけを指定すると、コマンドプロンプトで現在いるフォルダ

（カレントディレクトリ）に出力されます。

pg_dump -U postgres --format=p --file=c:\backup\test1.sql testdb1 # フルパス
pg_dump -U postgres --format=p --file=test1.sql testdb1           # ファイル名のみ

拡張子

ぶっちゃけ拡張子はなんでもいいです（たぶん）。

ただ次のようになることが多いので、合わせておいた方が無難かと。

① テキスト形式：「.sql」

② カスタム形式：「.dump」、「.custom」、「.backup」、「.dmp」

③ tar形式：拡張子をつけない、「.tar」

④ ディレクトリ形式：拡張子をつけない

実行確認

最後に4パターン（テキスト形式~ディレクトリ形式）で実行し、

どのような結果になるか簡単ですが紹介します。

テスト環境の用意

バックアップ動作確認用の環境を作成します。

余談ですがテスト環境を作成するには、pgbenchを使った便利な方法があります。

大量のテストデータを用意する場合などにも使えますので参考にしてみて下さい。

① テキスト形式でのバックアップ実行

念のため3パターンで実行

# --format=p : SQL文のテキストファイルで出力（次の3行は同じ意味）
pg_dump -U postgres --format=p --file=C:\BackupTest\test1_1.sql testdb
pg_dump -U postgres -Fp --file=C:\BackupTest\test1_2.sql testdb
pg_dump -U postgres --file=C:\BackupTest\test1_3.sql testdb

実行結果確認。3つとも問題なく実行可能。

メモ帳で開くとこんな感じ。SQLがずらずらと並びます。

② カスタム形式

2パターンで実行

# --format=c : カスタム形式のファイルで出力（次の2行は同じ意味）
pg_dump -U postgres --format=c --file=C:\BackupTest\test2_1.custom testdb
pg_dump -U postgres -Fc --file=C:\BackupTest\test2_2.custom testdb

問題なく実行完了。

サイズはテキスト形式と比べ明らかに減っています。

メモ帳では、よくわからない形になります。

③ tar形式

2パターンで実行

# --format=t : tar形式（次の2行は同じ意味）
pg_dump -U postgres --format=t --file=C:\BackupTest\test3_1.tar testdb
pg_dump -U postgres -Ft --file=C:\BackupTest\test3_2.tar testdb

こちらも問題なく実行完了。

サイズはテキスト形式と同じくらいになりました。

④ ディレクトリ形式

最後にディレクトリ形式です。

# --format=d : ディレクトリ形式（次の2行は同じ意味）
pg_dump -U postgres --format=d --file=C:\BackupTest\test4_1 testdb
pg_dump -U postgres -Fd --file=C:\BackupTest\test4_2 testdb

フォルダが作成され、その中にいくつかファイルができます。

フォルダの中身はこんな感じです。

以上、pg_dumpの解説でした。

少しでも参考になりましたらうれしい限りです。

リストアについて

リストアの方法についてはこちらで紹介しています。

https://postgresweb.com/post-4279

テストデータを作成する

動作検証等で簡単かつ素早く、テストデータを作成したいケースがあると思います。

（バックアップ等の動作確認に使用されるケースもあります。）

pgbenchという標準ツールを使い、「pgbench」というコマンドで実行できます。

pgbenchのありか

「pgbench」は「pg_dump」や「psql」と同じところにあります。

Windowsの場合：「C:\Program Files\PostgreSQL\XX\bin\」の中

Ubuntuの場合  ：「/usr/bin/」の中

テストデータを作成するコマンド

テスト用のデータベース（ここではtestdb）を作成します。

$ createdb testdb
$ createdb -U postgres testdb   # ユーザーを指定する場合

データを作成するコマンドは次のとおりです。

$ pgbench --initialize --scale=10 testdb

pgbench     …   ベンチマークツール（pgbench.exeのこと）

--initialize  …  テーブルの初期化

--scale         … 「scale=数値」で指定する。scale=1につきテーブルのサイズ15MB。

コマンド実行し「done.」と表示さればOKです（データの作成が完了しました）。

確認①：完了すると4つのテーブルが作成されます。

確認②：下の例では100万件ほどデータが登録されていることを確認できます。

以上、pgbenchを使用したデータの作成方法です。

locale（ロケール）とは

データベースクラスタの作成時などに登場するlocale（ロケール）とはについて解説します。

localeとは「地域の言語に応じた処理をしてくれるOSの仕組み」です。

日付や通貨の表示処理、文字列の並び替えの順序で使用します。

一例ですがlocaleはこんな感じで登場します。

$ initdb --no-locale --encording=UTF8

とはいっても、PostgreSQLではlocaleの使用は非推奨です。

localeを設定すると、そのlocale依存した動きになり、

別の環境に移行しづらくなってしまうためとのこと。

ちなみにロケールを使用しないことを「--no-locale」と記述しますが、

「--locale=C」としても全く同じ意味です。