Interacting with PostgreSQL

• The psql client has a lot of features that will make your PostgreSQL life easier
• Besides PostgreSQL commands (SELECT, INSERT, UPDATE, CREATE TABLE), psql provides meta-commands
• PostgreSQL commands are sent to the server, meta-commands are processed by psql itself
• A meta-command begins with a backslash character ( \ )

You can obtain a list of all the meta-commands using the \? meta-command :

postgres=# \?

General

\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
connect to new database (currently "edb")

\h [NAME]
help on syntax of SQL commands, * for all commands)

\prompt [TEXT] NAME
prompt user to set internal variable

\password [USERNAME]
securely change the password for a user

\q
quit psql

\timing
toggle timing of commands (default off)

\! [COMMAND]
execute command in shell or start interactive shell

NOTE :
[ ] : Optional Variables
| : denotes Or (a | b : a or b)

Using the psql Client

Connecting to a Database :

After Installing PostgreSQL you can login in psql client as following
$ psql -d movies

Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

movies=# \q

The psql program is a text-based interface to a PostgreSQL database. When you are running
psql, you won't see a graphical application no buttons or pictures or other bells and whistles,
just a text-based interface. Later, I'll show you another client application that does provide a
graphical interface

A Simple Query :

At this point, you should be running the psql client application. Let's try a very simple query:

$ psql -d movies
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

movies=# SELECT user;

current_user
---------------
Deepu
(1 row)

movies=# \q

Creating the prototype databases using initdb

Setting Environment Variable : (editing .bash_profile file)

1. Open Terminal
2. Switch user to Postgres "su - postgres"
3. Set the following variables
   

• PGPORT= Port No (eg : 5432)
  
• PGDATA= Absolute Path to your data directory (/opt/PostgresPlus/8.3/data)
  
• PATH= Absolute Path to your bin directory (/opt/PostgresPlus/8.3/bin)
• save file
  

Checking Environment Variables :

1. open Terminal
2. Switch user to postgres "su - postgres"
3. type following commands to check EVars
   

• echo $PATH (should display the postgres installed port no)
  
• echo $PGPORT (should point to the data directory)
  
• echo $PGDATA (should display the bin directory path)

Creating prototype DBs using Initdb :

• open terminal
• type "initdb" (command creates the two prototype databases template0 and template1)
  

Creating a new database with createdb :

• open terminal
• switch user to postgres
• type psql (you will be connected to postgres database "Postgres=#")
  
• After starting the postmaster, use the "createdb" command to create the database .
• Secondly you can also login using psql and execute following command to create a database Postgres=#CREATE DATABASE movies;

Creating a new PostgreSQL user :

• Use the createuser command to tell PostgreSQL which users are allowed to access your
  database.
• You can also use "CREATE USER username" command in psql client to create a user.

Logging inside PostgreSQL Database using PSQL :

• As root user execute :
  
  • su – postgres
  • psql –d movies (u will be directly connected to movies database)
    
• You will get logged inside psql client

Key Points :

• optional values can be inserted in psql command
• -p : you can specify the PORT no to be connected using this
  
• -U : you can specify the USER through which you want to connect to DB (user has to be exist)
  
• -h : you can specify the HOSTNAME/IP name on which Postgres installed
  
• -d : you can specify the DATABASE name to be connected

full psql command as follows :

• psql -d database -U user -p port -h host
• eg : psql -d movies -U movies -p 5444 -h localhost
• In the above example database is : movies, user : movies, port no : 5444, host : localhost
• Default Values - database : postgres, User : postgres, Port no : port on which u installed postgres (5432) host : localhost
• Default values are the values you inserted in ur .bash_profile file, U can specify default Username and Host names too in your environment variables (PGUSER)

These settings will initialize the all the settings required to work on PostgreSQL... Start working and post your comments...

Installing PostgreSQL on Linux using RPMs

Downloading the RPMs :
As I mentioned in the previous post the RPMs are available on PostgreSQL FTP site and all of its mirrors.

• Select version of Postgres to Install
  
• Select Platform - Linux
• Select rpm / srpm
• Select Linux Flavour (redhat/fedora)
• Download RPMs Listed there
  

Installing and Upgrading PostgreSQL RPMs :

• Installing PGDG RPMs are as easy as installing any RPMs
  rpm -ivh package_name.version.arch.rpm
• Unless specified, on minor release upgrades (i.e., upgrading from 8.1.0 to 8.1.1 or 8.1.3, etc1), you may use usual RPM upgrade process :
  rpm -Uvh package_name.version.arch.rpm
• Please note that on every new major version upgrade, youhave to follow dump/reload sequence.

Sequence of RPMs to Install :

1. rpm -ivh postgresql-libs-8.3.7-1PGDG.rhel5.i386.rpm
2. rpm -ivh postgresql-debuginfo-8.3.7-1PGDG.rhel5.i386.rpm
3. rpm -ivh postgresql-8.3.7-1PGDG.rhel5.i386.rpm
4. rpm -ivh postgresql-devel-8.3.7-1PGDG.rhel5.i386.rpm
5. rpm -ivh postgresql-docs-8.3.7-1PGDG.rhel5.i386.rpm
6. rpm -ivh postgresql-server-8.3.7-1PGDG.rhel5.i386.rpm

Creating the Cluster using initdb :

1. Open Terminal
2. su - postgres (changes your identity from root user to postgreSQL super user)
   
3. Move to /opt/PostgresPlus/8.3/bin using "cd /opt/PostgresPlus/8.3/bin"
4. execute "initdb -D data" (creates the Cluster, a new directory named 'data')

To work in with your Database :

1. Start Cluster using "pg_ctl -D start"
2. To Chech the status use "pg_ctl status"
   
3. Login inside the Cluster using "psql" command (continue your work in DataBase)
   
4. reload : to reload the server configurations
5. restart : to restart (stop/start) Server
   
6. To stop the Cluster use "initdb -D stop"

Advance Settings :
Now every time you need to go to /opt/PostGresPlus/8.3/bin folder to execute the commands like psql, pg_ctl, etc., instead of doing this activity every time we can set the environment variables like PATH, PGDATA, PGPORT in .bash_profile file of User (postgres User)

Steps to follow :

1. open Terminal
2. Login as postgres User
3. open .bash_profile file using "vi .bash_profile"
4. insert the Lines PATH= PGDATA= PGPORT= and save the file
5. now you can run all postgres commands from any location from terminal as postgres user

we'll see these things in detail in the next post....

Installing PostgreSQL on Linux using Binaries

PostgreSQL Installation Prerequisites

• When you install PostgreSQL, you can start with prebuilt binaries or you can compile PostgreSQL from source code.
• GNU make is required
• You need an ISO/ANSI C compiler
• tar is required to unpack the source distribution
• The GNU Readline library
• The zlib compression library

You can download Postgres Binary Sources from here
for Standard Server click here (bineries)

Installation using Binaries :

1. Go to setup file folder
2. Unpack tar file using "tar -zxvf .tar.gz"
3. Move to unpacked folder
4. Install .bin file using "./.bin
5. Give Username (add if not available)
   
6. Set Port Number
7. Give password for User
8. Install
   

Uninstalling :

1. Go to Location : /opt/PostgresPlus/8.3/Uninstall
2. execute : ./Uninstall_Postgres_Plus
3. Go to Applications - Add/Remove Software
4. Search for Postgres Packages
5. Uncheck them and Save Settings

Potgres Terminology & Limitations

Postgres Terminology :

Common Database Object Names

Relation : Table or Index

Tuple : Row

Attribute : Column

Storage Object Names

Page : Data Block (when block is on disk)

Buffer : Page (when block is in memory)

Database Limitations :

Maximum Indexes / Table : Unlimited

Maximum Columns / Table : 250-1600

Maximum Rows / Table : Unlimited

Maximum Field Size : 1 GB

Maximum Row Size : 1.6 TB

Maximum Table Size : 32 TB

Maximum Database Size : Unlimited

History of PostgreSQL

The world’s most advanced open source database

Designed for extensibility and customization

ANSI/ISO compliant SQL support

Actively developed for more than 20 years

• University POSTGRES (1986-1993)
• Postgres95 (1994-1995)
• PostgreSQL (1996-2008)
  

Active global support community
Support Mailing Lists http://www.postgresql.org/community/lists

Major Features of PostgreSQL

Portable

• Written in ANSI C
• Supports Windows, Linux, and major UNIX platforms

Reliable

• ACID Compliant
• Supports Transactions
• Supports Savepoints
• Uses Write Ahead Logging

Scalable

• Uses Multiversion Concurrency Control
• Uses Row-Level Locking
• Supports Table Partitioning
• Supports Tablespaces

Secure

• Employs Host-Based Access Control
• Provides Object-Level Permissions
• Supports Logging & Auditing Functionality

Available

• Replication Support
• Support for High Availability

Advanced

• Supports Triggers & Functions
• Supports Custom Procedural Languages PL/pgSQL, PL/Perl, PL/TCL, PL/PHP, …
• Supports Hot-Backup and Point-in-Time Recovery
• Warm Standby

Prerequisites to Learn PostgreSQL

Before we proceed further to learn PostgreSQL one should have basic knowledge of these following terms related to Database System

DATABASE

RDBMS

ORDBMS

RELATION

TUPLE

ATTRIBUTE

DEGREE

CARDINALITY

PRIMARY KEY

CANDIDATE KEY

ALTERNATE KEY

SUPER KEY

FOREIGN KEY

COMPOSITE KEY

NON KEY ATTRIBUTES

ER MODELING

ER MODEL

NORMALIZATION

ER NOTATION