I am trying to connect to a remote postgresql database and am having no luck when I run rake. For example: rake outputs:>pg_dump: [archiver (db)] connection to database "dbname" failed: could >not connect to server: No such file or directory > Is the server running locally and accepting > connections on Unix domain socket "/tmp/.s.PGSQL.5432"? >dropdb: could not connect to database template1: could not connect to >server: No such file or directory > Is the server running locally and accepting > connections on Unix domain socket "/tmp/.s.PGSQL.5432"? >createdb: could not connect to database template1: could not connect to >server: No such file or directory > Is the server running locally and accepting > connections on Unix domain socket "/tmp/.s.PGSQL.5432"? >psql: could not connect to server: No such file or directory > Is the server running locally and accepting > connections on Unix domain socket "/tmp/.s.PGSQL.5432"?my database.ymp looks like this:>development: > adapter: postgresql > database: dbname > host: 192.168.1.25 > port: 5432 > username: webuser > password: passwordWhy is it trying to use the sockets when I need it to connect remotely? It almost seems like it is ignoring the host: Any thoughts? -Robby -- /*************************************** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | robby-/Lcn8Y7Ot69QmPsQ1CNsNQ@public.gmane.org * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting & Development * --- Now hosting Ruby on Rails Apps --- ****************************************/
Robby Russell wrote:> I am trying to connect to a remote postgresql database and am having no > luck when I run rake. > > Why is it trying to use the sockets when I need it to connect remotely? > It almost seems like it is ignoring the host:Indeed! Take a look at the tasks in the Rakefile. If the database config specifies a host, the command-line invocations of psql, createdb, etc should include the -h option. This would be a quick patch; give it a shot :) For the truly obtuse among us, run pgpool on a local domain socket pointing to the remote PostgreSQL database. jeremy
* Robby Russell <robby-/Lcn8Y7Ot69QmPsQ1CNsNQ@public.gmane.org> [0211 22:11]:> I am trying to connect to a remote postgresql database and am having no > luck when I run rake. > > For example: > > rake outputs: > > >pg_dump: [archiver (db)] connection to database "dbname" failed: could > >not connect to server: No such file or directoryThe :clone_structure_to_test can''t work remotely, since it sets up pg_dump is set up to talk to a local socket (another consequence of this is that you need ''trust'' access to the local domain socket when running the unit tests, which makes Baby Jesus cry if you don''t want your Rails coders to be able to login as the postgres superuser). I tried fixing this last week, but it''s complicated by the fact that pg_dump doesn''t take a password. One fix (I havent'' tried yet) is to use a ~/.pgpass file to code the database settings. It''s not ideal, but then you can: a) secure the db properly b) run the tests against a remote db It''s also worth mentioning that the rails db user needs ''create database'' privilege (for the dropdb/createdb operation in the :purge_test_database task to work. Personally I''d like to see us get away from pg_dump and find another way to pull the schema from development and drop everything from the test db (rather than dropdb/createdb). I''m beginning to think this should be done in the adapter..> > Is the server running locally and accepting > > connections on Unix domain socket "/tmp/.s.PGSQL.5432"? > >dropdb: could not connect to database template1: could not connect to> >development: > > adapter: postgresql > > database: dbname > > host: 192.168.1.25 > > port: 5432 > > username: webuser > > password: password > > Why is it trying to use the sockets when I need it to connect remotely? > It almost seems like it is ignoring the host:-- ''Ugh, it''s like there''s a party in my mouth and everyone''s throwing up.'' -- Fry Rasputin :: Jack of All Trades - Master of Nuns
On Mar 1, 2005, at 4:22 AM, Dick Davies wrote:> * Robby Russell <robby-/Lcn8Y7Ot69QmPsQ1CNsNQ@public.gmane.org> [0211 22:11]: >> I am trying to connect to a remote postgresql database and am having >> no >> luck when I run rake. >> >> For example: >> >> rake outputs: >> >>> pg_dump: [archiver (db)] connection to database "dbname" failed: >>> could >>> not connect to server: No such file or directory > > The :clone_structure_to_test can''t work remotely, since it sets up > pg_dump is set up > to talk to a local socket (another consequence of this is that you > need ''trust'' > access to the local domain socket when running the unit tests, which > makes Baby > Jesus cry if you don''t want your Rails coders to be able to login as > the postgres > superuser). >I also have a problem with this and I am looking into changing the way the test database is cloned so that it doesn''t use dropdb and createdb. I am also looking into adding the capability of having the test setup do its cloning as a database user that owns the database and has full rights to it and then logging off before running the tests as the user I have the web site log in as which has much fewer permissions.> I tried fixing this last week, but it''s complicated by the fact that > pg_dump doesn''t take a password. > > One fix (I havent'' tried yet) is to use a ~/.pgpass file to code the > database settings. > It''s not ideal, but then you can: > > a) secure the db properly > b) run the tests against a remote db >There is an undocumented environment variable you can set. If you set PQpass to your password, it will not prompt for one.> It''s also worth mentioning that the rails db user needs ''create > database'' > privilege (for the dropdb/createdb operation in the > :purge_test_database task to work. > > Personally I''d like to see us get away from pg_dump and find another > way to > pull the schema from development and drop everything from the test db > (rather than dropdb/createdb). > I''m beginning to think this should be done in the adapter.. > >I am looking into this sort of thing. However, due to my other projects, it may be a while before I get something that works. Do you know if anyone else is working on this so I don''t duplicate someone else''s effort?>>> Is the server running locally and accepting >>> connections on Unix domain socket "/tmp/.s.PGSQL.5432"? >>> dropdb: could not connect to database template1: could not connect to > >>> development: >>> adapter: postgresql >>> database: dbname >>> host: 192.168.1.25 >>> port: 5432 >>> username: webuser >>> password: password >> >> Why is it trying to use the sockets when I need it to connect >> remotely? >> It almost seems like it is ignoring the host: >Kim
Dick Davies wrote:> Personally I''d like to see us get away from pg_dump and find another way to > pull the schema from development and drop everything from the test db > (rather than dropdb/createdb). > I''m beginning to think this should be done in the adapter..Rails assumes a "live db" approach: work with the development db using some kind of tool then version-control the schema dump. The development database is the authoritative source. Instead, I write out the schema SQL in a text file and generate a master db from it. Then createdb can use the master as a template to create the development and test databases. Here''s a sample config/database.yml and Rakefile: - database.yml --- # Don''t need database, etc. Specify username and rely on .pgpass superuser: username: foobar # Contains the "master" schema and fixtures data. Used as a template # to create development and test database. We don''t need a username # and other details since we won''t ever connect from Rails. fixtures: database: fixtures development: adapter: postgresql database: development username: foodev password: abc test: adapter: postgresql database: test username: footest password: def - Rakefile --- SCHEMA_SQL = "#{RAILS_ROOT}/db/schema.pgsql" FIXTURES_SQL = "#{RAILS_ROOT}/db/fixtures.pgsql" desc ''Regenerate everything the master schema.'' task :regen => [:regenerate_fixtures, :fresh_dev_db, :yaml_fixtures] desc ''Regenerate fixtures database from the master schema.'' task :regenerate_fixtures => :environment do conf = ActiveRecord::Base.configurations superuser = conf[''superuser''][''username''] fixtures = conf[''fixtures''][''database''] `dropdb -U #{superuser} #{fixtures}` `createdb -U #{superuser} #{fixtures}` or abort ''createdb failed'' `psql -U #{superuser} -f #{SCHEMA_SQL} #{fixtures}` or abort ''schema load failed'' `psql -U #{superuser} -f #{FIXTURES_SQL} #{fixtures}` or abort ''fixtures load failed'' end task(:fresh_test_db => :environment) { clone_fixtures_to :test } task(:fresh_dev_db => :environment) { clone_fixtures_to :development } def clone_fixtures_to(target_label) conf = ActiveRecord::Base.configurations superuser = conf[''superuser''][''username''] target, fixtures = conf[target_label.to_s][''database''], conf[''fixtures''][''database''] `dropdb -U #{superuser} #{target}` `createdb -U #{superuser} -T #{fixtures} #{target}` end desc ''Dump the fixtures database to yaml fixtures.'' task :yaml_fixtures => :environment do path = "#{RAILS_ROOT}/test/fixtures" sql = ''SELECT * FROM %s'' ActiveRecord::Base.establish_connection(:fixtures) ActiveRecord::Base.connection.table_names.each do |table_name| i = ''000'' File.open("#{path}/#{table_name}.yml", ''wb'') do |file| file.write ActiveRecord::Base.connection.select_all(sql % table_name).inject({}) { |hash, record| hash["#{table_name}_#{i.succ!}"] = record hash }.to_yaml end end end Then make your test tasks depend on the :fresh_test_db task instead of :clone_structure_to_test. All fixtures will be loaded, so you don''t have to declare them in your test. Instead, isolate your changes to the test database by starting a transaction in setup and rolling back in teardown. This makes managing foreign key constraints simple and is much faster than deleting and inserting all fixtures for every test. Ramble off, jeremy
Jeremy Kemper wrote:> - database.yml --- > # Contains the "master" schema and fixtures data. Used as a template > # to create development and test database. We don''t need a username > # and other details since we won''t ever connect from Rails. > fixtures: > database: fixturesOops. The provided :yaml_fixtures task dumps YAML fixtures by connecting to the fixtures database, so you need to provide full connection info here or modify the task to hit development instead. jeremy
* Kim <kim-7lDDVWa6PKfQT0dZR+AlfA@public.gmane.org> [0312 16:12]:> > On Mar 1, 2005, at 4:22 AM, Dick Davies wrote: > > >* Robby Russell <robby-/Lcn8Y7Ot69QmPsQ1CNsNQ@public.gmane.org> [0211 22:11]:> >> rake outputs: > >> > >>>pg_dump: [archiver (db)] connection to database "dbname" failed:> >The :clone_structure_to_test can''t work remotely> I also have a problem with this and I am looking into changing the way > the test database is cloned so that it doesn''t use dropdb and createdb.In essence a drop/createdb is not much different to deleting all tables/ indexes/sequences and rebuilding them, and we are only talking about the test db in any case. The only benefit is a warm fuzzy feeling, and I''m not sure if the extra complexity (in the Rakefile or the adapter) is worth it.> I am also looking into adding the > capability of having the test setup do its cloning as a database user > that owns the > database and has full rights to it and then logging off before running > the tests as > the user I have the web site log in as which has much fewer permissions.Again it would be nice, but it does seem to increase the effort in getting a test environment up and running.> >a) secure the db properly > >b) run the tests against a remote db> There is an undocumented environment variable you can set. If you set > PQpass to your password, it will not prompt for one.Nice, thanks. Actually it seems PGPASSWORD is what you''re describing, but setting that up before system(''pg_dump ...'') should DTRT. I''ll have a go tweaking the Rakefile.> >Personally I''d like to see us get away from pg_dump and find another > >way to > >pull the schema from development and drop everything from the test db > >(rather than dropdb/createdb). > >I''m beginning to think this should be done in the adapter..> I am looking into this sort of thing. However, due to my other > projects, it may > be a while before I get something that works. Do you know if anyone > else is working on this so I don''t duplicate someone else''s effort?Not that I know of. I notice the mysql adapter can do this, but from a quick glance at the code its using ''SHOW xxx'' type commands. psql supports the same features with its ''\'' commands, but if you''ve ever run ''psql -E'' you''ll know what sort of nosebleed code that generates... -- ''Everybody''s a jerk. You, me, this jerk.'' -- Bender Rasputin :: Jack of All Trades - Master of Nuns