迁移sqlite3到postgresql数据库

2020年1月6日 11:10 阅读 939 评论 0

明年要去就职了,询问了一下组长,咱们公司的数据库以后都用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.5432

5432端口被服务器顺利的利用起来。

编写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自带数据库数据迁移(失败、繁琐不推荐,但理论可行)

为了让 django 完成翻译,创建好这些数据库表,我们再一次请出我的工程管理助手 manage.py。切换到 manage.py 文件所在的目录(项目根目录)下,分别运行 pipenv run python manage.py makemigrations 和 pipenv run python manage.py migrate 命令:

python manage.py makemigrations
Migrations for 'blog':
  blog\migrations\0001_initial.py
    - Create model Category
    - Create model Tag
    - Create model Post

> pipenv run python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, blog, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying blog.0001_initial... OK
  Applying sessions.0001_initial... OK

当我们执行了 python manage.py makemigrations 后,django 在 blog 应用的 migrations 目录下生成了一个 0001_initial.py 文件,这个文件是 django 用来记录我们对模型做了哪些修改的文件。目前来说,我们在 models.py 文件里创建了 3 个模型类,django 把这些变化记录在了 0001_initial.py 里。

不过此时还只是告诉了 django 我们做了哪些改变,为了让 django 真正地为我们创建数据库表,接下来又执行了 python manage.py migrate 命令。django 通过检测应用中 migrations 目录下的文件,得知我们对数据库做了哪些操作,然后它把这些操作翻译成数据库操作语言,从而把这些操作作用于真正的数据库。

你可以看到命令的输出除了 Applying blog.0001_initial... OK 外,django 还对其它文件做了操作。这是因为除了我们自己建立的 blog 应用外,django 自身还内置了很多应用,这些应用本身也是需要存储数据的。可以在 settings.py 的 INSTALLED_APP 设置里看到这些应用,当然我们目前不必关心这些。

对于了解数据库语言的人,你可以运行下面的命令看看 django 究竟为我们做了什么:

python manage.py sqlmigrate blog 0001

你将看到输出了经 django 翻译后的数据库表创建语句,这有助于你理解 django ORM 的工作机制。

选择数据库版本 我们没有安装任何的数据库软件,django 就帮我们迁移了数据库。这是因为我们使用了 Python 内置的 SQLite3 数据库。

SQLite3 是一个十分轻巧的数据库,它仅有一个文件。你可以看一到项目根目录下多出了一个 db.sqlite3 的文件,这就是 SQLite3 数据库文件,django 博客的数据都会保存在这个数据库文件里。

django 在 settings.py 里为我们做了一些默认的数据库配置:

blogproject/settings.py

# **其它配置选项...**
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}

其它配置选项... 可以看到默认的数据库引擎就是使用的 SQLite3。

当然一些人倾向于使用 MySQL 等大型数据库,至于 django 如何配置 MySQL 这里就不赘述了,你可以自行使用搜索引擎或者查阅 django 的官方文档解决。对于一个小型博客而言,SQLite3 数据库足以胜任。

用 django 的方式操作数据库

数据库最主要的操作就是往里面存入数据、从中取出数据、修改已保存的数据和删除不再需要的数据(合称 CURD)。和创建数据库表一样,django 为这些操作提供了一整套方法,从而把我们从数据库语言中解放出来。我们不用学习如何利用数据库语言去完成这些操作,只要简单地调用几个 Python 函数就可以满足我们的需求。

我们先将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()
  1. 导入数据,试一下是不是成功了

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文件夹中的所有文件。

原创文章,转载请注明出处:https://boywithacoin.cn/article/qian-yi-sqlite3dao-postgresqlshu-ju-ku/


您尚未登录,请 登录注册 后评论
    0 人参与 | 0 条评论
    暂时没有评论,欢迎来尬聊!