psql による PostgreSQL サーバへのログイン・接続方法

方法 1. デフォルト値

psql

方法 2. コマンドの引数

-U, -d は省略して書けます。

psql ユーザ名 データベース名

psql -U ユーザ名 -d データベース名

psql -h ホスト -p ポート ユーザ名 データベース名

psql -h ホスト -p ポート -U ユーザ名 -d データベース名

• psql - PostgreSQL: Documentation

◯ パスワードを忘れた場合

pg_hba.conf を開き peer または md5 から trust に書き換えます。パスワードが無効になります。

# 変更前
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5

# 変更後
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust

詳細はリンク先をご確認ください。

• PostgreSQL パスワード 忘れた - Google 検索結果

◯ Docker を使用している場合

ご自身のローカルのパソコンで Docker を使って PostgreSQL サーバを立ち上げている場合は、たとえローカルホストであっても -h localhost または -h 127.0.0.1 を明示してログインしてください。そうしないと以下のようなエラーに遭遇します。理由も記載しました。

• "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory - Qiita

方法 3. パスワードファイル

この「方法 3. パスワードファイル」はログインするたびにユーザ名などを入力するのが面倒な場合は便利な気がしますが...

◯ ファイルの書式

コロン : で区切ります。

ホスト:ポート:データベース名:ユーザ名:パスワード

◯ ファイルの場所

1. macOS, Ubuntu の場合
  ~/.pgfile

2. Windows の場合
  %APPDATA%\postgresql\pgpass.conf

3. あるいは環境変数 PGPASSFILE でファイルパスを指定できます。

方法 4. 環境変数

この「方法 4. 環境変数」も便利な気がしますが...

export PGHOST=ホスト
export PGPORT=ポート番号
export PGDATABASE=データベース名
export PGUSER=ユーザ名
export PGPASSWORD=パスワード
psql

set PGHOST=ホスト
set PGPORT=ポート番号
set PGUSER=ユーザ名
set PGDATABASE=データベース名
set PGPASSWORD=パスワード
psql

$env:PGHOST="ホスト"
$env:PGPORT="ポート番号"
$env:PGUSER="ユーザ名"
$env:PGDATABASE="データベース名"
$env:PGPASSWORD="パスワード"
psql

• 33.14. Environment Variables - PostgreSQL: Documentation

◯ ワンライナーでログインしたい

環境変数 PGPASSWORD を使った、つぎのような書き方があるそうです。

PGPASSWORD=パスワード psql -U ユーザ名 データベース名

# ワンライナーでは書けないので２行で書いてください...orz
set PGPASSWORD=パスワード
psql -U ユーザ名 データベース名

$env:PGPASSWORD="パスワード"; psql -U ユーザ名 データベース名

• How do I specify a password to 'psql' non-interactively? - Stackoverflow

まとめ

PostgreSQL サーバへのログイン、接続する際にホスト名等を指定する方法は以下の４通りがあります。

値が指定されなかった場合、デフォルトの値が適用されます。プロンプト、パスワードファイル、環境変数でそれぞれ別の値を指定した場合、どの値が優先されるのかは調べてないので分かりません。

psql ってなんだ？

以下の２つがポイントかなと思いました。

1. psql クライアントと PostgreSQL サーバを区別する
2. psql クライアントと psql コマンドを区別する

psql クライアントは PostgreSQL サーバを操作するための対話端末 interactive terminal です。psql クライアンはそれ１つでアプリとして動作します。

psql - PostgreSQL interactive terminal

psql クライアントは、自分のパソコンのなかで動いている PostgreSQL サーバだけではなく、ネットワーク上にいるほかのパソコンで動いている PostgreSQL サーバも操作できます。これは psql コマンドでホスト名やポート番号を指定できることからもわかります。

そして psql クライアントは psql コマンドで起動することができます。psql はクライアントです。データベースが動いているサーバとは違うものです。ややこしいのですがサーバとクライアントを区別すると良いような気がします。

以下のリンクの中で "PostgreSQL Client Applications" の一覧の中に psql コマンドがあるのがわかります。つまり psql はクライアントということです。

PostgreSQL Client Applications - PostgreSQL: Documentation

自分は サーバとクライアントが別物であると言う意識が希薄になっていました。これは psql コマンドが、なにも指定しないと自動的に localhost, 127.0.0.1 にある PostgreSQL サーバに接続されるからです。

Python での PostgreSQL 接続方法

もはや psql 関係ないですが...

◯ psycopg2 の書式

import psycopg2

connection = psycopg2.connect(
    user="postgres",
    password="secret",
    host="localhost",
    dbname="postgres"
)

◯ SQLAlchemy の書式

from sqlalchemy import create_engine

# dialect[+driver]://user:password@host/dbname[?key=value..]
URL = "postgresql+psycopg2://ユーザ名:パスワード@ホスト名/データベース名"
engine = create_engine(URL)

URL は RFC1738 に定められた書式だそうです（PEP 249 で定められてはいない、Python に限定して言えば SQLAlchemy の中だけで使われていそうな気配を感じます）。

The string form of the URL is dialect[+driver]://user:password@host/dbname[?key=value..], where dialect is a database name such as mysql, oracle, postgresql, etc., and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle, etc. Alternatively, the URL can be an instance of URL.

• https://docs.sqlalchemy.org/en/13/core/engines.html#database-urls