Symfoware

Symfowareについての考察blog

Debian 9(Stretch)にPostgreSQL 9.6をapt-getでインストールし、外部アクセスを許可する

Debian 9にPostgreSQL 9.6をインストールしてみます。

過去の記事を参考にしました。
Debian 8(Jessie)にPostgreSQL 9.4をapt-getでインストールする



PostgreSQLのインストール



apt-getでインストール


# apt-get install postgresql-9.6




インストール時のログの抜粋です。


(略)
postgresql-client-common (181) を設定しています ...
systemd (232-25) のトリガを処理しています ...
postgresql-common (181) を設定しています ...
ユーザ postgres をグループ ssl-cert に追加

Creating config file /etc/postgresql-common/createcluster.conf with new version
Creating config file /etc/logrotate.d/postgresql-common with new version
(略)
postgresql-9.6 (9.6.3-3) を設定しています ...
Creating new cluster 9.6/main ...
config /etc/postgresql/9.6/main
data /var/lib/postgresql/9.6/main
locale ja_JP.UTF-8
socket /var/run/postgresql
port 5432
update-alternatives: /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) を提供するために自動モードで /usr/share/postgresql/9.6/man/man1/postmaster.1.gz を使います
postgresql-contrib-9.6 (9.6.3-3) を設定しています ...
python3 (3.5.3-1) を設定しています ...
running python rtupdate hooks for python3.5...
(略)




ユーザー「postgres」が自動的に作成されています。
また、python3も一緒にインストールされるようです。

postgresユーザーに切り替えて、psqlを実行。


# su - postgres
postgres@debian9:~$ psql
psql (9.6.3)
"help" でヘルプを表示します.

postgres=#




バージョン9.6.3がインストールできました。

「\q」で終了します。


postgres=# \q
$







データベースユーザーの追加(createuser)



データベースに接続するためのユーザーを作成してみます。
今回は「pgadmin」というスーパーユーザーを作成してみます。


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




これでユーザーが作成できました。
ログイン出来るかテストしてみます。


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

postgres=#




接続できました。
データベースを作成してみます。


postgres=# create database sample;
CREATE DATABASE
postgres=#




「\l」でデータベースの一覧が表示できます。


postgres=# \l

                                         データベース一覧
名前    | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) |    
アクセス権    
-----------+----------+------------------+-------------+-------------------+-----------------------
postgres | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8     |
sample    | pgadmin | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8     |
template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8     | =c/postgres         +
         |         |                 |             |                 | postgres=CTc/postgres
template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8     | =c/postgres         +
         |         |                 |             |                 | postgres=CTc/postgres
(4 行)




ちゃんとsampleデータベースが作成できています。






外部接続許可



別の端末から接続できるようにしてみます。
まず、postgresql.confを編集。


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




60行目付近のlisten_addressesを'localhost'から'*'に変更します。


#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all

listen_addresses = '*'




続いてpg_hba.confを編集。


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




「IPv4 local connections」に、接続を許可するIPの範囲を指定。
今回は192.168.1.0/24の範囲を追加しました。


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




編集が終わったら、PostgreSQLを再起動。


# service postgresql restart




Psycopg2を使用して、接続をテストしてみます。


  1. # -*- coding:utf-8 -*-
  2. import psycopg2
  3. con = psycopg2.connect(
  4.     host = "192.168.1.101",
  5.     port = 5432,
  6.     database="sample",
  7.     user="pgadmin",
  8.     password="P@ssw0rd")
  9. con.close()
  10. print(u'ok')




ちゃんと外部から接続できたようです。


$ python sample.py
ok




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

  1. 2017/06/28(水) 22:09:02|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

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. | 編集
次のページ