Introduction to PostgreSQL

About PostgreSQL.

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others.

Prerequisites.

We can install PostgreSQL database on ubuntu based systems using the following command:

          $ sudo apt-get install postgresql

Basics of PostgreSQL database.

The user "postgres" should have already been configured by the installation. A password will be missing.

As root issue the command: passwd postgres to assign a password for user postgres.

Login as user postgres: su – postgres

Create s database: createdb testdb

Connect to database: psql testdb

          Leads you to a command prompt like this:
                         testdb=#

          Enter \q to exit

Following commands are useful:

• \l : List databases
  
• \c database-name : List databases
  
• \d : List tables in database
  
• \d table-name : Describe table
  
• select * from table-name : List table contents

More Commands:

Create a user:

• Command line: [prompt]$ createuser dude
  
• SQL: CREATE USER dude WITH PASSWORD 'supersecret';
  Change with ALTER USER
  

Grant Privilages:

• SQL: GRANT UPDATE ON table-name to dude
  
• SQL: GRANT SELECT ON table-name to dude
  
• SQL: GRANT INSERT ON table-name to dude
  
• SQL: GRANT DELETE ON table-name to dude
  
• SQL: GRANT RULE ON table-name to dude
  
• SQL - Do it all: GRANT ALL PRIVILEGES ON table-name to public
  

Delete a user:

• Command line: [prompt]$ dropuser SuperDude
  

Delete a database:

Command line:

• [prompt]$ destroydb testdb
• [prompt]$ dropdb testdb

SQL: 

• DROP DATABASE testdb;

Create a database:

• Command line: [prompt]$ createdb testdb -U user-name -W
  You will be prompted for a password. (or execute as Linux user postgres without -U and -W options)
• SQL: CREATE DATABASE testdb

Backup a database:

• [prompt]$ pg_dumpall > outfile
• [prompt]$ pg_dumpall -Fc dbname > outfile

Version Upgrades:

• Dump: [prompt]$ postgresql-dump -t /var/lib/pgsql/backup/db.bak -p /var/lib/pgsql/backup/old -d
• Restore: [prompt]$ psql -e template1 < /var/lib/pgsql/backup/db.bak
  The table template1 is the default administrative database.

User Managements:

User Creation:

Command line:

• [prompt]$ createuser john

SQL: 

•  CREATE ROLE john;

To add login privilege:

• CREATE ROLE john LOGIN;
• CREATE ROLE john WITH LOGIN; # mimic of above command
• CREATE USER john; #alternative of CREATE ROLE which adds LOGIN

You can also add the LOGIN attribute with ALTER ROLE:

• ALTER ROLE john LOGIN;
• ALTER ROLE john NOLOGIN; # remove login

You can also create groups via CREATE GROUP (Which is now also aliased to CREATE ROLE), and then grant or revoke access to other roles:

• CREATE GROUP admin LOGIN;
• GRANT admin TO john;
• REVOKE admin FROM john;

Pros and Cons of PostgreSQL.
Pros:

• Very feature rich
  
• GIS add-on functionality
  
• Flexible full-text search
  
• Multiple replication options to suit your environment and requirements
  
• Powerful server-side procedural languages are available, and can be extended (PL/pgSQL is installed by default, but others like Perl, Python, Ruby, TCL, etc are available)
  
• Writing your own extensions is pretty easy
  
• Uses multi-version concurrency control, so concurrent performance rocks
  
• Fully ACID compliant
  
• Commercial support through multiple third-parties is available
  
• Well-documented
  
• Strong access-control framework
  
• Ability to add a column on a large table without locking the thing for a huge amount of time.
  
• Use multiple indexes.
  
• The error messages are more informative.
  
• PostgreSQL feels more like an open source Oracle

Cons:

• Less-mature replication software
  
• This may not be quite accurate, it's more that there's no single de-facto method that's recommended, widely-appropriate and widely-known by admins/users