做电力产品的外贸网站,网站报价方案,网站安全注意哪些问题吗,软件开发培训一#xff0c;postgresql 数据的备份与恢复
#xff08;一#xff09;数据库备份与恢复
1#xff0c;备份
windows环境
1 dump 逻辑备份
1#xff0c;用管理员身份打开power shell 2#xff0c;切换到本机 postgresql 安装目录下的 bin 目录#xff1a;
PS C…一postgresql 数据的备份与恢复
一数据库备份与恢复
1备份
windows环境
1 dump 逻辑备份
1用管理员身份打开power shell 2切换到本机 postgresql 安装目录下的 bin 目录
PS C:\Users\DFL cd D:\DFL\SOFTWARES\postgresql14\bin
PS D:\DFL\SOFTWARES\postgresql14\bin3执行 dump 将 test 数据库备份到桌面文件 appdb.bak
PS D:\DFL\SOFTWARES\postgresql14\bin .\pg_dump -h localhost -p 5432 -U postgres -d test C:\Users\DFL\Desktop\appdb.bak
口令:2 COPY 逻辑备份
Linuxubuntu环境
二数据表备份与恢复
二pgadmin4
一备份
1备份 table 2备份详情
请求参数
gid1
sid1
data{file: /student.bak.backup, format: plain, id: None, blobs: True, verbose: True, encoding: UTF8, role: postgres, only_data: True, use_insert_commands: True, include_create_database: True, disable_trigger: True, disable_quoting: True, database: postgres, tables: [[public, student]]}pgadmin4数据备份源码:
web/pgadmin/tools/backup/__init__.py
blueprint.route(/job/int:sid, methods[POST], endpointcreate_server_job
)
blueprint.route(/job/int:sid/object, methods[POST], endpointcreate_object_job
)
login_required
def create_backup_objects_job(sid):Args:sid: Server IDCreates a new job for backup task(Backup Database(s)/Schema(s)/Table(s))Returns:None# 获取请求data json.loads(request.data, encodingutf-8)backup_obj_type data.get(type, objects)try:# 获取文件路径backup_file filename_with_file_manager_path(data[file], (data.get(format, ) ! directory))except Exception as e:return bad_request(errormsgstr(e))# 获取服务器信息server get_server(sid)if server is None:return make_json_response(success0,errormsg_(Could not find the specified server.))# To fetch MetaData for the serverfrom pgadmin.utils.driver import get_driverdriver get_driver(PG_DEFAULT_DRIVER)manager driver.connection_manager(server.id)conn manager.connection()connected conn.connected()if not connected:return make_json_response(success0,errormsg_(Please connect to the server first.))# 获取备份工具这里是 pg_dumputility manager.utility(backup) if backup_obj_type objects \else manager.utility(backup_server)ret_val does_utility_exist(utility)if ret_val:return make_json_response(success0,errormsgret_val)# 准备填充 dump 命令的参数# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/student.bak.backup, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --verbose, --role, postgres, --formatp, --data-only, --disable-triggers, --create, --inserts, --disable-dollar-quoting, --encoding, UTF8, --table, public.student]args _get_args_params_values(data, conn, backup_obj_type, backup_file, server, manager)# 这里将参数中的所有双引号转义因为双引号在shell命令行中具有特殊含义它们在没有双引号的情况下运行。添加额外的引号以保存我们的双引号。escaped_args [escape_dquotes_process_arg(arg) for arg in args]try:# 用 utf-8 编码文件名bfile data[file].encode(utf-8) \if hasattr(data[file], encode) else data[file]# 区分不同的备份类型if backup_obj_type objects: # 备份数据对象数据库、模式、表数据args.append(data[database])escaped_args.append(data[database])p BatchProcess(descBackupMessage(BACKUP.OBJECT, server.id, bfile,*args,databasedata[database]),cmdutility, argsescaped_args)else: # 备份服务器数据p BatchProcess(descBackupMessage(BACKUP.SERVER if backup_obj_type ! globalselse BACKUP.GLOBALS,server.id, bfile,*args),cmdutility, argsescaped_args)manager.export_password_env(p.id)# Check for connection timeout and if it is greater than 0 then# set the environment variable PGCONNECT_TIMEOUT.if manager.connect_timeout 0:env dict()env[PGCONNECT_TIMEOUT] str(manager.connect_timeout)p.set_env_variables(server, envenv)else:p.set_env_variables(server)# 创建子进程执行 pg_dump 命令p.start()jid p.idexcept Exception as e:current_app.logger.exception(e)return make_json_response(status410,success0,errormsgstr(e))# Return responsereturn make_json_response(data{job_id: jid, desc: p.desc.message, Success: 1})右下角显示进程任务执行信息 查看进程任务查看备份任务执行情况
查看任务务行详情 - 红框中就是备份数据表时执行的 dump 命令。
下载备份文件 3备份文件
--
-- PostgreSQL database dump
---- Dumped from database version 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)-- Started on 2023-08-23 18:01:11 CSTSET 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;--
-- TOC entry 3640 (class 1262 OID 13799)
-- Name: postgres; Type: DATABASE; Schema: -; Owner: postgres
--CREATE DATABASE postgres WITH TEMPLATE template0 ENCODING UTF8 LOCALE zh_CN.UTF-8;ALTER DATABASE postgres OWNER TO postgres;\connect postgresSET 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;--
-- TOC entry 3634 (class 0 OID 24580)
-- Dependencies: 213
-- Data for Name: student; Type: TABLE DATA; Schema: public; Owner: postgres
--SET SESSION AUTHORIZATION DEFAULT;ALTER TABLE public.student DISABLE TRIGGER ALL;INSERT INTO public.student VALUES (1, 1, tname1);
INSERT INTO public.student VALUES (2, 1, tname1);
INSERT INTO public.student VALUES (3, 2, tname2);
INSERT INTO public.student VALUES (4, 3, tname3);
INSERT INTO public.student VALUES (5, 3, tname3);ALTER TABLE public.student ENABLE TRIGGER ALL;-- Completed on 2023-08-23 18:01:11 CST--
-- PostgreSQL database dump complete
--
5备份 schema
1对话框 2备份信息
blueprint.route(/job/int:sid, methods[POST], endpointcreate_server_job
)
blueprint.route(/job/int:sid/object, methods[POST], endpointcreate_object_job
)
login_required
def create_backup_objects_job(sid):Args:sid: Server IDCreates a new job for backup task(Backup Database(s)/Schema(s)/Table(s))Returns:None# 获取请求# {file: psche, format: plain, id: None, blobs: True, verbose: True, encoding: UTF8, role: postgres, data: True, dns_owner: True, dns_tablespace: True, dns_unlogged_tbl_data: True, no_comments: True, use_insert_commands: True, include_create_database: True, include_drop_database: True, database: postgres, schemas: [public]}data json.loads(request.data, encodingutf-8)# objectsbackup_obj_type data.get(type, objects)try:# 获取文件路径# /var/lib/pgadmin/storage/dangfulin2333_163.com/pschebackup_file filename_with_file_manager_path(data[file], (data.get(format, ) ! directory))except Exception as e:return bad_request(errormsgstr(e))# 获取服务器信息# Server 1server get_server(sid)if server is None:return make_json_response(success0,errormsg_(Could not find the specified server.))# To fetch MetaData for the serverfrom pgadmin.utils.driver import get_driverdriver get_driver(PG_DEFAULT_DRIVER)manager driver.connection_manager(server.id)conn manager.connection()connected conn.connected()if not connected:return make_json_response(success0,errormsg_(Please connect to the server first.))# 获取备份工具# /usr/lib/postgresql/14/bin/pg_dumputility manager.utility(backup) if backup_obj_type objects \else manager.utility(backup_server)ret_val does_utility_exist(utility)if ret_val:return make_json_response(success0,errormsgret_val)# 工具参数# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/psche, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --verbose, --role, postgres, --formatp, --no-owner, --create, --clean, --sectiondata, --no-tablespaces, --no-unlogged-table-data, --inserts, --no-comments, --encoding, UTF8, --schema, public, postgres]args _get_args_params_values(data, conn, backup_obj_type, backup_file, server, manager)# 这里将参数中的所有双引号转义因为双引号在shell命令行中具有特殊含义它们在没有双引号的情况下运行。添加额外的引号以保存我们的双引号。# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/psche, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --verbose, --role, postgres, --formatp, --no-owner, --create, --clean, --sectiondata, --no-tablespaces, --no-unlogged-table-data, --inserts, --no-comments, --encoding, UTF8, --schema, public, postgres]escaped_args [escape_dquotes_process_arg(arg) for arg in args]try:# 用 utf-8 编码文件名bfile data[file].encode(utf-8) \if hasattr(data[file], encode) else data[file]# 区分不同的备份类型if backup_obj_type objects: # 备份数据对象数据库、模式、表数据args.append(data[database])# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/psche, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --verbose, --role, postgres, --formatp, --no-owner, --create, --clean, --sectiondata, --no-tablespaces, --no-unlogged-table-data, --inserts, --no-comments, --encoding, UTF8, --schema, public, postgres]escaped_args.append(data[database])# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/psche, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --verbose, --role, postgres, --formatp, --no-owner, --create, --clean, --sectiondata, --no-tablespaces, --no-unlogged-table-data, --inserts, --no-comments, --encoding, UTF8, --schema, public, postgres]p BatchProcess(descBackupMessage(BACKUP.OBJECT, server.id, bfile,*args,databasedata[database]),cmdutility, argsescaped_args)else: # 备份服务器数据p BatchProcess(descBackupMessage(BACKUP.SERVER if backup_obj_type ! globalselse BACKUP.GLOBALS,server.id, bfile,*args),cmdutility, argsescaped_args)manager.export_password_env(p.id)# Check for connection timeout and if it is greater than 0 then# set the environment variable PGCONNECT_TIMEOUT.if manager.connect_timeout 0:env dict()env[PGCONNECT_TIMEOUT] str(manager.connect_timeout)p.set_env_variables(server, envenv)else:p.set_env_variables(server)# 创建子进程执行 pg_dump 命令p.start()jid p.idexcept Exception as e:current_app.logger.exception(e)return make_json_response(status410,success0,errormsgstr(e))# Return responsereturn make_json_response(data{job_id: jid, desc: p.desc.message, Success: 1})3备份文件
--
-- PostgreSQL database dump
---- Dumped from database version 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)-- Started on 2023-08-24 08:52:15 CSTSET 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;DROP DATABASE postgres;
--
-- TOC entry 3671 (class 1262 OID 13799)
-- Name: postgres; Type: DATABASE; Schema: -; Owner: -
--CREATE DATABASE postgres WITH TEMPLATE template0 ENCODING UTF8 LOCALE zh_CN.UTF-8;\connect postgresSET 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;--
-- TOC entry 3662 (class 0 OID 24629)
-- Dependencies: 216
-- Data for Name: circles; Type: TABLE DATA; Schema: public; Owner: -
----
-- TOC entry 3664 (class 0 OID 25273)
-- Dependencies: 218
-- Data for Name: company6; Type: TABLE DATA; Schema: public; Owner: -
----
-- TOC entry 3665 (class 0 OID 25280)
-- Dependencies: 219
-- Data for Name: department1; Type: TABLE DATA; Schema: public; Owner: -
----
-- TOC entry 3661 (class 0 OID 24611)
-- Dependencies: 215
-- Data for Name: orders; Type: TABLE DATA; Schema: public; Owner: -
----
-- TOC entry 3660 (class 0 OID 24580)
-- Dependencies: 213
-- Data for Name: student; Type: TABLE DATA; Schema: public; Owner: -
--INSERT INTO public.student VALUES (1, 1, tname1);
INSERT INTO public.student VALUES (2, 1, tname1);
INSERT INTO public.student VALUES (3, 2, tname2);
INSERT INTO public.student VALUES (4, 3, tname3);
INSERT INTO public.student VALUES (5, 3, tname3);--
-- TOC entry 3663 (class 0 OID 25264)
-- Dependencies: 217
-- Data for Name: t2; Type: TABLE DATA; Schema: public; Owner: -
----
-- TOC entry 3659 (class 0 OID 24577)
-- Dependencies: 212
-- Data for Name: teacher; Type: TABLE DATA; Schema: public; Owner: -
--INSERT INTO public.teacher VALUES (1, sname1);
INSERT INTO public.teacher VALUES (2, sname2);
INSERT INTO public.teacher VALUES (3, sname3);-- Completed on 2023-08-24 08:52:15 CST--
-- PostgreSQL database dump complete
--6备份 database
1对话框 2备份信息
blueprint.route(/job/int:sid, methods[POST], endpointcreate_server_job
)
blueprint.route(/job/int:sid/object, methods[POST], endpointcreate_object_job
)
login_required
def create_backup_objects_job(sid):Args:sid: Server IDCreates a new job for backup task(Backup Database(s)/Schema(s)/Table(s))Returns:None# 获取请求# {file: pdb, format: plain, id: None, blobs: True, verbose: True, encoding: UTF8, role: postgres, pre_data: True, data: True, post_data: True, dns_owner: True, dns_tablespace: True, use_insert_commands: True, include_create_database: True, include_drop_database: True, database: postgres}data json.loads(request.data, encodingutf-8)# objectsbackup_obj_type data.get(type, objects)try:# 获取文件路径# /var/lib/pgadmin/storage/dangfulin2333_163.com/pdbbackup_file filename_with_file_manager_path(data[file], (data.get(format, ) ! directory))except Exception as e:return bad_request(errormsgstr(e))# 获取服务器信息# Server 1server get_server(sid)if server is None:return make_json_response(success0,errormsg_(Could not find the specified server.))# To fetch MetaData for the serverfrom pgadmin.utils.driver import get_driverdriver get_driver(PG_DEFAULT_DRIVER)manager driver.connection_manager(server.id)conn manager.connection()connected conn.connected()if not connected:return make_json_response(success0,errormsg_(Please connect to the server first.))# 获取备份工具# /usr/lib/postgresql/14/bin/pg_dumputility manager.utility(backup) if backup_obj_type objects \else manager.utility(backup_server)ret_val does_utility_exist(utility)if ret_val:return make_json_response(success0,errormsgret_val)# 工具参数# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/pdb, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --verbose, --role, postgres, --formatp, --no-owner, --create, --clean, --sectionpre-data, --sectiondata, --sectionpost-data, --no-tablespaces, --inserts, --encoding, UTF8, postgres]args _get_args_params_values(data, conn, backup_obj_type, backup_file, server, manager)# 这里将参数中的所有双引号转义因为双引号在shell命令行中具有特殊含义它们在没有双引号的情况下运行。添加额外的引号以保存我们的双引号。# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/pdb, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --verbose, --role, postgres, --formatp, --no-owner, --create, --clean, --sectionpre-data, --sectiondata, --sectionpost-data, --no-tablespaces, --inserts, --encoding, UTF8, postgres]escaped_args [escape_dquotes_process_arg(arg) for arg in args]try:# 用 utf-8 编码文件名bfile data[file].encode(utf-8) \if hasattr(data[file], encode) else data[file]# 区分不同的备份类型if backup_obj_type objects: # 备份数据对象数据库、模式、表数据args.append(data[database])# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/pdb, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --verbose, --role, postgres, --formatp, --no-owner, --create, --clean, --sectionpre-data, --sectiondata, --sectionpost-data, --no-tablespaces, --inserts, --encoding, UTF8, postgres]escaped_args.append(data[database])# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/pdb, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --verbose, --role, postgres, --formatp, --no-owner, --create, --clean, --sectionpre-data, --sectiondata, --sectionpost-data, --no-tablespaces, --inserts, --encoding, UTF8, postgres]p BatchProcess(descBackupMessage(BACKUP.OBJECT, server.id, bfile,*args,databasedata[database]),cmdutility, argsescaped_args)else: # 备份服务器数据p BatchProcess(descBackupMessage(BACKUP.SERVER if backup_obj_type ! globalselse BACKUP.GLOBALS,server.id, bfile,*args),cmdutility, argsescaped_args)manager.export_password_env(p.id)# Check for connection timeout and if it is greater than 0 then# set the environment variable PGCONNECT_TIMEOUT.if manager.connect_timeout 0:env dict()env[PGCONNECT_TIMEOUT] str(manager.connect_timeout)p.set_env_variables(server, envenv)else:p.set_env_variables(server)# 创建子进程执行 pg_dump 命令p.start()jid p.idexcept Exception as e:current_app.logger.exception(e)return make_json_response(status410,success0,errormsgstr(e))# Return responsereturn make_json_response(data{job_id: jid, desc: p.desc.message, Success: 1})3备份文件
--
-- PostgreSQL database dump
---- Dumped from database version 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)-- Started on 2023-08-24 09:06:19 CSTSET 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;DROP DATABASE postgres;
--
-- TOC entry 3671 (class 1262 OID 13799)
-- Name: postgres; Type: DATABASE; Schema: -; Owner: -
--CREATE DATABASE postgres WITH TEMPLATE template0 ENCODING UTF8 LOCALE zh_CN.UTF-8;\connect postgresSET 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;--
-- TOC entry 3672 (class 0 OID 0)
-- Dependencies: 3671
-- Name: DATABASE postgres; Type: COMMENT; Schema: -; Owner: -
--COMMENT ON DATABASE postgres IS default administrative connection database;--
-- TOC entry 3 (class 3079 OID 24634)
-- Name: btree_gist; Type: EXTENSION; Schema: -; Owner: -
--CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA public;--
-- TOC entry 3673 (class 0 OID 0)
-- Dependencies: 3
-- Name: EXTENSION btree_gist; Type: COMMENT; Schema: -; Owner: -
--COMMENT ON EXTENSION btree_gist IS support for indexing common datatypes in GiST;--
-- TOC entry 2 (class 3079 OID 16394)
-- Name: postgres_fdw; Type: EXTENSION; Schema: -; Owner: -
--CREATE EXTENSION IF NOT EXISTS postgres_fdw WITH SCHEMA public;--
-- TOC entry 3674 (class 0 OID 0)
-- Dependencies: 2
-- Name: EXTENSION postgres_fdw; Type: COMMENT; Schema: -; Owner: -
--COMMENT ON EXTENSION postgres_fdw IS foreign-data wrapper for remote PostgreSQL servers;--
-- TOC entry 225 (class 1255 OID 24616)
-- Name: update_order_status(); Type: FUNCTION; Schema: public; Owner: -
--CREATE FUNCTION public.update_order_status() RETURNS triggerLANGUAGE plpgsqlAS $$
BEGINIF NEW.total_amount 1000 THENNEW.status : 已审核;ELSENEW.status : 待审核;END IF;RETURN NEW;
END;
$$;--
-- TOC entry 2321 (class 2328 OID 16403)
-- Name: test1; Type: FOREIGN DATA WRAPPER; Schema: -; Owner: -
--CREATE FOREIGN DATA WRAPPER test1 HANDLER public.postgres_fdw_handler VALIDATOR public.postgres_fdw_validator;--
-- TOC entry 2322 (class 1417 OID 16401)
-- Name: server1; Type: SERVER; Schema: -; Owner: -
--CREATE SERVER server1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname test,host 172.28.79.200,port 5432
);--
-- TOC entry 3675 (class 0 OID 0)
-- Name: USER MAPPING postgres SERVER server1; Type: USER MAPPING; Schema: -; Owner: -
--CREATE USER MAPPING FOR postgres SERVER server1 OPTIONS (password postgres,user postgres
);SET default_table_access_method heap;--
-- TOC entry 216 (class 1259 OID 24629)
-- Name: circles; Type: TABLE; Schema: public; Owner: -
--CREATE TABLE public.circles (c circle
);--
-- TOC entry 218 (class 1259 OID 25273)
-- Name: company6; Type: TABLE; Schema: public; Owner: -
--CREATE TABLE public.company6 (id integer NOT NULL,name text NOT NULL,age integer NOT NULL,address character(50),salary real
);--
-- TOC entry 219 (class 1259 OID 25280)
-- Name: department1; Type: TABLE; Schema: public; Owner: -
--CREATE TABLE public.department1 (id integer NOT NULL,dept character(50) NOT NULL,emp_id integer NOT NULL
);--
-- TOC entry 211 (class 1259 OID 16405)
-- Name: ft1; Type: FOREIGN TABLE; Schema: public; Owner: -
--CREATE FOREIGN TABLE public.ft1 (port integer
)
SERVER server1;--
-- TOC entry 215 (class 1259 OID 24611)
-- Name: orders; Type: TABLE; Schema: public; Owner: -
--CREATE TABLE public.orders (id integer NOT NULL,order_date date,total_amount numeric(10,2),status character varying(20)
);--
-- TOC entry 213 (class 1259 OID 24580)
-- Name: student; Type: TABLE; Schema: public; Owner: -
--CREATE TABLE public.student (sid integer NOT NULL,teacher_id integer DEFAULT 0 NOT NULL,tname character varying(100)
);--
-- TOC entry 212 (class 1259 OID 24577)
-- Name: teacher; Type: TABLE; Schema: public; Owner: -
--CREATE TABLE public.teacher (id integer NOT NULL,sname character varying(100)
);--
-- TOC entry 214 (class 1259 OID 24584)
-- Name: student_view; Type: VIEW; Schema: public; Owner: -
--CREATE VIEW public.student_view ASSELECT student.sid,student.teacher_id,student.tname,teacher.id,teacher.snameFROM (public.studentLEFT JOIN public.teacher ON ((student.teacher_id teacher.id)));--
-- TOC entry 217 (class 1259 OID 25264)
-- Name: t2; Type: TABLE; Schema: public; Owner: -
--CREATE TABLE public.t2 (c1 integer,c2 text
);--
-- TOC entry 3662 (class 0 OID 24629)
-- Dependencies: 216
-- Data for Name: circles; Type: TABLE DATA; Schema: public; Owner: -
----
-- TOC entry 3664 (class 0 OID 25273)
-- Dependencies: 218
-- Data for Name: company6; Type: TABLE DATA; Schema: public; Owner: -
----
-- TOC entry 3665 (class 0 OID 25280)
-- Dependencies: 219
-- Data for Name: department1; Type: TABLE DATA; Schema: public; Owner: -
----
-- TOC entry 3661 (class 0 OID 24611)
-- Dependencies: 215
-- Data for Name: orders; Type: TABLE DATA; Schema: public; Owner: -
----
-- TOC entry 3660 (class 0 OID 24580)
-- Dependencies: 213
-- Data for Name: student; Type: TABLE DATA; Schema: public; Owner: -
--INSERT INTO public.student VALUES (1, 1, tname1);
INSERT INTO public.student VALUES (2, 1, tname1);
INSERT INTO public.student VALUES (3, 2, tname2);
INSERT INTO public.student VALUES (4, 3, tname3);
INSERT INTO public.student VALUES (5, 3, tname3);--
-- TOC entry 3663 (class 0 OID 25264)
-- Dependencies: 217
-- Data for Name: t2; Type: TABLE DATA; Schema: public; Owner: -
----
-- TOC entry 3659 (class 0 OID 24577)
-- Dependencies: 212
-- Data for Name: teacher; Type: TABLE DATA; Schema: public; Owner: -
--INSERT INTO public.teacher VALUES (1, sname1);
INSERT INTO public.teacher VALUES (2, sname2);
INSERT INTO public.teacher VALUES (3, sname3);--
-- TOC entry 3513 (class 2606 OID 24633)
-- Name: circles circles_c_excl; Type: CONSTRAINT; Schema: public; Owner: -
--ALTER TABLE ONLY public.circlesADD CONSTRAINT circles_c_excl EXCLUDE USING gist (c WITH );--
-- TOC entry 3515 (class 2606 OID 25279)
-- Name: company6 company6_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--ALTER TABLE ONLY public.company6ADD CONSTRAINT company6_pkey PRIMARY KEY (id);--
-- TOC entry 3517 (class 2606 OID 25284)
-- Name: department1 department1_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--ALTER TABLE ONLY public.department1ADD CONSTRAINT department1_pkey PRIMARY KEY (id);--
-- TOC entry 3511 (class 2606 OID 24615)
-- Name: orders orders_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--ALTER TABLE ONLY public.ordersADD CONSTRAINT orders_pkey PRIMARY KEY (id);--
-- TOC entry 3508 (class 2606 OID 25272)
-- Name: student student_ck; Type: CHECK CONSTRAINT; Schema: public; Owner: -
--ALTER TABLE public.studentADD CONSTRAINT student_ck CHECK ((sid 0)) NOT VALID;--
-- TOC entry 3676 (class 0 OID 0)
-- Dependencies: 3508
-- Name: CONSTRAINT student_ck ON student; Type: COMMENT; Schema: public; Owner: -
--COMMENT ON CONSTRAINT student_ck ON public.student IS 检查约束;--
-- TOC entry 3518 (class 1259 OID 25290)
-- Name: fki_C; Type: INDEX; Schema: public; Owner: -
--CREATE INDEX fki_C ON public.department1 USING btree (emp_id);--
-- TOC entry 3509 (class 1259 OID 24604)
-- Name: index_test; Type: INDEX; Schema: public; Owner: -
--CREATE INDEX index_test ON public.student USING btree (tname COLLATE C bpchar_pattern_ops);--
-- TOC entry 3677 (class 0 OID 0)
-- Dependencies: 3509
-- Name: INDEX index_test; Type: COMMENT; Schema: public; Owner: -
--COMMENT ON INDEX public.index_test IS 测试;-- Completed on 2023-08-24 09:06:20 CST--
-- PostgreSQL database dump complete
--
7备份服务器
1对话框
2备份信息
blueprint.route(/job/int:sid, methods[POST], endpointcreate_server_job
)
blueprint.route(/job/int:sid/object, methods[POST], endpointcreate_object_job
)
login_required
def create_backup_objects_job(sid):Args:sid: Server IDCreates a new job for backup task(Backup Database(s)/Schema(s)/Table(s))Returns:None# 获取请求# {file: localhostserver, format: plain, id: None, blobs: False, verbose: True, encoding: UTF8, role: postgres, only_data: True, dns_owner: True, dns_privilege: True, dns_tablespace: True, use_insert_commands: True, disable_trigger: True, disable_quoting: True, type: server}data json.loads(request.data, encodingutf-8)# serverbackup_obj_type data.get(type, objects)try:# 获取文件路径# /var/lib/pgadmin/storage/dangfulin2333_163.com/localhostserverbackup_file filename_with_file_manager_path(data[file], (data.get(format, ) ! directory))except Exception as e:return bad_request(errormsgstr(e))# 获取服务器信息# Server 1server get_server(sid)if server is None:return make_json_response(success0,errormsg_(Could not find the specified server.))# To fetch MetaData for the serverfrom pgadmin.utils.driver import get_driverdriver get_driver(PG_DEFAULT_DRIVER)manager driver.connection_manager(server.id)conn manager.connection()connected conn.connected()if not connected:return make_json_response(success0,errormsg_(Please connect to the server first.))# 获取备份工具# /usr/lib/postgresql/14/bin/pg_dumpallutility manager.utility(backup) if backup_obj_type objects \else manager.utility(backup_server)ret_val does_utility_exist(utility)if ret_val:return make_json_response(success0,errormsgret_val)# 工具参数# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/localhostserver, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --database, postgres, --verbose, --role, postgres, --data-only, --disable-triggers, --no-owner, --no-privileges, --no-tablespaces, --inserts, --disable-dollar-quoting, --encoding, UTF8]args _get_args_params_values(data, conn, backup_obj_type, backup_file, server, manager)# 这里将参数中的所有双引号转义因为双引号在shell命令行中具有特殊含义它们在没有双引号的情况下运行。添加额外的引号以保存我们的双引号。# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/localhostserver, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --database, postgres, --verbose, --role, postgres, --data-only, --disable-triggers, --no-owner, --no-privileges, --no-tablespaces, --inserts, --disable-dollar-quoting, --encoding, UTF8]escaped_args [escape_dquotes_process_arg(arg) for arg in args]try:# 用 utf-8 编码文件名# /var/lib/pgadmin/storage/dangfulin2333_163.com/localhostserverbfile data[file].encode(utf-8) \if hasattr(data[file], encode) else data[file]# 区分不同的备份类型if backup_obj_type objects: # 备份数据对象数据库、模式、表数据args.append(data[database])# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/pdb, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --verbose, --role, postgres, --formatp, --no-owner, --create, --clean, --sectionpre-data, --sectiondata, --sectionpost-data, --no-tablespaces, --inserts, --encoding, UTF8, postgres]escaped_args.append(data[database])# [--file, /var/lib/pgadmin/storage/dangfulin2333_163.com/pdb, --host, 127.0.0.1, --port, 5432, --username, postgres, --no-password, --verbose, --role, postgres, --formatp, --no-owner, --create, --clean, --sectionpre-data, --sectiondata, --sectionpost-data, --no-tablespaces, --inserts, --encoding, UTF8, postgres]p BatchProcess(descBackupMessage(BACKUP.OBJECT, server.id, bfile,*args,databasedata[database]),cmdutility, argsescaped_args)else: # 备份服务器数据p BatchProcess(descBackupMessage(BACKUP.SERVER if backup_obj_type ! globalselse BACKUP.GLOBALS,server.id, bfile,*args),cmdutility, argsescaped_args)manager.export_password_env(p.id)# Check for connection timeout and if it is greater than 0 then# set the environment variable PGCONNECT_TIMEOUT.if manager.connect_timeout 0:env dict()env[PGCONNECT_TIMEOUT] str(manager.connect_timeout)p.set_env_variables(server, envenv)else:p.set_env_variables(server)# 创建子进程执行 pg_dump 命令p.start()jid p.idexcept Exception as e:current_app.logger.exception(e)return make_json_response(status410,success0,errormsgstr(e))# Return responsereturn make_json_response(data{job_id: jid, desc: p.desc.message, Success: 1})- 与备份表、schema、database 时使用 dump 不同的是备份 server 时使用 dumpall
3备份文件
--
-- PostgreSQL database cluster dump
---- Started on 2023-08-24 09:15:36 CSTSET default_transaction_read_only off;SET client_encoding UTF8;
SET standard_conforming_strings on;--
-- Databases
----
-- Database template1 dump
--\connect template1--
-- PostgreSQL database dump
---- Dumped from database version 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)-- Started on 2023-08-24 09:15:36 CSTSET 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;-- Completed on 2023-08-24 09:15:36 CST--
-- PostgreSQL database dump complete
----
-- Database postgres dump
--\connect postgres--
-- PostgreSQL database dump
---- Dumped from database version 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)-- Started on 2023-08-24 09:15:36 CSTSET 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;--
-- TOC entry 3660 (class 0 OID 24629)
-- Dependencies: 216
-- Data for Name: circles; Type: TABLE DATA; Schema: public; Owner: -
--SET SESSION AUTHORIZATION DEFAULT;ALTER TABLE public.circles DISABLE TRIGGER ALL;ALTER TABLE public.circles ENABLE TRIGGER ALL;--
-- TOC entry 3662 (class 0 OID 25273)
-- Dependencies: 218
-- Data for Name: company6; Type: TABLE DATA; Schema: public; Owner: -
--ALTER TABLE public.company6 DISABLE TRIGGER ALL;ALTER TABLE public.company6 ENABLE TRIGGER ALL;--
-- TOC entry 3663 (class 0 OID 25280)
-- Dependencies: 219
-- Data for Name: department1; Type: TABLE DATA; Schema: public; Owner: -
--ALTER TABLE public.department1 DISABLE TRIGGER ALL;ALTER TABLE public.department1 ENABLE TRIGGER ALL;--
-- TOC entry 3659 (class 0 OID 24611)
-- Dependencies: 215
-- Data for Name: orders; Type: TABLE DATA; Schema: public; Owner: -
--ALTER TABLE public.orders DISABLE TRIGGER ALL;ALTER TABLE public.orders ENABLE TRIGGER ALL;--
-- TOC entry 3658 (class 0 OID 24580)
-- Dependencies: 213
-- Data for Name: student; Type: TABLE DATA; Schema: public; Owner: -
--ALTER TABLE public.student DISABLE TRIGGER ALL;INSERT INTO public.student VALUES (1, 1, tname1);
INSERT INTO public.student VALUES (2, 1, tname1);
INSERT INTO public.student VALUES (3, 2, tname2);
INSERT INTO public.student VALUES (4, 3, tname3);
INSERT INTO public.student VALUES (5, 3, tname3);ALTER TABLE public.student ENABLE TRIGGER ALL;--
-- TOC entry 3661 (class 0 OID 25264)
-- Dependencies: 217
-- Data for Name: t2; Type: TABLE DATA; Schema: public; Owner: -
--ALTER TABLE public.t2 DISABLE TRIGGER ALL;ALTER TABLE public.t2 ENABLE TRIGGER ALL;--
-- TOC entry 3657 (class 0 OID 24577)
-- Dependencies: 212
-- Data for Name: teacher; Type: TABLE DATA; Schema: public; Owner: -
--ALTER TABLE public.teacher DISABLE TRIGGER ALL;INSERT INTO public.teacher VALUES (1, sname1);
INSERT INTO public.teacher VALUES (2, sname2);
INSERT INTO public.teacher VALUES (3, sname3);ALTER TABLE public.teacher ENABLE TRIGGER ALL;-- Completed on 2023-08-24 09:15:36 CST--
-- PostgreSQL database dump complete
---- Completed on 2023-08-24 09:15:36 CST--
-- PostgreSQL database cluster dump complete
-- pg_dump 二恢复
1恢复 table
1恢复 schema
1恢复 db
1恢复 database pg_restore 三
四
五
六
一
二
四
五
六
七
八
九
十
十一
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
《PostgreSQL 开发指南》第 08 篇 备份与恢复
