Postgres DB Setup for Maphub

Maphub with local PostgreSQL (9.2.1) Howto

(adapted from http://blog.willj.net/2011/05/31/setting-up-postgresql-for-ruby-on-rails-development-on-os-x/)

Install Postgres (via Homebrew)

$ brew install postgres

If this is your first install, create a database with:

$ initdb -E 'UTF-8' /usr/local/var/postgres 

Start manually with:

$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Stop with:

$ pg_ctl -D /usr/local/var/postgres stop -s -m fast

Now that the Postgres server is running we need to create a database for use in our rails app. This is really simple using the shell commands that ship with Postgres. First lets create a new user. Running the createuser command you will get an interactive prompt asking some questions about the user, answering ‘n’ is OK for all of them:

$ createuser maphub
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Next create the two databases you will need, development and test. Here you can see the options are given on the command line, the -O specifies the owner of the database (the user we just created) and -U specified the character encoding scheme to be used in the database.

$ createdb -Omaphub -Eutf8 maphub_development
$ createdb -Omaphub -Eutf8 maphub_test

Run the following command, you should find yourself at a database prompt:

$ psql -U maphub maphub_development

Drop the database by executing the following postgres command

$ dropdb maphub_development
$ dropdb maphub_test

Using Ubuntu

(adapted from http://xtremekforever.blogspot.com/2011/05/setup-rails-project-with-postgresql-on.html)

Install postgres with:

$ sudo apt-get install postgresql postgresql-contrib libpq-dev

Go into /etc/postgresql/8.4/main/postgresql.conf: and uncomment the following line of code:

listen_addresses = 'localhost'

Then go into /etc/postgresql/8.4/main/pg_hba.conf: and change the following code at the end (changing method to trust)

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Create the database:

$ createdb -Omaphub -Eutf8 maphub_development
$ createdb -Omaphub -Eutf8 maphub_test

To start the server:

$ sudo /etc/init.d/postgresql start

To restart the server:

$ sudo /etc/init.d/postgresql restart

Note

Make sure to reload the new database schema (you may have to manually edit the schema.rb files if some of the fields that should be text are still strings)