使用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存在少量显示问题，使用时请谨慎。
-
这个脚本支持自定义数量的表对象结构导出，脚本中只有示例表名。具体使用时应人工指定，导出全部表时，建议使用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
--
