37 lines
3.8 KiB
Markdown
37 lines
3.8 KiB
Markdown
---
|
||
page-title: "数据库从MySQL迁移到PostgreSQL - 『HomeAssistant』综合讨论区 - 『瀚思彼岸』» 智能家居技术论坛 - Powered by Discuz!"
|
||
url: https://bbs.hassbian.com/thread-24271-1-1.html
|
||
date: "2024-12-20 13:04:53"
|
||
---
|
||
*本帖最后由 ming88208 于 2024-2-22 13:59 编辑*
|
||
|
||
对于HA而言，PostgreSQL也许是更好的数据库，迁移后整库备份的时间从10分钟降低到10秒以内。方法：
|
||
|
||
1. 安装PostgreSQL数据库，并新建homeassistant用户和homeassistant表。
|
||
|
||
*复制代码* *隐藏代码*`docker run --name pg --restart=always -v /home/dbbackup:/home/dbbackup -e POSTGRES_PASSWORD=你的密码 -p 5432:5432 -v /home/docker/postgresql:/var/lib/postgresql/data -d postgres docker exec -it pg bash # -------- su postgres createuser homeassistant -P # 数据库密码 createdb -O homeassistant homeassistant`
|
||
|
||
2. 在ha的配置文件中切换到pgsql。
|
||
|
||
*复制代码* *隐藏代码*`recorder: # db_url: mysql://root:数据库密码@host.docker.internal:3306/homeassistant?charset=utf8mb4 db_url: postgresql://homeassistant:数据库密码@host.docker.internal:5432/homeassistant`
|
||
|
||
3. 在docker中重启ha进程，此时ha载入会初始化pgsql数据库。但我们不希望其初始化后进行数据写入，污染id设置，因此需要利用Navicat，在数据库结构初始化完毕的瞬间立马停止ha进程，此时表结构已经就绪，但所有表均没有任何记录。
|
||
|
||
4. 准备`/home/docker/pgloader/pgload.load`文件。
|
||
|
||
|
||
*复制代码* *隐藏代码*`LOAD DATABASE FROM mysql://root:数据库密码@localhost:3306/homeassistant INTO pgsql://homeassistant:数据库密码@localhost:5432/homeassistant WITH data only, workers = 8, concurrency = 1 CAST type datetime to timestamp drop default drop not null using zero-dates-to-null ;`
|
||
|
||
5. 运行PGLOADER
|
||
`docker run -it --rm --name=pgloader --net=host -v /home/docker/pgloader:/loads dimitri/pgloader`
|
||
|
||
6. 执行`pgloader /pgloader/pgload.load`进行数据迁移。
|
||
|
||
7. 在PGSQL中运行以下SQL语句，设置自增ID。其中第一行的作用是查找所有序列，结果应该如注释中所示。
|
||
|
||
|
||
*复制代码* *隐藏代码*`SELECT c.relname FROM pg_class c WHERE c.relkind ='S'; /* event_types_event_type_id_seq state_attributes_attributes_id_seq event_data_data_id_seq states_meta_metadata_id_seq statistics_meta_id_seq events_event_id_seq recorder_runs_run_id_seq schema_changes_change_id_seq statistics_runs_run_id_seq states_state_id_seq statistics_id_seq statistics_short_term_id_seq */ SELECT setval('event_types_event_type_id_seq', MAX(event_type_id)) FROM event_types; SELECT setval('state_attributes_attributes_id_seq', MAX(attributes_id)) FROM state_attributes; SELECT setval('event_data_data_id_seq', MAX(data_id)) FROM event_data; SELECT setval('states_meta_metadata_id_seq', MAX(metadata_id)) FROM states_meta; SELECT setval('statistics_meta_id_seq', MAX(id)) FROM statistics_meta; SELECT setval('events_event_id_seq', MAX(event_id)) FROM events; SELECT setval('recorder_runs_run_id_seq', MAX(run_id)) FROM recorder_runs; SELECT setval('schema_changes_change_id_seq', MAX(change_id)) FROM schema_changes; SELECT setval('statistics_runs_run_id_seq', MAX(run_id)) FROM statistics_runs; SELECT setval('states_state_id_seq', MAX(state_id)) FROM states; SELECT setval('statistics_id_seq', MAX(id)) FROM statistics; SELECT setval('statistics_short_term_id_seq', MAX(id)) FROM statistics_short_term;`
|
||
|
||
8. 重新运行ha容器。可以发现迁移后实体的历史数据仍然存在。
|
||
|
||
9. （踩坑修复）第一次迁移时操作不当，在数据库写入数据后再进行自增ID修改，导致能源数据异常。重新建立了一个新的PGSQL数据库后，严格按照以上步骤执行不再出现异常问题。目前一切正常，没有发现BUG。 |