I backed a table into a file using something like pg_dump --table=ex.tab
.
But when I restore the dump file into a newly created database, it complains that the schema ex
does not exist. Looking into the dump file ( listed below), it does not have a CREATE SCHEMA
statement.
Is there an option that I can add to pg_dump
or pg_store
so that the schema of the table is automatically created?
Note: I do this for multiple tables in the command line (multiple --table=
), and do not want to manually create schema
for each table beforehand (if possible).
--
-- PostgreSQL database dump
--
-- Dumped from database version 11.1 (Ubuntu 11.1-3.pgdg18.04+1)
-- Dumped by pg_dump version 11.1 (Ubuntu 11.1-3.pgdg18.04+1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: tab; Type: TABLE; Schema: ex; Owner: myuser
--
CREATE TABLE ex.tab (
name character varying(80),
state_ character varying(2)
);
ALTER TABLE ex.tab OWNER TO myuser;
--
-- Data for Name: tab; Type: TABLE DATA; Schema: ex; Owner: myuser
--
COPY ex.tab (name, state_) FROM stdin;
San Francisco CA
ST LOUIS MO
LOS ANGELES CA
\.
--
-- PostgreSQL database dump complete
--