StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is the most foolproof way to get your rails app working with postgres in the <strong><em>development environment</em></strong> in Ubuntu 13.10. </p> <p>1) Create rails app with postgres YAML and 'pg' gem in the Gemfile:</p> <pre><code>$ rails new my_application -d postgresql </code></pre> <p>2) Give it some CRUD functionality. If you're just seeing if postgres works, create a scaffold:</p> <pre><code>$ rails g scaffold cats name:string age:integer colour:string </code></pre> <p>3) As of <code>rails 4.0.1</code> the <code>-d postgresql</code> option generates a YAML that doesn't include a host parameter. <strong>I found I needed this.</strong> Edit the development section and create the following parameters: </p> <pre><code>encoding: UTF-8 host: localhost database: my_application_development username: thisismynewusername password: thisismynewpassword </code></pre> <p>Note the <code>database</code> parameter is for a database that doesn't exit yet, and the <code>username</code> and <code>password</code> are credentials for a role that doesn't exist either. We'll create those later on!</p> <p>This is how <code>config/database.yml</code> should look (no shame in copypasting :D ):</p> <pre><code>development: adapter: postgresql pool: 5 # these are our new parameters encoding: UTF-8 database: my_application_development host: localhost username: thisismynewusername password: thisismynewpassword test: # this won't work adapter: postgresql encoding: unicode database: my_application_test pool: 5 username: my_application password: production: # this won't work adapter: postgresql encoding: unicode database: my_application_production pool: 5 username: my_application password: </code></pre> <p>4) Start the postgres shell with this command:</p> <pre><code>$ psql </code></pre> <p>4a) You may get this error if your current user (as in your computer user) doesn't have a corresponding administration postgres role.</p> <pre><code>psql: FATAL: role "your_username" does not exist </code></pre> <p>Now I've only installed postgres once, so I may be wrong here, but I think postgres automatically creates an administration role with the same credentials as the user you installed postgres as. </p> <p>4b) So this means you need to change to the user that installed postgres to use the psql command and start the shell:</p> <pre><code>$ sudo su postgres </code></pre> <p>And then run</p> <pre><code>$ psql </code></pre> <p>5) You'll know you're in the postgres shell because your terminal will look like this:</p> <pre><code>$ psql psql (9.1.10) Type "help" for help. postgres=# </code></pre> <p>6) Using the postgresql syntax, let's create the user we specified in <code>config/database.yml</code>'s <strong>development</strong> section:</p> <pre><code>postgres=# CREATE ROLE thisismynewusername WITH LOGIN PASSWORD 'thisismynewpassword'; </code></pre> <p>Now, there's some subtleties here so let's go over them. </p> <ul> <li>The role's username, <em>thisismynewusername</em>, <strong>does not</strong> have quotes of any kind around it</li> <li>Specify the keyword <em>LOGIN</em> after the <em>WITH</em>. If you don't, the role will still be created, but it won't be able to log in to the database!</li> <li>The role's password, <em>thisismynewpassword</em>, needs to be in single quotes. <strong>Not double quotes</strong>.</li> <li>Add a semi colon on the end ;)</li> </ul> <p>You should see this in your terminal:</p> <pre><code>postgres=# CREATE ROLE postgres=# </code></pre> <p>That means, "ROLE CREATED", but postgres' alerts seem to adopt the same imperative conventions of git hub.</p> <p>7) Now, still in the postgres shell, we need to create the database with the name we set in the YAML. Make the user we created in step 6 its owner:</p> <pre><code>postgres=# CREATE DATABASE my_application_development OWNER thisismynewusername; </code></pre> <p>You'll know if you were successful because you'll get the output: </p> <pre><code>CREATE DATABASE </code></pre> <p>8) Quit the postgres shell:</p> <pre><code>\q </code></pre> <p>9) Now the moment of truth:</p> <pre><code>$ RAILS_ENV=development rake db:migrate </code></pre> <p>If you get this:</p> <pre><code>== CreateCats: migrating ================================================= -- create_table(:cats) -> 0.0028s == CreateCats: migrated (0.0028s) ======================================== </code></pre> <p><strong><em>Congratulations, postgres is working perfectly with your app.</em></strong> </p> <p>9a) On my local machine, I kept getting a permission error. I can't remember it exactly, but it was an error along the lines of</p> <pre><code>Can't access the files. Change permissions to 666. </code></pre> <p>Though I'd advise thinking very carefully about recursively setting write privaledges on a production machine, locally, I gave my whole app read write privileges like this: </p> <p>9b) Climb up one directory level:</p> <pre><code>$ cd .. </code></pre> <p>9c) Set the permissions of the my_application directory and all its contents to 666:</p> <pre><code>$ chmod -R 0666 my_application </code></pre> <p>9d) And run the migration again:</p> <pre><code>$ RAILS_ENV=development rake db:migrate == CreateCats: migrating ================================================= -- create_table(:cats) -> 0.0028s == CreateCats: migrated (0.0028s) ======================================== </code></pre> <p><strong>Some tips and tricks if you muck up</strong></p> <p>Try these before restarting all of these steps:</p> <p><strong>The mynewusername user doesn't have privileges to CRUD to the <code>my_app_development</code> database? Drop the database and create it again with mynewusername as the owner:</strong></p> <p>1) Start the postgres shell:</p> <pre><code>$ psql </code></pre> <p>2) Drop the <code>my_app_development</code> database. Be careful! Drop means utterly delete!</p> <pre><code>postgres=# DROP DATABASE my_app_development; </code></pre> <p>3) Recreate another <code>my_app_development</code> and make mynewusername the owner:</p> <pre><code>postgres=# CREATE DATABASE my_application_development OWNER mynewusername; </code></pre> <p>4) Quit the shell:</p> <pre><code>postgres=# \q </code></pre> <p><strong>The <code>mynewusername</code> user can't log into the database? Think you wrote the wrong password in the YAML and can't quite remember the password you entered using the postgres shell? Simply alter the role with the YAML password:</strong></p> <p>1) Open up your YAML, and copy the password to your clipboard:</p> <pre><code> development: adapter: postgresql pool: 5 # these are our new parameters encoding: UTF-8 database: my_application_development host: localhost username: thisismynewusername password: musthavebeenverydrunkwheniwrotethis </code></pre> <p>2) Start the postgres shell:</p> <pre><code>$ psql </code></pre> <p>3) Update <code>mynewusername</code>'s password. Paste in the password, and remember to put single quotes around it:</p> <pre><code>postgres=# ALTER ROLE mynewusername PASSWORD `musthavebeenverydrunkwheniwrotethis`; </code></pre> <p>4) Quit the shell:</p> <pre><code>postgres=# \q </code></pre> <p><strong>Trying to connect to localhost via a database viewer such as Dbeaver, and don't know what your postgres user's password is? Change it like this:</strong></p> <p>1) Run <code>passwd</code> as a superuser:</p> <pre><code>$ sudo passwd postgres </code></pre> <p>2) Enter your accounts password for <code>sudo</code> (nothing to do with postgres):</p> <pre><code>[sudo] password for starkers: myaccountpassword </code></pre> <p>3) Create the postgres account's new passwod:</p> <pre><code>Enter new UNIX password: databasesarefun Retype new UNIX password: databasesarefun passwd: password updated successfully </code></pre> <p><strong>Getting this error message?:</strong></p> <pre><code>Run `$ bin/rake db:create db:migrate` to create your database $ rake db:create db:migrate PG::InsufficientPrivilege: ERROR: permission denied to create database </code></pre> <p>4) You need to give your user the ability to create databases. From the psql shell:</p> <pre><code>ALTER ROLE thisismynewusername WITH CREATEDB </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload