Horton
Menu

Thread

  1. suggestion

    Jan Cruz <malebug@gmail.com> — 2006-02-23T23:39:58Z

    I hope in pg_dump there should be an option that will
dump a schema without the indexes (except of course primary indexes)

regards

  2. Re: suggestion

    Michael Glaesemann <grzm@myrealbox.com> — 2006-02-23T23:43:49Z

    On Feb 24, 2006, at 8:39 , Jan Cruz wrote:

> I hope in pg_dump there should be an option that will
> dump a schema without the indexes (except of course primary indexes)

Have you looked at the --schema-only flag?

http://www.postgresql.org/docs/current/interactive/app-pgdump.html

If this doesn't do what you want, can you give a bit more  
explanation? Also, what are you trying to do with this dump file?

Michael Glaesemann
grzm myrealbox com

  3. Re: suggestion

    Jan Cruz <malebug@gmail.com> — 2006-02-23T23:58:38Z

    On 2/24/06, Michael Glaesemann <grzm@myrealbox.com> wrote:
>
>
> Have you looked at the --schema-only flag?
>
> http://www.postgresql.org/docs/current/interactive/app-pgdump.html
>
> If this doesn't do what you want, can you give a bit more
> explanation? Also, what are you trying to do with this dump file?
>
> Michael Glaesemann
> grzm myrealbox com



--schema-only flag is the equivalence of -s

I also want an option that would exclude  "CREATE INDEX "  whenever
a schema is being dump

The reason for this is that whenever I tried to migrate database
whenever I restore a schema with indexes and then
restore the data separately it took more or less 24 hours instead of
the usual 1 hour more or less.

For example let say I want to use this particular schema from the test
server
since the stored functions and/or views are already updated

and then my data would come from the production server which has a different

version of postgres and the schema (particularly the stored functions/views)
that
would is already deprecated from the test server.

So I just need to dump the schema from the test server without the indexes
and restore the new schema to a new database/server and restore the dump
from the production server to the new database/server.

I hope I explain my side properly

  4. Re: suggestion

    Jim Nasby <jnasby@pervasive.com> — 2006-02-24T00:02:41Z

    On Fri, Feb 24, 2006 at 07:58:38AM +0800, Jan Cruz wrote:
> On 2/24/06, Michael Glaesemann <grzm@myrealbox.com> wrote:
> --schema-only flag is the equivalence of -s
> 
> I also want an option that would exclude  "CREATE INDEX "  whenever
> a schema is being dump

At least on my small test database, all the CREATE INDEX commands are
one-liners. This means it would be trivial to exclude them with
grep -v 'CREATE INDEX, or grab just them with grep 'CREATE INDEX'. I'd
just stick the greps in between cat and psql -f -.

> The reason for this is that whenever I tried to migrate database
> whenever I restore a schema with indexes and then
> restore the data separately it took more or less 24 hours instead of
> the usual 1 hour more or less.
> 
> For example let say I want to use this particular schema from the test
> server
> since the stored functions and/or views are already updated
> 
> and then my data would come from the production server which has a different
> 
> version of postgres and the schema (particularly the stored functions/views)
> that
> would is already deprecated from the test server.
> 
> So I just need to dump the schema from the test server without the indexes
> and restore the new schema to a new database/server and restore the dump
> from the production server to the new database/server.
> 
> I hope I explain my side properly

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

  5. Re: suggestion

    Andrew Dunstan <andrew@dunslane.net> — 2006-02-24T01:44:57Z

    
Jim C. Nasby wrote:

>On Fri, Feb 24, 2006 at 07:58:38AM +0800, Jan Cruz wrote:
>  
>
>>On 2/24/06, Michael Glaesemann <grzm@myrealbox.com> wrote:
>>--schema-only flag is the equivalence of -s
>>
>>I also want an option that would exclude  "CREATE INDEX "  whenever
>>a schema is being dump
>>    
>>
>
>At least on my small test database, all the CREATE INDEX commands are
>one-liners. This means it would be trivial to exclude them with
>grep -v 'CREATE INDEX, or grab just them with grep 'CREATE INDEX'. I'd
>just stick the greps in between cat and psql -f -.
>  
>

Much better than this, you can do a custom dump and then use 
pg_restore's --list and --use-list features to remove the things you 
don't want restored. pg_restore is wonderfully flexible.

cheers

andrew

  6. Re: suggestion

    Michael Glaesemann <grzm@myrealbox.com> — 2006-02-24T01:56:52Z

    On Feb 24, 2006, at 10:44 , Andrew Dunstan wrote:

> Much better than this, you can do a custom dump and then use  
> pg_restore's --list and --use-list features to remove the things  
> you don't want restored. pg_restore is wonderfully flexible.

That is nifty! Wow! Thanks, Andrew!

Michael Glaesemann
grzm myrealbox com

  7. Re: suggestion

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-02-24T04:45:47Z

    "Jan Cruz" <malebug@gmail.com> writes:
> On 2/24/06, Michael Glaesemann <grzm@myrealbox.com> wrote:
>> If this doesn't do what you want, can you give a bit more
>> explanation? Also, what are you trying to do with this dump file?

> I also want an option that would exclude  "CREATE INDEX "  whenever
> a schema is being dump

> The reason for this is that whenever I tried to migrate database
> whenever I restore a schema with indexes and then
> restore the data separately it took more or less 24 hours instead of
> the usual 1 hour more or less.

Restoring schema and data separately is guaranteed to be less efficient
than restoring a combined dump.  An option to omit indexes from the
schema dump will not fix this.  Indeed it will arguably make things
worse --- in the first place there are severe performance issues
associated with unindexed foreign-key checks, and in the second place
there is the foot-gun problem that you might forget to re-add the
indexes at all.

I think the right question to ask here is "why are you so intent on
using separate schema/data restores?"  That's not the recommended way
to go about things, and it never will be.

			regards, tom lane

  8. Re: suggestion

    Jan Cruz <malebug@gmail.com> — 2006-02-24T05:23:44Z

    >
> I think the right question to ask here is "why are you so intent on
> using separate schema/data restores?"  That's not the recommended way
> to go about things, and it never will be.
>
>                         regards, tom lane
>
Simply because it took me more or less 24 hours to restore the dump when the

index is already defined. And it would only take less than an hour to
restore the
data without the index then create the index that would only take less than
10 minutes.

BTW I am using postgresql 8.1.3

  9. Re: suggestion

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2006-02-24T05:36:34Z

    > Simply because it took me more or less 24 hours to restore the dump when 
> the
> index is already defined. And it would only take less than an hour to 
> restore the
> data without the index then create the index that would only take less 
> than 10 minutes.
> 
> BTW I am using postgresql 8.1.3

You still haven't answered the question - why are you doing separate 
schema and data dumps then loading them.  That will always be very, 
very, very, very slow.

Just do a normal data+schema dump and it will restore quickly.

Chris

  10. Re: suggestion

    Lukas Kahwe Smith <smith@pooteeweet.org> — 2006-02-24T07:25:44Z

    Tom Lane wrote:

> worse --- in the first place there are severe performance issues
> associated with unindexed foreign-key checks, and in the second place
> there is the foot-gun problem that you might forget to re-add the
> indexes at all.

MySQL has a syntax in ALTER TABLE similar to PGSQL's DISABLE/ENABLE 
TRIGGER to do the same for KEYS. That way you will not run the risk of 
forgetting the INDEX (though you could still forget to enable them).

Potentially there could be a switch in pg_restore to create all KEYS as 
disabled initially and automatically enable them after the import is 
complete. Note that it could be smart enough to give special treatment 
to foreign keys.

regards,
Lukas

  11. Re: suggestion

    Jan Cruz <malebug@gmail.com> — 2006-03-20T02:35:24Z

    I wish someone will include --disable-indexes when copying/restoring
just like --disable-triggers

Thanks