Postgresql – how to automatically create the associated schema when I `pg_restore` a table

pg-dumppg-restorepostgresql

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
--

Best Answer

Use pg_dump --schema option for this.

With:

$ psql
psql (9.4.19)
Type "help" for help.

mevzek=# CREATE SCHEMA test1;
CREATE SCHEMA
mevzek=# CREATE TABLE test1.atable (id SERIAL);
CREATE TABLE
mevzek=# \q

You can compare the results of the two commands:

$ pg_dump --table test1.atable > dump1.sql
$ pg_dump --schema test1 > dump2.sql
$ diff -u dump1.sql dump2.sql
--- dump1.sql   2019-07-18 12:55:53.000000000 -0500
+++ dump2.sql   2019-07-18 12:56:59.000000000 -0500
@@ -10,6 +10,15 @@
 SET check_function_bodies = false;
 SET client_min_messages = warning;

+--
+-- Name: test1; Type: SCHEMA; Schema: -; Owner: mevzek
+--
+
+CREATE SCHEMA test1;
+
+
+ALTER SCHEMA test1 OWNER TO mevzek;
+
 SET default_tablespace = '';

 SET default_with_oids = false;

If you add --table it does not seem to create the schema anymore. You could go around that with two commands: pg_dump --schema test1 --exclude-table-data '*' and then pg_dump --table test1.atable --data-only or similar.

Related Question