suggestion

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

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

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

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

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

"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

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

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

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

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

Thanks