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を再起動しよう。
あれ、何かエラーがあるぞ。システムエラーだって。無視無視。

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

![image.png](https://qiita-image-store.s3.amazonaws.com/0/186671/e0e2d2b9-9fcf-32b6-1e22-d7fa58a1da3f.png)

![image.png](https://qiita-image-store.s3.amazonaws.com/0/186671/0cdc1e58-33ef-b548-1bd4-3eef2ed0595f.png)

どうせ新しい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
```
今度こそ何にも残っていないようですね。

3. 新DBのインストール
  やっと新DBのインストールに入れた。
[本家のDOC](https://www.postgresql.org/download/linux/ubuntu/)にちゃんと書いてあるから

```
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](http://www.codebind.com/postgresql/how-to-install-postgresql-on-ubuntu-16-04-lts/)

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

```
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ができた。

### 事前作業
不要レコードの削除
  コピーとはいえ、一部の元テーブルにはあまりにもレコードが多いし、僕のテストにも必要ないので、コピーする前に削除しておきたい。（テーブルを除外するのも当然あるが、いちいち指定するのも面倒臭い。第一コピー元にもそれらのデータがいらないとわかっているから）
[これを参照した](https://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres)
まぁ、それほど正確な数字はいらないので、一応もっとも簡単な方法を採用した。

```
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
```

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