PostgreSQLでDBを複製する方法とシーケンスだけ作られない時の対処法 #PostgreSQL

はじめに

デモサイト用に作ったローカルに保存してあるDBをDBサーバーに複製する際に、シーケンスが作られず思ったより苦戦してしまったので備忘録として記録
※超初心者(完全未経験文系のエンジニア歴３ヶ月)が書いてる記事であることにご注意
※記事の内容や理解に間違いがあったらご指摘いただけると嬉しいです。

【DB】
PostgreSQL 11.3
（バージョン違いの際の注意点についても記載）

【OS（ローカル）】
Mac OS X
ProductVersion: 10.14.6
BuildVersion: 18G95

【OS（サーバー）】
CentOS Linux release 7.6.1810 (Core)

（0.サーバー側に空のDBを用意する）
1.dumpファイルを作成する
2.作成したdumpファイルをリストアする

基本的には、1・2だけで終わる。
今回はリストア先のサーバーに複数のDBが存在していたので、干渉がないように空のDBを用意してそこにリストアする手順で行なっている。

空っぽのDBを用意

SQL

    CREATE DATABASE hogeDB

Macのターミナルから直接実行。
バージョン違いの対処もできるようにするためにスクリプト形式でdumpファイルを出力する。
※普通にやる場合は、オプションで"-Fc"をつけてアーカイブ形式にした方が、容量も小さくなりBetterな気がする

スクリプト形式

    pg_dump -h localhost -U ユーザ名 DB名 > DB名.dump

アーカイブ形式

    pg_dump -h localhost -U ユーザ名 -Fc DB名 > DB名.dump

Macのターミナルから直接実行する

スクリプト形式

    psql -h localhost -U ユーザ名 -f DB名.dump hogeDB

アーカイブ形式

    pg_restore -h サーバーのIPアドレス -U ユーザ名 DB名.dump -d hogeDB

dump元とrestore先のPostgresqlのバージョン違いが主な原因。
異なるバージョンのPostgresqlで作成したdumpファイルは、一部中身を書き換えないとシーケンスが作成されない場合がある。

実際、バージョン8.1がインストールされているサーバーに、11.3で作成したdumpファイルをリストアしようとした際、オプションや形式を変えてもシーケンスがサーバー側で作成されず、その原因にすぐ気付けずに時間を無駄にしてしまった。

バージョン違いの対処方法としては、作成したdumpファイルを直接テキストエディタで開いて、AS integerの部分を削除すれば異なるバージョンでもシーケンス込みでリストアできる。

dumpファイル（スクリプト形式）　一部抜粋

    CREATE SEQUENCE public.シーケンス名
    AS integer --この行を消す
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

PostgreSQLのバージョンが8.1等の古いものだと「AS integer」という風に指定ができず、エラーとなってしまってシーケンス自体が作られないこととなる。

以下、「PostgreSQL 8.3.7文書」より引用

互換性
以下の例外を除き、CREATE SEQUENCEは標準SQLに従います。

標準におけるAS 式はサポートされていません。

次の値を取り出すには、標準のNEXT VALUE FOR式ではなくnextval()関数を使用します。

OWNED BY句はPostgreSQLの拡張です。

　
以下、「PostgreSQL 11.4文書」より引用

互換性
以下の例外を除き、CREATE SEQUENCEは標準SQLに従います。

次の値を取り出すには、標準のNEXT VALUE FOR式ではなくnextval()関数を使用します。

OWNED BY句はPostgreSQLの拡張です。

公式文書を見ても、例外でなくなっていることがわかる。
また、11.4の CREATE SEQUENCE 文を見るとオプションにしっかりと[ AS data_type ]が書けるようになっている。