django 开发网站-迁移 SQLite3 到 PostgreSQL 数据库
136 / 1 / 创建于 4年前
娃哈哈店长 的个人博客
明年要去就职了，询问了一下组长，咱们公司的数据库以后都用postgresql（这也很好理解吧，因为mysql也不再开源，另外mysql之前也要求编码格式为utf-8m之类的）。所以咱们也决定在自己服务器上搭建一个postgresql服务器波，开始干！😜
在linux上安装postgresql服务器
参考官方网站
http://www.postgres.cn/v2/download
这里我选择的10版本的postgreslq
wget https://ftp.postgresql.org/pub/source/v12.0/postgresql-12.0.tar.bz2
tar xjvf postgresql*.bz2 #解压至一个目录
cd potgresql-12.0
./configure --prefix=/opt/pgsql #拟安装至/opt/pgsql
make world
make install-world
adduser postgres #增加新用户，系统提示要给定新用户密码
mkdir /opt/pgsql/data #创建数据库目录给data文件夹降级，因为启动和关闭数据库的行为只能等（su - postgres）后能操作，而su - postgres的行为是将我们的当前用户等级降低（一般为root级）
chown -R postgres:postgres /opt/pgsql/data
su - postgres #使用postgres帐号操作
/opt/pgsql/bin/initdb -D /opt/pgsql/data #初始化数据库
/opt/pgsql/bin/pg_ctl -D /opt/pgsql/data -l logfile start #启动数据库
/opt/pgsql/bin/createdb django_blog #假定数据库名为gerericdb)
/opt/pgsql/bin/psql django_blog # (进入数据库内部)照着官方的步骤安装，进入数据库：
/opt/pgsql/bin/pg_ctl -D /opt/pgsql/data -l logfile start我们也可以使用我们自己地文件夹来装日志文件：
# 在root目录下
[root@VM_101_141_centos pgsql]# mkdir log
[root@VM_101_141_centos pgsql]# ls
bin data include lib log share
[root@VM_101_141_centos pgsql]# cd log
[root@VM_101_141_centos log]# touch pg_server.log
[root@VM_101_141_centos log]# cd ..
[root@VM_101_141_centos pgsql]# su - postgres创建新的文件、以及其他操作都需要我们回到root等级，如果我们创造新的文件夹放日志文件，别忘了将日志文件夹等级也降价，不然会出现 Permission denied
# 再降级来操作数据库
[root@VM_101_141_centos pgsql]# su - postgres
Last login: Thu Jan 2 17:34:35 CST 2020 on pts/1
-bash-4.2$ /opt/pgsql/bin/pg_ctl -D /opt/pgsql/data >/opt/pgsql/log/pg_server.log start
-bash: /opt/pgsql/log/pg_server.log: Permission denied
-bash-4.2$ exit
logout
[root@VM_101_141_centos pgsql]# pg_ctl start -l /opt/postgresql/log/pg_server.log
-bash: pg_ctl: command not found
[root@VM_101_141_centos pgsql]# /opt/pgsql/bin/pg_ctl -D /opt/pgsql/data >/opt/pgsql/log/pg_server.log start
pg_ctl: cannot be run as root看我就出错了，然后给文件降级后再来开启服务。
[root@VM_101_141_centos pgsql]# chown -R postgres:postgres /opt/pgsql/log
[root@VM_101_141_centos pgsql]# su - postgres
Last login: Thu Jan 2 17:41:02 CST 2020 on pts/1
-bash-4.2$ /opt/pgsql/bin/pg_ctl -D /opt/pgsql/data >/opt/pgsql/log/pg_server.log start
-bash-4.2$ netstat -an |grep 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
tcp6 0 0 ::1:5432 :::* LISTEN
unix 2 [ ACC ] STREAM LISTENING 29366099 /tmp/.s.PGSQL.5432
-bash-4.2$ （这里我给我的数据库命名为django_blog），并创建一个表为django_blog：
django_blog=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+----------+------------+------------+-----------------------
django_blog | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
django_blog=#查看端口监听状态：
[root@VM_101_141_centos postgresql-10.10]# netstat -an | grep 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
tcp6 0 0 ::1:5432 :::* LISTEN
unix 2 [ ACC ] STREAM LISTENING 29348241 /tmp/.s.PGSQL.54325432端口被服务器顺利的利用起来。
编写python脚本测试一下链接数据库是否成功。
import psycopg2
conn = psycopg2.connect(database="django_blog", user="postgres", password="yourpassword", host="yourhost", port="5432")
print ("Opened database successfully")呕吼，报错了，sth must go wrong：
psycopg2.OperationalError: could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "118.25.20.46" and accepting
TCP/IP connections on port 5432?查看服务器是否还在运行：
(env) [root@VM_101_141_centos include]# ps -ef|grep postgres
postgres 24381 1 0 15:26 ? 00:00:00 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
postgres 24382 24381 0 15:26 ? 00:00:00 postgres: logger process
postgres 24384 24381 0 15:26 ? 00:00:00 postgres: checkpointer process
postgres 24385 24381 0 15:26 ? 00:00:00 postgres: writer process
postgres 24386 24381 0 15:26 ? 00:00:00 postgres: wal writer process
postgres 24387 24381 0 15:26 ? 00:00:00 postgres: autovacuum launcher process
postgres 24388 24381 0 15:26 ? 00:00:00 postgres: stats collector process
postgres 26250 24381 0 15:40 ? 00:00:00 postgres: autovacuum worker process
root 26263 15097 0 15:40 pts/0 00:00:00 grep --color=auto postgres
(env) [root@VM_101_141_centos include]# 确实是在运行呀，我们看看监听端口和服务器的状态：
(env) [root@VM_101_141_centos data]# service postgresql status
Redirecting to /bin/systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2020-01-02 15:26:43 CST; 17min ago
Process: 24371 ExecStop=/usr/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
Process: 24378 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
Process: 24373 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 24381 (postgres)
CGroup: /system.slice/postgresql.service
├─24381 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
├─24382 postgres: logger process
├─24384 postgres: checkpointer process
├─24385 postgres: writer process
├─24386 postgres: wal writer process
├─24387 postgres: autovacuum launcher process
└─24388 postgres: stats collector process
Jan 02 15:26:42 VM_101_141_centos systemd[1]: Starting PostgreSQL database server...
Jan 02 15:26:43 VM_101_141_centos systemd[1]: Started PostgreSQL database server.
(env) [root@VM_101_141_centos data]# so？！whats wrong with this？
报错的代码解析为 服务器不存在或连接错误。
我们检查一下是否允许所有IP联入，并且IPv4是否trust 所有host：
/data/pg_hba.conf们没有问题:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 0.0.0.0/0 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 trust
#host replication postgres ::1/128 trust问题在哪呢？
我们尝试使用telnet工具来看一下端口是否能访问：
PS F:\workspace> telnet 118.25.20.46
正在连接118.25.20.46...无法打开到主机的连接。 在端口 23: 连接失败
PS F:\workspace>(env_default) PS F:\workspace> telnet 118.25.20.46 5432
正在连接118.25.20.46...无法打开到主机的连接。 在端口 5432: 连接失败
(env_default) PS F:\workspace>？难道是我的防火墙没关闭吗？不对啊我之前已经关闭了iptables服务（如果碰到相同问题的铜须可以试一下）。。
[root@VM_101_141_centos ~]# firewall-cmd --state
not running
[root@VM_101_141_centos ~]# service iptables status
Redirecting to /bin/systemctl status iptables.service
Unit iptables.service could not be found.
[root@VM_101_141_centos ~]# iptables -L -n
Chain INPUT (policy ACCEPT)
target prot opt source destination
Chain FORWARD (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
[root@VM_101_141_centos ~]# iptables -F
[root@VM_101_141_centos ~]# iptables -L -n
Chain INPUT (policy ACCEPT)
target prot opt source destination
Chain FORWARD (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
[root@VM_101_141_centos ~]# 后来想通了可能是腾讯云没开那个端口，后来也手动的去打开：
然后 reboot 系统。。
最后检查一下/data/postgresql.conf文件是否有问题：
# - Connection Settings -
#listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)原来是这里的postgresql.conf文件中的,应该把注释符号去掉
listen_addresses = '*' # what IP address(es) to listen on;确定一切正常我们再重启试试，查看一下断开的情况。：
-bash-4.2$ /opt/pgsql/bin/pg_ctl -D /opt/pgsql/data >/opt/pgsql/log/pg_server.log restart
-bash-4.2$ ss -lnt
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:80 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:5432 *:*
LISTEN 0 128 ::1:5432 :::*
-bash-4.2$ django自带数据库数据迁移（失败、繁琐不推荐，但理论可行）
我们先将sqlite3里数据打包拿下来：
python manage.py loaddata data.json会发现生成了一个django.json文件。
之后修改setting.py文件：
更换默认数据库引擎
# Database
# https://docs.djangoproject.com/en/2.0/ref/settings/#databases
# DATABASES = {
# 'default': {
# 'ENGINE': 'django.db.backends.sqlite3',
# 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
# }
# }
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'django_blog',
'USER': '',
'PASSWORD': '',
'HOST': '',
'PORT': '5432'
}
}更换完数据库后，再库中新建表，使用python manage.py migrate
导入前清空初始数据
这时候导入，会提示数据已存在错误，因为Django在migrate的时候，会存入一些初始数据在ContentType表中，将其清空即可。
python3 manage.py shell
>>> from django.contrib.contenttypes.models import ContentType
>>> ContentType.objects.all().delete()
>>> quit()- 导入数据，试一下是不是成功了
python3 manage.py loaddata datadump.json
安装我们需要的数据库之后，然后我们试着将数据包上载：
python manage.py loaddata data.json出错了，过程中会遇到很多bug
大致有，sqlite3和postgresql的数据空结构不同的问题、contenttype表和migrations表不统一的问题、外键主键等关系表问题等等
但是每次以debug都需要loaddata很久，感觉还是很糟心。而且因为迁移数据库，之前记录的debug的笔记不见了。这后面额部分很多都没记录下来，花了我大半天的时间也没成功。心痛了。。
总之，不是很推荐这种方法。如果发现用django自带的工具来迁移数据库有更好的额方法，可以给我提意见。
手动将sqlte3数据迁移到postgresql中
既然是手动，介绍几种方式
- 通过生成sql语句来迁移数据，利用工具（无、或者navicat）
- 利用其他数据库工具来迁移数据库
通过生成sql语句来迁移数据，利用工具（无、或者navicat）
利用navicat工具或者其他方式打开。
利用navicat生成数据库的sql语句：
保存我们旧表的sql文件。
右键点击我们的旧数据库：
但是由于sqlte3数据库和postgresql/mysql的结构区别有太多的不同，当我们运行sqlite3的sql文件时，需要对其中结构进行改造，比如sqlite3中不支持bool类型而是用int来存储的。（当我们对数据库migrate时生成的新库的表中会生成bool类型）。如果我们想将sqlite3中的数据完全完整的迁移过来，需要对SQL语句进行重构。
方法不是很建议尝试，1是花费太多时间，2如果数据量比较大，不如写一个自动化工具。
利用其他数据库工具来迁移数据库
参考文章：https://dba.stackovernet.com/cn/q/722
利用工具进行迁移得原来就是将整个数据库中得表一起进行格式转换后再进行导入，但是针对工具得不同可能会产生不同的结果，比如efs会产生新的字段trail**。之后在进行对数据库的修改，再将未导入的数据进行sql语句导入或者其他方式导入就可以了。
ETL工具pentaho可以在Windows和Linux上运行，并且免费：
ESF数据迁移工具包。我已经尝试过了，它运作良好，并且为您提供了从多种类型的数据库转换为多种类型的数据库的选项：例如sqlite，mysql，mssql，oracle，postgresql等等！
Full convert工具，网上的评测都觉得很好用，但是需要购买300多欧元。
这里展示一下用esf工具得结果：
使用esf工具迁移时，不需要先migrate数据表，如果已经migrate，则可以删除重建或者清空数据库。
迁移数据库记得不要覆盖数据库。
但是结构的差异会引起很多的问题：
====================================================
获取数据表'blog_article'结构 ...
创建数据表'blog_article' ...
总记录数:31
插入记录到'blog_article' ...
ERROR: value too long for type character varying(50)
CONTEXT: COPY blog_article, line 2, column slug: "django-mdeditorhou-tai-nei-qian-mdwen-zhang-bian-ji-editor-mdkai-yuan-xiang-mu"
插入0条记录
创建索引'blog_article' ...
花费的时间:00:00:01.219
====================================================正如我之前说的一样，不同得数据库之间得结构有太大得不同，导致迁移数据库得时候，如果能尽可能多的自动修复不同数据类型得转换，就已经很不错了。
让我们打开navicat来手动修改数据库
导出原表的sql语句：
我们截取一段从sqlite3导出的sql看看：
INSERT INTO "blog_article"("id", "title", "body", "create_date", "update_date", "views", "slug", "is_top", "author_id", "category_id", "img", "is_addtimeline", "summary") VALUES (4, 'Nginx + Gunicorn 服务器配置 Django', '---文章body-----', '2019-06-23 14:44:43.694891', '2019-11-14 07:37:26.224610', 1137, 'nginx-gunicorn-fu-wu-qi-pei-zhi-django', 0, 2, 2, 'media/article/2bafc00c504c4e56eadd9f27caee6fd4.bmp', 1, 'Nginx + Gunicorn 服务器配置 Django Django==2.0.3 Python==3.6');对应得我们发现bool字段的is_toop 和 is_addtimelinme为1/0型，但是在psostgres中我们需要存入f或者t。
修改sql语句：将所有这两个字段的数据的1改为true，0改为false。
导入成功！！
使用esf工具的好处就是：
- 无需我们重构表的结构，使用navicat功具重构的结构很多bug，事件类型为byte或者所有varchar为text。在进行修改需要很多时间，不建议！
- 只需修量少量的sql语句，对于无法迁移的表（但是表的结构已经迁移完成），我们只需要导出sql语句再稍微修改一下就可以了，还是很方便的，当然你也可以不用工具直接纯sql语句，但是结构、数据要修改的太多了，不建议！
我们之前提到过的，esf工具会生成多余的字段：
之后进行删除就好了。
之后需要对数据库进行makemigrations/migrate，如果遇到contenttypes问题或者说table已经存在，可以先将数据库的 django_migrations和django_content_type进行清空，再改settings链接到新的数据库，然后删除所有app下的makemigrations中的文件，进行makemigrations/migrate。之后将新的数据库的这两个表的数据再迁移到我们目标数据库中。这样处理是最稳妥的。
假如遇到问题
ValueError : unsupported pickle protocol: 5这个bug根据pick协议，我们的查询功能whoosh功能时当我们访问这个页面，就将信息缓存下来，由于服务器py版本和win版本不一样可能会导致这个问题，解决方法就是删除项目中django_blog\whoosh_index文件夹中的所有文件。
本作品采用《CC 协议》，转载必须注明作者和本文链接
关于 LearnKu
粤公网安备 44030502004330号
推荐文章：