Category: Math and Programming

PostgreSQL をインストールしたばかりのとき、以下のようなエラーを目にすることがあります。

最新の PostgreSQL 11 を Ubuntu 18.04 にインストールした際にも発生することがあります。

$ psql
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
psql: サーバに接続できませんでした: No such file or directory
      ローカルにサーバが稼動していますか?
      Unixドメインソケット"/var/run/postgresql/.s.PGSQL.5432"で通信を受け付けていますか?

このときの解決策として、PostgreSQLをアンインストールして環境を再構築するという記述が、しばしば見受けられます。

それで問題が解決すれば良いのですけれども、同じ問題が再発することがあります。

私の経験上、再インストールによって解決する問題は version を指定せずにパッケージマネージャから RDBMS をインストールして、正しいファイルや設定を読み込めていないことが多いので、そうでない場合にはあまり効果が期待できないです。

では、どうするかと言うと、経験上の判断から locale settings や cluster を見に行くことが多いです。

本題に入る前に、再現性を高めるために、いつものバージョン情報とインストール時の設定を掲載しておきます。

europa@Jupiter:~$ cat /etc/os-release 
NAME="Ubuntu"
VERSION="18.04.2 LTS (Bionic Beaver)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 18.04.2 LTS"
VERSION_ID="18.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=bionic
UBUNTU_CODENAME=bionic

$ psql -V
psql (PostgreSQL) 11.4 (Ubuntu 11.4-1.pgdg18.04+1)

導入時に行ったこと
参照: PostgreSQL: Linux downloads (Ubuntu)

$ sudo apt update && sudo apt -y upgrade
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)"-pgdg main | sudo tee /etc/apt/sources.list.d/pgdg.list
$ sudo apt update
$ sudo apt install postgresql-11  -y 
$ sudo passwd postgres && su - postgres

ここまででインストールに成功すると、冒頭 Connection refused error に遭遇することがあるかもしれません。

$ psql -p 5432 -h localhost
could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

psql: サーバに接続できませんでした: Connection refused
	サーバはホスト "localhost" (::1) で稼動しており、
	また、ポート 5432 で TCP/IP 接続を受け付けていますか?
サーバに接続できませんでした: Connection refused
	サーバはホスト "localhost" (127.0.0.1) で稼動しており、
	また、ポート 5432 で TCP/IP 接続を受け付けていますか?

まずはプログラムが動いているかどうか確認します。動作していないと、そもそも繋がりません。

$ service postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
   Active: active (exited) since Fri 2019-07-20 08:46:24 CST; 31min ago
 Main PID: 2178 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 1130)
   CGroup: /system.slice/postgresql.service

$ ps -ef|grep postgres
postgres 3950  2503  0 09:20 pts/0    00:00:00 grep postgres

プログラムが動作していることを確認できましたら、ソケット（プログラムとネットワークの接続口）の状態を見に行きます。
$ ss -tunelp|grep 5432
PostgreSQL に接続するために必要なところなので、きちんと動いていれば状態を確認できます。
ここの反応が今はありませんので、うまく行っていませんね。
ということで、現在のクライアント認証はどうなっているのかを見に行きます。
認証設定ファイルは "pg_hba.conf" という名前で保存されていることが伝統なので、探し出して現在の設定を把握します。
$ sudo find / -name pg_hba.conf
$

ところが、なぜか該当ファイルが見当たらないので、決め打ちで探しにいきます。
$ ls /etc/postgresql/
$ ls -a /etc/postgresql
.  ..  
そうすると、そこにあるはずのディレクトリが存在しません。
設定の問題以前に、設定ファイルそのものがありません。
ようやく、ここからが本題なのですけれども、必要なファイルが無いときは初期化したり、ツールで作成させると問題の解決につながることもあります。
$ sudo pg_createcluster 11 main --start
Creating new PostgreSQL cluster 11/main ...
/usr/lib/postgresql/11/bin/initdb -D /var/lib/postgresql/11/main --auth-local peer --auth-host md5
データベースシステム内のファイルの所有者は"postgres"となります。
このユーザがサーバプロセスも所有する必要があります。

データベースクラスタは以下のロケールで初期化されます。
  COLLATE:  C.UTF-8
  CTYPE:    C.UTF-8
  MESSAGES: C.UTF-8
  MONETARY: en_US.UTF-8
  NUMERIC:  en_US.UTF-8
  TIME:     en_US.UTF-8
そのためデフォルトのデータベース符号化方式はUTF8に設定されました。
デフォルトのテキスト検索設定はenglishに設定されました。

データベージのチェックサムは無効です。

既存のディレクトリ/var/lib/postgresql/11/mainの権限を修正します ... 完了
サブディレクトリを作成します ... 完了
max_connectionsのデフォルト値を選択します ... 100
shared_buffersのデフォルト値を選択します ... 128MB
selecting default timezone ... America/Chicago
動的共有メモリの実装を選択します ... posix
設定ファイルを作成します ... 完了
ブートストラップスクリプトを実行します ... 完了
ブートストラップ後の初期化を行っています ... 完了
データをディスクに同期します...完了

成功しました。以下のようにしてデータベースサーバを起動できます。

    pg_ctlcluster 11 main start

Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

必要なファイルが揃いますと、設定ファイルも検索で見つかるようになりますし、RDBMS にも接続できるようになります。
$ sudo find / -name pg_hba.conf
/etc/postgresql/11/main/pg_hba.conf

$ su - postgres -c psql
Password: 
これでも、まだ接続できなかったらサーバ側の設定でポートが開放されているか、認証設定はどうなっているかを疑ってください。
まとめますと、インストールされた RDBMS の version と、プログラムの起動状態、クライアント認証、初期化の有無、そして IP アドレスやポートのサーバ設定などを順を追って確認していくと問題の原因に行き当たる確率が高いです。



[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)
	

数年前から契約している、さくらインターネットの仮想専用サーバーの１つに「標準OSインストール」から CentOS x86_64 をいれて SSHD と Firewall と Port の設定をしようと思ったけど、うまく行かなったという話です。

さくらの VPS も何件か契約しておりまして、そのうち１つは CentOS 6.1 で動いていました。

これを久しぶりにアップデートしようとしたところ、Upgrade Tool はリンクが切れており、ミラーリストもURIが変わっていて、いろいろ大変なことに気がつきました。

力づくで解決するよりも新規にOSを入れ直して再設定したほうが早そうだったので /etc 以下のいくつかのファイルをローカルに保存して、コントロールパネルから OS を再インストールすることに決めました。

スタートアップスクリプト [Public] CentOS_yum-update を選択して、インストールは無事に終了しました。

# cat /proc/version
Linux version 3.10.0-957.10.1.el7.x86_64 (mockbuild@kbuilder.bsys.centos.org) (gccversion 4.8.5 20150623 (Red Hat 4.8.5-36) (GCC) ) #1 SMP Mon Mar 18 15:06:45 UTC 2019

VNCコンソールは使い勝手が悪いので、さっそくリモートからのアクセスを許容すべく、ポートの割当を書き換えて SSHD を再起動します。

# vi /etc/ssh/sshd_config
# systemctl restart sshd.service

つぎに割り当てたポートを開放し、不要なポートを閉鎖すべく、Firewalld の設定を書き換えます。

CentOS 6 までの iptables とは勝手が違いますね。

# start systemctl start firewalld
# firewall-cmd --version
0.4.4.5
# firewall-cmd --remove-service=ssh --permanent 
# firewall-cmd --add-service=ssh --zone=public --permanent
# cp /usr/lib/firewalld/services/ssh.xml /etc/firewalld/services/ssh.xml
# vi /etc/firewalld/services/ssh.xml
# firewall-cmd --reload
# firewall-cmd --list-all|grep -a port

リモートから接続されている場合、Firewalld を起動すると一度ここで通信が切断されます。

これで設定がうまく行ったのかと思いきや、ssh 接続を試みると Connection timed out でエラーになります。

何かがおかしいなと思い、開放されているポートを調べてみるのですが、設定的に間違いはなさそうです。

# ss -t -l -n
# grep -i port /etc/ssh/sshd_config

何かがおかしいので、一時的に22番ポートを開放すると、普通に ssh 接続できるようになります。

# vi /etc/ssh/sshd_config
# systemctl restart sshd.service
# vi /etc/firewalld/services/ssh.xml
# firewall-cmd --reload

これは一体どういうことなの？？？

ほかに理由が思い当たらないので「あまり意味はないだろうな」とも思いつつ、SELinux の設定も変更してみます。

# semanage port --add --type ssh_port_t --proto tcp NNNN
# semanage port --list | grep -w ssh

意味はないだろうとは思っていましたけど、当然ながら結果は同じでした。

調べてみると、さくらインターネットのVPSでは /etc/iptables/iptables.rule に独自の設定を行っているようです。

もしかしたら、22番以外のポートを閉じる設定をどこかで行っているのでないかと、総当りで探していると、こんなものを発見。

2019年6月27日　さくらのVPS 「パケットフィルタ」提供開始のお知らせ　
https://www.sakura.ad.jp/information/announcements/2019/06/27/1968200552/

新機能のパケットフィルタによってOSインストール時にはデフォルトで 22 番以外のポートが閉じられている設定になっていました。

しかも、パケットフィルタが導入されたのは、今日から数えて30日前の 6月27日 です。

これでは新しすぎて、いくら検索しても同一の事例が見つからないわけです。ほかの契約サーバでは問題なく ssh 接続できるのに、このサーバだけ接続できない理由にも合点がいきました。

結局、ネットワークの設定をほとんど見直して、半日を費やしましたけどコントロールパネルからパケットフィルタを無効化すると、問題なくリモート接続できるようになりました。

こうなったら、あとはもう SSH root login と Password Authentication を禁止、御役御免の22番は閉鎖して、ユーザー作成と公開鍵認証を済ませてしまえば、いつもどおりです。

# adduser piyo && usermod -aG sudo piyo
# mkdir /home/piyo
# su piyo && cd ~
$ mkdir /home/piyo/.ssh && chmod 700 /home/piyo/.ssh && sudo chown piyo:piyo /home/piyo/.ssh
$ touch /home/piyo/.ssh/authorized_keys && chmod 600 /home/piyo/.ssh/authorized_keys

いつの間にか、いろいろ変わっているので、定期的に環境を見直しておかないとダメですね。

[改訂新版]プロのためのLinuxシステム構築・運用技術 (Software Design plus)

最近 (今年の2月ぐらいに) 興味深い記事を読みました。

クエリビルダを利用する明確な利点は存在しないという内容です。ここ3年ぐらいの間に割と聞かれるようになったやつです※。

Stop using Knex.js — Using SQL query builder is an anti-pattern
https://medium.com/@gajus/stop-using-knex-js-and-earn-30-bf410349856c

その趣旨を一言で述べると、動的に作成される必要があるクエリにのみ例外的に Knex.js を使用して、それ以外は直に SQL を書いたほうが良いというもの。

Knex.js というのは node.js において最もよく使われている SQL クエリビルダで、私も手を抜くときに使っています。

MySQL にも PostgreSQL にも SQLite3 にも (不完全ながら DB2 にも) 対応しており、SQL を知っている人にとっては直感的に使えます。

何が良いというわけではないのですけれども、無意識に自然と使用していることが多いです。 SQL を直書きするときでさえ、気がつくと knex.raw() 関数を使っています。

しかし、データベースの一部の機能が使用できない、あるいは使用できないという誤解に基づき酷評されている場面を見たことがあります。

ここからが上の記事の内容なのですが、SQL で実現可能なことをクエリビルダで実現するために二度の学習が必要となり、knex.js に詳しい人も (SQLに詳しい人に比べれば) 少ないので問題が起こった際に調べる手間が大きいことが難点です。

そして、バッチ更新のように利用頻度が高いものが、公式ドキュメントに書かれていない (いま再確認したら batchInsert は書かれていましたけど) ので、それも誤解の一因になっているのかと思いました。

Knex.js – A SQL Query Builder for Javascript / Latest Release: 0.16.3
https://knexjs.org/

ドキュメントに書かれていないので、誤解されても仕方がないですが、データの更新 (UPDATE) や削除 (DELETE) を knex.js でバッチ処理で実行することは可能です。

もとのソースコードの SQL 文の生成あたりを読んでいて、トランザクションの中にクエリ (Array objects) を入れてみたら実行できるような気がしたので、ためしに入れてみたところ… 実際に動作することを確認できました。

できる根拠を尋ねられても、もう当時の考えを覚えてないので困ってしまうのですけれども、やってみたら実行できたのですよ。

const express = require('express');
const router = express.Router();
const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: "./myname.sqlite"
  }
});

/* Connect to (MySQL|PSQL|SQLite) */
router.get('/', (req, res) => {
  let tableName = 'name';
  let query = [{
      id: 1,
      name: 'Janie Doe'
    },
    {
      id: 2,
      name: 'Richard Roe'
    },
    {
      id: 3,
      name: 'John Schmoe'
    }];
  knex.transaction(trx => {
      let built = query.map(elem => {
        return knex(tableName)
          .where('id', elem.id)
          .update(elem)
          .transacting(trx);
      });
      return Promise.all(built)
        .then(trx.commit)
        .catch(trx.rollback);
    })
    .then()
    .catch();
});
module.exports = router;

このソースコードは myname という DB に name というテーブルを作成して、 hoge とか huga とか piyo とか foo とか waldo みたいな適当な初期値を入れておけば実行できます。

削除したい場合には、update(elem) の部分を del() に書き換えると同様に実行できます。もちろん、insert() に書き換えても動きます。

実行環境は以下のとおりです。

$ uname -a
Linux Ganymede 4.15.0-50-generic #54-Ubuntu SMP Mon May 6 18:46:08 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

$ node -v
v12.2.0

$ grep -i knex package.json 
    "knex": "^0.16.7",

$ sqlite3 --version
3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d

同じように公式ドキュメントには書かれていないのですけれども、日付の範囲指定なども whereBetween() 関数の中に入りそうに見えたので、入れてしまうとやっぱり動きます。

こうすると簡単かつ柔軟に範囲を指定できるようになって便利なんですよね。

const express = require('express');
const router = express.Router();
const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: "./mylog.sqlite"
  }
});

router.get('/', (req, res) => {
  let tableName = 'access';

  const offset = 8; // UTC time offset HKT 
  //const offset = 9;  
  let currentDate = new Date();
  currentDate.setTime(currentDate.getTime() + 60 * 60 * 1000 * offset);

  let nextDate = new Date();
  nextDate.setTime(nextDate.getTime() + 60 * 60 * 1000 * offset);
  nextDate.setDate(nextDate.getDate() + 1);

  let cd = currentDate.toISOString().split('T')[0];
  let nd = nextDate.toISOString().split('T')[0];

  knex(tableName)
    .select()
    .whereBetween('date', [cd, nd])
    .orderBy('date', 'desc')
    .then(rows => {rows.forEach(elem => {console.log(elem);});})
    .catch();
});
module.exports = router;

このように knex.js には、公式には謳われていなくても、既知の関数の組み合わせで実現できることがたくさんあります。

SQL で実行できることは、何かしらの方法で実行できることが多いです。

できそうに思えたのに実行できなかったのは OrderBy() の中に Array や連想配列を入れて、複数の条件で並び替えを行うことです (Knex.js ver 0.16.7 までの話で、今後は対応されるかもしれません)。

この場合はデータベース内に VIEW を作成しておいて tableName に VIEW の名前を代入してやると思い通りの結果を得ることができます。

SELECTの中で条件分岐したい場合などにも、あらかじめ VIEW を作成しておいて tableName に VIEW の名前を代入してやると簡単です (これもドキュメントに書いてあったかどうか…) 。

でも、それは「SQL で同じことができることを経験的に知っているからではないか」と言われてしまうと、冒頭の話題を考えざるを得なくなるわけです。

SQLで直にクエリを書くことを考えると、どちらが良いのでしょうね。

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

---

※ 一例をあげると

Why you should avoid ORMs (with examples in Node.js)
https://blog.logrocket.com/why-you-should-avoid-orms-with-examples-in-node-js-e0baab73fa5

Breaking Free From the ORM: Why Move On? Time to escape object relational mapping
https://medium.com/building-the-system/dont-be-a-sucker-and-stop-using-orms-190add65add4