Remote Postgres is driving me crazy!

Rails
1

Hi guys,

I need your help. Here is my database.yml:

development:
adapter: postgresql
database: db
username: username
password: mypassword

Connect on a TCP socket. Omitted by default since the client uses a

domain socket that doesn’t need configuration. Windows does not

have

domain sockets, so uncomment these lines.

host: 192.168.22.1 (this is a remote ip in local loop)
port: 5432

Yes, I commented out everything else. Whenever I make a script/generate
scaffold service_spiel, this is what I get:

exists app/controllers/
exists app/helpers/
exists app/views/service_spiels
exists test/functional/
dependency model
exists app/models/
exists test/unit/
exists test/fixtures/
identical app/models/service_spiel.rb
identical test/unit/service_spiel_test.rb
identical test/fixtures/service_spiels.yml
error Before updating scaffolding from new DB schema, try
creating a table for your model (ServiceSpiel)

My table in 192.168.22.1 is here:

\d service_spiels
View “service_spiels”
Column | Type | Modifiers
---------±-----------------------±----------
id | integer |
service | character varying(20) |
spiel | character varying(550) |
View definition: SELECT service_spiel.id, service_spiel.service,
service_spiel.spiel FROM service_spiel;

I tried out Kevin Jorgensen’s suggestion of using migrate and this is
what I get:

rake migrate
(in /home/nsadmin/brewedpages)
== Servicespiels: migrating

– create_table(:service_spiels)
NOTICE: CREATE TABLE will create implicit sequence
‘service_spiels_id_seq’ for SERIAL column ‘service_spiels.id’
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
‘service_spiels_pkey’ for table ‘service_spiels’
-> 0.1989s
== Service_spiels: migrated (0.1991s)

rake aborted!
PGError: ERROR: Option ‘search_path’ is not recognized
: SHOW search_path

Yes it pops with this error but the table was SUCCESSFULLY created! The
search_path in my database.yml is commented out.

Also, I tried one of the suggestions here at the forum of hardcoding my
connection in my models:

class ServiceSpiels < ActiveRecord::Base
unless connected?
establish_connection(
:adapter => “postgresql”,
:database => “db”,
:host => “192.168.22.1”,
:port => “5432”,
:username => “username”,
:password => “mypassword”
)
end
end

but it pops with this fault: Recognition failed “/service_spiels/”

This is driving me crazy! Please help!

bing

2

note that I can successfully connect to my db using .rb with the
follwing line:

dbh = DBI.connect(“dbi:Pg:db:192.168.22.1:5432”, “username”,
“mypassword”)

I can successfully connect to my db but I can’t get rails to get up!

3

On Mon, 2006-07-03 at 16:54 +0200, Bing T. wrote:

note that I can successfully connect to my db using .rb with the
follwing line:

dbh = DBI.connect(“dbi:Pg:db:192.168.22.1:5432”, “username”,
“mypassword”)

I can successfully connect to my db but I can’t get rails to get up!

did you install postgres gem?

gem install postgres

Craig

4

Craig W. wrote:

On Mon, 2006-07-03 at 16:54 +0200, Bing T. wrote:

note that I can successfully connect to my db using .rb with the
follwing line:

dbh = DBI.connect(“dbi:Pg:db:192.168.22.1:5432”, “username”,
“mypassword”)

I can successfully connect to my db but I can’t get rails to get up!

did you install postgres gem?

gem install postgres

Craig

hi craig,

yes, it is installed.

5

On Mon, 2006-07-03 at 17:51 +0200, Bing T. wrote:

did you install postgres gem?

gem install postgres

Craig

hi craig,

yes, it is installed.

then make sure that your database.yml:

  • uses spaces NOT tab characters
  • has the correct host spec
  • has the correct port/socket spec
  • has the correct schema_search_path if it is not ‘public’

Craig

6

then make sure that your database.yml:

  • uses spaces NOT tab characters
This I'm sure. I read a few posts about it. I recreated the yml file

making sure there are no tabs.

  • has the correct host spec
  • has the correct port/socket spec
Are we talking about the remote server ip and postgres port? I think

I have the host right bec. I’ve been able to connect to the db using
ruby. port/socket means the postgres standard port of 5432?

  • has the correct schema_search_path if it is not ‘public’

Do I still need to define my schema_search_path even if I don’t
actually intend to use rake migrate? Will I need the schema_search_path
for a script/generate scaffold command?

Craig

thanks,

Bing

7

Craig W. wrote:

On Mon, 2006-07-03 at 18:59 +0200, Bing T. wrote:

I have the host right bec. I’ve been able to connect to the db using
ruby. port/socket means the postgres standard port of 5432?

whether the host is local or remote, the hostspec has to be correct. If
a remote host, port would normally be 5432

  • has the correct schema_search_path if it is not ‘public’

Do I still need to define my schema_search_path even if I don’t
actually intend to use rake migrate? Will I need the schema_search_path
for a script/generate scaffold command?

it won’t find the db if the db is located in a schema other than public
or named the same as the user, and yes, this would impact things like
script/generate and rake db:migrate

Craig

Hey Craig,

appreciate all the help! I think I’m in big trouble with this. I am
still using a Postgres 7.2.2 which may not exactly have public
schema…this is pretty darn long tunnel I’m finding myself in, light is
getting farther and farther!

thanks,

Bing

8

On Mon, 2006-07-03 at 18:59 +0200, Bing T. wrote:

I have the host right bec. I’ve been able to connect to the db using
ruby. port/socket means the postgres standard port of 5432?

whether the host is local or remote, the hostspec has to be correct. If
a remote host, port would normally be 5432

  • has the correct schema_search_path if it is not ‘public’

Do I still need to define my schema_search_path even if I don’t
actually intend to use rake migrate? Will I need the schema_search_path
for a script/generate scaffold command?

it won’t find the db if the db is located in a schema other than public
or named the same as the user, and yes, this would impact things like
script/generate and rake db:migrate

Craig

9

On Mon, 2006-07-03 at 19:31 +0200, Bing T. wrote:

Hey Craig,

appreciate all the help! I think I’m in big trouble with this. I am
still using a Postgres 7.2.2 which may not exactly have public
schema…this is pretty darn long tunnel I’m finding myself in, light is
getting farther and farther!

if you don’t have schemas, then you probably don’t want a schema_path
entry at all, remove the line if that is the case - I don’t go back that
far myself.

I am looking at the postgres gem that I have installed 0.7.1 and it
states that it supports older versions of postgres than yours so that
should be ok

Craig

10

Hi craig,

my database.yml has the schema_path commented out. But according to the
comments on it, when commented, it defaults schema_path to $user,public.

I’m still not 100% sure but it seems like my pg 7.2.2 does not support
Schemas yet.

Bing

11

On Tue, 2006-07-04 at 06:32 +0200, Bing T. wrote:

Hi craig,

my database.yml has the schema_path commented out. But according to the
comments on it, when commented, it defaults schema_path to $user,public.

I’m still not 100% sure but it seems like my pg 7.2.2 does not support
Schemas yet.

try putting in just
schema_path:

(and 2 spaces)(or without spaces)

Craig

12

On Jul 3, 2006, at 9:32 PM, Bing T. wrote:

my database.yml has the schema_path commented out. But according to
the
comments on it, when commented, it defaults schema_path to
$user,public.

I’m still not 100% sure but it seems like my pg 7.2.2 does not support
Schemas yet.

Schemas are supported in PostgreSQL 7.3 (released Nov 2002) and later.

jeremy

13

On Jul 4, 2006, at 13:32 , Bing T. wrote:

I’m still not 100% sure but it seems like my pg 7.2.2 does not support
Schemas yet.

Please upgrade your PostgreSQL installation. The 7.2 series (released
in 2002) is no longer supported by the PostgreSQL community. There
have been 4 major releases since 7.2 (7.3, 7.4, 8.0, and 8.1). The
current release is 8.1.4 (released 2006-05-23; 8.1 was first released
2005-11-08) and includes additional features as well as much
improved performance and fixes to security issues and data-loss-
incurring bugs. (If you absolutely must stay with 7.2.x, at least
upgrade to 7.2.8, released 2005-05-09, the final version of 7.2.)

Michael G.
grzm seespotcode net

14

Hi All,

I just wanted to tell everyone that it was the longest way to prove a
point - that Rails is incompatible with Postgres 7.2.2 below.

Postgres 7.2.2 does not support schemas and therefore it will not work
with Rails…even if Ruby will. It will work with versions starting 7.3
(where schema support is given).

I have successfully found another server and hosted my postgres db
server remotely so with all settings configured properly, rails will
work like a charm with it.

Thanks guys for all the guidance!

Bing