Thread: pg_dump of only the structure from a client such as ruby
How hard would it be to do the following two statements (the equivalent of the following two statements) using a client?
pg_dump -i -U uuuuu -s -x -O -f file dbname
psql -U uuuuu -f file dbname
I'm trying to help out the rails people. Currently they have "rake" scripts which call pg_dump, dropdb, createdb, and psql. It would be nicer if this could be done via a database connection.
The psql piece I believe is fairly easy. But I don't know where to begin with the pg_dump piece of it. I took a glance at the pg_dump code and it is hugh... so I got scared and ran away.
Perry Smith ( pedz@easesoftware.com )
Ease Software, Inc. ( http://www.easesoftware.com )
On Aug 5, 2007, at 16:31 , Perry Smith wrote: > I'm trying to help out the rails people. Currently they have > "rake" scripts which call pg_dump, dropdb, createdb, and psql. It > would be nicer if this could be done via a database connection. Why would this be nicer? What's the advantage? pg_dump in particular *is* a client application. It isn't called from within another connection. As loading a schema from a file via psql, that's also fairly common practice. Alternatives to createdb or dropdb are just as easily handled by passing a command to psql using its -c flag. Another point to consider is that using CREATE/DROP DATABASE instead of the createdb/dropdb client applications requires a database connection: if you don't have a database to connect to, you can't use them. Of course, you could initially connect to the postgres database (or template1 in older version), but that could require configuring parameters for another database in database.yml. What's the advantage? Michael Glaesemann grzm seespotcode net
On Aug 5, 2007, at 6:07 PM, Michael Glaesemann wrote: > > On Aug 5, 2007, at 16:31 , Perry Smith wrote: > >> I'm trying to help out the rails people. Currently they have >> "rake" scripts which call pg_dump, dropdb, createdb, and psql. It >> would be nicer if this could be done via a database connection. > > Why would this be nicer? What's the advantage? pg_dump in > particular *is* a client application. It isn't called from within > another connection. As loading a schema from a file via psql, > that's also fairly common practice. Alternatives to createdb or > dropdb are just as easily handled by passing a command to psql > using its -c flag. Another point to consider is that using CREATE/ > DROP DATABASE instead of the createdb/dropdb client applications > requires a database connection: if you don't have a database to > connect to, you can't use them. Of course, you could initially > connect to the postgres database (or template1 in older version), > but that could require configuring parameters for another database > in database.yml. What's the advantage? Connecting to the postgres database is pretty trivial. If you want to create foo_development, you use all config stanza for development replace the database name with "postgres" (or "template1"). If you want to dump foo_development, it better be there! :-) I just submitted a patch to do create_database and drop_database to the postgresql adapter. I wanted to complete the other two missing pieces. The rake code for postgesql in this area is uglier than the mysql code. I mostly just wanted to clean it up. And, one problem I have is, often, pg_dump is not in my path. I find it odd that you are resistant to the idea. To me, the advantages are clear if it can be done without a tremendous amount of work. Why horse around with the environment variables to set such things as password, call sh, just to call pg_dump. Perry Smith ( pedz@easesoftware.com ) Ease Software, Inc. ( http://www.easesoftware.com ) Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems
Perry Smith <pedz@easesoftware.com> writes: > I find it odd that you are resistant to the idea. To me, the > advantages are clear if it can be done without a tremendous amount of > work. Well, it can't. pg_dump is an enormously complicated and frequently changed bit of code, and so you really really don't want to be copying it into other clients. For a long time there's been occasional talk of refactoring pg_dump into a library and wrapper program such that the library could be used by other clients. But that would be a large amount of work in itself, especially if you hoped to design a library API that was rich enough that it'd be meaningfully more flexible than pg_dump itself is. Aside from the sheer work involved, there's the danger of introducing bugs into what is certainly a critical part of our infrastructure. So it's never gotten further than "wouldn't it be nice" discussions. regards, tom lane PS: *please* see if you can get them to take out the usage of pg_dump's -i switch. Having code invoke that blindly borders on criminal negligence.
On Aug 5, 2007, at 7:37 PM, Tom Lane wrote: > Perry Smith <pedz@easesoftware.com> writes: >> I find it odd that you are resistant to the idea. To me, the >> advantages are clear if it can be done without a tremendous amount of >> work. > > Well, it can't. pg_dump is an enormously complicated and frequently > changed bit of code, and so you really really don't want to be copying > it into other clients. <snip> Thanks for the reply. > PS: *please* see if you can get them to take out the usage of > pg_dump's > -i switch. Having code invoke that blindly borders on criminal > negligence. I'll open a bug report and submit a patch... Thats the most I can do. Perry Smith ( pedz@easesoftware.com ) Ease Software, Inc. ( http://www.easesoftware.com ) Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems
On Aug 5, 2007, at 7:37 PM, Tom Lane wrote:
PS: *please* see if you can get them to take out the usage of pg_dump's
-i switch. Having code invoke that blindly borders on criminal
negligence.
Thank you again.
Take care,
Perry Smith ( pedz@easesoftware.com )
Ease Software, Inc. ( http://www.easesoftware.com )
On Aug 5, 2007, at 18:58 , Perry Smith wrote: > I find it odd that you are resistant to the idea. To me, the > advantages are clear if it can be done without a tremendous amount > of work. Why horse around with the environment variables to set > such things as password, call sh, just to call pg_dump. My resistance was to the idea that creating a connection through ActiveRecord was preferable to a system call. I believe that pg_dump/psql are only required if you're using config.active_record.schema_format = :sql. (I may be mistaken, as I exclusively use schema_format = :sql so any other possible dependents are already satisfied.) When using config.active_record.schema_format = :sql, pg_dump and psql pretty much need to be in your path. And if psql is already in your path, psql can be used with the -c flag to run arbitrary commands (such as CREATE/DROP DATABASE and also CREATE ROLE), so createdb and dropdb are not really necessary for most purposes. Of course, you still need a database to connect to. Personally, I don't want to put postgres/template1 connection information in database.yml for security reasons: these commands are not run very often and a great way to keep information secure is to not have it available. Personally I wish Rails did not include database.yml by default as I think it's much too easy to load it into an SCM repository. I use a custom rake task to set up the Rails application to take care of this as well as other details, including database and role creation. (This is especially helpful for running tests.) If you're not using config.active_record.schema_format = :sql, then perhaps pg_dump and psql are not required, and they aren't necessarily in your path. In that case it might make sense to use ActiveRecord for CREATE/DROP DATABASE as you aren't necessarily going to have psql otherwise in your path. However, in this case in my opinion it's better for the above-mentioned security reasons not to. Michael Glaesemann grzm seespotcode net
On Aug 6, 2007, at 13:17 , Perry Smith wrote: > I'm using config.active_record.schema_format = :sql. I like the > idea of constraints in the db -- I know that goes against a lot of > rails ideas. I think most who choose Postgres come to the same conclusion. Though do take care not to confuse Rails as a whole with ActiveRecord in particular. > The :ruby choice does not dump and load constraints (at least not > in the released version -- I have not verified this on edge Rails > yet). I doubt it does. DHH's take on "application database" (as much- discussed elsewhere) wouldn't make such developments a priority, if they'd even be considered for ActiveRecord. > The pg_dump and psql load have one short coming. I really do not > like warning messages. If I have a language loaded in the > database, the psql load of the database produces two warnings > (because I'm not loading it as postgres -- just a regular user with > createdb privilege. > > I might be drifting off the original subject but, what I did to > solve this was to hook up the create_database and drop_database and > I have it understand the template parameter. So, now in > database.yml, I have a template database (like foo_template) and > foo_test is copied from foo_template -- that avoides the error > messages and creates a test database with whatever I need in it in > one step. I've considered using a similar technique for testing. There was discussion on rails-core a few weeks ago about various migration/ testing related issues, IIRC. Haven't gotten around to it yet as my rake tasks and the roles I use have pretty much taken care of the issue for me. > One thing I thought about over the past evening is that I could > just beef up the :ruby schema dump and load to understand the > couple of things I need for it to understand: constraints and > functions But, I'm not sure what kind of quagmire I'm walking in to. Definitely not worth the effort. The :ruby schema dump is there only to support the migration SQL DSL. In my opinion, if you're using SQL not supported by the DSL, there's little reason to use it at all. Most likely the SQL will not be entirely portable anyway (leaving aside the point of whether or not that should even be a design goal) so why take the time to learn another microlanguage? :) It doesn't take much to have requirements beyond what the migration DSL provides, as you've already discovered, and to extend the DSL in a portable way would be quite an endeavor. Quagmire is a good word for it. Michael Glaesemann grzm seespotcode net
If you haven't seen it already, there is a rails plugin that adds support for foreign-key dependencies (among other things) to the migration domain specific language (DSL): http://www.redhillonrails.org/#foreign_key_migrations Another useful plug-in is "Transactional Migrations" which automatically wraps ActiveRecord migrations inside a transaction, if your database supports transactional DDL, as Postgres does. This neatly avoids messy half-completed database migrations and the need to clean them up by hand. On 8/6/07, Michael Glaesemann <grzm@seespotcode.net> wrote: > On Aug 6, 2007, at 13:17 , Perry Smith wrote: > > > I'm using config.active_record.schema_format = :sql. I like the > > idea of constraints in the db -- I know that goes against a lot of > > rails ideas. > > I think most who choose Postgres come to the same conclusion. Though > do take care not to confuse Rails as a whole with ActiveRecord in > particular. > > > The :ruby choice does not dump and load constraints (at least not > > in the released version -- I have not verified this on edge Rails > > yet). > > I doubt it does. DHH's take on "application database" (as much- > discussed elsewhere) wouldn't make such developments a priority, if > they'd even be considered for ActiveRecord. > > > The pg_dump and psql load have one short coming. I really do not > > like warning messages. If I have a language loaded in the > > database, the psql load of the database produces two warnings > > (because I'm not loading it as postgres -- just a regular user with > > createdb privilege. > > > > I might be drifting off the original subject but, what I did to > > solve this was to hook up the create_database and drop_database and > > I have it understand the template parameter. So, now in > > database.yml, I have a template database (like foo_template) and > > foo_test is copied from foo_template -- that avoides the error > > messages and creates a test database with whatever I need in it in > > one step. > > I've considered using a similar technique for testing. There was > discussion on rails-core a few weeks ago about various migration/ > testing related issues, IIRC. Haven't gotten around to it yet as my > rake tasks and the roles I use have pretty much taken care of the > issue for me. > > > One thing I thought about over the past evening is that I could > > just beef up the :ruby schema dump and load to understand the > > couple of things I need for it to understand: constraints and > > functions But, I'm not sure what kind of quagmire I'm walking in to. > > Definitely not worth the effort. The :ruby schema dump is there only > to support the migration SQL DSL. In my opinion, if you're using SQL > not supported by the DSL, there's little reason to use it at all. > Most likely the SQL will not be entirely portable anyway (leaving > aside the point of whether or not that should even be a design goal) > so why take the time to learn another microlanguage? :) It doesn't > take much to have requirements beyond what the migration DSL > provides, as you've already discovered, and to extend the DSL in a > portable way would be quite an endeavor. Quagmire is a good word for it. > > Michael Glaesemann > grzm seespotcode net > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >