Обсуждение: suggestion
I hope in pg_dump there should be an option that will <br /> dump a schema without the indexes (except of course primaryindexes)<br /><br /> regards<br />
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:
--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
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
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 <br /> just like --disable-triggers<br /><br /> Thanks<br/>