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

一种在Windows 7上安装Postgres的方法，用作Rails 3(3.0.7)项目的PostgreSQL数据库.

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

序言(您可以跳过此位)

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

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设置与几乎不费力的sqlite设置的痛苦时的动机:如果要部署到Heroku，他们目前正在使用Postgres，因此与Postgres一起使用时，在sqlite3上可以使用的一些SQL调用会中断.在本地调试postgres比在Heroku的服务器上调试要容易得多.

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.

所以我做了以下事情: (免责声明:我可能已经忘记包括我所做的一些事情了……花了我48个小时的辛苦工作才能使它起作用……如果以下建议对您不起作用，那么巨大的(共2300页！)，但是非常详尽的Postgres文档应该会有所帮助.建议您下载此文件无论如何，如果您是认真使用Postgres的人，因为它有很多内容，我才刚刚开始理解它的重要性.) (第二个免责声明:我几乎可以肯定地打破了20条明智的Postgres准则，并且在这样做的同时暴露了Postgres数据库中的安全漏洞.如果有经验的Postgres用户有明显的不同意见，请编辑我的帖子.)

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 documentation should 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.)

.步骤1.从此处，因为此处仅表示9.0 Windows 7上将支持.x.我保留了所有默认选项，并且在Postgres安装程序提示输入一个密码时(只是不完全确定在Internet上共享该信息的后果是什么.)，仅使用秘密"作为密码.很快就会发现我确定).在步骤3中，您将需要此密码.

.Step 1. Download and install PostgreSQL v9.0.4-1 from here because here said 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.

.步骤2.更改环境变量，以使Path(

.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...')

别忘了更改对文件夹PostgreSQL \ 9.0的访问权限，并删除该文件夹或内容上的任何默认只读权限. (您可能还需要重新启动计算机才能使它们生效-感谢@Gavin-尽管不太可能.)

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).

.步骤3.通过尝试创建新数据库来测试Postgres安装: 从命令行:createdb -U postgres mydb_as_postgres. 应该提示您立即输入密码，否则，可能是您需要首先启动服务器(我不记得是否需要这样做).最简单的方法是通过pgAdmin III，该文件应为C:\Program Files\PostgreSQL\9.0\bin之类的文件夹中的"pgAdmin3.exe".启动pgAdmin III后，左侧应该有一个名为对象浏览器"的面板.在其中应该有一棵树:

.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:

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

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

右键单击"PostgreSQL 9.0(localhost:5432)"，然后选择连接".

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

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

The createdb -U postgres mydb_as_postgres command 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:

Databases (2)应该列出2个名为mydb_as_postgres和postgres

Databases (2) which should list 2 databases called mydb_as_postgres and postgres

之所以称为_as_postgres是因为命令的-U postgres部分告诉Postgres使用postgres用户作为其所有者来创建数据库，当您未以postgres用户身份登录时，需要指定该数据库.我将所有文件存储为"AJames"用户，因此，如果您相同，并且希望以其他用户身份登录时继续开发应用程序，则需要立即为该用户创建一个Postgres角色"(请参见步骤4).

I called it _as_postgres because the -U postgres part 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).

.步骤4.通过pgAdmin III.右键单击登录角色"(对我而言):

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

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

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

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

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.

.步骤5.现在，您无需登录为postgres用户就可以创建新数据库.尝试输入:

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

createdb mydb_as_user

希望这对您有用.

.步骤6.好的，在rails的"db/"目录中有一个development.sqlite3文件.最初，我打算将下一个测试设置为将其从sqlite3转换为psql.
虽然我无法使它正常工作，但由于我使用的解决方案要求将数据存储在Heroku.com上的Rails应用程序中，因此我在这里做了尝试(请参阅从第7步开始的解决方案).对于只有本地应用程序且Heroku中没有数据的用户，他们不能使用相同的方法，因此他们可能需要探索以下内容:

.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:

x6.1首先，通过尝试从命令行输入以下命令来测试"psql":

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

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尝试输入:

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

它应该显示:

CREATE TABLE
mydb5=#

如果您签入pgAdmin III，您应该在下面看到该表:

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

对象浏览器>服务器组>服务器> PostgreSQL 9.0(localhost:5432)>数据库> mydb_as_user>模式>公共>表> users_table>

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

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

x6.3 Okay, next to try the conversion. Downloaded sqlite-shell precompiled 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.

sqlite3 development .dump | psql development2

您可能会收到类似以下错误:

you might get an error like:

psql: FATAL: database "development2" does not exist

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

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

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

就像我说的那样，我无法正常工作.我确定有一种解决该错误的方法，但是我想到了另一种方法，所以我改用了该解决方案(该方法需要一个Heroku帐户来保存您的数据，并使用Taps gem将sqlite3转换为psql(我相信):

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):

.步骤7.在pgAdmin III中，我创建了另一个数据库.在属性选项卡下，我设置名称:开发"，所有者:"AJames"(将其替换为您自己的Windows用户名).并在特权"选项卡下，设置角色:"public"并选中全部"选项(此选项会重置为未选中状态，因此我不确定是否有必要).

.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).

.步骤8.添加 gem 'pg', '0.11.0' 到您的gem文件.您可能还想删除以下内容: gem 'sqlite3' 在这一点上.

.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.

.第9步.将database.yml设置为建议在此处至:

.Step 9. set database.yml as suggested here to:

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.

.步骤10.从Rails应用的根目录中的命令行运行: rake db:migrate 这将在Postgres数据库中创建新的架构和所有表.

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

.步骤11.从命令行(再次在rails应用的根目录中)运行heroku db:pull，以将所有数据下拉至新的空Postgres数据库.我认为此时您的水龙头宝石将为您完成这项工作.

.Step 11. run heroku db:pull from 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.

.步骤12.希望没有步骤12！ ...现在应该可以为您工作了. RoR PostgreSQL调试愉快！如果有任何错误，请编辑或让我知道.

.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.

此外，这是可能有趣/有用的其他内容的列表:

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

• 这是关于Postgres密码的博客帖子 ，他们的目的是什么， 为什么需要它们，如何进行更改等.
• 这在创建空间数据库"下对于新手来说非常有用 了解pg_hba.conf的内容和第二个链接 里诺在上面中给出了在使用pgAdmin III GUI"下对 在尝试拟合之前进行测试，以查看postgres是否确实在工作 使用Rails(即尝试创建数据库并放入表格，然后 一些数据).
• 在庞大而又非常全面的Postgres文档中， 从第58页开始，"I.教程'.然后在pdf页面431(！) 我也发现第17章，服务器设置和操作"很有用.

• 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 about and 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.