Symfoware

Symfowareについての考察blog

Debian 8.7(Jessie)にPostgreSQL 9.6をインストールし、外部接続を許可する

こちらを参考に、DebianへPostgreSQL 9.6をインストール。
外部からの接続を許可する設定を行います。

Debian 8(Jessie)にPostgreSQL 9.4をインストールし、外部接続を許可する


リポジトリの追加



/etc/apt/sources.list.d/pgdg.listを作成
リポジトリのパスを記載します。


# echo deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main > /etc/apt/sources.list.d/pgdg.list




認証キーを追加


# apt-get install wget ca-certificates
# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -




リポジトリを更新


# apt-get update
# apt-get upgrade




インストール実行


# apt-get install postgresql-9.6




バージョン9.6.1がインストール出来ました。


# su - postgres
$ psql
psql (9.6.1)
"help" でヘルプを表示します.

postgres=#








外部接続許可



外部からの接続を許可します。

postgresql.confを編集。


# vi /etc/postgresql/9.6/main/postgresql.conf




60行目付近のlisten_addressesの記載を変更します。


# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'




続いて、pg_hba.confを編集。


# vi /etc/postgresql/9.6/main/pg_hba.conf




認証を受け付けるIPの範囲を追記します。


# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.1.1/24         md5




編集が終わったらサービスをリスタート。


# service postgresql restart



これで外部から接続できるようになります。





ユーザーの作成



接続用にユーザーを作成してみます。


# su - postgres
$ createuser --pwprompt --interactive pgadmin
新しいロールのためのパスワード: [パスワード入力]
もう一度入力してください:[上記とパスワード入力]
新しいロールをスーパーユーザにしますか? (y/n)y




テーマ:サーバ - ジャンル:コンピュータ

  1. 2017/01/15(日) 22:31:54|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

PostgreSQL multicorn extensionのインストールと設定

mysql_fdwで、PostgreSQLからMySQLのテーブルが参照できるようにしてみました。
mysql_fdwで、PostgreSQLからMySQLのデータベースに接続する

調べてみると、Python経由でMySQLに接続する拡張がありました。
multicorn
PGXN - multicorn

ソース
https://github.com/Kozea/Multicorn/tree/master/python/multicorn


MySQLというか、SqlAlchemyで接続できるデータベースなら何でもOKなようです。
データベース以外もLDAPやRSSにも対応。

さらに、独自の拡張がPythonで記載できるみたいですね。

Debian 8 + PostgreSQL 9.4.5で試してみます。



インストール



インストールはpgxnコマンドで行うことにしました。
これはPythonで作成されているようですね。
easy_installでインストールすることにします。

python-setuptoolsをインストールして、easy_installを使えるようにした後、
pgxnclientをインストールします。


# apt-get install python-setuptools
# easy_install pgxnclient



これでpgxnコマンドが使えるようになります。

C言語のビルドが実行されますので、事前に必要なライブラリをインストール。


# apt-get install make g++ python-dev
# apt-get install postgresql-server-dev-9.4




準備出来たらmulticornをインストール。


# pgxn install multicorn



これでインストールは完了です。





エラーになる場合のメモ



エラーになった時の対処法メモです。


# pgxn install multicorn
You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.
ERROR: command returned 1: ['/usr/bin/pg_config', '--libdir']



このエラーは、PostgreSQLのヘッダーなどが無い可能性有りです。
postgresql-server-dev-9.4をインストールしてやります。


# apt-get install postgresql-server-dev-9.4





# pgxn install multicorn
INFO: best version: multicorn 1.2.3
INFO: saving /tmp/tmp21bKuI/multicorn-1.2.3.zip
INFO: unpacking: /tmp/tmp21bKuI/multicorn-1.2.3.zip
INFO: building extension
ERROR: make executable not found: gmake




この場合は、コンパイル環境が不足しています。


# apt-get install make g++ python-dev







拡張機能の有効化



PostgreSQLに対して、クエリを実行していきます。

CREATE EXTENSIONでmulticornを有効にします。


  1. CREATE EXTENSION multicorn;




「alchemy_srv」という名前で、SqlAlchemy FDWを使用することにします。


  1. CREATE SERVER alchemy_srv FOREIGN DATA WRAPPER multicorn
  2. options (
  3. wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw'
  4. );




※ここで、こんなエラーが発生する場合、SqlAlchemyがインストールされていません。


ERROR: Error in python: ImportError
SQLステート:XX000
詳細:No module named sqlalchemy



SqlAlchemyをインストールし、PostgreSQLを再起動します。


# easy_install SqlAlchemy
# service postgresql restart





これでSqlAlchemyFdwが有効になりました。
サーバ、テーブルの関連付けを指定します。

db_urlに指定するのは、SqlAlchemyの接続文字列です。
MySQLの場合はこんな感じ。


mysql://user:pass@db-server-name/db-name?charset=utf8'




mysql_fdwで、PostgreSQLからMySQLのデータベースに接続する
こちらで試したのと同じレイアウトのテーブルと接続してみます。


  1. create foreign table mysql_table (
  2. warehouse_id int,
  3. warehouse_name text
  4. ) server alchemy_srv options (
  5. tablename 'warehouse',
  6. db_url 'mysql://admin:P@ssw0rd@192.168.1.102/test?charset=utf8'
  7. );




できたらselectしてみます。


  1. select * from mysql_table




※こんなエラーが出たら、MySQLdb(PythonからMySQLに接続するライブラリ)がインストールされていません。


ERROR: Error in python: ImportError
SQLステート:XX000
詳細:No module named MySQLdb




こちらを参考にインストール。
MariaDB 5.5にUbuntu 12.04 + Pythonで接続する(MySQL-python使用)


# apt-get install libmysqlclient-dev
# easy_install mysql-python




ちゃんとMySQLのテーブルが検索できました。

648_01.png


データを登録しようとすると・・・

648_02.png


ERROR: This FDW does not support the writable API
SQLステート:XX000




データの更新には対応していないようです。




テーマ:サーバ - ジャンル:コンピュータ

  1. 2015/10/15(木) 23:28:38|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

PostgreSQL + mysql_fdwでMySQLに仮想的なxml列を実現する

mysql_fdwをインストールしてみました。
mysql_fdwで、PostgreSQLからMySQLのデータベースに接続する

ふと、
「MySQLのtext列にあるxmlな文字列を、PostgreSQLのxmlとして扱えるのか?」
と思ったので試してみます。


PostgreSQLでxml列の操作は、過去試したことがあるのでこれを参考にしました。
PostgreSQLのXML列にJavaからデータを登録、取得する
PostgreSQLのXML列をJavaから検索する



MySQL側での準備



適当に、idとtext列を持つテーブル「xml_test」を作成します。


  1. create table xml_test(
  2. id int not null primary key,
  3. address text);




事前にサンプルデータを投入しておきました。


  1. insert into xml_test(id, address) values (1,
  2. '<doc>
  3.     <name>test1</name>
  4.     <value type="string">test1_str</value>
  5.     <value type="int">1</value>
  6. </doc>');
  7. insert into xml_test(id, address) values (2,
  8. '<doc>
  9.     <name>test2</name>
  10.     <value type="string">test2_str</value>
  11.     <value type="int">2</value>
  12. </doc>');
  13. insert into xml_test(id, address) values (3,
  14. '<doc>
  15.     <name>test3</name>
  16.     <value type="string">test3_str</value>
  17.     <value type="int">3</value>
  18. </doc>');






PostgreSQL側での準備



MySQLで作成したテーブルとリンクします。
この時、MySQL側ではデータ型を「text」とした列を、
データ型「xml」として作成しました。


  1. CREATE FOREIGN TABLE xml_test(
  2. id int,
  3. address xml)
  4. SERVER mysql_server
  5. OPTIONS (dbname 'test', table_name 'xml_test');






検索テスト



普通に検索。


  1. SELECT * FROM xml_test



647_01.png


nameタグの値だけ検索


  1. SELECT xpath('/doc/name/text()', address)
  2. FROM xml_test



647_02.png

動きました。正直、動かないと思っていたので驚きました。



「value」のアトリビュート「type」を取り出してみます。


  1. SELECT
  2. xpath('/doc/value/@type', address)
  3. FROM xml_test



647_03.png


「value」のアトリビュート「type」が「string」の値を取り出します。


  1. SELECT
  2. xpath('/doc/value[@type="string"]/text()', address)
  3. FROM
  4. xml_test



647_04.png


普通に使えて驚きました。




注意点




PostgreSQLからインサートしようとするとエラーになります。


  1. insert into xml_test(id, address) values (4, '<doc></doc>');




ERROR: cannot convert constant value to MySQL value
SQLステート:HV004
ヒント:Constant value data type: 142





MySQLのtextフィールドにxmlではない、空白などのデータがあると
検索エラーです。


・MySQL


  1. insert into xml_test(id, address) values (4, '');




・PostgreSQL


  1. SELECT
  2. xpath('/doc/value[@type="string"]/text()', address)
  3. FROM
  4. xml_test




ERROR: invalid XML content
SQLステート:2200N




いい線ついてると思ったのですが、使用できる局面は少ないかも。

テーマ:サーバ - ジャンル:コンピュータ

  1. 2015/10/12(月) 19:52:42|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

mysql_fdwで、PostgreSQLからMySQLのデータベースに接続する

PostgreSQLの拡張機能を使用して、Redisに接続してみました。
PostgreSQL拡張redis_fdwの使い方

今回は、MySQLに接続する拡張をインストールしてみます。
環境は

・Debian 8.2 + Postgresql 9.4.5
こちらでインストールしたものです。
Debian 8(Jessie)にPostgreSQL 9.4をインストールし、外部接続を許可する

・FreeBSD 10.2 + MySQL 5.6
こちらでインストールしたものです。
FreeBSD 10.2にMySQL 5.6をインストール&外部接続許可



MySQL Foreign Data Wrapper for PostgreSQL



gitから取得したソースからインストールすることにしました。
https://github.com/EnterpriseDB/mysql_fdw


※試行錯誤してインストールしたので、不要なものも含まれているかも。


ソースの取得とコンパイルに必要なものをインストール。


# apt-get install g++ make git




MySQLへの接続に必要なものをインストール。


# apt-get install mysql-client libmysqlclient-dev




PostgreSQL関連で必要なヘッダをインストール。


# apt-get install libpq-dev postgresql-server-dev-9.4





githubからソースを取得。


# cd /usr/local/src/
# git clone https://github.com/EnterpriseDB/mysql_fdw.git
# cd mysql_fdw/




makeしてインストールします。


# make USE_PGXS=1
# make USE_PGXS=1 install




念のため、ライブラリをロード。


# ldconfig



これでインストール完了です。





エラーになったら



make時にエラーメッセージが表示されることがあります。



make: mysql_config: コマンドが見つかりませんでした
make: mysql_config: コマンドが見つかりませんでした



この場合は、libmysqlclient-devが不足しています。


# apt-get install libmysqlclient-dev






You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.
Makefile:52: *** PostgreSQL 9.3, 9.4, 9.5 or 9.6 is required to compile this extension. 中止.



これや、以下のエラーの場合。


Makefile:47: /usr/lib/postgresql/9.4/lib/pgxs/src/makefiles/pgxs.mk: そのようなファイルやディレクトリはありません
Makefile:52: *** PostgreSQL 9.3, 9.4, 9.5 or 9.6 is required to compile this extension. 中止.



PostgreSQLのライブラリ、ヘッダーが不足しています。


# apt-get install libpq-dev postgresql-server-dev-9.4








mysql_fdw拡張を使用する



下準備ができたので、mysql_fdw拡張を使用してみます。
以下、psqlコマンドでの実行結果です。

テスト用のデータベースを作成し、切り替え。


# create database fdw_test;
CREATE DATABASE

# \c fdw_test;




拡張機能を有効化。


# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION




MySQLサーバーの情報を定義。


# CREATE SERVER mysql_server
# FOREIGN DATA WRAPPER mysql_fdw
# OPTIONS (host '192.168.1.102', port '3306');
CREATE SERVER




「mysql_server」の箇所は任意の名前を設定します。

PostgreSQLユーザーと、MySQLデータベースのユーザー情報の関連付けを行います。


PostgreSQLユーザー:pgadmin
MySQLユーザー:admin
MySQLパスワード:P@ssw0rd



上記情報で、先ほど作成した「mysql_server」の情報と関連付けます。


# CREATE USER MAPPING FOR pgadmin
# SERVER mysql_server
# OPTIONS (username 'admin', password 'P@ssw0rd');
CREATE USER MAPPING





テーブル名の関連付けを行います。
MySQL側のデータベース「test」は事前に作成しておきました。


# CREATE FOREIGN TABLE warehouse(
#     warehouse_id int,
#     warehouse_name text)
# SERVER mysql_server
#     OPTIONS (dbname 'test', table_name 'warehouse');
CREATE FOREIGN TABLE




インサートを試してみます。


fdw_test=# INSERT INTO warehouse values (1, 'UPS');
ERROR: failed to execute the MySQL query:
Table 'test.warehouse' doesn't exist




エラーになりました。
事前にMySQL側でテーブルを作成しておく必要がある模様。


mysql> CREATE TABLE warehouse(
    ->     warehouse_id int,
    ->     warehouse_name text);
Query OK, 0 rows affected (0.02 sec)




再度インサートを試すとエラーになりました。


fdw_test=# INSERT INTO warehouse values (1, 'UPS');
ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation




primary keyの指定が必要な模様。
MySQL側のテーブルを一旦ドロップ、作り直します。


mysql> CREATE TABLE warehouse(
    ->     warehouse_id int not null primary key,
    ->     warehouse_name text);
Query OK, 0 rows affected (0.02 sec)





これでインサートが成功しました。


fdw_test=# INSERT INTO warehouse values (1, 'UPS');
INSERT 0 1




PostgreSQL側でselect。


# select * from warehouse;
warehouse_id | warehouse_name
--------------+----------------
            1 | UPS
(1 行)




MySQL側でselect。


mysql> select * from warehouse;
+--------------+----------------+
| warehouse_id | warehouse_name |
+--------------+----------------+
|            1 | UPS            |
+--------------+----------------+
1 row in set (0.00 sec)




ちゃんと同じ内容が表示されます。


pgAdminからはテーブルとして認識されていないようです。

646_01.png


コマンドで確認すると、テーブルとして表示されました。


fdw_test=# \d
public | warehouse | 外部テーブル | pgadmin





テーマ:サーバ - ジャンル:コンピュータ

  1. 2015/10/12(月) 19:02:32|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

ユーザーを変更せずに、psqlを使用してPostgreSQLに接続する

PostgreSQLをインストールしているサーバーでの作業時、
データベースに接続するときは


# su - postgres
$ psql
psql (9.4.5)
"help" でヘルプを表示します.

postgres=#




こんな感じで、ユーザーを切り替えて接続しているのですが、
これが面倒くさい。


Debian 8(Jessie)にPostgreSQL 9.4をインストールし、外部接続を許可する

ここの手順で作成した、pgadminで接続する方法を調べてみました。
psqlのオプションを見てみると、


-U:ユーザ指定
-h:接続ホスト指定
-d:接続データベース指定。




というわけで、rootユーザーでも、このコマンドで接続できます。


# psql -U pgadmin -h localhost -d postgres




接続時はパスワードの入力を求められます。


# psql -U pgadmin -h localhost -d postgres
ユーザ pgadmin のパスワード:
psql (9.4.5)
SSL接続(プロトコル: TLSv1.2, 暗号化方式: ECDHE-RSA-AES256-GCM-SHA384, ビット長: 256, 圧縮: オフ)
"help" でヘルプを表示します.

postgres=#


テーマ:サーバ - ジャンル:コンピュータ

  1. 2015/10/12(月) 17:43:42|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
次のページ