如何將舊資料庫的資料還原至新資料庫

這次升級 odoo ，發現 account_move_line 的某些資料並沒有同步至 odoo 15，筆數有百筆以上，使用手動修改沒有效率，最簡單的方式就是複製舊資料庫的 Table ，還原至新資料庫後在進行操作

先使用 SQL 指令複製 DB v10 的 Table account_move_line 至新 Table account_move_line_x

create table account_move_line_x as table account_move_line;

將 table 資料轉存為 sql file

pg_dump -U odoo -d v10 -t account_move_line_x -f account_move_line_x.sql

將 sql 還原至目標資料庫 clone

psql -U odoo -d clone -f account_move_line.sql

將 Table account_move_line_x 中符合條件的 name 欄位資料複製到 account_move_line

UPDATE account_move_line AS a 
SET name = b.name
FROM account_move_line_x AS b
WHERE a.id = b.id
  AND a.journal_id = b.journal_id
  AND a.journal_id = 60
  AND a.name != b.name;

其他操作

這邊順道記錄之前誤殺了某個 column 的資料，使用相同流程還原的過程

誤殺 Column 資料，還原程序
1. 先使用 copy 指令複製原有的 Table account_invoice 至新 Table account_invoice_backup
CREATE TABLE account_invoice_backup AS 
TABLE account_invoice;

將備份 Table account_invoice_backup 加入原本的 Table account_invoice，並使用 id 作為連結的 key
SELECT account_invoice.id, account_invoice.amount_tax, account_invoice.govt_uniform_invoice_no, account_invoice.govt_uniform_invoice_date, govt_uniform_invoice_no_x, govt_uniform_invoice_date_x
FROM   account_invoice
INNER JOIN account_invoice_backup ON account_invoice.id = account_invoice_backup.id
where govt_uniform_invoice_no_x is not null;

將 govt_uniform_invoice_no_x 中的 empty 資料改為 null
update account_invoice_backup set govt_uniform_invoice_no_x = null
where govt_uniform_invoice_no_x = '';

將備份 Table account_invoice_backup 的 govt_uniform_invoice_date_x 資料複製到 govt_uniform_invoice_date
UPDATE account_invoice AS a 
SET govt_uniform_invoice_date = b.govt_uniform_invoice_date_x
FROM account_invoice_backup AS b
WHERE a.id = b.id 

將備份 Table account_invoice_backup 的 govt_uniform_invoice_no_x 資料複製到 govt_uniform_invoice_no
UPDATE account_invoice AS a 
SET govt_uniform_invoice_no = b.govt_uniform_invoice_no_x
FROM account_invoice_backup AS b
WHERE a.id = b.id