2018年11月13日

locale C の注意

PostgreSQL
EDB Postgres Standard版でのインスト―ル時の注意点 
(たぶんEDB Postgres Enterpriseも同じと思います)


CentOS 7 / Locale C を選択
CentOS 6 / Locale C 選択
MacOX / Locale C 選択

エンコードが
SQL Ascii
になるので注意
C00ng

c02.png






Windows7/10 での  Locale C を選択

エンコードは
utf8
なので、問題なし

001.png

win7
002.png
win10
10c.png


PostgreSQL 10.5文書
文字セットサポート





posted by a23 at 00:04| Comment(0) | PostgreSQL

2018年11月12日

ロケールのテスト

ロケール設定の確認方法
SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%';
\l
---------------------------------------------
データベース作成
CREATE DATABASE t1db;

CREATE DATABASE t2db --encoding = UTF-8 --locale = ja_JP.UTF-8 --template = template0;
CREATE DATABASE t3db --encoding = UTF-8 --locale = C --template = template0;

CREATE DATABASE t5db  WITH TEMPLATE="template1" ENCODING='EUC_JP'

CREATE DATABASE t6db encoding 'SJIS';
CREATE DATABASE t7db encoding 'EUC_JP';
CREATE DATABASE t8db encoding 'UTF8';
CREATE DATABASE t9db encoding 'UTF-8';

CREATE DATABASE test10 ENCODING 'UTF-8' LC_COLLATE 'ja-x-icu' LC_CTYPE 'ja-x-icu';

---------------------------------------------
データベース変更
\c t1db;
---------------------------------------------
テーブル作成
CREATE TABLE ltbl
(
  r1   VarChar(4),
  r2   text,
  r3   text
#####  r4   text COLLATE "ja-x-icu"
---------------------------------------------

ltb.csv (utf8で保存)
---------------------------------------------

PostgreSQLのCOPYコマンドで、CSVファイルからのテーブルへのインポート
COPY ltbl FROM '/usr/local/src/ltb.csv' WITH CSV;


COPY テーブル名 FROM /フルパス/CSVファイル' WITH CSV;
ヘッダ付き
COPY テーブル名 FROM /フルパス/CSVファイル' WITH CSV header;
---------------------------------------------

#####  UPDATE ltbl
#####  set  r4  = r2;

---------------------------------------------


ltb.csv (utf8保存)
---------------------------------------------
"あ","あ","(清音)"
"い","い","(清音)"
"は","は","(清音)"
"ば","ば","(濁音)"
"ぱ","ぱ","(半濁音)"
"ハ","ハ","(清音)"
"バ","バ","(濁音)"
"パ","パ","(半濁音)"
"ア","ア",""
"イ","イ",""
"ハ","ハ",""
"バ","バ",""
"パ","パ",""
"1","1",""
"9","9",""
"1","1",""
"9","9",""
"A","A",""
"B","B",""
"a","a",""
"a","a",""
"b","b",""
"A","A",""
"B","B",""
"a","a",""
"b","b",""
"AbC","AbC",""
"aBc","aBc",""
"葉","葉",""
"波","波",""
"","",""
"","",""
"@","@",""
"A","A",""
"T","T",""
"U","U",""
"","",""
"~","~",""
---------------------------------------------

SELECT * FROM ltbl ORDER BY r1;

SELECT * FROM ltbl ORDER BY r2;

#####  SELECT * FROM ltbl ORDER BY r4;




---------------------------------------------




---------------------------------------------
#####  は理解してからにする
PostgreSQL 10のICUコレーション


posted by a23 at 17:53| Comment(0) | PostgreSQL

2018年11月11日

PostgreSQL11 yum install CentOS7 (2)

pgtuneを参考にして、confのチューニング







url.png



1.Select version: 
11
----------------------------------------------------------
2.Select platform: 
CentOS 7
----------------------------------------------------------
3.Select architecture: 
x86_64
----------------------------------------------------------
4. Install the repository RPM: 


rpm -ihv pgdg-centos11-11-2.noarch.rpm

01.png

----------------------------------------------------------
5.Install the client packages:
yum install postgresql11
02.png

6.Optionally install the server packages:
yum install postgresql11-server
03.png
----------------------------------------------------------
7.Optionally initialize the database and enable automatic start:

(PGSETUP_INITDB_OPTIONS="-E UTF-8 --locale=ja_JP.UTF-8")
(PGSETUP_INITDB_OPTIONS="-E UTF-8 --locale=c") ←間違い

export PGSETUP_INITDB_OPTIONS="-E UTF-8 --locale=C"
/usr/pgsql-11/bin/postgresql-11-setup initdb

systemctl enable postgresql-11
systemctl start postgresql-11
04.png
-------------------------
8. パスワードを設定
Postgresユーザーのパスワード設定(PostgreSQLユーザー)
su - postgres
psql
alter role postgres with password 'Passwd643';
CREATE DATABASE test1;
\l
\q
exit
05.png
----------------------------------------------------------




----------------------------------------------------------

10. conf の検索 (必須では、ありませんので、必要に応じて、行ってください)
find / -name "pg*.conf" -print
y07.png
----------------------------------------------------------

11. confを編集し、接続許可
vi /var/lib/pgsql/11/data/pg_hba.conf
vi /var/lib/pgsql/11/data/postgresql.conf

ここでは
gedit /var/lib/pgsql/11/data/pg_hba.conf




----------------------------------------------------------
10. PostgreSQLの再起動とバージョン確認
systemctl restart postgresql-11
psql --version
y09.png
----------------------------------------------------------
11. Firewallの設定
firewall-cmd --add-service=postgresql --zone=public --permanent

ファイアーウォールの再起動
systemctl restart firewalld
06.png
----------------------------------------------------------



PostgreSQL 11 検証レポートレポート

posted by a23 at 14:06| Comment(0) | PostgreSQL