PostgreSQLにてALTER TABLEでカラムのデータ型を変更

下記のように普通に実行すると、USING式を指定してくださいとエラーが表示されます。

ALTER TABLE ttest ALTER COLUMN col1 TYPE bytea;

下記のように明示的に指定すると変更が可能です。

ALTER TABLE ttest ALTER COLUMN col1 TYPE bytea USING col1::bytea;

PostgreSQLで既存の列の型を変更する

以下の方法で可能

ALTER TABLE table_hoge ALTER COLUMN column_fuga TYPE bytea USING CAST(column_fuga AS bytea);

別ポートのPostgreSQLを操作する

起動など

pg_ctl -D データディレクトリ -P ポート番号 startなど

例）pg_ctl -D /home/hoge/data -P 5433 start

ユーザ作成

createuser -a -d -U ユーザ名 -P ユーザ名（パスワード入力プロンプト表示） --port=ポート番号;

例）createuser -a -d -U hoge -P hoge --port=5433 ;

データベース作成

createdb -E UTF-8 -O 所有者 -U 接続するユーザ名 データベース名 --port=5433;

例）createdb -E UTF-8 -O hoge -U fuga hoge --port=ポート番号;

PostgreSQLインストール

CentOS6.3に対してインストールを行います。 事前にデフォルトでインストールされているPostgreSQLは削除しておいてください。

リポジトリの追加とインストール

rpm -ivh http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm

yum install --enablerepo=pgdg92 postgresql92*

初期化

service postgresql-9.2 initdb

起動

service postgresql-9.2 start

自動起動と確認

chkconfig postgresql-9.2 on

chkconfig --list | grep postgresql-9.2

postgresユーザのパスワード設定

passwd postgres

データベースユーザの作成、データベースの作成

su - postgres

createuser -a -d -U postgres -P ユーザ名

createdb -E UTF-8 -O オーナー -U postgres データベース名

PostgreSQLのバックアップ/リストア

バックアップ

pg_dump データベース名 -b -o -Fc > バックアップファイル名

例）pg_dump hogedb -b -o -Fc > hogedb20130509.pgdmp

• -b・・・ラージオブジェクトをダンプに含める
• -o・・・各テーブルのオブジェクト識別子（OID）をデータの一部としてダンプする
• -Fc・・・custom形式で出力

リストア

データベースごとリストアする

pg_restore -U "データベースユーザ名" -d "データベース名" --no-password -v -F c "バックアップファイル名"

例）pg_restore -U "fugauser" -d "hogedb" --no-password -v -F c "hogedb20130509.pgdmp"

特定のテーブルをリストアする

pg_restore -U "データベースユーザ名" -d "データベース名" --no-password -t "テーブル名" -v -F c -a "バックアップファイル名"

例）pg_restore -U "fugauser" -d "hogedb" --no-password -t "footable" -v -F c -a "hogedb20130509.pgdmp"

特定のテーブルをリストアする際にはまったポイントとしては
テーブル名の大文字小文字。リストアされず困っていたらテーブル名が大文字でした。。

もうひとつ大事なポイントとして、「a」オプションです。
これはリストアする際にデータのみリストアするというもの。スキーマごとリストアする場合、インデックス等が生成されないので注意が必要です。