Create Postgres DB from command line in Ubuntu 14.04

On ubuntu shell,

sudo su postgres
createdb mydb -O dbownername

If it says “dbownername” role does not exist, you can create it with

createuser dbownername

 

If above commands does not work, you may be missing following

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

 

for more information  createdb manual  createuser manual

 

Copy MongoDB database from remote host to your machine

You can use the mongo console to copy the database from one host (machine) to another host (machine). It is usually helpful when we are copying large database.

Start the mongo console

  1. by typing command “mongo”
  2. run following commands to copy database
    use admin
    db.runCommand({
       copydb: 1,
       fromdb: "test",
       todb: "records",
       fromhost: "example.net"
    })

    For more information Mongo Documentation

List all databases in PostgreSQL psql

Quick bite
—————–

\list

or

\l

to list all databases

\connect {databasename}

to connect to database

\dt

to list tables in current database

Munch
————
psql is interactive frontend for PstgreSQL using which you can connect you postgres database and execute commands and SQL. The commands listed above fall under category meta commands.
More information can be found in psql documentation http://www.postgresql.org/docs/9.3/static/app-psql.html

How to do UPSERT query in PostgreSQL

WITH upsert AS (<update-query-here> RETURNING *) <insert-query-here>  WHERE NOT EXISTS (SELECT * FROM upsert);

<insert-query-here> Should be of form
“INSERT INTO table_name (column1, column2, …) SELECT ‘column_1_value’, ‘column_2_value’, …”
Hence if update query succeeds, insert query wont execute due to WHERE clause.

Thanks to reference http://www.the-art-of-web.com/sql/upsert/

Track db table schema changes using phpmyadmin

This is only for PHPMyAdmin users.

If you are using the mysql database , phpmyadmin offers lot of features to ease our database management tasks.
One of the task we usually come across is to keep log of schema changes in database table, like new fields addition, existing fields change, etc.
The traditional way how we do that is, we keep a list of sqls in a text file and pass this text file if anybody needs the schema change sqls. Well this is nice way, but
it requires us to make sure this file
1. Contains all the schema change sqls
2. Located to known place and can be easily retrieved. Remember where this file is, and is it correct? (this is for people with bad memory, like me)

Well I think the smarter way would be to use “tracking” feature of “Phpmyadmin”.
1. Click on “tracking” link from main menu of phpmyadmin.
2. It will show us list of untracked tables, click on “track table” link to start tracking the schema changes.

The drawback of this “tracking” is , you will need to start tracking from beginning i.e. when you created the table. This also means that the time you start tracking your tables schema changes will get recorded schema from that time. So, early bird wins here.

Since release 3.3.x a tracking mechanism is available.

For more details https://wiki.phpmyadmin.net/pma/Change_tracking.