3

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

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.