Postgresql のデータベースコピー

6205 ワード
PostgreSQL Database copy PostgreSQL テキストリンク

目的

ローカルで自由にデータをいじったりしてテストしたいので、サーバー側のデータをローカルDBにコピーすると思った。コマンド一つで終わるかと思ったら、意外と時間を食ってしまったから、その過程をメモする。

環境

Host：Mac Os Seirra High
VMsoft: VirutualBox
VM: Ubuntu 16.04
DB: Windows7 ＋ Posgresql9.6

手順

  1. 症状
    pg_dump で一発でデータベースを丸ごとコピーするかと思ったら、そうは簡単に行かない。
   pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname

なんと、「コピー元のDBは9.6で、お前のDBは9.5だからダメだよ」と怒られた。
ググった結果、方法色々ありそうですが、どれも確実じゃなさそうに見えるし、依存条件もあれこれあるし、まぁいい、どうせなら、ローカルDBも9.6にしちゃおうと。

2.旧DBの削除

DBの削除するぐらいなら、大した作業ではないだろうと思ったが、それはまた甘かった。

sudo apt-get --purge remove postgresql
sudo apt autoremove
dpkg -l | grep postgres

あれ、まだいっぱい残ってるんじゃないですか。

sudo apt-get --purge remove postgresql
sudo apt autoremove
dpkg -l | grep postgres

しょうがなくて、バージョン指定で全部削除してしまえ。

sudo apt-get --purge remove postgresql-9.5 postgresql-client-9.5 postgresql-client-common postgresql-common postgresql-contrib postgresql-contrib-9.5
sudo service --status-all  -> no services there

確かにサービス自体はなくなったが、なんかエラーででっている。一体どういうことですか。

Dropping cluster main...
Can't exec "": No such file or directory at /usr/bin/pg_ctlcluster line 283.
Error: could not stop server, aborting

なんかわけわからなくなるので、一旦VMを再起動しよう。
あれ、何かエラーがあるぞ。システムエラーだって。無視無視。

再起動したらいきなりまたエラーがでた。

どうせ新しいDBをインストールするか、無視。

今度みたら

dpkg -l | grep postgres -->
pc postgresql-9.5 9.5.10-0ubuntu0.16.04 amd64 object-relational SQL database, version 9.5 server

お、だいぶ減りましたね、よしよし。

sudo apt-get --purge remove postgresql-9.5

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be REMOVED:
  postgresql-9.5*
0 upgraded, 0 newly installed, 1 to remove and 35 not upgraded.
After this operation, 0 B of additional disk space will be used.
Do you want to continue? [Y/n]

実行して

dpkg -l | grep postgres

今度こそ何にも残っていないようですね。

  1. 新DBのインストール やっと新DBのインストールに入れた。 本家のDOCにちゃんと書いてあるから
sudo apt-get install postgresql-9.6

今度こそうまくいくんでしょう。
いやいや、そんな上手いことはないよ。案の定、エラーが出た。

: Unable to locate package postgresql-9.6
E: Couldn't find any package by glob 'postgresql-9.6'
E: Couldn't find any package by regex 'postgresql-9.6'

sudo apt-get update

を実施しても無駄。
色々探した結果、やはり下記のリンクが一番役立つ。
How to Install PostgreSQL 9.6 on Ubuntu 16.04LTS

よくよくみたら、なるほど、リポジトリを追加しなかったせいか。分かりにくいとはいえ、本家のドキュメントには書いてあるよ。自分の方が気が短かったかよ。

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
$ wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
sudo apt-get update
sudo apt-get upgrade 
sudo apt-get install postgresql postgresql-contrib libpq-dev pgadmin3

よし、やっとインストールできたぞ、じゃ、見てみよ。

psql -V
psql (PostgreSQL) 10.1

あれ、いつの間に9.6ではなく10.1をインストールしてしまった。
まぁいい、10.1なら9.6からデータを吸い上げても文句ないだろう。

早速DBとユーザを作る

create role mytest superuser password 'mytest';
create database mytest with owner=mytest;
ALTER ROLE "mytest" WITH LOGIN;

ここまできて、やっと新しいDBができた。

事前作業

不要レコードの削除
コピーとはいえ、一部の元テーブルにはあまりにもレコードが多いし、僕のテストにも必要ないので、コピーする前に削除しておきたい。（テーブルを除外するのも当然あるが、いちいち指定するのも面倒臭い。第一コピー元にもそれらのデータがいらないとわかっているから）
これを参照した
まぁ、それほど正確な数字はいらないので、一応もっとも簡単な方法を採用した。

SELECT schemaname,relname,n_live_tup 
  FROM pg_stat_user_tables 
  ORDER BY n_live_tup DESC;

それで大体どのテーブルにレコード数が余計に多いのがわかって、その中に不要なテーブルをTruncateしておく。

コピー

待望なコピー作業になった。

   pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname

今度はうまくいった。すごく時間はかかったけど。