Changing the defalult dbpath in mongoDB & Use existing database in newer version of mongo server

I was working in mongoDB 2.2,  Recently installed mongoDB 2.4 & it's default location is /var/lib/mongodb.

I was used /data/db as a default path in mongoDB 2.2, after I installed 2.4, I thought of using existing database which I created using 2.2.

Instead of backup & restore or import & export  I just change the dbpath in /etc/mongodb.conf.

here simple step to re-configuring the default dbpath and

1) stop the mongoDB server

root@boss[bin]#/etc/init.d/mongodb stop 
 Stopping database: mongodb.

 2) Open a file & change dbpath to desired location

root@boss[bin]#vi /etc/mongodb.conf
.....
.....
#dbpath=/var/lib/mongodb
dbpath=/data/db
.....
.....

3) restart the mongoDB

root@boss[bin]#/etc/init.d/mongodb strat 

root@boss[bin]#tail -f /var/log/mongodb/mongodb.log  
***** SERVER RESTARTED *****

Tue Jun 18 15:32:41.170 [initandlisten] MongoDB starting : pid=20270 port=27017 dbpath=/data/db 32-bit host=boss
Tue Jun 18 15:32:41.170 [initandlisten] 
Tue Jun 18 15:32:41.170 [initandlisten] ** NOTE: This is a 32 bit MongoDB binary.
Tue Jun 18 15:32:41.170 [initandlisten] **       32 bit builds are limited to less than 2GB of data (or less with --journal).
Tue Jun 18 15:32:41.170 [initandlisten] **       Note that journaling defaults to off for 32 bit and is currently off.
Tue Jun 18 15:32:41.170 [initandlisten] **       See http://dochub.mongodb.org/core/32bit
Tue Jun 18 15:32:41.170 [initandlisten] 
Tue Jun 18 15:32:41.170 [initandlisten] db version v2.4.4
Tue Jun 18 15:32:41.170 [initandlisten] git version: 4ec1fb96702c9d4c57b1e06dd34eb73a16e407d2
Tue Jun 18 15:32:41.170 [initandlisten] build info: Linux bs-linux32.10gen.cc 2.6.21.7-2.fc8xen #1 SMP Fri Feb 15 12:39:36 EST 2008 i686 BOOST_LIB_VERSION=1_49
Tue Jun 18 15:32:41.170 [initandlisten] allocator: system
Tue Jun 18 15:32:41.170 [initandlisten] options: { config: "/etc/mongodb.conf", dbpath: "/data/db", logappend: "true", logpath: "/var/log/mongodb/mongodb.log" }
Tue Jun 18 15:32:41.170 [initandlisten] exception in initAndListen: 10309 Unable to create/open lock file: /data/db/mongod.lock errno:13 Permission denied Is a mongod instance already running?, terminating
Tue Jun 18 15:32:41.170 dbexit: 
Tue Jun 18 15:32:41.170 [initandlisten] shutdown: going to close listening sockets...
Tue Jun 18 15:32:41.170 [initandlisten] shutdown: going to flush diaglog...
Tue Jun 18 15:32:41.170 [initandlisten] shutdown: going to close sockets...
Tue Jun 18 15:32:41.170 [initandlisten] shutdown: waiting for fs preallocator...
Tue Jun 18 15:32:41.170 [initandlisten] shutdown: closing all files...
Tue Jun 18 15:32:41.171 [initandlisten] closeAllFiles() finished
Tue Jun 18 15:32:41.171 [initandlisten] shutdown: removing fs lock...
Tue Jun 18 15:32:41.171 [initandlisten] couldn't remove fs lock errno:9 Bad file descriptor
Tue Jun 18 15:32:41.171 dbexit: really exiting now

its failed to start, have checked log file in (/var/log/mongodb/mongodb.log)got above error.

then I changed the owner & permission of /data/db as like mongoDB 2.4 default path /var/lib/mongodb/ 

4) get the owner & access details

root@boss[bin]#ls -l /var/lib/mongodb/
total 32808
-rw------- 1 mongodb nogroup 16777216 Jun 18 15:33 local.0
-rw------- 1 mongodb nogroup 16777216 Jun 18 15:33 local.ns
-rwxr-xr-x 1 mongodb mongodb        0 Jun 18 15:40 mongod.lock

 5)change the owner in new path (i.e /data/db )

root@boss[bin]#chown -R mongodb:nogroup /data/db
root@boss[bin]#chown mongodb:mongodb /data/db/mongod.lock

then I restart the mongodb by /etc/init.d/mongo start ( step 3 ). this time server started successfully.
starting  mongoDB

root@boss[bin]#/etc/init.d/mongodb strat 
 Starting database: mongodb. 

6) Running mongo

root@boss[bin]#mongo 
MongoDB shell version: 2.4.4
connecting to: test
> db 
test
>show dbs 
admin 0.0625GB
local 0.03125GB
rs 0.0625GB
snps1 0.0625GB
test 0.0625GB
week4 0.0625GB
week5 0.0625GB
week6 0.999755859375GB

by using mongo2.4 can able to access the all database which was created using mongo2.2.

without dump & restore / import & export the old database created using mongoDB 2.2 can be readily accessible by newer version of mongodb 2.4 by simply changing / configuring  the dbpath attribute in /etc/mongodb.conf

Install mongoDB on BOSS GNU/Linux

This post will help you out installing NoSQL mongoDB on BOSS GNU/Linux ( Debian-based Linux distribution ).  
BOSS version 4.0 & 5.0
latest version of mongoDB (2.4)
1) Import GPG key

root@boss[bin]#apt-key adv --keyserver keyserver.ubuntu.com --recv 7F0CEB10

 10gen package authenticated and it so require GPG key.

2) Create mongoDB 10gen package 

root@boss[bin]#echo 'deb http://downloads-distro.mongodb.org/repo/debian-sysvinit dist 10gen' | sudo tee /etc/apt/sources.list.d/10gen.list

above command will create the file which contain  package location latest version of mongoDB.

 3) Updates the package lists from a server
 

root@boss[bin]#sudo apt-get update

re-synchronize the package index files from their sources.
it may throw error, if the GPG key not imported properly

.......
.......
: GPG error: http://downloads-distro.mongodb.org dist Release: 
 The following signatures couldn't be verified because the public key is not available: 
 NO_PUBKEY 9ECBEC467F0CEB10

note : in our BOSS version 4.0 & 5.0 , mongoDB successfully installed and running even if the key not imported properly.

4) Installing mongoDB

root@boss[bin]#sudo apt-get install mongodb-10gen

 latest version of mongoDB automatically installed.

....
Reading package lists... Done
Building dependency tree       
Reading state information... Done
You might want to run `apt-get -f install' to correct these:
The following packages have unmet dependencies:
  libboost-dev: Depends: libboost1.49-dev but it is not installable
  mongodb: Depends: mongodb-dev but it is not going to be installed
           Depends: mongodb-server (>= 1:2.4.1-2) but it is not going to be installed
  mongodb-10gen: Conflicts: mongodb but 1:2.4.3-1 is to be installed
E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a solution).

 4.1) Install any dependencies that your previous installs missed.
in our above command, installation not succeed because of missing dependencies mongodb-dev & mongodb-server. issue the blow command to install those depends.  

root@boss[bin]#apt-get -f install 

.......
.......
Install these packages without verification [y/N]? y
Get:1 http://downloads-distro.mongodb.org dist/10gen mongodb-10gen 2.4.4 [86.8MB]
Fetched 86.8MB in 18s (4,718kB/s)                                                                      
Selecting previously deselected package mongodb-10gen.
(Reading database ... 154044 files and directories currently installed.)
Unpacking mongodb-10gen (from .../mongodb-10gen_2.4.4_i386.deb) ...
Processing triggers for man-db ...
Setting up mongodb-10gen (2.4.4) ...
insserv: warning: script 'K01postgresql-9.0' missing LSB tags and overrides
insserv: warning: script 'K01EnterpriseDBApachePhp' missing LSB tags and overrides
insserv: warning: script 'postgresql-9.0' missing LSB tags and overrides
insserv: warning: script 'EnterpriseDBApachePhp' missing LSB tags and overrides
Starting database: mongodb.

 by issuing above command mongoDB server installed & started successfully.

5) Know the location of config file

root@boss[bin]#ps -ef | grep mongo
mongodb  14603     1  0 10:53 ?        00:00:00 /usr/bin/mongod --config /etc/mongodb.conf
root     14655 18627  0 10:54 pts/3    00:00:00 grep mongo

also use 
5.1)Get the running instance of mongoDB

root@boss[bin]#ps -A | grep mongo 
 14603 ?        00:00:00 mongod

 5.2) Get the binary executable location of mongoDB

root@boss[bin]#whereis mongo 
 mongo: /usr/bin/mongo /usr/share/man/man1/mongo.1.gz

 by issuing above command we can come to know, the 

1. mongo server running on PID 14603
2. All binary file available under /usr/bin directory.
3. config file under /etc/mongodb.conf

6) Controlling mangoDB
starting  mongoDB

root@boss[bin]#/etc/init.d/mongodb strat 
 Starting database: mongodb.

get the status

root@boss[bin]#/etc/init.d/mongodb status 
 Checking status of database: mongodb running..

stopping mongoDB

root@boss[bin]#/etc/init.d/mongodb stop 
 Stopping database: mongodb.

7) Running mongo

root@boss[bin]#mongo 

MongoDB shell version: 2.4.4
connecting to: test
> db 
test
>

 by default its connecting test  database.
command "db" will return the current connected database, you can switch to any database by "use dbname"

note : here I logged in as root user, use sudo if you logged in an other then root user.
BOSS - Bharat Operating System Solution.

List the active connection/user in PostgreSQl,MySQL & mongoDB

As a DBA have to ensure number of active connection in database & which query consuming longer time to execute.

In PostgreSQL we can query from "pg_stat_activity" to know the status of the each connection.

when I was working in mongoDB(no-SQL) , wanted  to monitor the client  who currently accessing my server & the list active of operation.

here simple comparison,  how we to get  number of  active client (remote / local i.e different user ) connected to databases ( PostgreSQL,MySQL and mongoDB ) server.


PostgreSQL
----------------

select* from pg_stat_activity

postgres=# select datname, usename, application_name, client_addr,client_port,query_start,current_query from pg_stat_activity
 +------+---------+------------------------------+-------+---------+------+-------+------------------+
|datname  |usename   | application_name         | client_addr  | client_port | query_start 
+------+---------+------------------------------+-------+---------+------+-------+------------------+
template1 | postgres | pgAdmin III - Browser    | 127.0.0.1    |       56019 | 2013-06-14 19:52:52.775344+05:30
 SAAS     | postgres | psql                     | 192.168.1.10 |       58776 | 2013-06-14 19:58:33.084661+05:30
 koha     | postgres | pgAdmin III - Browser    | 192.168.0.2  |       56020 | 2013-06-14 19:52:52.729838+05:30
 pis      | postgres | pgAdmin III - Query Tool | 127.0.0.1    |       56022 | 2013-06-14 19:56:21.729744+05:30-

   - It display the list of active user/client ip address and query details.

Mysql
----------

show processlist; 

mysql> show processlist;
+------+---------+------------------------------+-------+---------+------+-------+------------------+
| Id   | User    | Host                         | db    | Command | Time | State | Info             |
+------+---------+------------------------------+-------+---------+------+-------+------------------+
| 3832 | root    | localhost                    | mysql | Query   |    0 | NULL  | show processlist |
| 3834 | erbnext | localhost                    | NULL  | Sleep   | 2524 |       | NULL             |
| 3837 | root    | solaimurugan.chennai.in:36125| koha  | Sleep   |    3 |       | NULL             |
+------+---------+------------------------------+-------+---------+------+-------+------------------+
3 rows in set (0.00 sec) 

it states that 3 active connections, 2 from localhost & 1 from client solaimurugan.chennai.in & it connects database mysql & koha.


mongoDB
------------

db.currentOp(true)

root@boss[bin]#./mongo  
MongoDB shell version: 2.2.3
-----------------------------------------------------------------------------------------------------------------------
db.currentOp(true).inprog.forEach(function(d){if(d.client && d.client!="0.0.0.0:0")printjson(d.client)})
----------------------------------------------------------------------------------------------------------------------
"solaimurugan.chennai.in:56231"
"127.0.0.1:49563" 
"192.168.31.101:50132" 
-------------------------------------------------------------------------------------------------------------------------------------

 mongoDB also has command to view total number of current connection & available connection to be established by client



db.serverStatus()

root@boss[bin]#./mongo  
MongoDB shell version: 2.2.3
-----------------------------------------------------------------------------------------------------------------------
db.serverStatus().connections
-----------------------------------------------------------------------------------------------------------------------------
{ "current" : 3, "available" : 816 }
------------------------------------------------------------------------------------------------------------------------------





Relation does not exist, but can access if i'm using within double quote

SQL is not case sensitive as long as you omit the quotes. The following names are all identical: 

           EMPLOYEE, EmPLoyee, emploYEE

The following tables are different: 

           "EMPLOYEE", "EmPLoyee", "emploYEE"

while using this 

\d EMPLOYEE (or)  \d employee

   it throw error : relation EMPLOYEE does not  exist.


\d "EMPLOYEE"

   it display the EMPLOYEE relation description 

if you get struck some where in PostgreSQL like this, do rename the table to either all upper/lower case.

1) \o /tmp/rename_tbl_lower

2) select 'ALTER TABLE '||'"'||tablename||'"'||' RENAME TO ' ||
lower(tablename)||';' from pg_tables where schemaname = 'public';

come out of psql then run

3) psql -U username database < /tmp/rename_tbl_lower


enter into psql 

\d employee 

 it display the EMPLOYEE relation description  


I've faced this issue while migrating data from Oracle to PostgreSQL  using Navigator tool. normally navigator tool ignore the constraint. it focus only the data migration part.  for this reason i just used ispirer-sqlways for convertion of  schema.

relation name created by  navigator and ispirer-sqlways are totally Ir-relevant, so I renamed all relation by upper/lower as said above.