PostgreSQL：(作業メモ)データベースを別サーバー向けに複製したい

　現在業務に使っているサーバーはPostgreSQL 8.4で、これから引っ越すとおそらく最新は PostgreSQL 9.6だろうってことを想定して、実際にデータベース含めてシステムが引越しできるかを検討しているわけです。

現行サーバーで、pg_dumpallする

　「24.1.2. pg_dumpallの使用」に従って、現行サーバーのコンソール上で「pg_dumpall > outfile」ってやりました。
　こりゃ簡単だと思ったものの、何度か「Password:」なんてプロンプトが表示されて少しパニクりました。　結果的には、スーパーユーザーであるpostgresさんのパスワード入力要求でした。
　データベースをまるごとダンプするために、各データベース（テンプレートも含めて）に片っ端から接続（\connect）して構造を暴き出している感じでした。　outfileを見てみたらわかりました。

ロケールをこの際UTF-8にしておきたい

　現行サーバーのデータベースが、うっかりEUC_JPで作ってしまっていたので、これをこの際UTF-8に正したいと考えました。
　outfile をローカルにダウンロードしてテキストエディターで読み込みます。　でも、たまたま930MBくらいあるファイルサイズなので、そうやすやすと読み込めるエディターってないんですよ。　私はEmEditor (テキストエディタ)を使用しています。　巨大ファイルでもスルスル読み込めてストレスが無いんです。
　不要なDBユーザーに関する「CREATE ROLE」や「ALTER ROLE」や「GRANT」文もついでにカットします。　レンタルサーバーのサービスで自動的に登録された使用していないDBユーザーを継承しないためです。
　肝心の「CREATE DATABASE」文をチェックして、「ENCODING = 'EUC_JP'」から「ENCODING = 'UTF8'」に変更したり、「SET client_encoding = 'UTF8';」にしつつ、ファイルを保存するときのエンコードもUTF-8(BOMなし)としました。　改行コードも「LFのみ」としました。

テスト環境はWindows10上にPostgreSQL 9.6.2-3 x64版

　配布されているインストーラー「postgresql-9.6.2-3-windows-x64.exe」を実行してインストールしました。　いや、その前にインストールするだけして全然使っていなかった 9.4をアンインストールして追加ソフト類も削除して、データベースファイル類も削除してからの、9.6インストールなのですけど。
　さて、いざ実行しようと、「psql -f infile postgres」をUSBメモリであるDドライブ上でコマンド プロンプトにて実行してみたところ動かない。　結果的には、「"C:\Program Files\PostgreSQL\9.6\bin\psql.exe" -f infile postgres postgres」となりました。　PostgreSQLインストール先にPATHが通っていないのでフルパス名で記述しました。　こういうのは面倒なので、システム環境変数PATHに「C:\Program Files\PostgreSQL\9.6\bin」を追加しておくと後でラクです。
　それと、わざわざ2つpostgresと書いているのは、一つ目はデータベース名で二つ目はDBユーザー名ということです。　二つ目のpostgresを省いて実行すると、Windows上のログインユーザー名を勝手に使って実行しようとして、そんなユーザーはいないみたいなエラーになってしまうための対策でした。
　ちゃんとデータベースが複製できているか、pgAdmin4を使ってスキーマを点検してみると、ちゃんと出来ているのが確認できてホッとしました。

PostgreSQL: 日数を求める

日数を求めるのに、日付と日付の引き算をします

　日付型どうしなら、引き算すると整数値が出てきます。

select cast('2016-12-01' as date) - cast('2016-11-01' as date) 

　タイムスタンプ型どうしなら、interval型で出てきます。

select cast('2016-12-01' as timestamp) - cast('2016-11-01' as timestamp) 

　interval型には数値と単位が融合していて便利な半面、単純に日数の数値だけほしいときに困ります。
　そんなときは、下記のように「extract(epoch from ～ )」を活用します。

select extract(epoch from (cast('2016-12-01' as timestamp) - cast('2016-11-01' as timestamp))) / (60 * 60 * 24) 

　interval型をepochで抽出すると秒数に変わるので、一日の秒数である「60 * 60 * 24」つまり「86400」で割れば良いとのことです。
　こちらのブログ記事を参考にしました。　ありがとうございます。
d.hatena.ne.jp

余談ですが、timestamp型どうしの日数を求めるときの注意点

select cast('2016-12-01 9:00:00' as timestamp) - cast('2016-11-01 15:00:00' as timestamp) 

　timestamp型の場合、時刻成分の影響で期待する日数が得られない場合があります。　この事例だと厳密には30日に達していないのですが、時刻成分を無視して日付単位で日数を得るには、「date_trunc('day', ～)」を活用します。

select date_trunc('day', cast('2016-12-01 9:00:00' as timestamp)) - date_trunc('day', cast('2016-11-01 15:00:00' as timestamp))