Postgresqlから外部のデータベースを操作する

あまプロでは、データベースを扱う案件を良く請けます。
先日、PostgreSQLからMySQLを操作する案件に携わりましたので、あメログ。

PostgreSQLからMySQL等の外部のデータベースへ接続するにはForeignDataWrapperという拡張機能を用います。
DBリンクという似た機能も有るのですが、これは外部のPostgreSQLとしか接続出来ません。
FDWであればMySQLだけに限らず、色々なDBへ接続できる様です。詳しくはこちらをご覧下さい。

今回はPostgreSQLからMySQLのlist_db.listsというテーブルに接続してみます。
どちらも同じローカルにインストールしている前提です。
リモートのデータベースに接続する場合は、適宜接続設定を行って下さい。
環境は以下の通り

Ubuntu 17.10
PostgreSQL 9.6.6
MySQL 5.7.20

先ず、MySQL用のFDWをインストールします。
Ubuntu（Debian系）では

sudo apt install postgresql-9.6-mysql-fdw

でインストール可能です。
恐らく他のディストリビューションでもインストール出来る筈です。

以降の操作にはデータベースでの管理者権限が必要です。
使用するユーザに管理者権限が付いてない場合は

GRANT ALL PRIVILEGES ON DATABASE データベース名 TO ユーザ名;

で、権限を付与して下さい。


psqlでPostgreSQL（の権限の付けられたデータベース）へログインします。
拡張機能を作成します。

CREATE EXTENSION mysql_fdw;

サーバを作成します。

CREATE SERVER サーバ名 FOREIGN DATA WRAPPER mysql_fdw OPTIONS ( host 'localhost', port '3306');

サーバ名は（以降の設定でも使うので）解り易い名前にして、ホストやポートは環境に合わせて適宜変更して下さい。

マッピングを作成します。

CREATE USER MAPPING FOR public SERVER サーバ名 OPTIONS ( password '******', username 'ユーザ名');

パスワードとユーザ名はMySQL側のパスワードとユーザ名を指定します。

外部テーブルを作成します。

CREATE FOREIGN TABLE lists ( id int, name text)
 SERVER サーバ名 OPTIONS ( dbname 'list_db', table_name 'lists');

テーブルの列名やデータ型はMySQLのテーブル定義に合わせます。
MySQLの場合は

desc テーブル名;

でテーブル定義が表示されます。
違う列名やデータ型に出来るんかな？
そこは未確認です。ひょっとしたら必要な列だけ取得出来るかも。

以上で設定は終わりです。

SELECT * FROM lists;

UPDATE lists SET name = 'ほげほげ';

こんな感じで、PostgreSQLからMySQLへ対してSQL( のDML)を発行する事が出来ます。

外部のデータベース故に色々と制約は有るのですが、結構便利です。

大量のファイル処理

あまプロではシェルスクリプトを作成する場合が有ります。

先日大量のファイルを操作するシェルスクリプトを作成する事が有りましたのであメログ。

 大量の（五万以上）のファイルをワンライナーで変換し様とした際に遭遇したエラー
.hoge.sh: ls: Argument list too long
引数リストが多過ぎますとな！

スクリプト内では
ls -1 *.jpg | 変換コマンド
というコマンドでファイル一覧を取得してたのですが、これがあきませんでした。
ディレクトリ内に多数（五万以上）の画像ファイルが有るので、*.jpgでフィルタして一覧を取得したかったのですが、 どうやら
ls -1 *.jpg
の内部で大量の引数に対応して無いエラーが発生している様です。
xargsを使ってないんかな？

ちなみにフィルタせずに
ls -1
だと大丈夫…何でやねん

仕方ないので
ls -1 | grep .jpg | 変換コマンド
で回避しました。
因みに引数に使用できる最大文字数は
getconf ARG_MAX
で取得出来ます。

無料のドメインを使ってみました

あまプロではwebサイトの構築をお受けする事が有ります。

先日、無料ドメインのFreenomを使う機会が有りました。
さくらVPSでCDNのCloudFlareを使って画像をキャッシュしたかったのですが、
CloudFlareはサブドメインでは使えないので、トップドメインを取得する必要が有ります。
開発用に一時的なトップドメインが必要でしたので、無料ドメインのFreenomを利用しました。

流れとしては

1. さくらVPS契約
   ネームサーバの利用申請
2. Freenom契約
   ネームサーバをさくらVPSのネームサーバで登録
3. Cloudflare契約
   Cloudflareのネームサーバを割り当てられるので、それをFreenomへ登録

こんな感じです。
ちょっとややこしいですけど、さくらVPS（６８５円）代のみでCDNの開発環境が構築出来ました。
頑張って他のVPS無料サービスを探したら、無料で出来るかもです。
CDNが良ぇ感じに画像をキャッシュしてくれるので、webサーバの負荷が分散されます。

只、Webサーバを数分でも止めると取得したドメインが削除されてしまいますorz
ほんの10分程度、バッチ処理の為にサービスを落としただけなんですけど、サービスを再起動した時には既に遅し、ドメインが削除されておりました。
Freenomの利用規約に書いてるみたいですが、いきなりサイトが表示されなくなった時は開発環境とはいえ焦りました。
まぁ同じドメインは直ぐに再取得出来るので、問題は無かったです。

さくらVPS＋ClouddFlare＋Freenomで開発環境。
皆さんもお試しあれ。

iptablesの設定

あまプロではUbuntuのサーバ設定をお受けする事が有ります。

ファイアウォールでiptablesを使うのですが、どうも再起動すると設定が消えてしまいます。保存されへんのです…。

例えば
sudo iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport ポート番号 -j ACCEPT
として、ポートを開放して、

sudo apt install iptables-persistent
iptables保存用パッケージをインストールして
sudo /etc/init.d/netfilter-persistent save
保存しても
reboot後に
sudo iptables --line-numbers -L
が確認すると、設定が保存されないんですよね…。

しゃーないんで、
sudo vim /etc/iptables/iptables.rules
で直接ファイル編集しました。
これで再起動後もポートが開放されました。

apt upgradeでエラー発生

あまプロではUbuntuの案件を受ける事が有ります。


先日、リモートサーバーでアップデートしておりましたら
sudo apt upgrade
いつもは
Running hooks in /etc/ca-certificates/update.d...
done.
と表示される所が
E: Sub-process /usr/bin/dpkg exited unexpectedly
と表示されてしまいました。
何かエラーが発生した様です。

再度
sudo apt upgrade
すると
E: dpkg was interrupted, you must manually run 'sudo dpkg --configure -a' to correct the problem.
と返って来て、アップデートが完了しませんorz
ここは素直に
sudo dpkg --configure -a
してみると
Running hooks in /etc/ca-certificates/update.d...
done.
無事にアップデートが完了しました。

ＳＳＨでサーバに接続できない場合

あまプロではリモートのサーバへ接続してお仕事する案件が有ります。

SSHでリモートサーバへ接続すると、初回に

The authenticity of host 'サーバのIP' can't be established.
RSA key fingerprint is SHA256:**********************************************************
Are you sure you want to continue connecting (yes/no)?
と訊かれます。
ほんまにサーバに接続して良いならyesを入力します。すると
Warning: Permanently added 'ホストのIP' (RSA) to the list of known hosts.







サーバ（の公開鍵）が登録され、次回から公開鍵が一致すれば、訊かれる事はなくなります。

所が、サーバの再インストールやIP変更等が行われると、公開鍵の不一致でログイン時に警告が発生します。

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@    WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!     @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
Someone could be eavesdropping on you right now (man-in-the-middle attack)!
It is also possible that a host key has just been changed.
The fingerprint for the RSA key sent by the remote host is
SHA256:****************************************************************
Please contact your system administrator.

そんな時は
  remove with:
  ssh-keygen -f "/home/ローカルユーザ/.ssh/known_hosts" -R "サーバのIP"
メッセージ通りに一旦登録を削除すると良いです。

Arduino IDE 1.8.5が起動しない

あまプロではArduinoをつかった組み込みプログラミングの講座も行っています。

先日、 Ubuntu17.10へアップグレードしたんですが、どうやらその時に設定が変わった様で、ArduinoIDEが起動しなくなってしまいました…。

sudo ./arduino
Invalid MIT-MAGIC-COOKIE-1 keyPicked up JAVA_TOOL_OPTIONS:
Invalid MIT-MAGIC-COOKIE-1 keyjava.awt.AWTError: Can't connect to X11 window server using ':0' as the value of the DISPLAY variable.
    at sun.awt.X11GraphicsEnvironment.initDisplay(Native Method)
    at sun.awt.X11GraphicsEnvironment.access$200(X11GraphicsEnvironment.java:65)
    at sun.awt.X11GraphicsEnvironment$1.run(X11GraphicsEnvironment.java:115)
    at java.security.AccessController.doPrivileged(Native Method)
    at sun.awt.X11GraphicsEnvironment.<clinit>(X11GraphicsEnvironment.java:74)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:264)
    at java.awt.GraphicsEnvironment.createGE(GraphicsEnvironment.java:103)
    at java.awt.GraphicsEnvironment.getLocalGraphicsEnvironment(GraphicsEnvironment.java:82)
    at sun.awt.X11.XToolkit.<clinit>(XToolkit.java:126)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:264)
    at java.awt.Toolkit$2.run(Toolkit.java:860)
    at java.awt.Toolkit$2.run(Toolkit.java:855)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.awt.Toolkit.getDefaultToolkit(Toolkit.java:854)
    at java.awt.SystemColor.updateSystemColors(SystemColor.java:473)
    at java.awt.SystemColor.<clinit>(SystemColor.java:465)
    at processing.app.Theme.init(Theme.java:84)
    at processing.app.Base.<init>(Base.java:219)
    at processing.app.Base.main(Base.java:144)

X11 window serverに接続できないエラーが出るのですが、どうしたものか…。