Postgresql 的数据库复制

2 年 ago

逸, 科

2 minutes

为了确定一个行动或计划的原因或目标。

因为我想在本地自由地修改和测试数据，所以我考虑将服务器端的数据复制到本地数据库中。本以为只需要一个命令就能完成，但意外地花费了很多时间，因此记录下了整个过程。

环境

主机: macOS Sierra High
虚拟机软件: VirtualBox
虚拟机: Ubuntu 16.04
数据库: Windows7 + PostgreSQL 9.6

操作步骤

当使用pg_dump命令一次性地复制整个数据库时，并没有想象中那么简单。

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

令人惊讶的是，“源数据库是9.6，而你的数据库是9.5，所以不行啊”，我被责备了。根据搜索结果，有很多种方法，但似乎都不太可靠，而且有很多依赖条件，算了，反正就顺便把本地数据库更新到9.6吧。

2. 删除旧数据库
认为删除数据库不算什么大的工作，结果我又错了。

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

因为有点搞不懂，所以先重新启动一下虚拟机吧。
喂，好像有个错误。好像是系统错误。无视无视。

重新启动后，突然又出现了一个错误。

无论是安装新的数据库还是忽视它。

这次看起来
dpkg -l | grep postgres –>
pc postgresql-9.5 9.5.10-0ubuntu0.16.04 amd64 对象关系 SQL 数据库，版本 9.5 服务器

哦，减少了不少，好棒好棒。

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

这次好像什么都没剩下了呢。

我终于开始安装新的数据库了，因为在官方文档上有详细说明。

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

在Ubuntu 16.04LTS上安装PostgreSQL 9.6的方法可以执行，但毫无意义。
经过各种搜索的结果，下面的链接仍然是最有用的。

仔细一看，原来是因为没有添加存储库。虽然有点难以理解，但是在官方文档中有写着啊。是自己太心急了吗？

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导入数据应该没问题。

立即创建数据库和用户

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

到了这一步，终于完成了新的数据库。

预先准备

不需要删除记录
虽然是复制，但某些原始表中记录太多，而且对我的测试也不必要，所以希望在复制之前删除它们。（当然也可以排除表，但每次都要指定太麻烦。而且首先我知道这些数据对复制源也是不必要的）
参考了这个
嗯，不需要非常准确的数字，所以采用了最简单的方法。

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

然后，大致上确定哪张表中的记录数过多，将其中的不必要的表清空。

复制

这成为了令人期待的复制工作。

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

这次确实成功了，虽然花了很多时间。