在 Windows 上安装 Postgres 以与 Ruby-on-Rails 一起使用

Question

提问by AJP

Currently I get the following error:

目前我收到以下错误：

PGError (FATAL: password authentication failed for user "postgres" ):

PGError（致命：用户“postgres”的密码验证失败）：

when my app tries to access the database.

当我的应用程序尝试访问数据库时。

I wanted to test my SQL calls against postgres as my app regular breaks when pushed up to production on Heroku because of the stricter requirements of postgres (which I think is probably a healthy thing) over sqlite 3. So I have a similar request to thisfor a not out of date tutorial for installing postgres for use with ruby on rails on windows (7) please. I thought it'd be a quick 20 minutes of downloading and installing but 2 and a bit hours later and I don't think I'm very close yet. So far I've:

我想针对 postgres 测试我的 SQL 调用，因为我的应用程序在 Heroku 上推送到生产时会定期中断，因为 postgres 的要求比 sqlite 3 更严格（我认为这可能是一件健康的事情）。所以我对此有类似的要求请获取有关安装 postgres 以在 windows (7) 上的导轨上使用 ruby 的未过时教程。我认为下载和安装需要 20 分钟，但 2 小时后，我认为我还没有很接近。到目前为止，我已经：

Downloaded and installed Postgres Version 8.4.8-1 from here

从这里下载并安装 Postgres 版本 8.4.8-1

Set my environment variables such that Path (for User) is: C:\Ruby192\bin;C:\Program Files (x86)\PostgreSQL\8.4\bin

设置我的环境变量，使路径（对于用户）是： C:\Ruby192\bin;C:\Program Files (x86)\PostgreSQL\8.4\bin

Gemfile:

宝石档案：

gem 'pg', '0.11.0'  # instead of gem 'sqlite3', '1.3.3'

gem 'pg', '0.11.0'  # instead of gem 'sqlite3', '1.3.3'

ran bundle installfor my rails app seemingly successfully, but haven't found easy way to validate installation yet.

bundle install似乎成功运行了我的 rails 应用程序，但还没有找到验证安装的简单方法。

set database.yml as suggested hereto:

按照此处的建议将 database.yml 设置为：

development:
  adapter: postgresql
  database: db/development
  username: postgres
  password: secret
  host: localhost
  encoding: UTF8
  pool: 5
  timeout: 5000

I know I need to set up a user name and password for Postgres, maybe also start the postgres server, connect to it(?) and put in my local IP address I'll connect to it on into a config files somewhereand then edit one of the other .conf files in 'C:\Program Files (x86)\PostgreSQL\8.4\data' etc...

我知道我需要为 Postgres 设置一个用户名和密码，也许还需要启动 postgres 服务器，连接到它（？）并输入我的本地 IP 地址，我将连接到它的某个配置文件中，然后编辑'C:\Program Files (x86)\PostgreSQL\8.4\data' 等中的其他 .conf 文件之一...

I think Rails has made me soft, am I over thinking things or is it actually fairly tricky to set up and I should just go back to Sqlite3, for which there's also the awesomely useful SQLite Manager, Firefox plugin?

我认为 Rails 使我变得软弱，是我想多了，还是设置起来相当棘手，我应该回到 Sqlite3，其中还有非常有用的 SQLite 管理器、Firefox 插件？

I'm still searching for a beginners guide to installing and using Postgres for rails but so far have only been confused by most of the stuff I look at / tried following like this, this, this, this, this(for Snow Leopard), this(linux).

我仍在寻找安装和使用 Postgres for rails 的初学者指南，但到目前为止，我只对我看到/尝试遵循的大多数内容感到困惑，这，这个，这个，这个，这个（对于雪豹），这（Linux）。

Any pointers would be much appreciated. Thanks!

任何指针将不胜感激。谢谢！

James

詹姆士

Answer 1

回答by AJP

An approach to installing Postgres on windows 7 for use as PostgreSQL database for a rails 3 (3.0.7) project.

在 Windows 7 上安装 Postgres 以用作 rails 3 (3.0.7) 项目的 PostgreSQL 数据库的方法。

Preamble(you can skip this bit)

序言（你可以跳过这一点）

So the first thing to point out is that Postgres is not just a different file extension from .sqlite3, it's a whole mechanism for managing your databases. As such it has a client/server model, of which you'll need to set up both to use Postgres as the database for your rails app.

所以首先要指出的是，Postgres 不仅仅是一个与 .sqlite3 不同的文件扩展名，它是一个管理数据库的完整机制。因此，它有一个客户端/服务器模型，您需要将两者都设置为使用 Postgres 作为 Rails 应用程序的数据库。

Motivation for going through considerable pain of Postgres setup versus almost effortless sqlite setup: if you're deploying to Heroku, they're currently using Postgres so some of your SQL calls that are fine on sqlite3 will break when used with Postgres. It's much easier to debug postgres locally rather than when it's on Heroku's servers.

与几乎毫不费力的 sqlite 设置相比，经历 Postgres 设置的巨大痛苦的动机：如果您要部署到 Heroku，他们目前正在使用 Postgres，因此当与 Postgres 一起使用时，一些在 sqlite3 上正常的 SQL 调用将中断。在本地调试 postgres 比在 Heroku 的服务器上调试要容易得多。

So I did the following things: (Disclaimer: I may have forgotten to include some of the things I did... it took me over 48 hours of on and off pain to get it to work... if the following advice doesn't work for you then the huge (2300 pages!!) but very thorough Postgres documentationshould help. I'd recommend downloading this anyway if you're serious about using Postgres as it has a lot of material that I've only just begun to understand the significance of.) (Second disclaimer: I have almost certainly broken 20 sensible Postgres guidelines and exposed security holes in the Postgres database whilst doing so. If there any obvious things an experienced Postgres user disagrees with, please edit my post.)

所以我做了以下事情：（免责声明：我可能忘记包括我所做的一些事情......我花了超过 48 小时的断断续续的痛苦才让它工作......如果以下建议没有的话'不适合你，那么巨大的（2300 页！！）但非常详尽的 Postgres 文档应该会有所帮助。如果你真的想使用 Postgres，我建议你下载这个，因为它有很多我刚刚开始的材料以了解其重要性。）（第二个免责声明：我几乎可以肯定地违反了 20 条合理的 Postgres 准则，并在这样做时暴露了 Postgres 数据库中的安全漏洞。如果有经验的 Postgres 用户不同意任何明显的事情，请编辑我的帖子。）

.Step 1.Download and install PostgreSQL v9.0.4-1 from herebecause heresaid only 9.0.x would be supported on windows 7. I kept all the default options and just used 'secret' as the password when prompted by the Postgres installer for one (again not entirely sure what the consequences of sharing that info on the internet is... will soon find out I'm sure). You'll need this password in step 3.

.Step 1.从这里下载并安装 PostgreSQL v9.0.4-1因为这里说 Windows 7 只支持9.0.x。我保留了所有默认选项，并在 Postgres 安装程序提示时使用了“secret”作为密码一方面（再次不完全确定在互联网上共享该信息的后果是什么......我相信很快就会发现）。您将在第 3 步中需要此密码。

.Step 2.Change environment variables such that Path (for system, not user(I'm not sure if this is significant or not)) is: C:\Program Files\PostgreSQL\9.0\bin
(n.b. I'm on 64-bit windows hence it not being installed for 32-bit in 'C:\Program Files (x86)\PostgreS...')

.Step 2.更改环境变量，使得 Path（对于系统，而不是用户（我不确定这是否重要））是：C:\Program Files\PostgreSQL\9.0\bin
（nb 我在 64 位 Windows 上，因此它没有被安装为 32- 'C:\Program Files (x86)\PostgreS...' 中的位

Don't forget to change access rights to folder PostgreSQL\9.0 and remove any default readonly rights on the folder or content. (You may also need to restart your computer for these to take effect - thanks @Gavin -although not likely).

不要忘记更改对文件夹 PostgreSQL\9.0 的访问权限并删除对文件夹或内容的任何默认只读权限。（您可能还需要重新启动计算机才能使这些生效-谢谢@Gavin-尽管不太可能）。

.Step 3.Test Postgres installation by trying to create a new database: From command line: createdb -U postgres mydb_as_postgres. You should be prompted to enter the password now, if you're not it may be that you need to start the server first (I can't remember whether I needed to do this or not). The easiest way is through pgAdmin III, which should be 'pgAdmin3.exe' in a folder somewhere like C:\Program Files\PostgreSQL\9.0\bin. Once you've started pgAdmin III there should be a panel on the left called 'Object Browser'. In this there should be a tree with:

.Step 3.通过尝试创建新数据库来测试 Postgres 安装：从命令行：createdb -U postgres mydb_as_postgres。现在应该提示您输入密码，如果不是，则可能是您需要先启动服务器（我不记得是否需要这样做）。最简单的方法是通过 pgAdmin III，它应该是像C:\Program Files\PostgreSQL\9.0\bin. 启动 pgAdmin III 后，左侧应该有一个名为“对象浏览器”的面板。在这应该有一棵树：

Server Groups > Servers > PostgreSQL 9.0 (localhost:5432)

服务器组 > 服务器 > PostgreSQL 9.0 (localhost:5432)

Right click on 'PostgreSQL 9.0 (localhost:5432)' and select 'Connect'.

右键单击“PostgreSQL 9.0 (localhost:5432)”并选择“连接”。

The createdb -U postgres mydb_as_postgrescommand should create a new databse called 'mydb_as_postgres' which you can check by firing up pgAdmin III and double clicking on 'PostgreSQL 9.0 (localhost:5432)'. Under this there should be:

该createdb -U postgres mydb_as_postgres命令应该创建一个名为“mydb_as_postgres”的新数据库，您可以通过启动 pgAdmin III 并双击“PostgreSQL 9.0 (localhost:5432)”来检查它。在此之下应该有：

Databases (2)which should list 2 databases called mydb_as_postgresand postgres

Databases (2)其中应该列出 2 个名为mydb_as_postgres和的数据库postgres

I called it _as_postgresbecause the -U postgrespart of the command tells Postgres to create the database with the postgres user as it's owner, which you need to specify when you're not signed in as the postgres user. I have all of my files stored as 'AJames' user though so if you're the same and want to keep developing your app when signed in as a different user you need to create a Postgres 'role' for that user now (see step 4).

我调用它_as_postgres是因为-U postgres命令的一部分告诉 Postgres 以 postgres 用户作为它的所有者创建数据库，当你没有以 postgres 用户身份登录时，你需要指定它。我将我的所有文件都存储为“AJames”用户，因此如果您是相同的并且希望在以其他用户身份登录时继续开发您的应用程序，您现在需要为该用户创建一个 Postgres“角色”（请参阅步骤4）。

.Step 4.Through pgAdmin III. Right-click on Login Roles (which for me is in):

.Step 4.通过 pgAdmin III。右键单击登录角色（对我来说是）：

Object Browser > Server Groups > Servers > PostgreSQL 9.0 (localhost:5432) > Login Roles

对象浏览器 > 服务器组 > 服务器 > PostgreSQL 9.0 (localhost:5432) > 登录角色

Right-click on Login Roles and select 'New Login Role...' in Role name, put in your operating system user name, which for me is AJames, and fill in your password under the 'Role Privileges' tab, I checked all the boxes, but an experienced postgres user would likely strongly recommend to only check the 'inherits rights from parent roles' and the 'can create database objects' But I'm not an experienced user and just want to debug Rails SQL calls in Postgres so I also checked the 'Superuser' and 'Can create roles', just in case.

右键单击登录角色并在角色名称中选择“新建登录角色...”，输入您的操作系统用户名，对我来说是 AJames，并在“角色权限”选项卡下填写您的密码，我检查了所有框，但有经验的 postgres 用户可能会强烈建议只检查“从父角色继承权限”和“可以创建数据库对象”但我不是有经验的用户，只想在 Postgres 中调试 Rails SQL 调用，所以我还检查了“超级用户”和“可以创建角色”，以防万一。

.Step 5.You should now be able to create a new database without being signed in as the postgres user. Try typing:

.第 5 步。您现在应该能够在不以 postgres 用户身份登录的情况下创建新数据库。尝试输入：

createdb mydb_as_user

Hopefully this should work for you.

希望这对你有用。

.Step 6.Okay, so you've got a development.sqlite3 file in your rails 'db/' directory. Initially I was going to set the next test as converting this from sqlite3 to psql.
I couldn't get this to work though but I left my attempts here as the solution I used required having the data in a Rails app on Heroku.com (see instead the solution from step 7 onwards). For those who only have a local app and no data in Heroku, they can't use the same approach, so they might need to explore something like this:

.Step 6.好的，所以你在你的 rails 'db/' 目录中有一个 development.sqlite3 文件。最初我打算将下一个测试设置为将其从 sqlite3 转换为 psql。
我无法让它工作，但我把我的尝试留在这里，因为我使用的解决方案需要在 Heroku.com 上的 Rails 应用程序中包含数据（请参阅第 7 步之后的解决方案）。对于那些只有本地应用程序而 Heroku 中没有数据的人，他们无法使用相同的方法，因此他们可能需要探索如下内容：

x6.1 First, test 'psql' by trying a command from your command line like:

x6.1 首先，通过从命令行尝试以下命令来测试“psql”：

psql mydb_as_user

this should display something like below (after you've typed in your password):

这应该显示如下内容（在您输入密码后）：

C:>psql mydb_as_user
Password:
psql (9.0.4)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

mydb5=#

x6.2 try entering:

x6.2 尝试输入：

CREATE TABLE users_table (id integer, "name" text);

It should display:

它应该显示：

CREATE TABLE
mydb5=#

If you check in pgAdmin III, you should see the table there under:

如果您签入 pgAdmin III，您应该会看到下面的表格：

Object Browser > Server Groups > Servers > PostgreSQL 9.0 (localhost:5432) > Databases > mydb_as_user > Schemas > public > Tables > users_table >

对象浏览器 > 服务器组 > 服务器 > PostgreSQL 9.0 (localhost:5432) > 数据库 > mydb_as_user > Schemas > public > Tables > users_table >

x6.3 Okay, next to try the conversion. Downloaded sqlite-shellprecompiled binary for windows.
x6.4 Create a new directory, I used 'C:\temp' and put the sqlite3.exe and your development.sqlite3 files in it.
x6.5 Use the following commands (which are from here) to dump the development.sqlite3 database into Postgres.

x6.3 好的，接下来试试转换。为 Windows下载了 sqlite-shell预编译二进制文件。
x6.4 创建一个新目录，我使用“C:\temp”并将 sqlite3.exe 和您的 development.sqlite3 文件放入其中。
x6.5 使用以下命令（来自此处）将 development.sqlite3 数据库转储到 Postgres。

sqlite3 development .dump | psql development2

you might get an error like:

您可能会收到如下错误：

psql: FATAL: database "development2" does not exist

x6.6 so I went into pgAdmin III and made a development 2 database, tried the command again and got:

x6.6 所以我进入 pgAdmin III 并制作了一个 development 2 数据库，再次尝试该命令并得到：

ERROR:  syntax error at or near "PRAGMA"
LINE 1: PRAGMA foreign_keys=OFF;
        ^
BEGIN
COMMIT

Like I said, I couldn't get it to work. I'm sure there's a way of getting round that error but I thought of a different way and so I instead used this solution (which requires a Heroku account to have your data and does the conversion from sqlite3 to psql using the Taps gem (I believe):

就像我说的，我无法让它发挥作用。我确定有一种方法可以解决该错误，但我想到了一种不同的方法，所以我改用了这个解决方案（它需要一个 Heroku 帐户才能拥有您的数据，并使用 Taps gem 将 sqlite3 转换为 psql（我相信）：

.Step 7.in pgAdmin III I created another database. Under the properties tab I set name: 'development', owner: 'AJames' (replace this with your own Windows user name). And under the privileges tab, set role: 'public' and checked the ALL option (thought this resets to unchecked so I'm not sure that's necessary).

.Step 7.在 pgAdmin III 中我创建了另一个数据库。在属性选项卡下，我设置了名称：'development'，所有者：'AJames'（用您自己的 Windows 用户名替换它）。在权限选项卡下，设置 role: 'public' 并选中 ALL 选项（认为这会重置为未选中，所以我不确定是否有必要）。

.Step 8.add gem 'pg', '0.11.0'to your gem file. You'll probably also want to remove the: gem 'sqlite3'at this point too.

.Step 8.添加 gem 'pg', '0.11.0'到您的 gem 文件中。gem 'sqlite3'在这一点上，您可能还想删除：。

.Step 9.set database.yml as suggested hereto:

.Step 9.按照此处的建议将 database.yml 设置为：

development:
  adapter: postgresql
  database: db/development
  username: AJames # replace this with your own user name
  password: secret # replace this with your own password
  host: localhost
  encoding: UTF8
  pool: 5
  timeout: 5000

If you are working on an open source project and don't want your password to be made publicly available, have a look at some of the answers to Securely providing the database password in a Rails app.

如果您正在处理一个开源项目并且不希望您的密码公开可用，请查看在Rails 应用程序中安全地提供数据库密码的一些答案。

.Step 10.from command line in your rails app's root directory run: rake db:migrateThis will create the new schema and all the tables in the Postgres database.

.Step 10.在你的 Rails 应用根目录的命令行中运行： rake db:migrate这将创建新的模式和 Postgres 数据库中的所有表。

.Step 11.run heroku db:pullfrom your command line (again from in the root directory of your rails app) to pull all your data down and into your new empty Postgres database. I think at this point your taps gem will be doing this work for you.

。步骤11.运行heroku db:pull从您的命令行（从您的Rails应用程序的根目录下重新）拉所有的数据下来，到新的空Postgres数据库。我认为此时您的 taps gem 将为您完成这项工作。

.Step 12.Hopefully there is no step 12! ...and it should now be working for you. Happy RoR PostgreSQL debugging! Please edit, or let me know, if there are any errors in this.

.第12步。希望没有第12步！...它现在应该对你有用。RoR PostgreSQL 调试愉快！如果有任何错误，请编辑或告诉我。

Also, here's a list of additional stuff that might be interesting/useful:

此外，这里列出了可能有趣/有用的其他内容：

This is a blog post about Postgres passwords, what they're for, why you need them, how to change them etc.
This, under 'Creating a spatial database', is useful for newbies to understand what pg_hba.conf is aboutand the second link that Reno gave above, under the 'Using pgAdmin III GUI' is useful to testing to see if postgres is actually working, before trying to fit it with Rails (i.e. try creating a database and putting a table and some data into it).
In the huge but very comprehensive Postgres documentation, I'd start on page 58, 'I. tutorial'. Then on pdf page 431(!) there's 'Chapter 17. Server setup and operation' that I also found useful.

这是一篇关于 Postgres 密码、它们的用途、为什么需要它们、如何更改它们等的博客文章。
这一点，在“创建空间数据库”，是非常有用的新手要了解什么是pg_hba.conf里约和第二链接，里诺以上了，在“使用的pgAdmin III GUI”是有用的测试，看看是否Postgres的实际工作, 在尝试将其与 Rails 匹配之前（即尝试创建一个数据库并将一个表和一些数据放入其中）。
在庞大但非常全面的 Postgres 文档中，我会从第 58 页开始，'I. 教程'。然后在 pdf page 431(!) 上有“第 17 章服务器设置和操作”，我也发现它很有用。

Answer 2

回答by Vincent Thieu

the answer from AJP is the correct one with just a small re-config. The line

AJP 的答案是正确的，只需重新配置即可。线

    database: db/development

does not work for me. I have to change it to

对我不起作用。我必须把它改成

    database: development

I use this for ruby on rails for the command

我将它用于 ruby on rails 的命令

    rake db:create

and

和

    rake db:migrate

to work

上班

Answer 3

回答by Reno

None of your links appeared to be the (arguably) most useful documentation - the officialpostgresql docs. I recently configure a Linux Mint box with postgresql and django using a combination of those documents and these, though the latter are specific to Linux.

您的链接似乎都不是（可以说）最有用的文档 -官方postgresql 文档。我最近使用这些文档和这些文档的组合配置了一个带有 postgresql 和 django 的 Linux Mint 框，尽管后者特定于 Linux。

I'd worry more about validating the postgresql side of things, less about rails. That is to say, your question should be "How can I set up and test a postgresql server on a Windows 7 box", moreso than you need to know how to get it locked into rails.

我更担心验证 postgresql 方面，而不是 Rails。也就是说，您的问题应该是“如何在 Windows 7 机器上设置和测试 postgresql 服务器”，而不是您需要知道如何将其锁定到导轨中。

Edit: Maybe thiscould also be of use to you - official postgresql wiki with detailed installation guides.

编辑：也许这对你也有用 - 官方 postgresql wiki 有详细的安装指南。

Good luck!

祝你好运！

在 Windows 上安装 Postgres 以与 Ruby-on-Rails 一起使用

提问by AJP

回答by AJP

回答by Vincent Thieu

回答by Reno

相关推荐

最近更新

标签

在 Windows 上安装 Postgres 以与 Ruby-on-Rails 一起使用

提问by AJP

回答by AJP

回答by Vincent Thieu

回答by Reno

相关推荐

Ruby-on-rails 模型和助手中的常用方法

Ruby-on-rails 将虚拟属性添加到 json 输出

Ruby-on-rails rails where() sql 查询数组

Ruby-on-rails 如何在rails中创建对象数组？

相关推荐

最近更新

标签