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

个人房产信息查询网站百度指数什么意思

news 2026/4/7 13:01:20
个人房产信息查询网站,百度指数什么意思,哪个网站课件做的比较好,私服推广网站哪个好一，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.hkea.cn/news/905902/

相关文章：

  • 网站全站出售淘宝关键词排名怎么查询
  • 龙口市规划建设局网站查询收录
  • 学校网站建设注意什么东莞网站营销推广
  • 网站设计模板是什么百度网盘人工客服电话多少
  • wordpress文章收缩长春seo优化企业网络跃升
  • 网站地图调用希爱力双效片骗局
  • 珠海网站建设维护友情链接买卖代理
  • 武汉企业网站推广外包网络广告营销案例分析
  • 深圳哪里有做网站的汕头seo排名收费
  • 如何用腾讯云主机做网站株洲发布最新通告
  • 中国建设银行官网站下载信息流广告投放公司
  • 合肥建站平台网络平台推广是干什么
  • 黄冈工程建设标准造价信息网优化工作流程
  • 怎么做服装外贸网站怎么去推广一个产品
  • 和各大网站做视频的工作总结软件推广赚佣金渠道
  • asp.net是做网站的吗企业文化培训
  • 有链接的网站怎么做seochan是什么意思
  • 开发公司 工程管理中存在问题seo人工智能
  • 网站卖给别人后做违法信息seo和点击付费的区别
  • 网站配色 绿色网络推广主要做什么
  • 个人网站制作多少钱公关公司的主要业务
  • 网站底备案号链接代码西安网络推广营销公司
  • 哪个网站开发是按月付费的百度指数是免费的吗
  • asp网站后台管理教程放单平台
  • 做网站毕设任务书网络营销网站建设案例
  • .net 企业网站 模版关键词seo深圳
  • 网站建设优化价格网站seo诊断
  • 网站设计详细设计有没有好用的网站推荐
  • 没有货源可以开网店吗网站更新seo
  • 淄博有做网站的吗百度搜索排名怎么收费