Symfoware

Symfowareについての考察blog

Postgresql 10.3をUbuntu Server 18.04にインストールし、外部アクセスを許可する

Postgresql 10.3をUbuntu Server 18.04にインストールしてみます。
こちらを参考にしました。
Postgresql 10.3をUbuntu Server 16.04にインストールし、外部アクセスを許可する
https://wiki.postgresql.org/wiki/Apt


リポジトリの追加



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


$ echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" | sudo tee -a /etc/apt/sources.list.d/pgdg.list



pgdg.listを作成したら認証キーを追加


$ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -



リポジトリを更新


$ sudo apt update
$ sudo apt upgrade



インストール実行


$ sudo apt install postgresql-10



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


$ sudo su - postgres
postgres@:~$ psql
psql (10.3 (Ubuntu 10.3-1.pgdg18.04+1))
Type "help" for help.

postgres=#





外部接続許可



外部からの接続を許可します。
postgresql.confを編集。


$ sudo vi /etc/postgresql/10/main/postgresql.conf



60行目付近、listen_addressesのコメントを解除
「localhost」から「*」に変更し、すべての通信を受け入れます。


# - Connection Settings -

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



続いて、pg_hba.confを編集。


$ sudo vi /etc/postgresql/10/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



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


$ sudo service postgresql restart



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



ユーザーの作成



接続用に「pgadmin」というユーザーを作成してみます。


$ sudo su - postgres
$ createuser --pwprompt --interactive pgadmin
Enter password for new role: [パスワード入力]
Enter it again: [上記とパスワード入力]
Shall the new role be a superuser? (y/n) y



作成したユーザーで接続できるかテスト。


$ psql -h localhost -U pgadmin -d postgres
Password for user pgadmin:
psql (10.3 (Ubuntu 10.3-1.pgdg18.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#



ついでにデータベースやテーブルを作成してみます。


postgres=# create database sample;
CREATE DATABASE

postgres=# \c sample
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "sample" as user "pgadmin".

sample=# create table test (id int, value text);
CREATE TABLE

sample=# insert into test (id, value) values (1, '日本語テスト');
INSERT 0 1

sample=# select * from test;
id |    value    
----+--------------
1 | 日本語テスト
(1 row)

sample=#



いい感じです。

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

  1. 2018/05/05(土) 23:32:25|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

Postgresql 10.3をUbuntu Server 16.04にインストールし、外部アクセスを許可する

Postgresql 10.3をUbuntu Server 16.04にインストールしてみます。

過去記事のこちらの手順を参考にしました。
Debian 8.7(Jessie)にPostgreSQL 9.6をインストールし、外部接続を許可する


リポジトリの追加




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


$ sudo vi /etc/apt/sources.list.d/pgdg.list



以下の1行を記載。


deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main




pgdg.listを作成したら認証キーを追加


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




リポジトリを更新


$ sudo apt update
$ sudo apt upgrade




インストール実行


$ sudo apt install postgresql-10




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


$ sudo su - postgres
postgres@nginx:~$ psql
psql (10.3 (Ubuntu 10.3-1.pgdg16.04+1))
"help" でヘルプを表示します。

postgres=#







外部接続許可




外部からの接続を許可します。
postgresql.confを編集。


$ sudo vi /etc/postgresql/10/main/postgresql.conf




60行目付近、listen_addressesのコメントを解除
「localhost」から「*」に変更し、すべての通信を受け入れます。


# - Connection Settings -

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





続いて、pg_hba.confを編集。


$ sudo vi /etc/postgresql/10/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;




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


$ sudo service postgresql restart




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




ユーザーの作成



接続用に「pgadmin」というユーザーを作成してみます。


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




作成したユーザーで接続できるかテスト。


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

postgres=#




ついでにデータベースを作成してみます。


postgres=# create database sample;
CREATE DATABASE

postgres=# \c sample
SSL 接続 (プロトコル: TLSv1.2、暗号化方式: ECDHE-RSA-AES256-GCM-SHA384、ビット長: 256、圧縮: オフ)
データベース "sample" にユーザ "pgadmin" として接続しました。
sample=#



いいですね。

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

  1. 2018/04/22(日) 19:53:15|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

PostgreSQL + cstore_fdwによるログテーブルの容量圧縮

PostgreSQL 10にcstore_fdw拡張をインストールしました。
cstore_fdw(PostgreSQLカラムストア拡張)のインストールと設定

データサイズがどの程度圧縮されるか試してみます。


テーブルの作成とデータの登録



登録日や顧客ID。ログデータを格納するtextフィールドを3つ持つテーブルでテストしてみます。


log_1:通常
log_2:cstore_fdw,無圧縮
log_2:cstore_fdw,pglz圧縮




使用したCREATE文は以下のとおりです。


CREATE TABLE log_1 (
id bigint NOT NULL,
create_at timestamp NOT NULL,
customer_id bigint NOT NULL,
log_data_1 text NOT NULL,
log_data_2 text NOT NULL,
log_data_3 text NOT NULL
);

CREATE FOREIGN TABLE log_2 (
id bigint NOT NULL,
create_at timestamp NOT NULL,
customer_id bigint NOT NULL,
log_data_1 text NOT NULL,
log_data_2 text NOT NULL,
log_data_3 text NOT NULL
)
SERVER cstore_server
OPTIONS(compression 'none');


CREATE FOREIGN TABLE log_3 (
id bigint NOT NULL,
create_at timestamp NOT NULL,
customer_id bigint NOT NULL,
log_data_1 text NOT NULL,
log_data_2 text NOT NULL,
log_data_3 text NOT NULL
)
SERVER cstore_server
OPTIONS(compression 'pglz');




ダミーデータはこんなPythonプログラムを実行し、10,000,000行用意しました。
出力したCSVファイルの容量は8.2GBになりました。


  1. # -*- coding:utf-8 -*-
  2. import datetime
  3. def main():
  4.     
  5.     with open('sample.csv', 'w') as f:
  6.         for i in xrange(1, 10000001):
  7.             write_line(f, i)
  8.             
  9.             if i % 100000 == 0:
  10.                 print i
  11.             
  12.     
  13. def write_line(f, i):
  14.     
  15.     # 適当に日付を作成
  16.     create_at = datetime.datetime.fromtimestamp(1000000000 + i).strftime('%Y-%m-%d %H:%M:%S')
  17.     customer_id = i % 10000
  18.     log_text = 'abcdefghijklmnopqrstuvwxyx' * 10
  19.     
  20.     # id, create_at, customer_id, log_data_1, log_data_2, log_data_3
  21.     f.write('%d,%s,%d,%s,%s,%s\n' % (i, create_at, customer_id, log_text, log_text, log_text) )
  22. if __name__ == '__main__':
  23.     main()




COPYでデータを登録します。


sample=# COPY log_1 FROM '/path/to/sample.csv' with CSV;
COPY 10000000
sample=# COPY log_2 FROM '/path/to/sample.csv' with CSV;
COPY 10000000
sample=# COPY log_3 FROM '/path/to/sample.csv' with CSV;
COPY 10000000




cstore_fdwのテーブルはANALYZEを実行。


sample=# ANALYZE log_2;
ANALYZE
sample=# ANALYZE log_3;
ANALYZE



これで準備出来ました。




テーブルサイズ



PostgreSQLで各テーブルの総サイズと平均サイズを知る
PostgreSQL :: テーブルのサイズ(物理容量)とレコード数を調べる

上記を参考に、こんなクエリーで使用している容量をチェックしてみます。


SELECT
    relname,
    (reltuples / 1000) as trows,
    (relpages / 128) as mbytes
FROM
    pg_class
WHERE
    relname in ('log_1', 'log_2', 'log_3')
ORDER BY
    relname;


relname | trows | mbytes
---------+-----------+--------
log_1 | 10000.008 | 8680
log_2 |     10000 | 7790
log_3 |     10000 |    249
(3 rows)




text領域の内容がすべて同じとうこともあり、pglz圧縮を指定したlog_3テーブルは
通常テーブルの3%の容量にとどまっています。
これはすごい。




データの検索



idを指定して検索してみます。
全くインデックスは作成していません。
※cstore_fdwのテーブルはそもそもインデックスが作成できません。


  1. # -*- coding:utf-8 -*-
  2. import time
  3. import psycopg2
  4. def search_log(table):
  5.     con = psycopg2.connect(
  6.         host = "192.168.1.103",
  7.         port = 5432,
  8.         database="sample",
  9.         user="pgadmin",
  10.         password="P@ssw0rd")
  11.     cur = con.cursor()
  12.     cur.execute('select * from %s where id = 1' % table)
  13.     con.close()
  14.     print(u'ok')
  15. if __name__ == '__main__':
  16.     start = time.time()
  17.     search_log('log_1')
  18.     elapsed_time = time.time() - start
  19.     print('%f [sec]' % elapsed_time)




実行速度はこのようになりました。


log_1:12.048581 [sec]
log_2:0.087165 [sec]
log_3:0.069793 [sec]




cstore_fdwのテーブル、圧倒的な速度です。
log_1に対して2つのインデックスを作成し、create_atやcustomer_idで検索してみることにしました。


CREATE INDEX idx_log_1_create_at ON log_1 (create_at);
CREATE INDEX idx_log_1_customer_id ON log_1 (customer_id);




create_atの範囲指定で検索してみます。


  1. # -*- coding:utf-8 -*-
  2. import time
  3. import psycopg2
  4. def search_log(table):
  5.     con = psycopg2.connect(
  6.         host = "192.168.1.103",
  7.         port = 5432,
  8.         database="sample",
  9.         user="pgadmin",
  10.         password="P@ssw0rd")
  11.     cur = con.cursor()
  12.     query = """
  13. select
  14. min(id) as minid
  15. ,max(id) as maxid
  16. from
  17. %s
  18. where
  19. create_at >= '2001-10-01 00:00:00'
  20. and create_at < '2001-11-01 00:00:00'
  21. """ % table
  22.     cur.execute(query)
  23.     row = cur.fetchone()
  24.     print(row[0], row[1])
  25.     con.close()
  26.     print(u'ok')
  27. if __name__ == '__main__':
  28.     start = time.time()
  29.     search_log('log_3')
  30.     elapsed_time = time.time() - start
  31.     print('%f [sec]' % elapsed_time)




実行速度はこのようになりました。


log_1:5.932731 [sec]
log_2:0.520668 [sec]
log_3:0.473164 [sec]




customer_idを指定しての検索。


  1. # -*- coding:utf-8 -*-
  2. import time
  3. import psycopg2
  4. def search_log(table):
  5.     con = psycopg2.connect(
  6.         host = "192.168.1.103",
  7.         port = 5432,
  8.         database="sample",
  9.         user="pgadmin",
  10.         password="P@ssw0rd")
  11.     cur = con.cursor()
  12.     query = """
  13. select
  14. count(*) as cnt
  15. from
  16. %s
  17. where
  18. customer_id = 1234
  19. """ % table
  20.     cur.execute(query)
  21.     row = cur.fetchone()
  22.     print(row[0])
  23.     con.close()
  24.     print(u'ok')
  25. if __name__ == '__main__':
  26.     start = time.time()
  27.     search_log('log_3')
  28.     elapsed_time = time.time() - start
  29.     print('%f [sec]' % elapsed_time)




実行速度はこのようになりました。


log_1:0.690689 [sec]
log_2:0.632206 [sec]
log_3:0.715657 [sec]




インデックスを作成したテーブルと遜色ない速度で検索が実行されています。

・似たような文字列を保存している
・検索パターンが不定なのでインデックスが作成しづらい

こんなテーブルには有効なのではと思います。


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

  1. 2018/01/17(水) 15:52:34|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

cstore_fdw(PostgreSQLカラムストア拡張)のインストールと設定

ログデータを登録しているPostgreSQLのテーブルがあるのですが、サイズが肥大化してきました。
なんとか圧縮する方法はないか調べていると、このブログを見つけました。
PostgreSQLカラムストア拡張(cstore_fdw)の検証

cstore_fdwというカラムストアを再現するための拡張が圧縮に有効なようです。
https://github.com/citusdata/cstore_fdw

インストールと設置を試してみます。


Ubuntu 16.04 + PostgreSQL 10



導入する環境は、Ubuntu 16.04 + PostgreSQL 10です。
こちらの手順でインストールしたものを使用します。

PostgreSQL 10.1をUbuntu 16.04へインストールし、外部からの接続を許可




cstore_fdwのインストール



こちらのBuildingを参考にインストールを進めました。
https://github.com/citusdata/cstore_fdw

ドキュメントの記載とは異なりますが、実際に必要だったものはこちら。


$ sudo apt install protobuf-c-compiler
$ sudo apt install make g++
$ sudo apt install postgresql-server-dev-10




githubからソースを取得してビルド&インストールします。


$ cd /usr/local/src
$ sudo git clone https://github.com/citusdata/cstore_fdw.git
$ cd /usr/local/src/cstore_fdw
$ sudo PATH=/usr/local/pgsql/bin/:$PATH make
$ sudo PATH=/usr/local/pgsql/bin/:$PATH make install




続いて、postgresql.confを編集しビルドしたcstore_fdwをロードするよう指定します。


$ sudo vi /etc/postgresql/10/main/postgresql.conf




146行目付近。

shared_preload_libraries = 'cstore_fdw'


を追記します。


# - Kernel Resource Usage -

#max_files_per_process = 1000         # min 25
                                        # (change requires restart)
#shared_preload_libraries = ''         # (change requires restart)
shared_preload_libraries = 'cstore_fdw'




ファイルを編集したら、サービスを再起動。


$ sudo service postgresql restart



これで設定は完了です。





cstore_fdw拡張の有効化とテーブルの作成



postgresqlに接続し、現在有効となっている拡張機能を表示してみます。
※pgadminは作成したユーザー


$ psql -h localhost -d postgres -U pgadmin
psql (10.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#




事前にsampleというデータベースを作っておきました。
使用するデータベースをsampleに変更。


postgres=# \c sample




「\dx」で拡張の一覧を表示。


sample=# \dx
                 List of installed extensions
Name | Version | Schema |         Description        
---------+---------+------------+------------------------------
plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)




まだcstore_fdwは存在しません。
「CREATE EXTENSION cstore_fdw」を実行し、有効にします。


sample=# CREATE EXTENSION cstore_fdw;




ちゃんと登録できました。


sample=# \dx
                         List of installed extensions
    Name    | Version | Schema |                 Description                
------------+---------+------------+---------------------------------------------
cstore_fdw | 1.6     | public     | foreign-data wrapper for flat cstore access
plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)





続いてテーブルを作成していきます。
まず、SERVERを定義。


CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;




上記で作成した「cstore_server」を指定してテーブルを作成します。


CREATE FOREIGN TABLE log_table (
id bigint NOT NULL,
log_data text NOT NULL
)
SERVER cstore_server
OPTIONS(compression 'pglz');



compressionは「none(無圧縮)」か「pglz(圧縮)」が選択できます。
これでlog_tableというテーブルが作成できました。


# \d
             List of relations
Schema | Name    |     Type     | Owner
--------+-----------+---------------+---------
public | log_table | foreign table | pgadmin
(1 row)




データをinsertしようとすると、エラーになります。


sample=# insert into log_table (id, log_data) values (1, 'test');
ERROR: operation is not supported




データの登録は、


COPY [テーブル名] FROM '[cvsファイルのパス]' WITH CSV;



によるファイルからの登録か、


insert into [テーブル名] select ...;



のようにvalueをselectで指定する形式でないと登録できないそうです。


とりあえず、こんなクエリーで登録しました。


sample=# insert into log_table select 1, 'test';




登録を実行したら、ANALYZEで最適化。
(実はここの意味がよくわかっていない)


sample=# ANALYZE log_table;




selectでデータが登録できていることが確認できます。


sample=# select * from log_table;
id | log_data
----+----------
1 | test
(1 row)




upadteやdeleteもサポートされていません。


sample=# update log_table set log_data = 'test2';
ERROR: operation is not supported
sample=# delete from log_table where id = 1;
ERROR: operation is not supported




truncateによる全件削除は可能です。


sample=# truncate table log_table;
TRUNCATE TABLE
sample=# select * from log_table;
id | log_data
----+----------
(0 rows)




引き続き、どの程度の圧縮効果があるのかや検索速度について調べてみようと思います。


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

  1. 2018/01/16(火) 23:02:40|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

PostgreSQL 10.1にJava(JDBCドライバ)で接続

UbuntuにPostgreSQL 10.1をインストールしてみました。
PostgreSQL 10.1をUbuntu 16.04へインストールし、外部からの接続を許可

この環境にJavaで接続してみようと思います。
過去記事を参考にしました。
FreeBSD + PostgreSQL 9.3.2 にJavaで接続する



JDBCドライバの取得



こちらからJDBCドライバを入手します。
https://jdbc.postgresql.org/download.html

JDBCドライバのバージョン表記、変わりましたね。
プログラムはJava 8で実行する予定なので、「PostgreSQL JDBC 4.2 Driver, 42.1.4」のリンクをクリックしました。

808_01.png

「postgresql-42.1.4.jar」が取得できます。
このjarをビルドパスに含めておきます。




サンプル



データベースへの接続と検索のサンプルはこのようになりました。


  1. package com.fc2.blog68.symfoware;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.Statement;
  6. public class MainProcess {
  7.     
  8.     public static void main(String... args) {
  9.         
  10.         String url = "jdbc:postgresql://192.168.1.103:5432/sample";
  11.         String user = "pgadmin";
  12.         String pass = "P@ssw0rd";
  13.         
  14.         try (Connection con = DriverManager.getConnection(url, user, pass);
  15.                 Statement stmt = con.createStatement();
  16.                 ResultSet rs = stmt.executeQuery("select * from test")) {
  17.             
  18.             while (rs.next()) {
  19.                 System.out.println(rs.getInt("id"));
  20.                 System.out.println(rs.getString("val"));
  21.             }
  22.             
  23.             
  24.         } catch (Exception e) {
  25.             e.printStackTrace();
  26.         }
  27.     }
  28. }



無事データベースに接続し、クエリーを実行できました。

テーマ:プログラミング - ジャンル:コンピュータ

  1. 2017/11/18(土) 17:18:58|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
次のページ