Thread: Schema dump
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
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: https://www.postgresql.org/message-id/6234.1569941612%40sss.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 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:
https://www.postgresql.org/message-id/6234.1569941612%40sss.pgh.pa.us
>
> regards, tom lane
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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: > > https://www.postgresql.org/message-id/6234.1569941612%40sss.pgh.pa.us > > > > > regards, tom lane > > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com