In this article, a quick guide to the most commonly used Postgres commands
sudo apt-get install postgresql postgresql-contrib
Create superuser from the commad line
sudo -u postgres createuser –superuser digideps
Create database from command line
sudo -u postgres createdb newDatabaseName
Login via command line (postgres is the default root user)
sudo -u postgres psql [dbname]
DEVELOPER OPERATIONS / QUERIES
Change password of the postgres user
Grant permission on db1 to user1
grant all privileges on database db1 to user1
List tables in a db
Export db snapshot
sudo -u postgres pg_dump dbname —clean > dump.sql
Import db from snapshot
sudo -u postgres psql dbname < dump.sql
One of my clients recently needed to implement an application-level encryption for some existing applications.
Unfortunately, the design pattern used for the database layer obliged me to write an independent script to browse the database and encrypt the data for each user using a user-specific two-way encryption key.
So, I ended up writing a simple utility class that applies a user-defined function (modifies) to the defined columns and records (via a filter query) of each database table.
It can be used for any relational database server supported by PDO (MySQL, SQL Server, MySQL, Oracle). I’ve only used it with MySQL but it should work smoothly with all the others.
Db modifier https://github.com/elvisciotti/DbModifier
An example of usage is in the README, hope it’s clear enough.
You can basically use this to mass editing Db records with a function that is NOT easily implementable with a simple MySQL Query, e.g. encrypt the data with a custom PHP function.
NoSQL: If Only It Was That Easy at Marked As Pertinent
What am I going to build my next app on? Probably Postgres. Will I use NoSQL? Maybe. I might also use Hadoop and Hive. I might keep everything in flat files. Maybe I’ll start hacking on Maglev. I’ll use whatever is best for the job. If I need reporting, I won’t be using any NoSQL. If I need caching, I’ll probably use Tokyo Tyrant. If I need ACIDity, I won’t use NoSQL. If I need a ton of counters, I’ll use Redis. If I need transactions, I’ll use Postgres. If I have a ton of a single type of documents, I’ll probably use Mongo. If I need to write 1 billion objects a day, I’d probably use Voldemort. If I need full text search, I’d probably use Solr. If I need full text search of volatile data, I’d probably use Sphinx.
Which is faster: MySQL or MongoDB? Does it depend on the use case? – Quora
To me, the Mysql vs MongoDB benchmarks are revealing as they show that with beefy hardware, a NoSQL engine does not have to be faster than a well indexed Mysql Table, which might seem the case.
MySQL became the most popular relational database on the planet by turning its weaknesses into strengths and successfully leveraging its ubiquity.
It would be foolish to predict the same success that MySQL enjoyed for MongoDB, because the underlying market context has changed.