PostgreSQL 備份與還原 | 六小編 Editor Leon

本文為 PostgreSQL 備份之筆記，這裡的備份為冷備份。

下面以 postgres 這個帳號操作，它是 Linux 帳號，而在 PostgreSQL 裡面也有同名帳號，因為這樣，所以可以透過 PostgreSQL 的 peer 認證機制做後續的操作，簡單說就是只要 Linux 用戶確定是 postgres，那就可以直接通過 PostgreSQL 身份認證了。

PostgreSQL 備份的基本指令是 pg_dump，與之相對的，還原的基本指令是 pg_restore，下面先談備份。

備份

假設有個資料庫 mocha，最簡單的備份命令如下：

$ pg_dump --file=mocha.sql --dbname=mocha

備份下來的 mocha.sql 就是充滿 SQL 語句的純文字檔，節錄如下：

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.2 (Ubuntu 15.2-1.pgdg22.04+1)
-- Dumped by pg_dump version 15.2 (Ubuntu 15.2-1.pgdg22.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: _prisma_migrations; Type: TABLE; Schema: public; Owner: ap
--

CREATE TABLE public._prisma_migrations (
    id character varying(36) NOT NULL,
    checksum character varying(64) NOT NULL,
    finished_at timestamp with time zone,
    migration_name character varying(255) NOT NULL,
    logs text,
    rolled_back_at timestamp with time zone,
    started_at timestamp with time zone DEFAULT now() NOT NULL,
    applied_steps_count integer DEFAULT 0 NOT NULL
);

ALTER TABLE public._prisma_migrations OWNER TO ap;

從裡面可以看到，它的 SQL 語句都有指定 OWNER，這意味著未來還原的時候，新的機台上也要先建好那位 owner，以本例來說，就是 ap 這個帳號，如果不想要這樣的行為，可以加 --no-owner 取消掉：

$ pg_dump --file=mocha.sql --no-owner --dbname=mocha

其他的參數可以隨喜使用，列舉一些如下：

--clean，在備份檔中加入刪除原有資料庫的指令，不用手動砍掉重練。
--create，在備份檔中加入建立資料庫的指令，不用手動建 mocha 資料庫。

範例：

$ pg_dump --file=mocha.sql --clean --create --no-owner --dbname=mocha

把這行指令變成腳本：

#!/bin/bash

pg_dump \
--file=/mocha-data/mocha_"$(date --iso-8601='date')".sql \
--clean \
--create \
--no-owner \
--dbname=mocha \

檔名的部分用了 bash 的日期變數自動帶入。

把腳本存到 /usr/local/bin/backup-postgresql-mocha.sh，記得加上可執行權限。

另外儲存的資料夾在 /mocha-data/，記得也要設好寫入權限。

把這腳本設為 cron job 定期執行。

異地備份

本地備份完，可以上傳到異地、異域、異國、異星，如此即使地球爆炸了也得以保全資料到那美克星。

Linux 沒有什麼是一份腳本解決不了的，如果有，就兩份。

下面我們利用 Bash 腳本把備份的 SQL 檔案透過 HTTP 傳送給另一台 Synology NAS：

#!/bin/bash

curl \
--data "api=SYNO.API.Auth" \
--data "version=7" \
--data "method=login" \
--data "account=OneDogIsCute" \
--data "passwd=TwoDogsAreSilly" \
-c /tmp/cookies.txt \
http://200.0.0.78:5000/webapi/entry.cgi \

curl \
--form "api=SYNO.FileStation.Upload" \
--form "version=3" \
--form "method=upload" \
--form "path=/db_backup/mocha" \
--form "create_parents=True" \
--form "overwrite=overwrite" \
-L \
-b /tmp/cookies.txt \
-F "file=@/mocha-data/mocha-$(date --iso-8601='date').sql" \
http://200.0.0.78:5000/webapi/entry.cgi \

第一部份登入 Synology NAS 保存 cooke，第二步走 HTTP POST 把檔案丟上去。

同樣地，這份腳本也可以設為 cron job 定期執行，要注意的是它和前一個備份任務之間最好有足夠的時間差，避免備份檔還沒生成就執行上傳作業。

還原

還原的部份，可以問 ChatGPT。:p

備份

異地備份

還原

Share