IntroductionPostgreSQLAdministrationV11

Course Agenda: PostgreSQL 9.0

•  In this Course we will cover:

•  Introduction & Architecture

•  PostgreSQL System Architecture

•  Installation

•  Configuration

•  Creating and Managing PostgreSQL Databases

•  PSQL

•  PGADMIN III

•  Security

•  SQL Primer

•  Backup, Recovery and PITR

•  Routine Maintenance Tasks

•  PostgreSQL Data Dictionary

Module 1

Objectives

•  In this module you will learn:

•  History of PostgreSQL

•  Major Features

•  New Features in PostgreSQL 9

•  Multi-Version Concurrency Control (MVCC)

•  Write-Ahead Logging

•  Architectural Overview

•  Limits

•  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-current) •  Active global support community

•  Support Mailing Lists

•  http://www.Postgresql.org/community/lists/ •  Support Forums

•  http://www.enterprisedb.com/products/Postgres_plus.do

•  Production support SLA’s available from EnterpriseDB

•  Portable

•  Written in ANSI C

•  Supports Windows, Linux, Mac OS/X and major

UNIX platforms

•  Reliable

•  ACID Compliant

•  Supports Transactions

•  Supports Savepoints

•  Uses Write Ahead Logging

•  Scalable

•  Uses Multi-version Concurrency Control

•  Supports Table Partitioning

•  Supports Tablespaces

•  Secure

•  Employs Host-Based Access Control

•  Provides Object-Level Permissions

•  Supports Logging

•  SSL

•  Available

•  Replication Support

•  Support for High Availability

•  Advanced

•  Full Text Search

•  Supports Triggers & Functions

•  Supports Custom Procedural Languages

•  PL/pgSQL, PL/Perl, PL/TCL, PL/PHP, …

New Features in PostgreSQL 9

•  PostgreSQL 9.0 was released in September 2010 with

features which were requested for years.

•  Streaming Replication (Hot standby)

•  GRANT/REVOKE ALL TABLES IN SCHEMA

•  Full support for 64-bit Windows

•  Per column triggers and conditional trigger execution

•  EXPLAIN enhancements. Output in JSON, XML, or

YAML.

•  Page cost estimates at tablespace level

•  RADIUS (Remote Authentication Dial In User Service)

•  New contrib module pg_upgrade to support in-place

Multi-Version Concurrency Control (MVCC)

Maintain data consistency internally

•  While querying a database each transaction sees a

snapshot of data (a database version) as it was some

time ago

•  Prevent transactions from viewing inconsistent data

•  Provides transaction isolation in concurrent transactions

•  Readers do not block writers and writers do not block

Write Ahead Logs (WAL)

•  Write Ahead Logging

•  Makes a record of each insert/update/delete before it actually takes place

•  System does not consider data ‘safe’ until log is written to disk •  Provides recovery in case of system crash or failure

•  Characteristics

•  Similar to Oracle REDO logs (no separate undo) •  Database grows with undo information

•  Logs never fill up on long transactions •  Fast, single-phase Recovery

Architectural Overview

Connectors

Limit Value

Maximum Database Size Unlimited Maximum Table Size 32 TB Maximum Row Size 1.6 TB Maximum Field Size 1 GB Maximum Rows per Table Unlimited

Maximum Columns per Table 250-1600 (Depending on Column types)

•  Limitations are generally defined by •  Operating System Limits

•  Compile-Time Parameters •  Data Type Usage

•  General Database Limitations

PostgreSQL Terminology

•  PostgreSQL was designed in academia •  Objects are defined in academic terms

•  Terminology based on relational calculus / algebra •  Common Database Object Names

Industry Term PostgreSQL Term

Table or Index Relation

Row Tuple

Summary

•  In this module you learned:

•  History of PostgreSQL

•  Major Features

•  New Features in PostgreSQL 9

•  Multi-Version Concurrency Control (MVCC)

•  Write-Ahead Logging

•  Architectural Overview

•  Limits

Module 2

Objectives

•  In this session you will learn:

•  Architectural Summary •  Shared Memory

•  Statement Processing •  Utility Processes

•  Interprocess Communication •  Disk Read Buffering

•  Disk Write Buffering

•  Background Writer Cleaning Scan •  Commit & Checkpoint

•  Physical Database Architecture •  Data Directory Layout

•  PostgreSQL uses processes, not threads

•  Postmaster process acts as supervisor

•  Several utility processes perform background work

(postmaster starts them, restarts them if they die)

•  One backend process per user session

(postmaster listens for new connections)

•  Background writer

•  Writes dirty data blocks to disk, performs checkpoints

•  WAL writer

•  Flushes write-ahead log to disk

•  Autovacuum launcher

•  Starts autovacuum workers as needed

•  Autovacuum workers

•  Recover free space for reuse

•  Logging collector

•  Routes log messages to syslog, eventlog, or log files

•  Stats collector

•  Collects usage statistics by relation and block

•  Archiver

•  Archives write-ahead log files

•  PostgreSQL buffer cache

(shared_buffers) reduces

OS reads.

•  Read the block once, then

examine it many times in

cache.

Disk Read Buffering

•  Blocks are written to

disk only when needed:

•  To make room for

new blocks

•  At checkpoint time

Disk Write Buffering

Shared (data) Buffers

•  Background writer scan

attempts to ensure an

adequate supply of

clean buffers.

•  Backends write dirty

buffers at need.

Background Writer Cleaning Scan

Shared (data) Buffers

•  Before Commit

•  Uncommitted updates are in memory

•  After Commit

•  Committed updates written from shared memory to disk (write-ahead log file)

•  After Checkpoint

•  Modified data pages are written from shared memory to the data files

PostgreSQL Physical Database Architecture

•  A database cluster is a collection of databases that are managed by a single server instance.

•  Each cluster has a separate data directory, TCP port, and its own set of processes.

•  Each cluster can contain multiple databases, each of which can contain a separate set of tables, indexes, views, etc.

Data Directory Layout

•  File-per-table, file-per-index.

•  A tablespace is a directory.

•  Each database that uses that tablespace gets a

subdirectory.

•  Each relation using that tablespace/database

combination gets one or more files, in 1GB chunks.

•  Additional files used to hold auxiliary information (free

space map, visibility map)

Data Directory Layout

/opt/PostgreSQL/9.0/

base

16571

/PostgreSQL/tblspace/

Database OID Table & Index Files

pg_tblspc 16486 16486 10629 10635 16631 16628 10235 10239 12144 12762 data (tablespace OID)

Database 16486 has tables in both the default and a user-defined tablespace

Installation Directory Layout

•  bin – Programs.

•  data – Data directory.

•  doc – Documentation.

•  include – Header files.

•  installer, scripts – Installer files.

•  lib – Libraries.

•  pgAdmin III – Graphical administration tool.

•  StackBuilder – Use to install additional components.

•  symbols – Debugging symbols.

Page Layout

•  Page Header

•  General information about the page •  Pointers to free space

•  24 bytes long

•  Row/Index Pointers

•  Array of offset/length pairs pointing to the actual rows/index entries •  4 bytes per item

•  Free Space

•  Unallocated space

•  New pointers allocated from the front, new rows/index entries from the rear

•  Row/Index Entry

•  The actual row or index entry data

•  Special

•  Index access method specific data •  Empty in ordinary tables

•  Backends write data to

WAL buffers.

•  Flush WAL buffers

periodically (WAL

writer), on commit, or

when buffers are full.

•  Group commit.

Write Ahead Logging (WAL)

•  Archiver spawns a task to copy away pg_xlog log files when full.

Transaction Log Archiving

Shared (data) Buffers WAL Buffer

Summary

•  In this session you learned:

•  Architectural Summary •  Shared Memory

•  Statement Processing •  Utility Processes

•  Interprocess Communication •  Disk Read Buffering

•  Disk Write Buffering

•  Background Writer Cleaning Scan •  Commit & Checkpoint

•  Physical Database Architecture •  Data Directory Layout

Module 3

Installation

Objectives

•  In this module you will learn:

•  OS User & Permissions •  Installation

•  Setting environmental variables •  Clusters

•  Creating a database cluster

•  Starting and Stopping the Server (pg_ctl) •  Connect to the server using psql

OS User & Permissions

•  PostgreSQL runs as a daemon (Unix / Linux) or service (Windows) •  All PostgreSQL processes and data files must be owned by a user in

the OS

•  OS user is un-related to database user accounts

•  For security reasons, the OS user must not be root or an administrative account

•  During installation a postgres locked user will be created on linux •  Windows does not have locked users; a password is required

Installation Options

There are several installation options: •  One-Click installer

•  Operating system package •  RPM/YUM

•  Debian/Ubuntu DEB •  FreeBSD port

•  Solaris package •  Source code

One-Click Installation

Download the one-click installer from:

Installation

•  Run the postgresql binary using root user.

Installation

•  Installation Directory: Choose the location where you want to install PostgreSQL

Installation

•  Data Directory: Choose the location where you want to install default

Installation

•  Provide the database superuser and installation user password.

Installation

•  On the next step of wizard you can specify the port number on which your default cluster will run.

•  Default port is 5432.

•  Finally you can choose the Locale and click next and install the PostgreSQL.

Installation

•  Stackbuilder is a package manager that can be used to

download and install additional PostgreSQL applications

and driver

•  During installation this can be unchecked if no add-on

tool needs to be installed

After Installation

•  Once installed you will get PostgreSQL running on the specified port. •  You will also get a PostgreSQL Menu to work with database cluster.

Setting environmental variables

•  Setting Environment Variables is very important for trouble free startup/shutdown of the database server.

•  PATH - should point correct bin directory

•  PGDATA - should point to correct data cluster directory

•  PGPORT - should point correct port on which database cluster is running

•  PGUSER – specifies the default database user name •  Edit .profile to set the variables

•  In Windows set these variables using my computer properties page.

Clusters

•  Each instance of PostgreSQL is referred to as a “cluster” •  Comprised of a data directory that contains all data and

configuration files

•  Referred to in two ways

•  Location of the data directory •  Port number

•  A single server can have many installations and you can create multiple clusters using initdb.

Creating a Database Cluster

• 

Use initdb to create a database cluster. Must be run

as the OS user that the instance will run as.

initdb –D <data directory>

• 

-D <data directory> - Database cluster directory

• 

-U <super user> - Select the database super user

name

• 

-E <encoding> - Specify the database encoding

• 

After creating a new database cluster, modify

postgresql.conf and pg_hba.conf, be sure to assign a

unique port # to the cluster in postgresql.conf

Starting and Stopping the Server (pg_ctl)

•  Use pg_ctl to start and stop the server from the

command line

•  pg_ctl start [<options>] - Start the server.

•  pg_ctl stop [<options>] - Stop the server.

•  pg_ctl restart [<options>] - Restart the server.

•  pg_ctl status [<options>] - Display server status.

Starting and Stopping the Server (pg_ctl)

•  When stopping or restarting the server:

•  -m smart (the defaults) waits for all clients to exit

•  -m fast rolls back active transactions, closes open

connections, and shuts down cleanly

•  -m immediate performs an immediate, abnormal

shutdown (i.e. a crash)

•  Other useful options

•  -D <datadir> to specify an alternate cluster location

•  -l <logfile> to specify an alternate log file, when

Connecting to server using psql

•  You can use psql utility to connect to a postgres cluster. •  psql [OPTIONS]... [DBNAME [USERNAME]]

•  Database to connect to may also be specified using the –d option.

•  The user to connect as may be specified with -U.

•  In a psql session the connection may be changed by using \c[onnect] [DBNAME [USERNAME]]

•  template0=# \c PostgreSQL test

•  You are now connected to database "PostgreSQL" as user "test".

•  PostgreSQL=>

Summary

•  In this module you learned:

•  OS User & Permissions

•  Installation – one-click installer •  Setting environmental variables •  Clusters

•  Creating a database cluster

•  Starting and Stopping the Server (pg_ctl) •  Connect to the server using psql

Lab Exercise - 1

•  Choose the platform on which you want to install PostgreSQL.

•  Download PostgreSQL one-click installer from Enterprisedb website for chosen platform.

•  Install PostgreSQL.

Module 4

Configuration

Objectives

•  In this module you will learn:

•  Setting PostgreSQL Parameters •  Access Control

•  Connection Settings

•  Security and Authentication Settings •  Memory Settings

•  Query Planner Settings •  WAL Settings

•  Log Management

•  Background Writer Settings •  Vacuum Cost Settings

Setting PostgreSQL Parameters

•  There are many configuration parameters that effect the behavior of the database system.

•  All parameter names are case-insensitive.

•  Every parameter takes a value of one of four types: boolean, integer, floating point, or string.

•  One way to set these parameters is to edit the file postgresql.conf,

The Server parameter file: postgresql.conf

•  Holds parameters used by cluster •  Parameters case-insensitive

•  Normally stored in data directory •  Initdb installs default copy

•  Some parameters only take effect on server restart (pg_ctl) •  # used for comments

•  One parameter per line

Setting PostgreSQL Parameters

•  Some parameters can be changed per session using SET command •  Some parameters can be changed at user level using ALTER USER •  Some parameters can be changed at the database level using

ALTER DATABASE

•  SHOW command can be used to see settings

Connection Settings

•  listen_addresses (default localhost): Specifies the addresses on which the server is to listen for connections. Use * for all. •  port (default 5432): The port the server listens on.

•  max_connections (default 100): Maximum number of concurrent connections the server can support.

•  superuser_reserved_connections (default 3): Number of connection slots reserved for superusers.

•  unix_socket_directory (default /tmp): Directory to be used for UNIX socket connections to the server.

Security and Authentication Settings

•  authentication_timeout (default: 1 minute): Maximum time to complete client authentication, in seconds.

•  ssl (default: off). Enables SSL connections.

•  ssl_ciphers: List of SSL ciphers that may be used for secure connections.

Memory Settings

•  shared_buffers (default: really small). Size of PostgreSQL shared buffer pool. Rule of thumb is 25% of system memory to a maximum of 8GB, on Linux; or 512MB, on Windows.

•  temp_buffers (default: 8MB): Amount of memory used by each backend for caching temporary table data.

•  work_mem (default: 1MB): Amount of memory used for each sort or hash operation before switching to temporary disk files. Default is conservative, but don't overdo it.

•  maintenance_work_mem (default: 16MB): Amount of memory used for each index build or VACUUM.

Query Planner Settings

•  random_page_cost (default 4.0): Estimated cost of a random page fetch, in abstract cost units. May need to be reduced to account for caching effects.

•  seq_page_cost (default 1.0): Estimated cost of a sequential page fetch, in abstract cost units. May need to be reduced to account for caching effects. Must always set

random_page_cost >= seq_page_cost.

•  effective_cache_size (default 128M): Used to estimate the cost of an index scan. Rule of thumb is 75% of system memory.

Write Ahead Log Settings

•  wal_level (default: minimal). Determines how much information is written to the WAL. Change this to enable replication. Other values are archive and hot_standby.

•  fsync (default on): Turn this off to make your database much faster – and silently cause arbitrary corruption in case of a system crash.

•  wal_buffers (default: 64kB): The amount of memory used in

shared memory for WAL data. May need to be raised to 1-16 MB on busy systems.

•  checkpoint_segments (default 3): Maximum number of 16MB WAL file segments between checkpoints. Default is too small! •  checkpoint_timeout (default 5 minutes): Maximum time

Where To Log

•  log_destination. Valid values are combinations of stderr, csvlog, syslog, and eventlog, depending on platform.

•  logging_collector. Enables advanced logging features. csvlog requires logging_collector.

•  log_directory. Directory where log files are written. Requires logging collector.

•  log_filename. Format of log file name (e.g. postgresql-%Y-%M-%d.log). Allows regular log rotation. Requires logging collector. •  log_rotation_age. Automatically rotate logs after this much

time. Requires logging_collector.

•  log_rotation_size. Automatically rotate logs when they get this big. Requires logging_collector.

When To Log

•  client_min_messages (default NOTICE). Messages of this severity level or above are sent to the client.

•  log_min_messages (default WARNING). Messages of this severity level or above are sent to the server.

•  log_min_error_statement (default ERROR). When a message of this severity or higher is written to the server log, the

statement that caused it is logged along with it.

•  log_min_duration_statement (default -1, disabled): When a statement runs for at least this long, it is written to the server log, with its duration.

What To Log

•  log_connections (default off): Log successful connections to the server log.

•  log_disconnections (default off): Log some information each time a session disconnects, including the duration of the

session.

•  log_error_verbosity (default “default”): Can also select “terse” or “verbose”.

•  log_duration (default off): Log duration of each statement. •  log_line_prefix: Additional details to log with each line.

•  log_statement (default none): Legal values are none, ddl, mod (DDL and all other data-modifying statements), or all.

Background Writer Settings

•  bgwriter_delay (default 200 ms): Specifies time between activity rounds for the background writer.

•  bgwriter_lru_maxpages (default 100): Maximum number of pages that the background writer may clean per activity round. •  bgwriter_lru_multiplier (default 2.0): Multiplier on buffers

scanned per round. By default, if system thinks 10 pages will be needed, it cleans 10 * bgwriter_lru_multiplier of 2.0 = 20.

Vacuum Cost Settings

•  vacuum_cost_delay (default 0 ms): The length of time, in milliseconds, that the process will wait when the cost limit is exceeded.

•  vacuum_cost_page_hit (default 1): The estimated cost of vacuuming a buffer found in the PostgreSQL buffer pool.

•  vacuum_cost_page_miss (default 10): The estimated cost of vacuuming a buffer that must be read into the buffer pool.

•  vacuum_cost_page_dirty (default 20): The estimated cost charged when vacuum modifies a buffer that was previously clean.

•  vacuum_cost_limit (default 200): The accumulated cost that will cause the vacuuming process to sleep.

Autovacuum Settings

•  autovacuum (default on). Controls whether the autovacuum launcher runs, and starts worker processes to vacuum and analyze tables.

•  log_autovacuum_min_duration (default -1). Autovacuum tasks running longer than this duration are logged.

•  autovacuum_max_workers (default 3). Maximum number of autovacuum worker processes which may be running at one time.

Summary

•  In this module you learned:

•  Setting PostgreSQL Parameters •  Access Control

•  Connection Settings

•  Security and Authentication Settings •  Memory Settings

•  Query Planner Settings •  WAL Settings

•  Log Management

•  Background Writer Settings •  Vacuum Cost Settings

Lab Exercise - 1

•  You are working as DBA. Make necessary changes in server parameter file for following settings:

•  So that the server allows up to 200 connected users

•  So the server should reserve 10 connection slots for DBA

Lab Exercise – 2

•  Working as a DBA is a challenging job and to track down certain

activities on the database server logging has to be implemented. Go through server parameters that control logging and implement

following:

•  Save all the error message in a file inside pg_log folder in your cluster data directory (e.g. c:\edbdata)

•  Log all queries and their time which are taking more than 5 seconds to execute

•  Log the users who are connecting to the database cluster •  Make above changes and verify them

Module 5

Creating and Managing PostgreSQL

Databases

Objectives

•  In this module you will learn:

•  Object Hierarchy •  Creating Databases •  Creating Schemas •  Schema Search Path •  Roles

•  Users

•  Groups

Database Cluster Database

Schema Role/Groups

(Users)

Table View _Sequence Functions

Tablespace

Database

•  A database is a named collection of SQL objects. It is a collection of schemas and the schemas contain the tables, functions, etc.

•  Databases are created with the CREATE DATABASE command and destroyed with the DROP DATABASE command.

•  To determine the set of existing databases:

•  SQL: SELECT datname FROM pg_database;

Creating Databases

•  There is a program that you can execute from the shell to create new databases, createdb.

•  createdb dbname

•  Create Database command can be used to create a database in a cluster.

•  Syntax:

CREATE DATABASE name

[ [ WITH ] [ OWNER [=] dbowner ] [ TEMPLATE [=] template ]

[ ENCODING [=] encoding ] [ TABLESPACE [=] tablespace ]

Creating Schemas

•  Schemas:

•  A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named

objects, including data types, functions, and operators.

•  There are several reasons why one might want to use schemas: •  To allow many users to use one database without interfering

with each other.

•  To organize database objects into logical groups to make them more manageable.

•  Third-party applications can be put into separate schemas so they cannot collide with the names of other objects.

Creating Schemas (cont)

•  To create a schema, use the CREATE SCHEMA command. Give the schema a name of your choice.

•  Syntax:

•  CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ]

•  CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]

•  For example:

•  CREATE SCHEMA edb AUTHORIZATION goldy_dba; •  CREATE SCHEMA pgplus;

Schema Search Path

•  Qualified names are tedious to write, so we use table names directly in queries.

•  If no schema name is given, the schema search path determines which schemas are searched for matching table names

•  e.g.:

SELECT * FROM employee

•  This statement will find the first employee table the schemas listed in the search path

Schema Search Path

•  The first schema named in the search path is called the current schema if that named schema exist. Aside from being the first schema searched, it is also the schema in which new tables will be created if the CREATE

TABLE command does not specify a schema name.

•  To show the current search path, use the following command:

•  SHOW search_path;

•  In the default setup this returns:

•  search_path •  --- •  "$user",public

•  The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored. The second element refers to the public schema that we have seen already. •  To put our new schema in the path, we use:

•  Database users are different than operating system users

•  Users can be created in SQL using “CREATE USER” command or using the “createuser” utility.

SQL Example:

CREATE USER edb PASSWORD 'secret';

CREATE USER edb_dba CREATEDB CREATEUSER;

CREATE USER edb_temp VALID UNTIL '2010-08-31'; DROP USER goldy;

•  Similar to groups in Unix

•  Can be granted permissions like users Example:

CREATE GROUP dba_group WITH USER goldy_dba; CREATE GROUP temps;

ALTER GROUP temps ADD USER goldy_temp; DROP GROUP temps;

•  A role can be either a database user or a collection of database users, called a group. A user is a role that can login to a database. A group is a role that cannot be used to login to any database.

•  Groups can own database objects and can assign privileges on those objects to other roles to control who has access to which objects.

•  Furthermore, it is possible to grant membership in a group to another group, thus allowing the member role use of privileges assigned to the role it is a member of.

•  Database groups are global across a database cluster installation.

•  CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes the LOGIN privilege

•  Users can be added to ROLES similarly to the way they can be added to GROUPS

Database Cluster Database

Table View Sequence

Tablespace

Object Ownership

Users

Functions Schema

Access to tables is given and taken using the GRANT and REVOKE SQL commands

Examples:

GRANT UPDATE, DELETE ON emp TO goldy_temp; GRANT ALL ON dept TO GROUP temps;

REVOKE UPDATE, DELETE ON emp FROM goldy_temp; GRANT USAGE ON SCHEMA pgplus TO goldy;

Summary

•  In this module you learned:

•  Object Hierarchy •  Creating Databases •  Creating Schemas •  Schema Search Path •  Roles

•  Users

•  Groups

Lab Exercise - 1

•  You are working as a DBA. A new website is to be developed for online music store.

•  Create a database user edbstore in your existing cluster.

•  Create a edbstore database with ownership of edbstore user. •  Login inside edbstore database using edbstore user and create

edbstore schema.

•  To load sample tables execute instructor-supplied script in this database using psql command (do not use pgadmin):

•  Open a cmd and write (in case of LINUX use terminal)

cd "c:\Program Files\PostgreSQL\9.0\bin“

psql –U edbstore –d edbstore –f c:\edbstore.sql In Linux: cd /opt/PostgreSQL.9.0/bin

Lab Exercise - 2

•  An e-music online store website application developer wants to add online buy/sell facility and has asked you to separate all tables used in online transactions, here you have suggested to use schemas. Implement following suggested options:

•  Create an ebuy user with password ‘lion’

•  Create an ebuy schema which can be used by ebuy user

•  Login as ebuy user, create a table sample1 and check whether that table belongs to ebuy schema or not.

Lab Exercise - 3

•  Retrieve a list of databases using an SQL query and psql meta command.

•  Retrieve a list of tables in edbstore database and check which schema and owner they have.

Module 6

PSQL

Objectives

•  This module will cover:

•  PSQL Command Line Parameters •  Entering PSQL Commands

•  PSQL Meta-Commands •  PSQL SET Parameters

•  psql has its own set of commands, all of which start with a backslash (\). These are in no way related to SQL commands, which operate in the server. Psql commands only affect psql.

•  Some commands accept a pattern. This pattern is a modified regex. Key points:

•  * and ? are wildcards

•  Double-quotes are used to specify an exact name, ignoring all special characters and preserving case

Connecting

psql [OPTIONS]... [DBNAME [USERNAME]]

•  Database to connect to may also be specified using the -d DBNAME option. The user to connect as may be specified with -U. These

over-ride [DBNAME [USERNAME]] on the command line. •  In a psql session the connection may be changed by using \c

[onnect] [DBNAME [USERNAME]]

template0=# \c edb test

You are now connected to database “edb" as user "test". edb=>

Connection options

•  -h HOSTNAME (database server host or socket directory) •  -p PORT (database server port)

•  If these options are not specified, $PGHOST and $PGPORT are used. •  If those are not specified either, a local socket connection will be used,

On startup...

•  psql will execute commands from $HOME/.psqlrc, unless option -X is specified.

•  -f FILENAME will execute the commands in FILENAME, then exit •  -c COMMAND will execute COMMAND (SQL or internal) and then

exit

•  --help will display all the startup options, then exit •  --version will display version info and then exit

Entering commands

•  psql uses the command line editing capabilities that are available in the native OS. Generally, this means

•  Up and Down arrows cycle through command history

•  on UNIX, there is tab completion for various things, such as SQL commands and to a more limited degree, table and field names

•  disabled with -n

•  \s will show the command history

•  \s FILENAME will save the command history •  \e will edit the query buffer and then execute it •  \e FILENAME will edit FILENAME

Scripting

•  Script files are read in via -f FILENAME or \i FILENAME. •  This is equivalent to piping FILENAME to psql's stdin. •  psql variables can be used to store values or arguments •  for use in scripts.

•  They are set either with -v NAME=VALUE or \set NAME VALUE.

•  \set NAME with no value shows the value of NAME •  \unset NAME un-sets (deletes) NAME

•  Variables are used by prefixing their name with a colon; e.g.

edb=# \set test text('testing')‏ edb=# select :test;

Output

•  There are numerous ways to control output. •  The most important are:

•  -o FILENAME or \o FILENAME will send query output (excluding STDERR) to FILENAME (which may be a pipe)‏ •  \g FILENAME executes the query buffer,

sending output to FILENAME (may be a pipe)‏ •  -q runs quietly.

Useful for querying the database in shell scripts via backticks •  \x toggles expanded output

•  -t prints tuples only. Equivalent to \pset tuples_only •  \t toggles tuple-only output

Output (cont’d)‏

•  \timing toggles the display of timing information. The time displayed includes round-trip time to and from the server

•  \echo [string] [...]

•  echos the arguments to STDOUT, followed by a newline •  With no string a blank line is output

•  If the first argument is -n, no newline is output

•  \qecho is the same as echo, except output is written to the query output channel as set by \o or -o

Information Commands

•  \l[ist][+]

•  List the names, owners,

•  and character set encodings of all the databases in the server. •  If + is appended to the command name,

•  database descriptions are also displayed.

•  \dn[+] [pattern]

•  Lists schemas (namespaces)‏

•  + adds permissions and description to output

•  \df[+] [pattern]

•  Lists functions

Information Commands (cont’d)‏

•  \d(i, s, t, v, S)[+] [pattern]

•  List information about indexes, sequences, tables, views or System objects. Any combination of letters may be used in any order, e.g.: \dvs

•  + displays comments •  \d[+] [pattern]

•  For each relation describe/display the relation structure details •  + displays any comments associated with the columns of the

table, and if the table has an OID column

Other common psql commands

•  \q or ^d

•  Quits the psql program. •  \cd [ directory ]

•  Change current working directory

•  Tip: To print your current working directory, use \! pwd.

•  \! [ command ]

•  Executes the specified command

•  If no command is specified, escapes to a separate Unix shell (CMD.EXE in Windows)‏

Help

•  \?

•  Shows help information about psql commands •  \h [command]

•  Shows information about SQL commands

•  If command isn't specified, lists all SQL commands •  psql --help

Summary

•  In this module you learned about:

•  Command Line Parameters •  Entering Commands

•  Meta-Commands •  Set Parameters

Lab Exercise – 1

1.  Connect to database using psql 2.  Switch databases.

3.  Describe the customers table.

4.  Describe the customers table including description. 5.  List all databases.

6.  List all schemas. 7.  List all tablespaces.

8.  Execute an sql statement, saving the output to a file.

9.  Do the same thing, just saving data, not the column headers. 10.  Create a script via another method, and execute from psql. 11.  Display the dept table in extended format.

12.  Create a script that will not display all data but will echo the table name and show how long the statement took.

Module 7

PGADMIN III

Objectives

•  In this module you will learn: •  pgAdmin III

•  Registering a server

•  Viewing and Editing Data •  Query Tool •  Databases •  Languages •  Schemas •  Domains •  Functions •  Sequences •  Tables •  Columns •  Constraints •  Indexes •  Maintenance •  Rules •  Triggers •  Types •  Views •  Tablespaces •  Roles

•  Click on the plug icon to add a server

•  There are 2 common error messages that you encounter

while connecting to a PostgreSQL database:

•  Could not connect to Server: Connection refused

•  This error occurs when either the database server

isn't running OR the server isn't configured to

accept external TCP/IP connections.

•  FATAL: no pg_hba.conf entry

•  This means your server can be contacted over the

network, but is not configured to accept the

connection. Your client is not detected as a legal

user for the database. You will have to add an

•  Right-click on a server entry to modify its properties •  Click on the trash can to remove a server’s entry

Right-click on a table and select View Data

View Data

Use the View Data or

View Filtered Data

buttons

View Filtered Data

Databases

•  The databases menu allows you to create a new database, or run a report on the databases in a cluster

•  The menu for an individual database allows you to perform operations on that database

•  Create a new object in the database •  Drop the database

•  Open the Query Tool with a script to re-create the database

•  Run reports

•  Perform maintenance •  Backup or Restore

Functions

•  Functions, Trigger Functions and Procedures are all identical except for their Return Type

•  Trigger Functions have a fixed return type of “trigger”

•  Procedures do not return anything; their return type is “void”

Rules

Rules can be applied to tables or views

Triggers

You must create a trigger function before you can create a trigger

See Also

•  For documentation on PGADMIN 3. Please find the URL below:

Summary

•  In this module you learned:

•  pgAdmin III

•  Registering a server

•  Viewing and Editing Data •  Query Tool •  Databases •  Languages •  Schemas •  Domains •  Functions •  Sequences •  Tables •  Columns •  Constraints •  Indexes •  Maintenance •  Rules •  Triggers •  Types •  Views •  Tablespaces •  Roles

Module 8

Security

Objectives

•  In this module you will learn:

•  Authentication •  Authorization •  Levels of security •  pg_hba.conf file •  Users •  Object ownership •  Access control

•  Secure access is a two step process:

•  Authentication – Ensures a user is who he/she claims to be •  Authorization - Ensures an authenticated user has access to

only the data for which he/she has been granted the appropriate privileges.

Server & Application Security pg_hba.conf

Database Object Security Schemas & Users

Table Security Grant & Revoke

•  Host based access

•  Controls (by ip / subnet) authentication mechanism •  Adds authorization control by ip / subnet

# TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only

local all all md5 # IPv4 local connections:

host customer readonly 62.124.15.9 md5 host all all 192.168.12.10/32 md5 host all all 127.0.0.1/32 trust

•  Database users are different than operating system users

•  Users can be created in SQL using “CREATE USER” command or using the “createuser” utility.

SQL Example:

CREATE USER scott PASSWORD 'tiger';

CREATE USER scott_dba CREATEDB CREATEUSER;

CREATE USER scott_temp VALID UNTIL '2006-05-01'; DROP USER scott CASCADE;

Object Ownership

Database Cluster Database

Table View Sequence

Tablespace Users

Functions Schema

Access to tables is given and taken using the GRANT and REVOKE SQL commands:

Examples:

GRANT UPDATE, DELETE ON emp TO scott_temp; GRANT ALL ON dept TO GROUP temps;

REVOKE UPDATE, DELETE ON emp FROM scott_temp; GRANT USAGE ON SCHEMA psteinhe TO SCOTT;

•  Application access is controlled by settings in both postgresql.conf and pg_hba.conf

•  Set the following parameters in postgresql.conf:

listen_addresses max_connections superuser_reserved_connections port unix_socket_directory unix_socket_group unix_socket_permissions

Application Access

Summary

•  In this module you learned:

•  Authentication & Authorization •  Levels of security

•  pg_hba.conf file •  Users

•  Object ownership •  Access control

Lab Exercise - 1

•  You are working as PostgreSQL DBA. Your server box have 2 network cards with ip addresses 1.1.1.1 and 10.1.10.1. 1.1.1.1 is used for internal LAN and 10.1.10.1 is used by the web server to connect users from external network. Your server should accept

TCP/IP connections both from internal and external users.

•  Configure your server to accept connections from external and internal networks.

Lab Exercise - 2

•  You are working as a PostgreSQL DBA. A developer showed you following error:

•  Predict the problem and suggest the solution

psql: could not connect to server: Connection refused (0x0000274D/ 10061)

Is the server running on host “1.1.1.1" and accepting TCP/IP connections on port 5432?

Lab Exercise - 3

•  A new developer has joined. His ID number is 89. Create a new user by name dev89 and password ‘password89’. Then assign necessary privileges to dev89 so that he can connect to the edbstore database and view all tables.

Lab Exercise - 4

•  A new developer joins e-music corp. He has ip address 1.1.1.89. He is not able to connect from his machine to the PostgreSQL server and gets the following error on the server

•  Configure your server so that the new developer can connect from his machine.

FATAL: no pg_hba.conf entry for host “1.1.1.89", user “dev89", database “edbstore", SSL off

Module 9

SQL Primer

Objectives

•  In this module you will learn:

•  Data Types •  Tables •  SQL Queries •  insert, delete •  update, select •  Quoting •  Using SQL Functions •  Constraints

•  Dropping or Removing Database Objects •  Views

Data Types

Name Aliases Description

boolean bool state of true or false

bigint int8 signed eight-byte integer

bigserial serial8 autoincrementing eight-byte integer

bytea binary data ("byte array")

character varying [ (n) ] varchar [ (n) ] variable-length character string

character [ (n) ] char [ (n) ] fixed-length character string

date calendar date (year, month, day)

double precision float8 double precision floating-point number (8 bytes)

integer int, int4 signed four-byte integer

interval [ fields ] [ (p) ] time span

money currency amount

numeric [ (p, s) ] decimal [ (p, s) ] exact numeric of selectable precision

smallint int2 signed two-byte integer

•  Syntax

CREATE TABLE table_name ( col_1 data_type,

col_2 data_type, … col_n data_type );

•  Example

CREATE TABLE departments(

department_id integer, name varchar(50)

);

•  Syntax

INSERT INTO table [ ( column [, ...] ) ]

{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

•  Example

INSERT INTO departments (department_id, name) VALUES (1, 'Development');

•  Syntax

INSERT INTO table [ ( column [, ...] ) ]

{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

•  Example

INSERT INTO emp (empno, ename, job)

VALUES (2,'JOHN', 'MANAGER'),(3, 'MARY', 'CLERK'), (4, 'HARRY', 'MANAGER');

•  Syntax

SELECT column_1, column_2 , … column_n FROM table

WHERE condition

ORDER BY column_list

•  Example

SELECT department_id, name FROM departments

WHERE department_id = 1 ORDER BY name;

•  Syntax

UPDATE [ ONLY ] table

SET column = {expression | DEFAULT} [,...] [ FROM fromlist ] [ WHERE condition ]

•  Example

UPDATE departments SET name='DEVELOPMENT' WHERE department_id=1;

UPDATE

•  Syntax

DELETE FROM [ ONLY ] table [ WHERE condition ]

•  Example

DELETE FROM departments WHERE department_id = 2;

•  Single quotes and dollar quotes are used to specify non-numeric values

e.g. 'hello world'

'2011-07-04 13:36:24' '{1,4,5}'

$$A string "with" various 'quotes' in.$$ $foo$A string with $$ quotes in $foo$

•  Double quotes are used for names of database objects which either clash with keywords, contain mixed case letters, or contain characters other than a-z, 0-9 or underscore.

e.g. select * from "select"

create table "HelloWorld" ...

select * from "Hi everyone & everything"

•  Can be used in SELECT statements and WHERE clauses •  Include

•  String Functions •  Format Functions

•  Date & Time Functions •  Aggregate Functions •  Example SELECT lower(name) FROM departments; SELECT *

Using SQL Functions

Format Functions

Function Return Type Description Example

to_char(timestamp, text) text convert time stamp to string to_char(current_timestamp, 'HH12:MI:SS')

to_char(interval, text) text convert interval to string to_char(interval '15h 2m 12s', 'HH24:MI:SS')

to_char(int, text) text convert integer to string to_char(125, '999')

to_char(double precision,

text) text convert real/double precision to string to_char(125.8::real, '999D9')

to_char(numeric, text) text convert numeric to string to_char(-125.8, '999D99S')

to_date(text, text) date convert string to date to_date('05 Dec 2000', 'DD Mon YYYY')

to_number(text, text) numeric convert string to numeric to_number('12,454.8-', '99G999D9S')

•  Use two vertical bar symbols ( || ) to concatenate strings together

•  Example

SELECT 'Department ' || department_id || ' is: ' || name FROM departments;

•  WHERE clauses can contain SELECT statements •  Example:

This will only return department names that have corresponding employees.

SELECT dname FROM dept WHERE deptno IN (

SELECT deptno FROM emp);

•  Inner joins are the most common

•  Only rows that have corresponding rows in the joined table are returned

•  Example:

SELECT ename, dname FROM emp, dept

WHERE emp.deptno = dept.deptno;

•  Used to make complex SQL statements easier to read •  Can also reduce the amount of typing required

•  Example:

SELECT ename, dname FROM emp e, dept d

WHERE e.deptno = d.deptno;

•  Returns all rows even if there is no corresponding row in the joined table

•  Add one of the following to the FROM clause

•  table LEFT [ OUTER ] JOIN table ON condition •  table RIGHT [ OUTER ] JOIN table ON condition •  table FULL [ OUTER ] JOIN table ON condition •  PostgreSQL syntax example:

SELECT ename, dname FROM emp

RIGHT OUTER JOIN dept

ON (emp.deptno = dept.deptno);

•  Check Constraints •  Not-Null Constraints •  Unique Constraints •  Primary Keys

•  Foreign Keys CREATE TABLE emp

(

empno numeric(4) NOT NULL, ename varchar(10), job varchar(9), mgr numeric(4), hiredate datetime, sal numeric(7,2), comm numeric(7,2), deptno numeric(2),

CONSTRAINT emp_pk PRIMARY KEY (empno),

CONSTRAINT emp_ref_dept_fk FOREIGN KEY (deptno) REFERENCES dept (deptno) ON UPDATE NO ACTION ON DELETE NO ACTION,

CONSTRAINT emp_sal_ck CHECK (sal > 0)

Constraints

•  Use the DROP command •  To remove a table:

•  Syntax:

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE]

•  Used to generate unique keys •  Syntax to create a sequence

CREATE [TEMPORARY | TEMP] SEQUENCE name [INCREMENT [ BY ] increment]

[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]

[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

•  Example

CREATE SEQUENCE department_id_seq;

•  nextval() -

Advance sequence and return new value

•  currval() -

Most recently used value for specific sequence

•  setval() -

Set next returned value for a sequence

Using Sequences

Sequence example:

INSERT INTO departments (department_id, name)

•  The optimizer treats views as a subquery

•  PostgreSQL views by default are not updatable without creating rules

CREATE OR REPLACE VIEW sales_emp AS SELECT *

FROM emp

WHERE deptno = 30;

•  Indexes are a common way to enhance performance •  PostgreSQL supports several index types:

•  B-tree (default)

•  Hash – only used when the WHERE clause contains a simple comparison using the “=“ operator (discouraged because they are not crash safe)

•  Index on Expressions – use when quick retrieval speed is needed on an often used expression. Inserts and updates will be slower.

•  Partial Index – Indexes only rows that satisfy the WHERE clause (the WHERE clause need not include the indexed column). A query must include the same WHERE clause to use the partial index.

CREATE INDEX <name> on <table> (<column>);

CREATE INDEX <name> ON <table> USING HASH (<column>); CREATE INDEX <name> on <table>(expression(<column(s)>));

CREATE INDEX <name> ON <table> (<column>) WHERE <where clause>;

Summary

•  In this module you learned:

•  Data Types •  Tables •  SQL Queries •  insert, delete •  update, select •  Using SQL Functions •  Constraints

•  Dropping or Removing Database Objects •  Views

Lab Exercise - 1

•  Test your knowledge:

1. Initiate an PSQL session

2. PSQL commands access the database. True/False

3. The following SELECT statement executes successfully:

SELECT ename, job, sal AS Salary FROM emp;

True/False

4. The following SELECT statement executes successfully:

SELECT *

FROM emp;

True/False

5. There are coding errors in the following statement. Can you identify them?

Lab Exercise - 2

•  Write a statement for following:

•  The HR department needs a report of all employees. Write a query to display the name, department number, and department name for all employees.

•  Create a report to display employees’ name and employee number along with their manager’s name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.

•  Create a report for the HR department that displays employee names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label.

Lab Exercise - 3

•  Write a query that displays the employee number and name of all employees who work in a department with any employee whose name contains a u.(use subquery)

•  Update and delete data in the EMP table.

•  Change the name of employee 7566 to Drexler.

•  Change the salary to $1,000 for all employees who have a salary less than $900.

•  Verify your changes to the table. •  Delete MILLER from the EMP table.