当前位置： 首页 > news >正文

网站首页排名免费网站推广网站短视频

news 2025/7/28 1:23:50
网站首页排名,免费网站推广网站短视频,ibm用来做测试的网站,免费网站可以做cpa一，postgresql 数据的备份与恢复 （一）数据库备份与恢复 1，备份 windows环境 1> dump 逻辑备份 1，用管理员身份打开power shell 2，切换到本机 postgresql 安装目录下的 bin 目录： 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\bin>

3，执行 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 逻辑备份

Linux（ubuntu）环境

（二）数据表备份与恢复

二，pgadmin4

（一）备份

1，备份 table

在这里插入图片描述在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
2，备份详情：

请求参数：

gid1
sid=1
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'], endpoint='create_server_job'
)
@blueprint.route('/job/<int:sid>/object', methods=['POST'], endpoint='create_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, encoding='utf-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(errormsg=str(e))# 获取服务器信息server = get_server(sid)if server is None:return make_json_response(success=0,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(success=0,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(success=0,errormsg=ret_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', '--format=p', '--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(desc=BackupMessage(BACKUP.OBJECT, server.id, bfile,*args,database=data['database']),cmd=utility, args=escaped_args)else:                               # 备份服务器数据p = BatchProcess(desc=BackupMessage(BACKUP.SERVER if backup_obj_type != 'globals'else BACKUP.GLOBALS,server.id, bfile,*args),cmd=utility, args=escaped_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, env=env)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(status=410,success=0,errormsg=str(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'], endpoint='create_server_job'
)
@blueprint.route('/job/<int:sid>/object', methods=['POST'], endpoint='create_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, encoding='utf-8')# 'objects'backup_obj_type = data.get('type', 'objects')try:# 获取文件路径# '/var/lib/pgadmin/storage/dangfulin2333_163.com/psche'backup_file = filename_with_file_manager_path(data['file'], (data.get('format', '') != 'directory'))except Exception as e:return bad_request(errormsg=str(e))# 获取服务器信息# <Server 1>server = get_server(sid)if server is None:return make_json_response(success=0,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(success=0,errormsg=_("Please connect to the server first."))# 获取备份工具# '/usr/lib/postgresql/14/bin/pg_dump'utility = 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(success=0,errormsg=ret_val)# 工具参数# ['--file', '/var/lib/pgadmin/storage/dangfulin2333_163.com/psche', '--host', '127.0.0.1', '--port', '5432', '--username', 'postgres', '--no-password', '--verbose', '--role', 'postgres', '--format=p', '--no-owner', '--create', '--clean', '--section=data', '--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', '--format=p', '--no-owner', '--create', '--clean', '--section=data', '--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', '--format=p', '--no-owner', '--create', '--clean', '--section=data', '--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', '--format=p', '--no-owner', '--create', '--clean', '--section=data', '--no-tablespaces', '--no-unlogged-table-data', '--inserts', '--no-comments', '--encoding', 'UTF8', '--schema', 'public', 'postgres']p = BatchProcess(desc=BackupMessage(BACKUP.OBJECT, server.id, bfile,*args,database=data['database']),cmd=utility, args=escaped_args)else:                               # 备份服务器数据p = BatchProcess(desc=BackupMessage(BACKUP.SERVER if backup_obj_type != 'globals'else BACKUP.GLOBALS,server.id, bfile,*args),cmd=utility, args=escaped_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, env=env)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(status=410,success=0,errormsg=str(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'], endpoint='create_server_job'
)
@blueprint.route('/job/<int:sid>/object', methods=['POST'], endpoint='create_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, encoding='utf-8')# 'objects'backup_obj_type = data.get('type', 'objects')try:# 获取文件路径# '/var/lib/pgadmin/storage/dangfulin2333_163.com/pdb'backup_file = filename_with_file_manager_path(data['file'], (data.get('format', '') != 'directory'))except Exception as e:return bad_request(errormsg=str(e))# 获取服务器信息# <Server 1>server = get_server(sid)if server is None:return make_json_response(success=0,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(success=0,errormsg=_("Please connect to the server first."))# 获取备份工具# '/usr/lib/postgresql/14/bin/pg_dump'utility = 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(success=0,errormsg=ret_val)# 工具参数# ['--file', '/var/lib/pgadmin/storage/dangfulin2333_163.com/pdb', '--host', '127.0.0.1', '--port', '5432', '--username', 'postgres', '--no-password', '--verbose', '--role', 'postgres', '--format=p', '--no-owner', '--create', '--clean', '--section=pre-data', '--section=data', '--section=post-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', '--format=p', '--no-owner', '--create', '--clean', '--section=pre-data', '--section=data', '--section=post-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', '--format=p', '--no-owner', '--create', '--clean', '--section=pre-data', '--section=data', '--section=post-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', '--format=p', '--no-owner', '--create', '--clean', '--section=pre-data', '--section=data', '--section=post-data', '--no-tablespaces', '--inserts', '--encoding', 'UTF8', 'postgres']p = BatchProcess(desc=BackupMessage(BACKUP.OBJECT, server.id, bfile,*args,database=data['database']),cmd=utility, args=escaped_args)else:                               # 备份服务器数据p = BatchProcess(desc=BackupMessage(BACKUP.SERVER if backup_obj_type != 'globals'else BACKUP.GLOBALS,server.id, bfile,*args),cmd=utility, args=escaped_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, env=env)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(status=410,success=0,errormsg=str(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'], endpoint='create_server_job'
)
@blueprint.route('/job/<int:sid>/object', methods=['POST'], endpoint='create_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, encoding='utf-8')# 'server'backup_obj_type = data.get('type', 'objects')try:# 获取文件路径# '/var/lib/pgadmin/storage/dangfulin2333_163.com/localhostserver'backup_file = filename_with_file_manager_path(data['file'], (data.get('format', '') != 'directory'))except Exception as e:return bad_request(errormsg=str(e))# 获取服务器信息# <Server 1>server = get_server(sid)if server is None:return make_json_response(success=0,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(success=0,errormsg=_("Please connect to the server first."))# 获取备份工具# '/usr/lib/postgresql/14/bin/pg_dumpall'utility = 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(success=0,errormsg=ret_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/localhostserver'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', '--format=p', '--no-owner', '--create', '--clean', '--section=pre-data', '--section=data', '--section=post-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', '--format=p', '--no-owner', '--create', '--clean', '--section=pre-data', '--section=data', '--section=post-data', '--no-tablespaces', '--inserts', '--encoding', 'UTF8', 'postgres']p = BatchProcess(desc=BackupMessage(BACKUP.OBJECT, server.id, bfile,*args,database=data['database']),cmd=utility, args=escaped_args)else:                               # 备份服务器数据p = BatchProcess(desc=BackupMessage(BACKUP.SERVER if backup_obj_type != 'globals'else BACKUP.GLOBALS,server.id, bfile,*args),cmd=utility, args=escaped_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, env=env)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(status=410,success=0,errormsg=str(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 篇 备份与恢复

http://www.tj-hxxt.cn/news/56780.html

相关文章：

  • 网站开发深圳故事性营销软文
  • wordpress计数插件深圳正规seo
  • 深圳政府门户网站建设评价下百度安装
  • 做网站用cms好吗合肥疫情最新消息
  • 做网站获取手机号码网站排名优化客服
  • 制作页培训大连百度seo
  • 相册制作模板平台优化是什么意思
  • 四川煤矿标准化建设网站免费大数据平台
  • 大学生毕业论文管理系统入口湘潭seo公司
  • 做dm素材网站百度快照有什么用
  • 网站开发毕设任务书百度快照优化推广
  • 视频如何上传到wordpress太原百度网站快速优化
  • 做自己移动端网站怎么优化关键词
  • 网站建站企业网络推广包括哪些
  • 常见的三种网站类型seo外链推广工具下载
  • 网站集约化建设 技术越秀seo搜索引擎优化
  • 中装建设信息流优化师
  • wordpress做出影视网站站长工具ping检测
  • 沈阳网站建设公司怎么样爱站网
  • 长沙网站快速优化排名搜索引擎优化到底是优化什么
  • 合肥做百度网站电影站的seo
  • 免费做图网站衡阳seo快速排名
  • 哪个网站容易做二级域名南京网站制作
  • 网站速度提升什么软件可以排名次
  • 做网站的收费标准网站seo排名优化软件
  • 企业网站建设的常见流程西地那非片
  • 镇江建工建设集团网站百度一下你就知道官网首页
  • wordpress微信付款郑州seo竞价
  • wordpress去谷歌插件河南智能seo快速排名软件
  • 做山西杂粮的网站优化大师官网