在PostgreSQL備份和恢復(fù)自動化干貨分享！

進(jìn)餐小能手 2021-09-22 18:16:38 瀏覽數(shù) (3050)

反饋

關(guān)鍵的 PostgreSQL 客戶端包含有價值的數(shù)據(jù)，應(yīng)定期備份 PostgreSQL 數(shù)據(jù)庫。它的過程非常簡單，重要的是要清楚地了解技術(shù)和假設(shè)。

SQL 轉(zhuǎn)儲

這種轉(zhuǎn)儲方法背后的想法是使用 SQL 命令從 DataCenter1 生成一個文本文件，當(dāng)反饋到 DataCenter2 服務(wù)器時，將重新創(chuàng)建與轉(zhuǎn)儲時相同狀態(tài)的數(shù)據(jù)庫。在這種情況下，如果客戶端無法訪問主服務(wù)器，他們可以訪問 BCP 服務(wù)器。PostgreSQL 為此提供了實(shí)用程序 pg_dump。該命令的基本用法是： pg_dump dbname >backupoutputfile.db。

圖片標(biāo)題

如您所見，pg_dump 將其結(jié)果寫入標(biāo)準(zhǔn)輸出。下面，我們將看到這如何有用。

pg_dump 是一個常規(guī)的 PostgreSQL 客戶端應(yīng)用程序。這意味著您可以從任何有權(quán)訪問數(shù)據(jù)庫的遠(yuǎn)程主機(jī)執(zhí)行此備份過程。pg_dump 不以特殊權(quán)限運(yùn)行。特別是，它必須對您要備份的所有表具有讀訪問權(quán)限，因此在實(shí)踐中，您幾乎總是必須以數(shù)據(jù)庫超級用戶身份運(yùn)行它。

pg_dump 創(chuàng)建的轉(zhuǎn)儲在內(nèi)部是一致的，也就是說，轉(zhuǎn)儲代表 pg_dump 開始運(yùn)行時數(shù)據(jù)庫的快照。pg_dump 在工作時不會阻止對數(shù)據(jù)庫的其他操作。（具有排他鎖的例外，例如大多數(shù)形式的 ALTER TABLE。）

重要提示：如果您的數(shù)據(jù)庫模式依賴于 OID（例如作為外鍵），您必須指示 pg_dump 也轉(zhuǎn)儲 OID。為此，請使用 -o 命令行選項(xiàng)。

SQL 轉(zhuǎn)儲自動化

首先，創(chuàng)建劇本 pgbackup.yml
創(chuàng)建角色 pgbackup，它將從 pgbackup.yml 中調(diào)用

Pgbackup.yml

---
- hosts: database_prim:database_replica
  gather_facts: true
  vars_files:
    - mysecret_vars/{{ environ }}.yml
# This is to Identify if DB is Primary and replicating data to secondary
  tasks:
  - name: select pg status
    command: psql -c "SELECT pg_is_in_recovery();"
    register: IsPromoted
    changed_when: False
    environment:
      PGDATABASE: "{{ pg_database }}"
      PGUSER : "{{ pg_username }}"
      PGPASSWORD : "{{ pg_password }}"
#Get the DB parameter from run time on Client application, Not required if you have parameters
  - block:
    - name: Get client database settings
      shell: "awx-manage print_settings | grep '^DATABASES'"
      register: results
      changed_when: False
      delegate_to: "{{ groups['client’][0] }}"
    - name: Ingest client database settings
      set_fact:
        client_db_settings: "{{ results.stdout | regex_replace('DATABASES\\s+= ', '') }}"
      delegate_to: "{{ groups['client'][0] }}" 
    - include_role:
        name: pgbackup
    when: "'f' in IsPromoted.stdout"
  tags: pgbackup

pgbackup 角色

---
- name: Determine the timestamp for the backup.
  set_fact:
    now: '{{ lookup("pipe", "date +%F-%T") }}'
- name: Create a directory for a backup to live.
  file:
    path: '{{ backup_dir.rstrip("/") }}/{{ now }}/'
    mode: 0775
    owner: root
    state: directory
- name: Create a directory for non-instance specific backups
  file:
    path: '{{ backup_dir.rstrip("/") }}/common/'
    mode: 0775
    owner: root
    state: directory
# create dump, Here adding runtime param. You can add param whatever ways
- name: Perform a PostgreSQL dump.
  shell: "pg_dump --clean --create --host='{{ client_db_settings.default.HOST }}' --port={{ client_db_settings.default.PORT }} --username='{{ tower_db_settings.default.USER  }}' --dbname='{{ tower_db_settings.default.NAME }}' > pgbackup.db"
  args:
    chdir: '{{ backup_dir.rstrip("/") }}/common/'
  environment:
    PGPASSWORD: "{{ client_db_settings.default.PASSWORD }}"
- name: Copy file with owner and permissions
  copy：
    src: '{{ backup_dir.rstrip("/") }}/common/pgbackup.db'
    dest: '{{ backup_dir.rstrip("/") }}/{{ now }}/'
    remote_src: yes

庫存文件

[all:vars]
# database settings
.linux.us.ams1907.com
[client]
linuxclient.us.com
[database_prim]
linuxmas.us.com
[database_replica]
linuxreplica.us.com

秘密變量存儲：如果要存儲任何加密數(shù)據(jù)，請創(chuàng)建此文件

mysecret_vars/{{ environ }}.yml
              ansible-vault encrypt mysecretvar.yml

存儲此類參數(shù)：pg_password、pg_username 和 pg_database

恢復(fù)轉(zhuǎn)儲

pg_dump 創(chuàng)建的文本文件旨在由 psql 程序讀取?；謴?fù)轉(zhuǎn)儲的一般命令形式是 psql dbname < infile

在數(shù)據(jù)中心 2 中恢復(fù)

圖片標(biāo)題

Infile 是您用作 pg_dump 命令的備份輸出文件的文件。該命令不會創(chuàng)建數(shù)據(jù)庫 dbname，因此您必須在執(zhí)行 psql 之前從 template0 自己創(chuàng)建它（例如，使用 createdb -T template0 dbname）。psql 支持類似于 pg_dump 的選項(xiàng)，用于指定要連接的數(shù)據(jù)庫服務(wù)器和要使用的用戶名。有關(guān)更多信息，請參閱 psql 參考頁。

在還原 SQL 轉(zhuǎn)儲之前，擁有對象或被授予轉(zhuǎn)儲數(shù)據(jù)庫中對象權(quán)限的所有用戶必須已經(jīng)存在。如果不這樣做，則還原將無法重新創(chuàng)建具有原始所有權(quán)和/或權(quán)限的對象。

無論哪種方式，您都將擁有一個僅部分恢復(fù)的數(shù)據(jù)庫?；蛘?，您可以指定整個轉(zhuǎn)儲應(yīng)作為單個事務(wù)恢復(fù)，以便完全完成或完全回滾恢復(fù)。這種模式可以通過將 -1 或 --single-transaction 命令行選項(xiàng)傳遞給 psql 來指定。使用此模式時，請注意，即使是最小的錯誤也可能回滾已運(yùn)行數(shù)小時的還原。但是，這可能仍然比在部分還原轉(zhuǎn)儲后手動清理復(fù)雜數(shù)據(jù)庫更可取。

數(shù)據(jù)庫恢復(fù)

創(chuàng)建 pgrecover.yml

為 pgrecover 創(chuàng)建角色

pgrecover.yml

- hosts: database_prim[0] 
  tasks:     
    - name: Get client database settings
      shell: "awx-manage print_settings | grep '^DATABASES'"
      register: results
      changed_when: False
      delegate_to: "{{ groups['client'][0] }}"
    - name: Ingest client database settings
      set_fact:
        tower_db_settings: "{{ results.stdout | regex_replace('DATABASES\\s+= ', '') }}"
      delegate_to: "{{ groups['client'][0] }}"
# Create User
    - name: PostgreSQL | Create test user if its not there
      postgresql_user:
        name: "test"
        password: "{{ client_db_settings.default.PASSWORD }}"
        port: "5432"
        state: present
        login_user: "postgres"
        no_password_changes: no
      become: yes
      become_user: "postgres"
      become_method: su     
# Create Database
    - name: PostgreSQL | Create test Database if its not there
      postgresql_db:
        name: "test"
        owner: "test"
        encoding: "UTF-8"
        lc_collate: "en_US.UTF-8"
        lc_ctype: "en_US.UTF-8"
        port: "5432"
        template: "template0"
        state: present
        login_user: "postgres"
      become: yes
      become_user: "postgres"
      become_method: su  
    - include_role:
        name: pgrecover

現(xiàn)在 pgreceover 角色

---
- name: Create a directory for non-instance specific backups
  file:
    path: '{{ backup_dir.rstrip("/") }}/restore/'
    mode: 0775
    owner: root
    state: directory
- name: Copy file for restore
  copy:
    src: '{{ backup_dir.rstrip("/") }}/common/client.db'
    dest: '{{ backup_dir.rstrip("/") }}/restore/'
    remote_src: yes
- name: Perform a PostgreSQL restore 
 shell: "psql --host='{{ client_db_settings.default.HOST }}' --port={{ client_db_settings.default.PORT }} --username='{{ client_db_settings.default.USER }}' --dbname='test' < ./client.db"
  args:
    chdir: '{{ backup_dir.rstrip("/") }}/restore/'
  environment:
    PGPASSWORD: "{{ client_db_settings.default.PASSWORD }}"

庫存和秘密變量文件將與我們在 pgbackup 流程中的相同。

使用 pg_dumpall

pg_dump 一次只轉(zhuǎn)儲一個數(shù)據(jù)庫，它不會轉(zhuǎn)儲有關(guān)角色或表空間的信息（因?yàn)樗鼈兪羌悍秶亩皇敲總€數(shù)據(jù)庫的）。為了支持方便地轉(zhuǎn)儲數(shù)據(jù)庫集群的全部內(nèi)容，提供了 pg_dumpall 程序。pg_dumpall 備份給定集群中的每個數(shù)據(jù)庫，還保留集群范圍的數(shù)據(jù)，例如角色和表空間定義。該命令的基本用法是：

pg_dumpall > 輸出文件

可以使用 psql 恢復(fù)生成的轉(zhuǎn)儲： psql -f infile Postgres。

（實(shí)際上，您可以指定任何現(xiàn)有的數(shù)據(jù)庫名稱作為開始，但是如果您要重新加載到一個空集群中，那么通常應(yīng)該使用 Postgres。）在恢復(fù) pg_dumpall 轉(zhuǎn)儲時始終需要具有數(shù)據(jù)庫超級用戶訪問權(quán)限，因?yàn)槟鞘切枰謴?fù)角色和表空間信息。如果您使用表空間，請注意轉(zhuǎn)儲中的表空間路徑是否適合新安裝。

pg_dumpall 通過發(fā)出命令來重新創(chuàng)建角色、表空間和空數(shù)據(jù)庫，然后為每個數(shù)據(jù)庫調(diào)用 pg_dump。這意味著雖然每個數(shù)據(jù)庫將在內(nèi)部保持一致，但不同數(shù)據(jù)庫的快照可能不會完全同步。

通過在自動化腳本中實(shí)現(xiàn)微小的更改，您可以將其更改為 pg_dumpall。

SQL

0 人點(diǎn)贊