Category: database

PostgreSQL 9.3 : Installation on ubuntu 14.04

Hi guys, I just started installing postgres on my ubuntu VM. I referred some docs, and followed this one: https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04

Its pretty much explained in this page. But just explaining here the important things.

You can install postgres by ubuntu’s own apt packaging system. Update local apt repository.

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

Postgres uses role based access for the unix users. After the installation a default role called ‘postgres’ will be created. You can login to postgres account and start using or creating new roles with Postgres.

Sign in as postgres user

$ sudo -i -u postgres

Access the postgres console by

$ psql

But i cannot enter into the console and I got the following error:

postgres@8930a29k5d05:/home/rails/my_project$ psql
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

What could be the reason for this error?

So just gone through Postgres doc (http://www.postgresql.org/docs/9.3/static/server-start.html). You can see the same error under the section 17.3.2. Client Connection Problems. But the solution is not mentioned.

Original Reason: PostgreSQL Server was not running after the installation.

I tried rebooting the system and via init script the server should run automatically. But the server is not running again. I understood that something prevents postgres from running the server. What is it?

Just check your postgres server is running or not

$ sudo -aux | grep post
postgres@8930a29k5d05:/home/rails/my_project$ ps -aux | grep postgres
root       136  0.0  0.2  47124  3056 ?        S    06:10   0:00 sudo -u postgres -s
postgres   137  0.0  0.3  18164  3220 ?        S    06:10   0:00 /bin/bash
postgres   140  0.0  0.2  15572  2192 ?        R+   06:10   0:00 ps -aux
postgres   141  0.0  0.0   4892   336 ?        R+   06:10   0:00 grep post

The server is not running.

Run the server manually by

root@8930a29k5d05:/home/rails/my_project#  /etc/init.d/postgresql start
 * Starting PostgreSQL 9.3 database server
                                                                                                                                                         [ OK ] 
root@8930a29k5d05:/home/rails/my_project# ps aux | grep postgres
postgres   158  0.1  2.0 244928 20752 ?        S    06:28   0:00 /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf
postgres   160  0.0  0.3 244928  3272 ?        Ss   06:28   0:00 postgres: checkpointer process

postgres   161  0.0  0.4 244928  4176 ?        Ss   06:28   0:00 postgres: writer process

postgres   162  0.0  0.3 244928  3272 ?        Ss   06:28   0:00 postgres: wal writer process

postgres   163  0.0  0.5 245652  6000 ?        Ss   06:28   0:00 postgres: autovacuum launcher process

postgres   164  0.0  0.3 100604  3336 ?        Ss   06:28   0:00 postgres: stats collector process

root       178  0.0  0.0   8868   884 ?        S+   06:28   0:00 grep --color=auto post
root@8930a29k5d05:/home/rails/my_project#

Now the server starts running. If still not works, then try to reconfigure your locales as mentioned here

$ dpkg-reconfigure locales

It is strange that, after installing such a popular database software, it doesn’t provide any information regarding the failure of its own server. It should give the developers some clue so that they can save their precious time.

The reason of this failure, what I concluded is
1. After installation we have to run the server manually
OR
2. I tried resetting the locales (So if no locales set in the machine may prevented the postgres from starting automatically?)

How to create a migration file dynamically by meta programming in rails 4.0

If you want to create a migration file from a module written in lib file or somewhere from your ruby file and execute it, use the metaprogramming which can create a class or method dynamically. The following code snippet shows the methods we use and gives a better idea to create migration file dynamically.

def create_columns(tb_with_cols)
    add_columns = ""
    tb_name = tb_with_cols.keys.first
    columns = tb_with_cols.values.first
    columns.each { |c_name, c_type| add_columns << "\tadd_column(':#{tb_name}', :#{c_name}, :#{c_type})\n" }

    add_columns
 end

 def migration_file_content(tb_with_cols)
   cols = create_columns(tb_with_cols)
<<-RUBY
  class AddMissingColumnsToTable < ActiveRecord::Migration
     def change_table
    #{cols}
   end
 end
  RUBY
 end


 def write_content_to_file(path, content)
    File.open(path, 'w+') do |f|
      f.write(content)
    end
 end

Just call the method migration_file_content in your code. Pass the parameter tb_with_cols as a Hash, in which the key is the table_name and value is the columns that should be added to that table. Ex:

tb_with_cols = {:users => {:name => :string, :age => :integer, :address => :text} }
content = migration_file_content(tb_with_cols)
write_content_to_file("#{Rails.root}/db/migrations/', content)

After that call the method write_content_to_file with your new migration file path and the content from our migration_file_content method. 🙂

How to reinstall mongodb in ubuntu linux

Before reinstalling mongodb, in your linux system check whats installed in the system

 $ sudo dpkg -l | grep mongo
 ii  mongodb-org                              2.6.3                                       amd64        MongoDB open source document-oriented database system (metapackage)
 ii  mongodb-org-mongos                       2.6.3                                       amd64        MongoDB sharded cluster query router
 ii  mongodb-org-server                       2.6.3                                       amd64        MongoDB database server
 ii  mongodb-org-shell                        2.6.3                                       amd64        MongoDB shell client
 ii  mongodb-org-tools                        2.6.3                                       amd64        MongoDB tools

Remove all
$ sudo apt-get remove mongodb*

Install mongodb again, check mongodb org

$ sudo apt-get install -y mongodb-org

For particular version

$ sudo apt-get install -y mongodb-org=2.6.5 mongodb-org-server=2.6.5 mongodb-org-shell=2.6.5 mongodb-org-mongos=2.6.5 mongodb-org-tools=2.6.5


check whats newly installed

$ sudo dpkg -l | grep mongo
 ii  mongodb-org                              2.6.8                                       amd64        MongoDB open source document-oriented database system (metapackage)
 ii  mongodb-org-mongos                       2.6.8                                       amd64        MongoDB sharded cluster query router
 ii  mongodb-org-server                       2.6.8                                       amd64        MongoDB database server
 ii  mongodb-org-shell                        2.6.8                                       amd64        MongoDB shell client
 ii  mongodb-org-tools                        2.6.8                                       amd64        MongoDB tools

check mongodb server status

$ sudo service mongod status

Start mongodb server

$ sudo service mongod start

Start mongo shell

$ mongo

check mongodb log file here

 $ tail -n 200 /var/log/mongodb/mongod.log

ThinkingSphinx::SphinxError (undefined method `next_result’ for Mysql2 (rails 3)

I got this error after my thinking sphinx updation to the newest version of ‘3.0.5’. The error shows in the exact line of the code Model.search in my Rails controller. I updated my mysql2 version to ‘0.3.13’, and the issue is solved.
So in you Gemfile update the mysql2 version:

gem 'mysql2', '0.3.13'

And do

$ bundle install

rake assets:precompile – database configuration does not specify adapter

Even if we are specified the database configuration for adapter in our database.yml, sometimes we are getting the error “database configuration does not specify adapter”. Usually you are doing the command for development 

$ rake assets:precompile -t

And in your database.yml, configuration for development is specified. But if you are doing 

$ rake assets:precompile -t RAILS_ENV=development

then you can see the error goes.
Another way is check your database.yml for production database configuration is there or not. If you adds the production database configuration then also it works! Because while doing the precompile for assets without specifying the environment then it checks all environments db configuration.

Another way is add the following in your application.rb file

config.assets.initialize_on_precompile = false

then also it works.

Install PostgresSQL in Ubuntu 12.04

Install the postgres software by

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

Set up your password

Goto the postgres prompt

$ sudo -u postgres psql

Inside that you can set the password for the DB user postgres

ALTER USER postgres PASSWORD 'YourPassword';

References:
xtremekforever.blogspot
postgresql-password-authentication-failed-for-user-postgres