Wednesday 21 August 2013

Using database on heroku

Heroku allows applications to be hosted which need database. Either mysql or postgresql can be used, it is easier to host an application which uses postgres hence we shall be using the same.

To use postgresql you may want to check this .

After logging in to your heroku account in the terminal (check this) add an addon for the current app by
using $ heroku addons:add heroku-postgresql:dev

Then login to your account and check the app, your database can be observed  here.


You can then use the host,database,user and password information in your application to access the tables in the database.

use $ su postgresql_username

to login to postgresql .

Then to access the remote database on heroku , use
$ psql -h host -U user database

(note: here host,user and database implies those indicated in the image or the details from here.)


Now you are logged in. you can now enter create table etc sql commands to be executed on the remote server.
The table can then be accessed in the application.But note that the database in psql and not mysql.


if your app is using mysql, then convert mysql to psql replace the following set of instructions:

mysql_connect to pg_connect
mysql_query to pg_query
mysql_fetch_row to pg_fetch_row

and so on.

Installing phppgadmin and configuring it with postgresql in linux

phppgadmin can be used for developing application which uses psql for database.

Lets begin :

1.install postgresql  by  $ sudo apt-get install postgresql

2. install phppgadmin by $ sudo apt-get install phppgadmin

3. Create a user for postgresql by $ sudo -u postgres createuser -d -r -P username
then enter password for the user created.

4.make apache acknowledge postgresql as a database server by editing the conf file of apache
$sudo gedit '/etc/apache2/apache2.conf'  
and add this in a new line  at the end                   Include /etc/phppgadmin/apache.conf  

5.Restart apache2 server by  $ sudo /etc/init.d/apache2 restart

6. If localhost/phppgadmin throws up 404  then configure postgresql by
editing the conf file  $ sudo gedit /etc/postgresql/9.1/main/postgresql.conf

and add this at the end of file in a new line           listen_addresses = 'localhost'

7. now edit  pg_hba.conf by $ sudo vi /etc/postgresql/9.1/main/pg_hba.conf

Replace         local all all ident sameuser            with              local   all         all                               md5

That would be it.

you can also access postgres by  $ sudo -u postgres psql username