使用PostgreSQL 腳本導出資料庫的DDL

使用PostgreSQL 腳本導出資料庫的DDL

本文主要介紹如何使用基於 PostgreSQL pgdump編寫的自定義腳本來導出資料庫的DDL。

一、文件說明：

1、pgdump基礎語句.sql：pgdump基礎語法。 2、PGSQL_bak_Full.sh：實現使用pgdump導出全部對象腳本。 3、PGSQL_bak_Proc_Fun.sh：實現使用pgdump僅導出存儲過程和函數對象DDL腳本。 說明：pgdump使用上述腳本導出的DDL存在少量顯示問題，使用時請謹慎。 4、PGSQL_bak_Table.sh：實現使用pgdump導出指定一個或多個表、視圖 DDL腳本。 註：

  • 這個腳本支援自定義數量的表對象結構導出，腳本中只有示例表名。具體使用時應人工指定，導出全部表時，建議使用PGSQL_bak_Full.sh。

  • 各腳本均支援同時導出一個或多個資料庫。

二、腳本結構

具體的腳本格式如下：

#!/bin/bash
#全備方式，一般在從機上執行，適用於小中型PGSQL資料庫
​
#source /etc/profile    #載入系統環境變數
#source ~/.bash_profile  #載入用戶環境變數
#set -o nounset       #引用未初始化變數時退出
#set -o errexit      #執行shell命令遇到錯誤時退出
​
#01 連接資料庫所需認證資訊
user="postgres"
password="****************"
host="106.54.134.160"
port="5432"
#02 需備份的資料庫，數組形式，如果只需備份一個資料庫，只需保留一個庫名稱即可
db=("postgres"  "kingland")
#03 備份路徑及文件名格式設置
pgsql_path="/usr/local/backupdata"
backup_path="${pgsql_path}/backup"
date=$(date +%Y-%m-%d_%H-%M-%S)
backup_log="${pgsql_path}/backup.log"
#建立備份目錄
if [ ! -e $backup_path ];then
  mkdir -p $backup_path
fi
 
echo "開始備份資料庫：${db[*]}"
 
#04 備份
backup_sql(){
  dbname=$1
  backup_name="${dbname}_${date}.sql"
  #-R備份存儲過程，函數，觸發器
  pg_dump -s  "host=$host  user=$user port=$port  password=$password dbname= $dbname">$backup_path/$backup_name
  if [[ $? == 0 ]];then
    cd $backup_path
    echo "$date 備份 $dbname成功 "
  else
    cd $backup_path
    rm -rf $backup_name
    echo "$date 備份 $dbname 失敗 "
  fi
}
​
#05 循環備份
length=${#db[@]}
for (( i = 0; i < $length; i++ )); do
    backup_sql ${db[$i]} >> $backup_log 2>&1
done
 
echo "備份結束,結果查看 $backup_log"
du $backup_path/*$date* -sh | awk '{print "文件:" $2 ",大小:" $1}'
​

說明：

01：連接資料庫所需認證資訊

用戶為資料庫用戶和密碼，埠號為默認的5432；

02：需備份的資料庫

支援一個或多個資料庫備份，資料庫名稱需要使用雙引號引起來；

03：備份路徑及文件名格式設置

要確保pgsql_path變數中對應的物理路徑要存在，特別是backup文件夾也要提前建好；具體的文件名稱格式使用時間後綴，該格式可以自定義；

04、05：循環備份

根據前面的配置進行循環備份，如果備份失敗，腳本會自動刪除過程備份數據。

備份記錄自動記錄log文件到backup.log中。

三、其他說明

各文件關鍵參數區別

1、PGSQL_bak_Full.sh

  #-R備份存儲過程，函數，觸發器
  pg_dump -s  "host=$host  user=$user port=$port  password=$password dbname= $dbname">$backup_path/$backup_name

-s

–schema-only

只輸出對象定義（模式），不輸出數據。

2、PGSQL_bak_Proc_Fun.sh

  #-Fc備份存儲過程(函數)定義
  pg_dump -s -Fc "host=$host  user=$user port=$port  password=$password dbname= $dbname">$backup_path/$backup_name

3、PGSQL_bak_Table.sh

 #--t只導出指定表 這裡示例表test ，實際使用中應按需指定
  pg_dump -s -t test  "host=$host  user=$user port=$port  password=$password dbname= $dbname">$backup_path/$backup_name

這裡的test是測試表，實際生產活動中應修改為對應的表名；如果一次性要導出多個表，使用pg_dump -t table1 -t table2 -t table3 這種格式。

四、結果說明

導出結構文本內容格式：

--
-- PostgreSQL database dump
--
​
-- Dumped from database version 10.15 (Ubuntu 10.15-0ubuntu0.18.04.1)
-- Dumped by pg_dump version 10.15 (Ubuntu 10.15-0ubuntu0.18.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;
​
--
-- Name: DATABASE postgres; Type: COMMENT; Schema: -; Owner: postgres
--
COMMENT ON DATABASE postgres IS 'default administrative connection database';
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
--
-- Name: delete_ebd_compute_log_three_month_ago_data(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION public.delete_ebd_compute_log_three_month_ago_data() RETURNS void
    LANGUAGE plpgsql
    AS $$ 
begin 
select * from test;
END;
$$;
​
ALTER FUNCTION public.delete_ebd_compute_log_three_month_ago_data() OWNER TO postgres;
--
-- Name: sales_tax(real); Type: FUNCTION; Schema: public; Owner: postgres
--
​
CREATE FUNCTION public.sales_tax(subtotal real) RETURNS real
    LANGUAGE plpgsql
    AS $$
   BEGIN
      RETURN subtotal * 0.06;
   END;
   $$;
​
ALTER FUNCTION public.sales_tax(subtotal real) OWNER TO postgres;
SET default_tablespace = '';
SET default_with_oids = false;
​
--
-- Name: test; Type: TABLE; Schema: public; Owner: postgres
--
​
CREATE TABLE public.test (
    id integer,
    name character varying(38)
);
ALTER TABLE public.test OWNER TO postgres;
​
--
-- Name: v_test; Type: VIEW; Schema: public; Owner: postgres
--
CREATE VIEW public.v_test AS
 SELECT test.id,
    test.name
   FROM public.test;
​
ALTER TABLE public.v_test OWNER TO postgres;
--
-- PostgreSQL database dump complete
--
​

 

Tags:

VirMach 便宜 VPS

QNews

QNews