Postgres リモートホストからの接続他

今まで使ってきたデータベースはMySQLがほとんどなのですが、最近少し、Postgresを触っています。

今回はパスワードの設定とリモートからの接続など。

Contents

1 postgresへのログイン
2 メモリ不足による起動の失敗
- 2.1 リモートからの接続

postgresへのログイン

postgresへのログインですが、いつも

#su - postgres　

#ハイフンをつけるとユーザーのシェルも切り替わりディレクトリの位置は /var/lib/pgsql　になる
-bash-4.2$ psql
postgres=# (ここからいつもどおりにSQLが打てる)

#su - postgres　

#ハイフンをつけるとユーザーのシェルも切り替わりディレクトリの位置は /var/lib/pgsql　になる

-bash-4.2$ psql

postgres=# (ここからいつもどおりにSQLが打てる)

とコマンドをうっていました。これはロール(MySQLでいうところのユーザー)、postgres, データベースpostgresを選択しています。

ロールやデータベースを選択したい場合

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

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

になります。ここでユーザー名、データベース名にpostgresをいれてログインしようとしましたが、認証エラーが発生。

psql: FATAL: Peer authentication failed for user "postgres"

1	psql: FATAL: Peer authentication failed for user "postgres"

どうやら設定ファイルを正確に設定していないとおこるエラーのようです。

下記のように設定ファイルを修正

＃このファイルに認証方法がかかれています。
#インストールの仕方によってファイルのパスが違うようなので、
#findコマンドを使って確認しましょう。
vim /var/lib/pgsql/data/pg_hba.conf

#この部分を下記のように修正
#左からホストのタイプ、データベース、データベースのユーザー、ログインのメソッドの意味になります。
(修正前)local   all   all     peer  
(修正後)local   all   postgres md5  

＃再起動
#これもインストール方法で異なるようです。
#service postgres restart
#/etc/rc.d/init.d/postgresql restar
#などで再起動できないことがあります。
#その場合、下記コマンドで再起動します。
su postgres
pg_ctl restart -D /var/lib/pgsql/data

#このあとロールやデータベースを選択したコマンドで再度ログインすればパスワードをきかれます。
#パスワードは設定していなければpostgresになるかと思います。
#設定したい場合は下記コマンドで。
su - postgres
-bash-4.2$ psql
postgres=# \password postgres

＃このファイルに認証方法がかかれています。

#インストールの仕方によってファイルのパスが違うようなので、

#findコマンドを使って確認しましょう。

vim /var/lib/pgsql/data/pg_hba.conf

#この部分を下記のように修正

#左からホストのタイプ、データベース、データベースのユーザー、ログインのメソッドの意味になります。

(修正前)local all all peer

(修正後)local all postgres md5

＃再起動

#これもインストール方法で異なるようです。

#service postgres restart

#/etc/rc.d/init.d/postgresql restar

#などで再起動できないことがあります。

#その場合、下記コマンドで再起動します。

su postgres

pg_ctl restart -D /var/lib/pgsql/data

#このあとロールやデータベースを選択したコマンドで再度ログインすればパスワードをきかれます。

#パスワードは設定していなければpostgresになるかと思います。

#設定したい場合は下記コマンドで。

su - postgres

-bash-4.2$ psql

postgres=# \password postgres

インストールから基本的な流れまで
CentOS7.0（Vagrant）にPostgreSQL 9.4インストール

ログイン失敗時の対処法
peer認証の関係でpsqlログインできない時の対処法

pg_ctlコマンドに関して

PostgreSQLの起動・停止
 pg_ctl – postgresサーバの起動と停止

pg_hba.confに関して

他ホストから接続するための設定
Peer authentication failed for user “postgres”

メモリ不足による起動の失敗

postgresに割り当てるOSの共有メモリが少ないと起動に失敗することがあります。

例えば自分の場合、起動したところ下記のようなエラーメッセージが出力されました。

共有メモリセグメントを作成できません: 無効な引数です
詳細:  失敗したシステムコールはshmget(key=5432001, size=41222144, 03600)でした。
ヒント:  通常このエラーは、PostgreSQL が要求した共有メモリセグメントがカーネルのSHMMAX パラメータを超えた場合に発生します。この要求サイズを減らすこともできますし、SHMMAX を増やしてカーネルを再構築することもできます。要求サイズ（現在 41222144 バイト）を減らしたい場合は PostgreSQL の shared_buffers もしくは max_connections を減らしてください。
        この要求サイズがすでに小さい場合、これがカーネルの SHMMIN より小さくなってしまっているかもしれません。そのような場合は要求サイズを大きくするか、 SHMMIN をそれにふさわしい値に再構成してください。
        共有メモリの設定に関する詳細情報は、PostgreSQL のドキュメントに記載されています。

共有メモリセグメントを作成できません: 無効な引数です

詳細: 失敗したシステムコールはshmget(key=5432001, size=41222144, 03600)でした。

ヒント: 通常このエラーは、PostgreSQL が要求した共有メモリセグメントがカーネルのSHMMAX パラメータを超えた場合に発生します。この要求サイズを減らすこともできますし、SHMMAX を増やしてカーネルを再構築することもできます。要求サイズ（現在 41222144 バイト）を減らしたい場合は PostgreSQL の shared_buffers もしくは max_connections を減らしてください。

この要求サイズがすでに小さい場合、これがカーネルの SHMMIN より小さくなってしまっているかもしれません。そのような場合は要求サイズを大きくするか、 SHMMIN をそれにふさわしい値に再構成してください。

共有メモリの設定に関する詳細情報は、PostgreSQL のドキュメントに記載されています。

どうやら下記コマンドで調べられるようです。

cat /proc/sys/kernel/shmmax
33554432

1 2	cat /proc/sys/kernel/shmmax 33554432

自分の場合、要求(41222144)に対し、実際のサイズが33554432だったため、起動に失敗しました。

下記コマンドでサイズを上げてから起動すると無事成功。

echo 63554432 > /proc/sys/kernel/shmmax

1	echo 63554432 > /proc/sys/kernel/shmmax

PostgreSQLのSHMMAXの計算式の検証

リモートからの接続

無事、ロールとデータベースを選択できるようにはなったのですが、リモートホストからは接続できません。リモートから接続するためには下記のようにします。前提条件としてiptablesやfirewallでport(postgresはディフォルトだと5432)を開放しておきます。

また設定ファイルを修正します。

 vim /var/lib/pgsql/data/postgresql.conf
(修正前)#listen_addresses = '*' 
(修正後)listen_addresses = '*' 

(修正前)#port = 5432  
(修正後)port = 5432  #もしポートを変えたい場合はここを変更すればOKです。

 vim /var/lib/pgsql/data/pg_hba.conf
#先ほどのlocalとほぼ一緒データベースのユーザーのあとに許可するホストを設定できる0.0.0.0/0はOK
(修正前)host    all             all             127.0.0.1/32     ident
(修正後)host    all             postgres       0.0.0.0/0         md5

#再起動
pg_ctl restart -D /var/lib/pgsql/data 

#リモートからログインできていればOK

vim /var/lib/pgsql/data/postgresql.conf

(修正前)#listen_addresses = '*'

(修正後)listen_addresses = '*'

(修正前)#port = 5432

(修正後)port = 5432 #もしポートを変えたい場合はここを変更すればOKです。

vim /var/lib/pgsql/data/pg_hba.conf

#先ほどのlocalとほぼ一緒データベースのユーザーのあとに許可するホストを設定できる0.0.0.0/0はOK

(修正前)host all all 127.0.0.1/32 ident

(修正後)host all postgres 0.0.0.0/0 md5

#再起動

pg_ctl restart -D /var/lib/pgsql/data

#リモートからログインできていればOK

開放されたか否かは下記サービスでチェック
Portチェックテスト【外部からのPort開放確認】

リモート接続に関して
postgresqlを他ホストからもつなげるようにする

ちなみにパスワードはdefaultでpostgresかと思いましたが、設定されていないこともあるようです。

その場合、認証方法をidentやmd5ではなく、trustにするとパスワードなしでログインできます。

その時に

ALTER USER postgres with encrypted password 'パスワード';

1	ALTER USER postgres with encrypted password 'パスワード';

上記のように入力してあげればパスワードをセットすることができるので、一度ログアウトし、認証方法をmd5に再修正して入ることができます。