Postgresql 12 using pgAdmin4, menu Tools/Backup/Format-Plain to backup a db to a sql file like this:
--
-- PostgreSQL database dump
--
-- Dumped from database version 12.3
-- Dumped by pg_dump version 12.3
-- Started on 2020-08-06 17:38:54
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- TOC entry 202 (class 1259 OID 17973)
-- Name: customers; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.customers (
"CustomerID" character varying(32) NOT NULL,
"Name" character varying(128),
"AddressLine" character varying(128)
);
ALTER TABLE public.customers OWNER TO postgres;
--
-- TOC entry 3232 (class 0 OID 17973)
-- Dependencies: 202
-- Data for Name: customers; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.customers ("CustomerID", "Name", "AddressLine") FROM stdin;
\.
-- Completed on 2020-08-06 17:38:54
--
-- PostgreSQL database dump complete
--
Create a new empty db and try to run above sql file to create schema and data. Error happens on the backslash under "COPY": pgAdmin got error:
ERROR: syntax error at or near "\"
LINE 45: \.
^
SQL state: 42601
Character: 1092
How could I correctly run the sql file to restore schema and data?
You must restore a plain format pg_dump with psql. pgAdmin or other clients juat won't do, unless you used the --inserts option of pg_dump.
The reason is that other clients cannot handle the mixture of SQL statements and COPY data in a single input file.