Quick intro to Postgres for developers

postgresIn this article, a quick guide to the most commonly used Postgres commands

INSTALL

sudo apt-get install postgresql postgresql-contrib

ADMIN OPERATIONS

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

Describe table1

d+ table1;

Change password of the postgres user

password postgres

Grant permission on db1 to user1

grant all privileges on database db1 to user1

List databases

l

List users

du

List tables in a db

dt

IMPORT/EXPORT

Export db snapshot

sudo -u postgres pg_dump dbname —clean > dump.sql

Import db from snapshot

sudo -u postgres psql dbname < dump.sql

script to modify DB values filtering by column and rows

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.

Repository:
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 and MySQL notes

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.