You could use SQLite in your development environment and switch it up for production, however, it makes far more sense to use the same database in development and production.
My personal preference is to use PostrgreSQL, its open source and gaining traction in the Rails world due to stability and rather nifty features. Heroku also uses PostgreSQL so its a good option if you plan on using their services.
See the excellent PostgreSQL Documentation for more information.
1. Install postgresql and admin tools through the package manager
sudo apt-get install postgresql libpq-dev phppgadmin pgadmin3
Change the postgresql configuration to solve this error
FATAL: Peer authentication failed for user
sudo nano /etc/postgresql/9.1/main/pg_hba.conf
Replace local peer with local md5 in the file. Then reload postgresql to apply the change.
sudo /etc/init.d/postgresql reload
2. Setup your databases from the command line
a) Login to postgresql prompt as the postgres user
sudo su postgres -c psql
b) Create a postgresql user for your project
create user username with password 'password';
TIP: If you are running tests, then to ease permission issues setup your postgres user with the same name and password as your Ubuntu user and make him a postgres superuser ( \du lists users with permissions).
alter user username superuser;
c) Create the development and test databases
create database projectname_development;
create database projectname_test;
create database projectname_test;
d) Give permissions to the user on the databases
grant all privileges on database projectname_development to username;
grant all privileges on database projectname_test to username;
grant all privileges on database projectname_test to username;
e) Finish your postgresql session
\q
Other useful commands in a postgresql session
Update password
alter user username with password ‘new password’;
Destroy database
drop database projectname;
Connect to specific database
\c databasename
Common MySQL commands with postgresql shortcut
| MySQL | Postresql |
|---|---|
| SHOW DATABASES | \l |
| SHOW TABLES | \d |
| SHOW COLUMNS | \d table |
3. Place a database configuration file in your rails project
Save the code below as app/config/database.yml and rake db:migrate your project
development:
adapter: postgresql
encoding: unicode
database: projectname_development
pool: 5
username: username
password: password
test:
adapter: postgresql
encoding: unicode
database: projectname_test
pool: 5
username: username
password: password
adapter: postgresql
encoding: unicode
database: projectname_development
pool: 5
username: username
password: password
test:
adapter: postgresql
encoding: unicode
database: projectname_test
pool: 5
username: username
password: password
4. Misc Errors encountered
If you are importing data (or using a seed file) then you may run across this error.
PG::Error: ERROR: duplicate key value violates unique constraint "table_name_pkey"
Your import didn’t use the sequence so we need to reset it to be in sync again.
Note when replacing table_name use a plural.
sudo su postgres -c psql
\c database_name
select setval('table_name_id_seq', (select max(id) + 1 from table_name));
\c database_name
select setval('table_name_id_seq', (select max(id) + 1 from table_name));
