In my rails app (v 3.2.8), so far, I've used migrations without a problem.
I am using PostgreSQL 9.2 as the DB. I tweaked my application.rb to use sql instead of schema dumper (uncommented config.active_record.schema_format = :sql).
After that, I started to get this error on migration:
$ rake db:migrate
[ALL MIGRATION STUFF IS PRINTED HERE]
pg_dump: [archiver (db)] connection to database "my_dev_db" failed: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (fe80::1) and accepting
TCP/IP connections on port 5432?
rake aborted!
Error dumping database
I tried manually on the command line(logged in as the same user on my Mac)
<!-- language: lang-sh -->
$ pg_dump my_dev_db > /tmp/db.sql
No problems with that...happily dumps into `/tmp/db.sql`
Why is rails having trouble with `pg_dump`? (I am on Mac OSX Lion)
===========
Adding more diagnosis information
===========
$tail -10 /usr/local/var/postgres9.2/pg_hba.conf
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication rogert trust
#host replication rogert 127.0.0.1/32 trust
#host replication rogert ::1/128 trust
$ sudo lsof -p 62444 | awk '$5 == "unix" && $NF ~ /\// { print $NF }'
/tmp/.s.PGSQL.5432
$ ps auxw | grep post
postgres 1403 0.0 0.0 2435492 640 s007 S+ 21Oct12 0:00.05 bash
root 1401 0.0 0.0 2498096 128 s007 S 21Oct12 0:00.02 su postgres
rogert 62517 0.0 0.0 2426700 388 s001 R+ 9:21PM 0:00.00 grep post
rogert 62448 0.0 0.0 2481656 500 ?? Ss 8:46PM 0:00.03 postgres: wal writer process
rogert 62447 0.0 0.0 2481656 752 ?? Ss 8:46PM 0:00.07 postgres: writer process
rogert 62446 0.0 0.0 2481656 1040 ?? Ss 8:46PM 0:00.00 postgres: checkpointer process
rogert 62444 0.0 0.1 2481656 5368 s001 S 8:46PM 0:00.02 /usr/local/Cellar/postgresql/9.2.1/bin/postgres -D /usr/local/var/postgres9.2
$ rake db:migrate --trace
[ALL MIGRATION STUFF IS PRINTED HERE]
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/activerecord-3.2.8/lib/active_record/railties/databases.rake:393:in `block (3 levels) in <top (required)>'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/rake-0.9.2.2/lib/rake/task.rb:205:in `call'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/rake-0.9.2.2/lib/rake/task.rb:205:in `block in execute'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/rake-0.9.2.2/lib/rake/task.rb:200:in `each'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/rake-0.9.2.2/lib/rake/task.rb:200:in `execute'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/rake-0.9.2.2/lib/rake/task.rb:158:in `block in invoke_with_call_chain'
/Users/rogert/.rvm/rubies/ruby-1.9.3-p194/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/rake-0.9.2.2/lib/rake/task.rb:151:in `invoke_with_call_chain'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/rake-0.9.2.2/lib/rake/task.rb:144:in `invoke'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/activerecord-3.2.8/lib/active_record/railties/databases.rake:162:in `block (2 levels) in <top (required)>'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/rake-0.9.2.2/lib/rake/task.rb:205:in `call'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/rake-0.9.2.2/lib/rake/task.rb:205:in `block in execute'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/rake-0.9.2.2/lib/rake/task.rb:200:in `each'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/rake-0.9.2.2/lib/rake/task.rb:200:in `execute'
/Users/rogert/.rvm/gems/ruby-1.9.3-p194@rails_3.2.8/gems/rake-0.9.2.2/lib/rake/task.rb:158:in `block in invoke_with_call_chain'
/Users/rogert/.rvm/rubies/ruby-1.9.3-p194/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize'
$sudo vi /usr/local/var/postgres9.2/postgresql.conf
[search for listen address]
# - Connection Settings -
listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
Whats weird is that if I totally remove these two lines from my rails app, the migration works. So if its a problem with TCP connections and listenting, how is the migration itself working (but won't as soon as I turn these two back on again)
Most likely your PostgreSQL instance is configured not to listen on TCP/IP, at least on localhost.
In postgresql.conf check listen_addresses. See the documentation. Most likely it is set to '' (the empty string) so the server only listens on UNIX domain sockets.
psql, pg_dump etc will still work if Pg isn't listening on TCP/IP because they default to connecting to a local unix domain socket. The Ruby pg gem is a wrapper around libpq, the same client library used by psql etc, and it also defaults to using a unix domain socket unless connection parameters are specified explicitly.
However, Rails appears to be passing an explicit IP address to pg_dump - causing it to try to connect over TCP/IP, which it appears Pg isn't listening on, causing the observed "connection refused" error.
Alternately, it's possible your PostgreSQL is compiled to default to a port other than 5432. The same setting is compiled in as a default in libpq, so it'll connect to the new port automatically. However, if Rails specifies an explicit port to pg_dump when trying to run it, it'll use that in preference to the built-in default. Check the port directive in postgresql.conf; if it is not commented out or set to 5432 this is probably your problem. The port directive is documented in the same page linked to above.
BTW, to locate postgresql.conf run psql template1 -c "SHOW config_file;" while Pg is running.