PostgreSQLの使い方 (Windows環境)

SQL

PostgreSQLの使い方 (Windows環境)

Windows環境で、PostgreSQLを使う際の情報があまり見つからなかったので、
調べてまとめてみた。
自分が使っている環境は、Ver 9.3.1 です。


○ GUIで管理する
使っているアプリケーションは、
１．PostgreSQL
２．pgAdmin III
３．ActiveRecordなどでテーブル操作 (CUI)
４．Netbeansのデータベース機能でデータ操作


２番は、新規ユーザを使ったり、新規データベースを作成したりするのに使ってます。
３番は、テーブル作るのに使います。
４番は、直接データを操作するのに使います。


○ ユーザについて
デフォルトのユーザ(スーパーユーザ)は、 postgres というユーザです。
ユーザを追加したい場合は、pgAdmin III を使って、ログインロールを追加します。


○ データの保存場所
psql -u postgres でPostgresqlにログインします。
そこで
show data_directory;
を実行すると、データの保存場所を確認できます。


自分のWindows環境では
C:/Program Files/PostgreSQL/9.3/data
でした。


ちなみに、インストールした後に、データの保存場所を変える方法はどうするのかよくわかりません。
UNIXだと、PGDATA 変数を変えればいいみたいです。


Windowsでも、PGDATA環境変数を新しく設定すればいいのかもしれませんが、
おそらく、ここに書いてあるように、レジストリの値を変更しないといけないように思います。
http://ashleyangell.com/2009/01/moving-the-default-postgresql-data-directory-windows/


○ バックアップ編
コールドバックアップと、ホットバックアップ。
あまり詳しくないですが、
コールドバックアップは、上記のようなデータ保存ディレクトリをそのままコピーする方法。
定期的に、コールドバックアップを取って置き、何か問題が起きれば、とりあえず、コピーしたデータをまた戻して来れば、タイムマシンみたいにデータを戻すことができるようです。

ホットバックアップは、SQL形式(あるいはカスタム形式)で、データを記述しておき、
復元する際は、それに従って、データベースを作り直します。
そのため、SQLのバージョンが違ったりしても対応できるようです。
実際は、本番環境にディプロイするときなどに使うんでしょうか。あとは、メジャーバージョンアップするときに。



※ ここから先は、マニュアルや、ネットの情報を集めながら書いてまとめたもので、自身で何度も検証できたわけではないので、
※ 実際に、この情報をもとにバックアップ・リストアする場合は、事前に検証して行うようにしてください。


1. コールドバックアップ
PostgreSQLを停止させてから、
上記の、データディレクトリを、コピーしておく。

(※ただし、自分ではこの方法でバックアップ・復元させたことがないので、うまくいくかわかりません。
もしかすると、フォルダのパーミッション(右クリックセキュリティ)を変更しないと復元できないかも。。)


2. ホットバックアップ
pg_dump [options] [データベース名] > [出力先] にてバックアップを取る

例) pg_dump -U postgres -F custom development_db > development_db.backup
※ ちなみに、出力先は、ユーザの権限のあるフォルダでないと、アクセス拒否されます。 (あるいは管理者権限でコマンドプロンプト起動？)


オプションとしては、
カスタム形式の指定は、 -F custom (マニュアルにカスタム形式が最も柔軟と書いてあるため)
特定のテーブルのみは、-t table_name


出力先は、 > my_database_dump.custom


※Windowsでも、リダイレクション(>や<や2>&1)はUNIXと同じなんですね。


3. (カスタム形式)ホットバックアップからのリカバリ
pg_restore -F custom [オプション] [バックアップファイル]

例) pg_restore -U postgres -F custom -d new_database development_db.backup
※ 先にpsql 内で、CREATE DATABASE new_database; して、新しいデータベース作っておく。


オプションとしては
リカバリ先のデータベースは、デフォルトでは、バックアップ作ったものと同名。
バックアップと別のデータベース名を指定するには、 -d database_name
オプションの -C と-c に関してはいまいちよくわからない。


※ SQL形式でバックアップしているなら、それを実行するだけでよい。(pg_restoreでなくpsqlでできる。)


○ コマンドでいろいろ頑張る場合
psql内で、以下を行う。


・データベースの作成
CREATE DATABASE database_name;
CREATE DATABASE database_name OWNER owner_name ;

・ユーザの作成
CREATE ROLE role_name WITH LOGIN PASSWORD 'PASSWORD';


・データベースのオーナー変更
ALTER DATABASE database_name OWNER TO user_name ;