PostgreSQL(一)利点の基礎(インストール、パラメータ構成を含む)を紹介する
9542 ワード
1.PostgreSQLの紹介
1.1基本概念
PostgreSQLは、カリフォルニア大学バークレー校のコンピュータ学部が開発したオブジェクト関係型データベース管理システムです.PostgreSQLは、柔軟なBSD-スタイルライセンスの下で発行されるフリーオブジェクトリレーショナル・データベース・サーバです.BSDプロトコルはビジネス統合に友好的なプロトコルであり、個人用、商用、学術研究にかかわらず、PostgreSQLを任意の目的で無料で使用、修正、配布することができます.したがって、PostgreSQLは、MySQLやFirebirdなどの他のオープンソース・データベース・システム、および独自のシステム(Oracle、Sybase、IBMのDB 2、Microsoft SQL Server)のほかに、ユーザーに選択肢を提供します.
1.2特性の利点
PostgreSQLは、ほとんどのSQLコンポーネント(サブクエリ、トランザクション、ユーザー定義タイプ、関数など)をサポートし、C、C++、Java、perl、tcl、pythonなど、非常に広い範囲の言語バインドを得ることができます.具体的な利点は次のとおりです.
(1).PostgreSQLの特性はSQL-2/SQL-92とSQL-3/SQL-99をカバーし、現在世界で最も豊富なデータ型をサポートするデータベースである.
(2).PostgreSQLは全機能のフリーソフトウェアデータベースであり、PostgreSQLはトランザクション、サブクエリ、マルチバージョン並列制御システム、データ整合性検査などの特性をサポートする唯一のフリーソフトウェアのデータベース管理システムである.
(3).PostgreSQLは古典的なC/S(client/server)構造を採用している.すなわち、クライアントがサーバ側のデーモンプロセスに対応するモードである.このデーモンプロセスはクライアントからのクエリー要求を分析し、計画ツリーを生成し、データ検索を行い、最終的に結果をグリッド化して出力した後、クライアントに返す.
(4).PostgreSQLはインタフェースのサポートも豊富で、ほとんどすべてのタイプのデータベースクライアントインタフェースをサポートしています.
2.システムアーキテクチャ
PostgreSQLデータベースは、接続管理システム(システムコントローラ)、コンパイル実行システム、ストレージ管理システム、トランザクションシステム、システムテーブルの5つの大部分から構成され、その構成構造と関係は図1-1に示す.
(1).接続管理システムは外部操作によるシステム要求を受け付け,操作要求を前処理・配布し,システム論理制御の役割を果たす.
(2).コンパイル実行システムはクエリーコンパイラ,クエリー実行器からなり,操作要求のデータベースにおける分析処理と変換作業を完了し,最終的に物理記憶媒体におけるデータの操作を実現する.
(3).ストレージ管理システムはインデックスマネージャ、メモリマネージャ、外部メモリマネージャからなり、物理データの格納と管理を担当し、コンパイルクエリーシステムのサポートを提供する.
(4).トランザクションシステムはトランザクションマネージャ、ログマネージャ、同時制御、ロックマネージャからなり、ログマネージャとトランザクションマネージャは操作要求処理のトランザクション整合性サポートを完了し、ロックマネージャと同時制御は同時アクセスデータの整合性サポートを提供する.
(5).システムテーブルはPostgreSQLデータベースのメタ情報管理センターであり、データベースオブジェクト情報とデータベース管理制御情報を含む.システムテーブルはメタデータ情報を管理し、PostgreSQLデータベースの各モジュールを有機的に接続し、効率的なデータ管理システムを形成する.
図1-1 PostgreSQLシステムアーキテクチャ
3.ソースコードのインストール
3.1環境準備
3.1.1 selinuxを閉じる
3.2インストールの導入
3.2.1関連パスの作成
3.3電源オンの設定
3.4常用パラメータ説明
以下に、必要に応じてカスタマイズできる一般的なパラメータを示します(デフォルトを選択するパラメータもたくさんあります).
3.4.1接続関連パラメータ
listen_addresses:サーバはTCP/IPアドレスを傍受する.デフォルトはlocalhostで、ローカル接続のみが可能であることを示します.「*」または「0.0.0.0」に設定することを推奨します.これは、ホスト上のすべてのIPをリスニングすることを意味します.より詳細な構成が必要な場合はpg_を変更する必要があります.hba.confプロファイル.
port:リスニングされたポート、デフォルトは5432です.
max_connections:データベースで許可される最大同時接続数.デフォルトは100で、必要に応じてカスタマイズされます.
superuser_reserved_connections:スーパーユーザーが事前に保持している接続数.デフォルトは3です.
3.4.2メモリ関連パラメータ
shared_buffers:バッファを共有するためのメモリ.
maintenance_work_mem:vacuum,analyze,create index,reindexコマンド実行効率に影響します.
work_mem:内部sortとhash操作で使用するメモリ量.
effective_cache_size:オプティマイザは、クエリーで使用できる最大メモリを仮定します.
wal_buffers:WAL(プリライトログ)データの共有メモリ量.
Autovacuum:vaccuumを自動的に実行し、デフォルトはonです.データの書き込み頻度が高いとメモリが多く消費され、クエリーのパフォーマンスが要求される場合はoffに設定し、空き時間にvaccumを手動で実行できます.
3.4.3ログ関連パラメータ
ロゴギングcollector:ログ収集をオンにしないで、デフォルトはoffです.オンにすると、他のログパラメータが有効になります.
log_rotation_size:ログファイルサイズ、デフォルトは10 MBです.50 MBの設定を推奨します.
log_truncate_on_rotation:ログファイルがすでに存在する場合、offの場合、新しく生成されたログは末尾に追加され、onの場合、元のログが上書きされます.offに設定することを推奨します.
log_destination:ログファイルフォーマット、デフォルトはstderr(標準エラー)で、csvlogを使用することを推奨します.
log_min_duration_statement:実行が遅いsqlを記録し、デフォルトは-1で、性能テストの時にこのパラメータを開くことをお勧めします.1000が1000 msを表すように設定します.
log_statement:関連するsql文を記録し、デフォルトはnoneです.全部で4種類あります:none,ddl,mod,all.noneは記録しないことを示す.ddlは、CREATE、ALTER、DROP文などのすべてのデータ定義コマンドを記録する.modはすべてのdml文を記録し、データ修正文INSERT、UPDATEなどを加える.all実行されたすべての文を記録します.
4.基本使用
4.1 psqlツール
psqlはPostgreSQLのコマンドラインインタラクティブクライアントツールで、psqlでデータベースに接続します.デフォルトのユーザーとデータベースはpostgresです.
コマンドラインの例:
psql –U username –d databasename
例:psql–U postgres–d postgres
表4-1 psql共通コマンド
コマンド#コマンド#
説明
\l
データベース・リストの表示
cライブラリ名
指定したライブラリへのデータベースの切り替え
\du
ユーザー・リストの表示
\d
表リストの表示
dテーブル名
指定したテーブルの構造の表示
\di
索引の表示
\dv
ビューの表示
\dx
関数の表示
\q
psqlを終了
4.2常用sql文
4.3共通システム表とビュー
システム・テーブルの概念は、システム・テーブルを使用すると、データベースに関する情報をよりよく表示できます.システム・ビューでは、システム・テーブルへのクエリーに便利なアクセス方法が提供されます.テーブルの詳細は、dテーブル名で表示できます.
(1).共通システム表
pg_database 現在のデータセットクラスタ内のデータベースの情報を格納します.
pg_クラス データ・テーブル、インデックス、シーケンス、ビュー、複合タイプ、およびいくつかの特殊な関係タイプのメタデータが格納されます.
pg_attributeは、pg_classなどのシステムテーブルを含むすべてのテーブルのフィールド情報を格納する.
pg_index インデックスの詳細を格納します.
(2).共通システムビュー
pg_indexes インデックス関連情報
pg_stat_activity システム・プロセスの情報.現在のセッションのアクティブなプロセスに関する情報が表示されます.
pg_tables データテーブルに関する情報
pg_settings データベース・パラメータの設定に関する情報
4.4よくある問題解決
4.4.1 databaseの削除
ERROR: database "viid "is being accessed by other users
DETAIL: There is 1 other session using the database.
他のセッションがこのデータベースを使用しているため、
解決方法1:
sqlを使用してユーザーのipを表示し、ユーザーを切断することができます.
このデータベースに接続されているすべての接続を強制的に切断し、データベースを削除します.
pg_terminate_backend():データベースとの接続プロセスidを終了する関数
pg_stat_Activity:サービスプロセスのプロパティとステータスを格納するシステムテーブル
pg_backend_pid():現在のセッションに添付されたサーバプロセスのIDを取得するシステム関数
4.4.2 databaseの作成
ERROR: source database "template1"is being accessed by other users
DETAIL: There are 1 other session(s) using the database.
なぜならpostgresqlには、2つのテンプレートライブラリtemplate 0とtemplate 1があり、ユーザーがデータベースを作成するときにデフォルトでtemplate 1をテンプレートライブラリとして作成しているからです.
解決方法1:
データベースを作成するときはtemplate 0というライブラリをテンプレートライブラリとして使用します.
このデータベースに接続されているすべての接続を強制的に切断します.
(1).データベースの初期化をインストールすると、template 0とtemplate 1は、同じで、きれいなライブラリで、内容も同じです.
(2).初期化が完了すると、ユーザーはtemplate 1をカスタマイズすることができ、例えばカスタム関数を追加することができ、新しいライブラリを作成するときにそのカスタム関数を添付し、新しいライブラリで作成する必要はありません.
(3).一般的にtemplate 0をクリーンなライブラリであることを保証するために、template 0の操作は許可されず、データベースのリカバリに役立ちます.データベース・リカバリで新しいライブラリを作成するときにtemplate 0をテンプレートとして指定し、きれいな新しいライブラリを作成できます.
(4).新しいライブラリを作成するときは新しいセッションに接続できませんが、上記の例のように、新しいセッションがテンプレートライブラリに接続されると作成に失敗します.
4.4.3サーバー接続数が制限を超えた
エラーは次のとおりです.
sorry, too many clients already
解決策2:
max_の変更connectionsパラメータ構成
Postgresqlインストールディレクトリの下のdataフォルダを開き、postgresql.auto.confファイルを見つけて、次の言葉を追加します.
max_connections = '800'
再起動pgの変更が完了しました
1.1基本概念
PostgreSQLは、カリフォルニア大学バークレー校のコンピュータ学部が開発したオブジェクト関係型データベース管理システムです.PostgreSQLは、柔軟なBSD-スタイルライセンスの下で発行されるフリーオブジェクトリレーショナル・データベース・サーバです.BSDプロトコルはビジネス統合に友好的なプロトコルであり、個人用、商用、学術研究にかかわらず、PostgreSQLを任意の目的で無料で使用、修正、配布することができます.したがって、PostgreSQLは、MySQLやFirebirdなどの他のオープンソース・データベース・システム、および独自のシステム(Oracle、Sybase、IBMのDB 2、Microsoft SQL Server)のほかに、ユーザーに選択肢を提供します.
1.2特性の利点
PostgreSQLは、ほとんどのSQLコンポーネント(サブクエリ、トランザクション、ユーザー定義タイプ、関数など)をサポートし、C、C++、Java、perl、tcl、pythonなど、非常に広い範囲の言語バインドを得ることができます.具体的な利点は次のとおりです.
(1).PostgreSQLの特性はSQL-2/SQL-92とSQL-3/SQL-99をカバーし、現在世界で最も豊富なデータ型をサポートするデータベースである.
(2).PostgreSQLは全機能のフリーソフトウェアデータベースであり、PostgreSQLはトランザクション、サブクエリ、マルチバージョン並列制御システム、データ整合性検査などの特性をサポートする唯一のフリーソフトウェアのデータベース管理システムである.
(3).PostgreSQLは古典的なC/S(client/server)構造を採用している.すなわち、クライアントがサーバ側のデーモンプロセスに対応するモードである.このデーモンプロセスはクライアントからのクエリー要求を分析し、計画ツリーを生成し、データ検索を行い、最終的に結果をグリッド化して出力した後、クライアントに返す.
(4).PostgreSQLはインタフェースのサポートも豊富で、ほとんどすべてのタイプのデータベースクライアントインタフェースをサポートしています.
2.システムアーキテクチャ
PostgreSQLデータベースは、接続管理システム(システムコントローラ)、コンパイル実行システム、ストレージ管理システム、トランザクションシステム、システムテーブルの5つの大部分から構成され、その構成構造と関係は図1-1に示す.
(1).接続管理システムは外部操作によるシステム要求を受け付け,操作要求を前処理・配布し,システム論理制御の役割を果たす.
(2).コンパイル実行システムはクエリーコンパイラ,クエリー実行器からなり,操作要求のデータベースにおける分析処理と変換作業を完了し,最終的に物理記憶媒体におけるデータの操作を実現する.
(3).ストレージ管理システムはインデックスマネージャ、メモリマネージャ、外部メモリマネージャからなり、物理データの格納と管理を担当し、コンパイルクエリーシステムのサポートを提供する.
(4).トランザクションシステムはトランザクションマネージャ、ログマネージャ、同時制御、ロックマネージャからなり、ログマネージャとトランザクションマネージャは操作要求処理のトランザクション整合性サポートを完了し、ロックマネージャと同時制御は同時アクセスデータの整合性サポートを提供する.
(5).システムテーブルはPostgreSQLデータベースのメタ情報管理センターであり、データベースオブジェクト情報とデータベース管理制御情報を含む.システムテーブルはメタデータ情報を管理し、PostgreSQLデータベースの各モジュールを有機的に接続し、効率的なデータ管理システムを形成する.
図1-1 PostgreSQLシステムアーキテクチャ
3.ソースコードのインストール
3.1環境準備
3.1.1 selinuxを閉じる
vi /etc/selinux/config
SELINUX="" disabled ( )chkconfig iptables off
systemctl disable firewalld# gcc
yum -y install gcc
# zlib
yum -y install zlib-devel
# readline
yum -y install readline-devel# postgres
groupadd postgres
# postgres
useradd -g postgres postgres
# postgres
passwd postgresvi /etc/profile
# ：
export PGHOME=/usr/local/pgsql
export PGDATA=$PGHOME/data
PATH=$PATH:$PGHOME/bin
# ：
source /etc/profile3.2インストールの導入
3.2.1関連パスの作成
mkdir /usr/local/pgsql/
mkdir /usr/local/pgsql/data
mkdir /usr/local/pgsql/log# postgresql-10.5.tar.gz
tar -zxvf postgresql-10.5.tar.gz
cd postgresql-10.5
# ./configure postgresql
./configure --prefix=/usr/local/pgsql
make
make install
# /usr/local/pgsql/ postgres
chown -R postgres:postgres /usr/local/pgsql/
# postgres
su - postgres
#
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
# ( /usr/local/pgsql/data )：
postgresql.conf( )： listen_addresses='*'
pg_hba.conf( )。
#
/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data
#
# postgres role ，
su - postgres && psql
alter role postgres with password 'postgres';
3.3電源オンの設定
# root
# pg /etc/init.d postgresql
cp /home/postgresql-10.5/contrib/start-scripts/linux /etc/init.d/postgresql
# ：
vi /etc/init.d/postgresql
# ：
--------------------------------------------------------------
## EDIT FROM HERE
# Installation prefix
prefix=/usr/local/pgsql
# Data directory
PGDATA="/usr/local/pgsql/data"
# Who to run the postmaster as, usually "postgres". (NOT "root")
PGUSER=postgres
# Where to keep a log file
PGLOG=/usr/local/pgsql/log/pg_server.log
--------------------------------------------------------------
# ：
chmod a+x /etc/init.d/postgresql
# chkconfig
chkconfig --add postgresql3.4常用パラメータ説明
以下に、必要に応じてカスタマイズできる一般的なパラメータを示します(デフォルトを選択するパラメータもたくさんあります).
3.4.1接続関連パラメータ
listen_addresses:サーバはTCP/IPアドレスを傍受する.デフォルトはlocalhostで、ローカル接続のみが可能であることを示します.「*」または「0.0.0.0」に設定することを推奨します.これは、ホスト上のすべてのIPをリスニングすることを意味します.より詳細な構成が必要な場合はpg_を変更する必要があります.hba.confプロファイル.
port:リスニングされたポート、デフォルトは5432です.
max_connections:データベースで許可される最大同時接続数.デフォルトは100で、必要に応じてカスタマイズされます.
superuser_reserved_connections:スーパーユーザーが事前に保持している接続数.デフォルトは3です.
3.4.2メモリ関連パラメータ
shared_buffers:バッファを共有するためのメモリ.
maintenance_work_mem:vacuum,analyze,create index,reindexコマンド実行効率に影響します.
work_mem:内部sortとhash操作で使用するメモリ量.
effective_cache_size:オプティマイザは、クエリーで使用できる最大メモリを仮定します.
wal_buffers:WAL(プリライトログ)データの共有メモリ量.
Autovacuum:vaccuumを自動的に実行し、デフォルトはonです.データの書き込み頻度が高いとメモリが多く消費され、クエリーのパフォーマンスが要求される場合はoffに設定し、空き時間にvaccumを手動で実行できます.
3.4.3ログ関連パラメータ
ロゴギングcollector:ログ収集をオンにしないで、デフォルトはoffです.オンにすると、他のログパラメータが有効になります.
log_rotation_size:ログファイルサイズ、デフォルトは10 MBです.50 MBの設定を推奨します.
log_truncate_on_rotation:ログファイルがすでに存在する場合、offの場合、新しく生成されたログは末尾に追加され、onの場合、元のログが上書きされます.offに設定することを推奨します.
log_destination:ログファイルフォーマット、デフォルトはstderr(標準エラー)で、csvlogを使用することを推奨します.
log_min_duration_statement:実行が遅いsqlを記録し、デフォルトは-1で、性能テストの時にこのパラメータを開くことをお勧めします.1000が1000 msを表すように設定します.
log_statement:関連するsql文を記録し、デフォルトはnoneです.全部で4種類あります:none,ddl,mod,all.noneは記録しないことを示す.ddlは、CREATE、ALTER、DROP文などのすべてのデータ定義コマンドを記録する.modはすべてのdml文を記録し、データ修正文INSERT、UPDATEなどを加える.all実行されたすべての文を記録します.
4.基本使用
4.1 psqlツール
psqlはPostgreSQLのコマンドラインインタラクティブクライアントツールで、psqlでデータベースに接続します.デフォルトのユーザーとデータベースはpostgresです.
コマンドラインの例:
psql –U username –d databasename
例:psql–U postgres–d postgres
表4-1 psql共通コマンド
コマンド#コマンド#
説明
\l
データベース・リストの表示
cライブラリ名
指定したライブラリへのデータベースの切り替え
\du
ユーザー・リストの表示
\d
表リストの表示
dテーブル名
指定したテーブルの構造の表示
\di
索引の表示
\dv
ビューの表示
\dx
関数の表示
\q
psqlを終了
4.2常用sql文
--
CREATE TABLE user (name VARCHAR(20), createdate DATE);
--
INSERT INTO user (name, createdate) VALUES(' ', '2016-07-22');
--
SELECT * FROM user;
--
UPDATE user set name = ' ' WHERE name = ' ';
--
DELETE FROM user WHERE name = ' ' ;
--
ALTER TABLE user ADD email VARCHAR(40);
--
ALTER TABLE user ALTER COLUMN createdate SET NOT NULL;
--
ALTER TABLE user RENAME COLUMN createdate TO cdate;
--
ALTER TABLE user DROP COLUMN email;
--
ALTER TABLE user RENAME TO backup;
--
DROP TABLE IF EXISTS backup;4.3共通システム表とビュー
システム・テーブルの概念は、システム・テーブルを使用すると、データベースに関する情報をよりよく表示できます.システム・ビューでは、システム・テーブルへのクエリーに便利なアクセス方法が提供されます.テーブルの詳細は、dテーブル名で表示できます.
(1).共通システム表
pg_database 現在のデータセットクラスタ内のデータベースの情報を格納します.
pg_クラス データ・テーブル、インデックス、シーケンス、ビュー、複合タイプ、およびいくつかの特殊な関係タイプのメタデータが格納されます.
pg_attributeは、pg_classなどのシステムテーブルを含むすべてのテーブルのフィールド情報を格納する.
pg_index インデックスの詳細を格納します.
(2).共通システムビュー
pg_indexes インデックス関連情報
pg_stat_activity システム・プロセスの情報.現在のセッションのアクティブなプロセスに関する情報が表示されます.
pg_tables データテーブルに関する情報
pg_settings データベース・パラメータの設定に関する情報
4.4よくある問題解決
4.4.1 databaseの削除
DROP DATABASE viid;ERROR: database "viid "is being accessed by other users
DETAIL: There is 1 other session using the database.
他のセッションがこのデータベースを使用しているため、
解決方法1:
sqlを使用してユーザーのipを表示し、ユーザーを切断することができます.
SELECT * FROM pg_stat_activity WHERE datname= ‘viid’;このデータベースに接続されているすべての接続を強制的に切断し、データベースを削除します.
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity WHERE datname='test'
AND pid<>pg_backend_pid();pg_terminate_backend():データベースとの接続プロセスidを終了する関数
pg_stat_Activity:サービスプロセスのプロパティとステータスを格納するシステムテーブル
pg_backend_pid():現在のセッションに添付されたサーバプロセスのIDを取得するシステム関数
4.4.2 databaseの作成
CREATE DATABASE viid;ERROR: source database "template1"is being accessed by other users
DETAIL: There are 1 other session(s) using the database.
なぜならpostgresqlには、2つのテンプレートライブラリtemplate 0とtemplate 1があり、ユーザーがデータベースを作成するときにデフォルトでtemplate 1をテンプレートライブラリとして作成しているからです.
解決方法1:
データベースを作成するときはtemplate 0というライブラリをテンプレートライブラリとして使用します.
CREATE DATABASE viid WITH template=template0;このデータベースに接続されているすべての接続を強制的に切断します.
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity WHERE datname='template1' AND pid<>pg_backend_pid();(1).データベースの初期化をインストールすると、template 0とtemplate 1は、同じで、きれいなライブラリで、内容も同じです.
(2).初期化が完了すると、ユーザーはtemplate 1をカスタマイズすることができ、例えばカスタム関数を追加することができ、新しいライブラリを作成するときにそのカスタム関数を添付し、新しいライブラリで作成する必要はありません.
(3).一般的にtemplate 0をクリーンなライブラリであることを保証するために、template 0の操作は許可されず、データベースのリカバリに役立ちます.データベース・リカバリで新しいライブラリを作成するときにtemplate 0をテンプレートとして指定し、きれいな新しいライブラリを作成できます.
(4).新しいライブラリを作成するときは新しいセッションに接続できませんが、上記の例のように、新しいセッションがテンプレートライブラリに接続されると作成に失敗します.
4.4.3サーバー接続数が制限を超えた
エラーは次のとおりです.
sorry, too many clients already
解決策2:
max_の変更connectionsパラメータ構成
Postgresqlインストールディレクトリの下のdataフォルダを開き、postgresql.auto.confファイルを見つけて、次の言葉を追加します.
max_connections = '800'
再起動pgの変更が完了しました
pg_ctl restart -D /usr/local/pgsql/data