Schema dump

Sonam Sharma <sonams1209@gmail.com> writes:

I took a schema dump using : pg_dump -n schema dbname .
When I restored this , it doesn't contain the constraints and indexes. Can
someone please help how to take a dump including all

Hmph ... works for me. Where by "works", I mean "the dump contains
constraints and indexes belonging to tables in the specified schema,
and not any others". Maybe you could provide a little more detail?

(One thing I notice is that the dump doesn't contain a "CREATE
SCHEMA schema" command, so you have to do that manually before
you restore. I guess this fits with the definition of the switch
as selecting objects *in* the named schema, but it's still a
possible gotcha.)

regards, tom lane

On 1/2/20 5:47 AM, Tom Lane wrote:

Sonam Sharma <sonams1209@gmail.com> writes:

I took a schema dump using : pg_dump -n schema dbname .
When I restored this , it doesn't contain the constraints and indexes. Can
someone please help how to take a dump including all

Hmph ... works for me. Where by "works", I mean "the dump contains
constraints and indexes belonging to tables in the specified schema,
and not any others". Maybe you could provide a little more detail?

(One thing I notice is that the dump doesn't contain a "CREATE
SCHEMA schema" command, so you have to do that manually before
you restore. I guess this fits with the definition of the switch
as selecting objects *in* the named schema, but it's still a
possible gotcha.)

What version? As I see:

pg_dump -U postgres -d production -n utility -f utility_sch.sql

In utility_sch.sql:

CREATE SCHEMA utility;

I ask because the docs:

https://www.postgresql.org/docs/12/app-pgdump.html

"Dump only schemas matching pattern; this selects both the schema
itself, and all its contained objects. "

support the idea that the schema definition is dumped.

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

On 1/2/20 5:47 AM, Tom Lane wrote:

Sonam Sharma <sonams1209@gmail.com> writes:

I took a schema dump using : pg_dump -n schema dbname .
When I restored this , it doesn't contain the constraints and indexes. Can
someone please help how to take a dump including all

Hmph ... works for me. Where by "works", I mean "the dump contains
constraints and indexes belonging to tables in the specified schema,
and not any others". Maybe you could provide a little more detail?

(One thing I notice is that the dump doesn't contain a "CREATE
SCHEMA schema" command, so you have to do that manually before
you restore. I guess this fits with the definition of the switch
as selecting objects *in* the named schema, but it's still a
possible gotcha.)

Caffeine kicked in, now I remember how the above happens:

pg_restore -n utility -f pgrestore_utility.sql /production_12.out

In pgrestore_utility.sql there is no CREATE SCHEMA.

This came up in a previous thread:

/messages/by-id/6234.1569941612@sss.pgh.pa.us

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks Adrian, it worked :)

On Thu, Jan 2, 2020, 9:50 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 1/2/20 9:04 AM, Sonam Sharma wrote:

Thanks Adrian, it worked :)

What worked?

Still not clear what was not working in the first place?

On Thu, Jan 2, 2020, 9:50 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 1/2/20 5:47 AM, Tom Lane wrote:

Sonam Sharma <sonams1209@gmail.com <mailto:sonams1209@gmail.com>>

writes:

I took a schema dump using : pg_dump -n schema dbname .
When I restored this , it doesn't contain the constraints and

indexes. Can

someone please help how to take a dump including all

Hmph ... works for me.  Where by "works", I mean "the dump contains
constraints and indexes belonging to tables in the specified schema,
and not any others".  Maybe you could provide a little more detail?

(One thing I notice is that the dump doesn't contain a "CREATE
SCHEMA schema" command, so you have to do that manually before
you restore.  I guess this fits with the definition of the switch
as selecting objects *in* the named schema, but it's still a
possible gotcha.)

Caffeine kicked in, now I remember how the above happens:

pg_restore -n utility -f pgrestore_utility.sql /production_12.out

In pgrestore_utility.sql there is no CREATE SCHEMA.

This came up in a previous thread:

/messages/by-id/6234.1569941612@sss.pgh.pa.us

                       regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com