数据库从MySQL迁移到PostgreSQL

ming88208

对于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。

xiongmx

技术贴一定帮顶，我以前用mariadb，崩过两次后就不玩了