「postgresqlメモ」の編集履歴（バックアップ）一覧に戻る

postgresqlメモ - (2005/12/13 (火) 15:49:22) の編集履歴(バックアップ)

---

postgresql8.1.0windows版インストール

最初は8.1.1をインストールしようとしたが日本語版がまだでていなかったようなので8.1.0で妥協。
http://www.postgresql.org/ftp/binary/v8.1.0/win32/ からpostgresql-8.1.0-1-ja.zipをダウンロード。
解凍して作成されたディレクトリ内の postgresql-8.1-ja.msi を実行。以下インストール時の様子。画像が切れてるな...

データのバックアップ

データベース全体をバックアップしてdb_backup.outというファイル名で出力する場合。

# su - postgres
$ /usr/local/pgsql/bin/pg_dumpall -o >
  /usr/local/pgsql/data/backup/db_backup.out
(以下1行で書く)

cronとかに登録しておけば自動バックアップできる。例えば毎日午前5時にバックアップする場合。

# crontab -e
(以下1行で書く)
0 5 * * * su - postgres -c 
'/usr/local/pgsql/bin/pg_dumpall -o >
/usr/local/pgsql/data/backup/db_backup.out'

簡単にテーブル作成

psqlでテーブルを作成する場合、ひとつひとつ手入力でテーブル作成するのは面倒。その場合は、SQL文を記述したファイルを用意してバッチで一発作成。

ファイル格納ディレクトリ作成。

# mkdir /home/hoge/batch

ファイル作成。例えばhoge1とhoge2というテーブルを二個作成する場合は以下。

# vi /home/hoge/batch/create.sql
--
-- hoge1
--
CREATE TABLE hoge1
(
  id     int4 NOT NULL DEFAULT 0
 ,name   varchar(255)
 ,PRIMARY KEY (id)
);

--
-- hoge2
--
CREATE TABLE hoge2
(
  id     int4 NOT NULL DEFAULT 0
 ,email   varchar(255)
 ,PRIMARY KEY (id)
);

以下コマンドでテーブル作成される。

# su - postgres
$ psql DB名 < /home/hoge/batch/create.sql 

linuxにポスグレをインストール

ソースファイルを以下からダウンロード。
ftp://ftp.jp.postgresql.org/source/v8.0.2/postgresql-8.0.2.tar.gz

ポスグレ用ユーザを追加

# adduser postgres

ダウンロードしてきたファイルをwinscpなどで/usr/local/srcに移動して、インストール開始。

解凍

# cd /usr/local/src
# tar xfvz postgresql-8.0.2.tar.gz
# rm postgresql-8.0.2.tar.gz  ←要らないので削除

インストール

# cd /usr/local/src/postgresql-8.0.2
# ./configure
# gmake
# gmake install

データ格納ディレクトリ作成

# mkdir /usr/local/pgsql/data

ポスグレユーザに権限を与える

# chown postgres /usr/local/pgsql/data

DBの初期化

# su - postgres
$ /usr/local/pgsql/bin/initdb --no-locale
  -E EUC_JP -D /usr/local/pgsql/data
(1行で書く)

起動

# /usr/local/pgsql/bin/postmaster \
  -D /usr/local/pgsql/data >logfile 2>&1 &

自動起動設定は /etc/rc.d/rc.local の最初に、以下の記述を追加。(1行で書く)

su - postgres -c "/usr/local/pgsql/bin/pg_ctl start
-D /usr/local/pgsql/data
-l /usr/local/pgsql/data/logfile
-o \"-i -p 5432\""

CSVデータをインポート

linuxにポスグレが入っている場合、以下コマンドでCSVデータをインポートできる。

# su - ポスグレユーザー名
$ /usr/local/pgsql/bin/psql DB名
$ SET client_encoding TO 'SJIS';
$ COPY テーブル名 FROM 'CSVファイル絶対パス' CSV;

【注意】

• CSVデータ定義とインポートするテーブルデータ定義が一緒じゃないとダメ。
• 改行だけしかない行も1レコードとして数えられるため、前もって置換しておかないとエラーになる。
• インポート時にエラーを何度も出すと、メモリを無駄に喰って行く。定期的にvaccumeすること。

関数

時間型を文字列型に変換

select to_char(now(), 'yyyymmdd');

結果
20050829

2つのテーブルを比較して重複しているもの以外を抽出

テーブルA

+----+------+
| id | name |
+----+------+
| 1  |  aa  |
| 2  |  bb  |
| 3  |  cc  |
| 4  |  dd  |
| 5  |  ee  |
| 6  |  ff  |
+----+------+

テーブルB

+----+------+
| id | name |
+----+------+
| 1  |  aa  |
| 2  |  bb  |
+----+------+

テーブルAにおいて、テーブルBに含まれるname以外の結果（ここではcc、dd、ee、ff）を取得したい場合は以下。

SELECT name
FROM A
WHERE name NOT IN
 (
   SELECT name
   FROM B
 );