PostgreSQL 初心者向け 導入備忘録

この記事について

Webフレームワークの勉強をするためにサックリインストールを済ませたい時のための備忘録
環境はWindows7-64 Postgres9.6

#インストール
PostgreSQL 公式
https://www.postgresql.org/

公式からダウンロードしてインストールする
基本的にデフォルトの設定のままで問題ない。パスワードを忘れないようにメモ。

デフォルト時の基本設定

設定ファイル：install_dir/data/postgresql.conf
接続制限 ：install_dir/data/pg_hba.conf
ログファイル：install_dir/data/pg_log

管理者名のデフォルト：postgres
ポート番号デフォルト：5432

概要

階層ツリー

データベースサーバ
　└ 1:N データベース
　　└1:N スキーマ(デフォルト:Public)
　　　└1:N テーブル、インデックス等

postgresにおけるデータベースとは

スキーマのコンテナ。
※他にもあるけどウェブフレームワークで使う場合それほど意識する必要はない

postgresにおけるスキーマとは

• 個々のデータベースが所有する名前空間。

• テーブル、インデックス等のオブジェクトは必ずいずれかの名前空間に所属する。デフォルトでは「Public」

• ユーザー名と同じ名前でスキーマを作ると、対象ユーザーのオブジェクト検索の優先順位が以下のようになる。

  １．自分の名前のスキーマ．オブジェクト名
  ２．Publicスキーマ．オブジェクト名

※Webフレームワークで使う場合スキーマを活用することは少ない。テストはテスト専用のデータベースを作成するのが一般的。

最初から知っておいた方がいいこと

・エラーメッセージについて

一部学術的な用語が用いられており所見時は混乱する

• リレーション ＝ テーブルのこと
• タプル ＝ レコードのこと
  • 例：「リレーションがありません。」→「テーブルがありません。」の意

・データベースを横断して処理ができない

基本的に接続したデータベース外にあるオブジェクトの操作はできない。管理者でも同じ。操作する場合はDBLINKの作成が必要となる。

・template0とは何か

データベースの作成テンプレート。
template0は完全デフォルト、template1は自分でカスタマイズして使うテンプレート。大体はtemplate0を使えばＯＫ．

#起動と確認

[Windowsの場合]
Windowsの場合、Postgresがサービスに登録されるのでサービスの管理から起動・停止。net start や 起動コマンドの本体である「pg_ctl」でもよい

※ネットワーク疎通確認コマンド
サーバ：netstat -a | find 5432
クライアント：telnet hostname 5432

#クライアントツール

psql

コマンドシェル。postgresqlインストール時に同時にインストールされる。

コマンド例：

psgl -U ユーザ名 -h ホスト名 -l
	データベース一覧の表示

psgl -U ユーザ名 -h ホスト名 -d DB名 
	データベースに接続

\h 
    SQLコマンドの文法ヘルプ * で全コマンドを表示
\x 
    拡張表示ON/OFF。クエリー結果のカラムが縦に表示されて見やすくなる。
\i [ファイルパス]
    ファイルからコマンドを読み込んで実行
\dt
    テーブル一覧を表示
\du
    ユーザー一覧を表示
\c [データベース名]
    別のデータベースに接続する

参考：postgres-cheatsheet

##pgAdmin

公式のGUIツール
https://www.pgadmin.org/

※postgresqlにバンドルされているバージョンは古いことがあるので、こちらから別途ダウンロードした方がよい。最新はpgadmin4 Ver2.0

オブジェクトがツリー表示され、右クリックからオブジェクトを操作できる。
基本構造を把握できるため、コマンドライン派も最初は使っておくとよい。

クエリーの結果の表示がやや重くストレスを感じる。
管理コマンド以外の操作は、別の軽いツールを使うことをおすすめする。

##その他のGUIツール

A5:SQL Mk2

https://a5m2.mmatsubara.com/
日本産でほぼ機能に不足はなく動作も軽快

###DBvear
https://dbeaver.jkiss.org/
現在進行形で開発中のアプリ
日本語対応していないが、SQL作成時のインテリセンスが強力

※他にも多くあるので、自分の好きなのを使おう。

#開発時の利用準備作業
インストール後に行う作業。pgadminで作業する場合。

##手順１．ユーザー作成

1. pgadmin起動。開発サーバがPostgreSQL X.Xの名前で登録済みになっている。
2. サーバーのアイコンを右クリックし、create⇒login/roleを選ぶ
3. 開発者アカウントの情報を入力し登録。

##手順２．データベース作成

1. 開発者アカウントと同名でデータベースを作成
2. 作成したデータベースの所有者を開発者アカウントに変更する。
3. 作成したデータベースのスキーマ「Public」の所有者を開発者アカウントに変更する

※後述するスキーマ削除を実行できるようにするため

##手順３．コマンドラインで接続確認

シェルより「psql -U 開発者アカウント」として接続を確認

##手順４．アプリケーションより接続確認

[Djangoでの例]
DB接続の設定ファイルを変更し、アプリケーションから接続を確認する

※Windowsは追加パッケージ psycopg2が必要。確認コマンドは「manage.py dbshell」等。

setting.py

  
	DATABASES = {
	    'default': {
	        'ENGINE': 'django.db.backends.postgresql',
	        'NAME': 'DevUser',
	        'USER': 'DevUser',
	        'PASSWORD': 'Password',
	        'HOST': 'localhost',
	        'PORT': '',
	    }
	}

##TIPS:テーブル等を全削除する

テーブル等を全削除したいときは、スキーマを削除して再作成するのが早い

drop schema public cascade;
create schema public;