Question 235701 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – pg_dump fails to dump specific schema

I am using pg_dump (15.3) to dump information on a database.

This command works:
pg_dump --schema-only --schema foo -d foodb -U postgres -Fp and dumps among other things a CREATE SCHEMA foo; statement

This command doesn’t:
pg_dump --schema-only --schema cron -d foodb -U postgres -Fp since it only dumps the following:

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;

--
-- PostgreSQL database dump complete
--

Note that it doesn’t complain that the schema does not exist.

SELECT a.* FROM information_schema.schemata a where schema_name in ('foo','cron');

shows the same output (apart from the schema_name) for both schemata

I am using a timescaledb (timescale/timescaledb-ha:pg15.3-ts2.11.0-all) from Docker if this is of any help

Tags:

2

Answers


  1. 0

    Try running this command if your pg_dump is not dumping the cron schema;

    pg_dump --schema=cron -d yourdatabase -U postgres -Fp -f cron_schema_dump.sql

    Replace yourdatabase with the name of your actual database.

    Login or Signup to reply.
  2. 0

    Schemas created as part of an extension will not get dumped by the command you show, giving the symptoms you show. cron is probably created by the extension pg_cron.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search