0% found this document useful (0 votes)

561 views274 pages

Introduction Postgre SQLAdministration V11

IntroductionPostgreSQLAdministrationV11

Uploaded by

cooljai619

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

561 views274 pages

Introduction Postgre SQLAdministration V11

IntroductionPostgreSQLAdministrationV11

Uploaded by

cooljai619

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

You are on page 1/ 274

Introduction to PostgreSQL Administration

2011 EnterpriseDB. All rights reserved.

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
Moving Data

2011 EnterpriseDB. All rights reserved.

Module 1
Introduction and Architecture

2011 EnterpriseDB. All rights reserved.

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
PostgreSQL Terminology

2011 EnterpriseDB. All rights reserved.

History of PostgreSQL

The worlds 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 SLAs available from EnterpriseDB
http://www.enterprisedb.com

2011 EnterpriseDB. All rights reserved.

PostgreSQL Lineage

2011 EnterpriseDB. All rights reserved.

Major Features
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

2011 EnterpriseDB. All rights reserved.

Major Features (continued)

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,
Supports Hot-Backup and Point-in-Time Recovery
Warm Standby
2011 EnterpriseDB. All rights reserved.

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
upgrades from 8.3 or 8.4 to 9.0.
2011 EnterpriseDB. All rights reserved.

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
readers

2011 EnterpriseDB. All rights reserved.

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
Log currently needs to log whole blocks after a checkpoint

2011 EnterpriseDB. All rights reserved.

Architectural Overview

Connectors

libpq (C API)

2011 EnterpriseDB. All rights reserved.

Database Limitations
Limitations are generally defined by
Operating System Limits
Compile-Time Parameters
Data Type Usage
General Database Limitations
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)

Maximum Indexes per Table

Unlimited

2011 EnterpriseDB. All rights reserved.

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

Column

Attribute

2011 EnterpriseDB. All rights reserved.

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
PostgreSQL Terminology

2011 EnterpriseDB. All rights reserved.

Module 2
PostgreSQL System Architecture

2011 EnterpriseDB. All rights reserved.

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
Installation Directory Layout
Page Layout
Transaction Logging (WAL) and Archiving

2011 EnterpriseDB. All rights reserved.

Architectural Summary
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)

2011 EnterpriseDB. All rights reserved.

Shared Memory Creation

2011 EnterpriseDB. All rights reserved.

Statement Processing

2011 EnterpriseDB. All rights reserved.

Utility Processes
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

2011 EnterpriseDB. All rights reserved.

More Utility Processes

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

2011 EnterpriseDB. All rights reserved.

Interprocess Communication

2011 EnterpriseDB. All rights reserved.

Disk Read Buffering

PostgreSQL buffer cache
(shared_buffers) reduces
OS reads.
Read the block once, then
examine it many times in
cache.

2011 EnterpriseDB. All rights reserved.

Shared (data) Buffers

Disk Write Buffering

Blocks are written to
disk only when needed:
To make room for
new blocks

Shared (data) Buffers

At checkpoint time

CHECKPOINT

2011 EnterpriseDB. All rights reserved.

Background Writer Cleaning Scan

Background writer scan
attempts to ensure an
adequate supply of
clean buffers.
Backends write dirty
buffers at need.

Shared (data) Buffers

BGWRITER

2011 EnterpriseDB. All rights reserved.

Commit & Checkpoint

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

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

PostgreSQL Physical Database Architecture

2011 EnterpriseDB. All rights reserved.

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)
Each file name is a number (see pg_class.relfilenode)

2011 EnterpriseDB. All rights reserved.

Data Directory Layout

Database OID
/opt/PostgreSQL/9.0/

16486

data

base
16571
pg_tblspc

16628

Table & Index Files

10235
10239
12144
12762

(tablespace OID)

(symbolic link)

/PostgreSQL/tblspace/

16486

Database 16486 has tables in both the

default and a user-defined tablespace

2011 EnterpriseDB. All rights reserved.

16732

10629
10635
16631
16636

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.
pg_env.bat/sh Set up environment.

2011 EnterpriseDB. All rights reserved.

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

2011 EnterpriseDB. All rights reserved.

Page Structure

2011 EnterpriseDB. All rights reserved.

Write Ahead Logging (WAL)

Backends write data to
WAL buffers.
Flush WAL buffers
periodically (WAL
writer), on commit, or
when buffers are full.
Group commit.

2011 EnterpriseDB. All rights reserved.

Shared (data) Buffers

WAL
Buffer

Transaction Log Archiving

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

Shared (data) Buffers

ARCHIVER

2011 EnterpriseDB. All rights reserved.

WAL
Buffer

Summary

In this session you learned:

2011 EnterpriseDB. All rights reserved.

Module 3
Installation

2011 EnterpriseDB. All rights reserved.

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

2011 EnterpriseDB. All rights reserved.

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

2011 EnterpriseDB. All rights reserved.

Installation Options
There are several installation options:
One-Click installer
Operating system package
RPM/YUM
Debian/Ubuntu DEB
FreeBSD port
Solaris package
Source code

2011 EnterpriseDB. All rights reserved.

One-Click Installation
Download the one-click installer from:
http://www.enterprisedb.com/products/pgdownload.do

2011 EnterpriseDB. All rights reserved.

Installation
Run the postgresql binary using root user.
Setup: Welcome message from the packaged installer.

2011 EnterpriseDB. All rights reserved.

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

2011 EnterpriseDB. All rights reserved.

Installation
Data Directory: Choose the location where you want to install default
database cluster.

2011 EnterpriseDB. All rights reserved.

Installation
Provide the database superuser and installation user password.
Read the instruction given in snapshot.

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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

2011 EnterpriseDB. All rights reserved.

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.
pg_ctl reload [<options>] - Reload configuration file.

2011 EnterpriseDB. All rights reserved.

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
starting the server

2011 EnterpriseDB. All rights reserved.

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=>
DBNAME and USERNAME default to the operating system user

2011 EnterpriseDB. All rights reserved.

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

2011 EnterpriseDB. All rights reserved.

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.
Log into PostgreSQL using psql.

2011 EnterpriseDB. All rights reserved.

Module 4
Configuration

2011 EnterpriseDB. All rights reserved.

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
Autovacuum Settings

2011 EnterpriseDB. All rights reserved.

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,
which is normally kept in the data directory.

2011 EnterpriseDB. All rights reserved.

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
Can also be specified as command line option

2011 EnterpriseDB. All rights reserved.

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
pg_settings catalog table lists settings information

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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
between checkpoints.

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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.
log_temp_files (default -1): Log temporary files of this size or
larger, in kilobytes.

2011 EnterpriseDB. All rights reserved.

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.
Primary tuning technique is to lower bgwriter_delay.

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

Summary
In this module you learned:

Setting PostgreSQL Parameters

2011 EnterpriseDB. All rights reserved.

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
Maximum time to complete client authentication will be 10 seconds

2011 EnterpriseDB. All rights reserved.

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

2011 EnterpriseDB. All rights reserved.

Module 5
Creating and Managing PostgreSQL
Databases

2011 EnterpriseDB. All rights reserved.

Objectives
In this module you will learn:

Object Hierarchy
Creating Databases
Creating Schemas
Schema Search Path
Roles
Users
Groups
Access Control

2011 EnterpriseDB. All rights reserved.

Object Hierarchy
Database Cluster
Role/Groups

Database

(Users)

Tablespace

Schema

Table

Column

Constraint

2011 EnterpriseDB. All rights reserved.

View

Sequence

Index

Trigger

Functions

Rule

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;
PSQL META COMMAND: \l (backslash lowercase L)

2011 EnterpriseDB. All rights reserved.

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 ]
[ CONNECTION LIMIT [=] connlimit ] ]

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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;

2011 EnterpriseDB. All rights reserved.

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

2011 EnterpriseDB. All rights reserved.

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:

SET search_path TO myschema, public;

2011 EnterpriseDB. All rights reserved.

Users
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;

2011 EnterpriseDB. All rights reserved.

Groups
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;

2011 EnterpriseDB. All rights reserved.

Create Users and Groups

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.

2011 EnterpriseDB. All rights reserved.

Roles
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

2011 EnterpriseDB. All rights reserved.

Object Ownership
Database Cluster
Database

Users

Tablespace
Schema

Table

2011 EnterpriseDB. All rights reserved.

View

Sequence

Functions

Access Controls
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;

2011 EnterpriseDB. All rights reserved.

Summary
In this module you learned:

Object Hierarchy
Creating Databases
Creating Schemas
Schema Search Path
Roles
Users
Groups
Access Control

2011 EnterpriseDB. All rights reserved.

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
psql U edbstore d edbstore f edbstore.sql

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

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.

2011 EnterpriseDB. All rights reserved.

Module 6
PSQL

2011 EnterpriseDB. All rights reserved.

Objectives
This module will cover:

PSQL Command Line Parameters

Entering PSQL Commands
PSQL Meta-Commands
PSQL SET Parameters

2011 EnterpriseDB. All rights reserved.

Conventions
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

2011 EnterpriseDB. All rights reserved.

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=>

DBNAME and USERNAME default to the operating system user

2011 EnterpriseDB. All rights reserved.

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,
except on Windows, where a local TCP connection will be used.

2011 EnterpriseDB. All rights reserved.

100

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

2011 EnterpriseDB. All rights reserved.

101

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
\w FILENAME will save the query buffer to FILENAME

2011 EnterpriseDB. All rights reserved.

102

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;
testing

2011 EnterpriseDB. All rights reserved.

103

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

2011 EnterpriseDB. All rights reserved.

104

Output (contd)
\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

2011 EnterpriseDB. All rights reserved.

105

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
+ adds owner, language, source code and description to output

2011 EnterpriseDB. All rights reserved.

106

Information Commands (contd)

\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
without a pattern, \d[+] is equivalent to \dtvs[+]

2011 EnterpriseDB. All rights reserved.

107

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)

2011 EnterpriseDB. All rights reserved.

108

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
Lists command line options for psql

2011 EnterpriseDB. All rights reserved.

109

Summary
In this module you learned about:

Command Line Parameters

Entering Commands
Meta-Commands
Set Parameters

2011 EnterpriseDB. All rights reserved.

110

Lab Exercise 1
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

Connect to database using psql

Switch databases.
Describe the customers table.
Describe the customers table including description.
List all databases.
List all schemas.
List all tablespaces.
Execute an sql statement, saving the output to a file.
Do the same thing, just saving data, not the column headers.
Create a script via another method, and execute from psql.
Display the dept table in extended format.
Create a script that will not display all data but will echo the table name
and show how long the statement took.

2011 EnterpriseDB. All rights reserved.

111

Module 7
PGADMIN III

2011 EnterpriseDB. All rights reserved.

112

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

2011 EnterpriseDB. All rights reserved.

113

Columns
Constraints
Indexes
Maintenance
Rules
Triggers
Types
Views
Tablespaces
Roles

Introduction to pgAdmin III

2011 EnterpriseDB. All rights reserved.

114

Registering a Server
Click on the plug icon to add a server

2011 EnterpriseDB. All rights reserved.

115

Common Connection Problems

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
entry for each of your clients to the pg_hba.conf
file.
2011 EnterpriseDB. All rights reserved.

116

Changing a Servers Registration

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

2011 EnterpriseDB. All rights reserved.

117

Viewing Data
Right-click on a table and select View Data

2011 EnterpriseDB. All rights reserved.

118

Viewing Data
View Filtered Data

Use the View Data or

View Filtered Data
buttons

View Data

2011 EnterpriseDB. All rights reserved.

119

Sorting and Filtering Data

2011 EnterpriseDB. All rights reserved.

120

Query Tool

2011 EnterpriseDB. All rights reserved.

121

Query Tool - Data Output

2011 EnterpriseDB. All rights reserved.

122

Query Tool - Explain

2011 EnterpriseDB. All rights reserved.

123

Query Tool - Messages

2011 EnterpriseDB. All rights reserved.

124

Query Tool - History

2011 EnterpriseDB. All rights reserved.

125

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
Modify the databases properties

2011 EnterpriseDB. All rights reserved.

126

Creating a Database

2011 EnterpriseDB. All rights reserved.

127

Creating a Database

2011 EnterpriseDB. All rights reserved.

128

Databases Report

2011 EnterpriseDB. All rights reserved.

129

Backup and Restore

2011 EnterpriseDB. All rights reserved.

130

Schemas

2011 EnterpriseDB. All rights reserved.

131

Schemas

2011 EnterpriseDB. All rights reserved.

132

Schemas - Grant Wizard

2011 EnterpriseDB. All rights reserved.

133

Domains

2011 EnterpriseDB. All rights reserved.

134

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

2011 EnterpriseDB. All rights reserved.

135

Sequences

2011 EnterpriseDB. All rights reserved.

136

Tables

2011 EnterpriseDB. All rights reserved.

137

Tables - Columns

2011 EnterpriseDB. All rights reserved.

138

Tables - Constraints

2011 EnterpriseDB. All rights reserved.

139

Tables - Indexes

2011 EnterpriseDB. All rights reserved.

140

Tables - Maintenance

2011 EnterpriseDB. All rights reserved.

141

Rules
Rules can be applied to
tables or views

2011 EnterpriseDB. All rights reserved.

142

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

2011 EnterpriseDB. All rights reserved.

143

Views

2011 EnterpriseDB. All rights reserved.

144

Tablespaces

2011 EnterpriseDB. All rights reserved.

145

Tablespaces

2011 EnterpriseDB. All rights reserved.

146

Roles

2011 EnterpriseDB. All rights reserved.

147

See Also
For documentation on PGADMIN 3. Please find the URL below:
http://www.enterprisedb.com/docs/en/9.0/pgadmin/using.html

2011 EnterpriseDB. All rights reserved.

148

Summary
In this module you learned:
pgAdmin III
Registering a server
Viewing and Editing Data
Query Tool
Databases
Languages
Schemas
Domains
Functions
Sequences
Tables

2011 EnterpriseDB. All rights reserved.

149

Columns
Constraints
Indexes
Maintenance
Rules
Triggers
Types
Views
Tablespaces
Roles

Module 8
Security

2011 EnterpriseDB. All rights reserved.

150

Objectives
In this module you will learn:

Authentication
Authorization
Levels of security
pg_hba.conf file
Users
Object ownership
Access control
Application access parameters

2011 EnterpriseDB. All rights reserved.

151

Authentication and Authorization

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.

2011 EnterpriseDB. All rights reserved.

152

Levels of Security

Server & Application Security

pg_hba.conf
Database Object Security
Schemas & Users
Table Security
Grant & Revoke

2011 EnterpriseDB. All rights reserved.

153

pg_hba.conf Access control

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
host
all
all
host
all
all

62.124.15.9
192.168.12.10/32
127.0.0.1/32

md5
md5
trust

# IPv6 local connections:

host
all
all

::1/128

password

2011 EnterpriseDB. All rights reserved.

154

Users
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;

2011 EnterpriseDB. All rights reserved.

155

Object Ownership
Database Cluster
Database

Users

Tablespace
Schema

Table

2011 EnterpriseDB. All rights reserved.

View

Sequence

156

Functions

Access Controls
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;

2011 EnterpriseDB. All rights reserved.

157

Application Access
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

2011 EnterpriseDB. All rights reserved.

158

Summary
In this module you learned:

Authentication & Authorization

Levels of security
pg_hba.conf file
Users
Object ownership
Access control
Application access parameters

2011 EnterpriseDB. All rights reserved.

159

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.

2011 EnterpriseDB. All rights reserved.

160

Lab Exercise - 2
You are working as a PostgreSQL DBA. A developer showed you
following error:
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?

Predict the problem and suggest the solution

2011 EnterpriseDB. All rights reserved.

161

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.

2011 EnterpriseDB. All rights reserved.

162

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

FATAL: no pg_hba.conf entry for host 1.1.1.89", user dev89", database

edbstore", SSL off

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

2011 EnterpriseDB. All rights reserved.

163

Module 9
SQL Primer

2011 EnterpriseDB. All rights reserved.

164

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
Sequences
Indexes

2011 EnterpriseDB. All rights reserved.

165

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

serial

serial4

autoincrementing four-byte integer

text

variable-length character string

time [ (p) ] [ without time zone ]

time of day (no time zone)

time [ (p) ] with time zone

timetz

timestamp [ (p) ] [ without time zone ]

timestamp [ (p) ] with time zone
2011 EnterpriseDB. All rights reserved.

time of day, including time zone

date and time (no time zone)

timestamptz

date and time, including time zone

166

CREATE TABLE (simple form)

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)
);

2011 EnterpriseDB. All rights reserved.

167

INSERT
Syntax
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT }
[, ...] ) | query }

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

2011 EnterpriseDB. All rights reserved.

168

Multi Value INSERT

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');

2011 EnterpriseDB. All rights reserved.

169

Simple SELECT
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;

2011 EnterpriseDB. All rights reserved.

170

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

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

2011 EnterpriseDB. All rights reserved.

171

DELETE
Syntax
DELETE FROM [ ONLY ] table
[ WHERE condition ]

Example
DELETE FROM departments
WHERE department_id = 2;

2011 EnterpriseDB. All rights reserved.

172

Quoting
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"

2011 EnterpriseDB. All rights reserved.

173

Using SQL Functions

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 *
FROM departments
WHERE lower(name) = 'development';

2011 EnterpriseDB. All rights reserved.

174

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')

to_timestamp(text, text)

timestamp with time

zone

convert string to time stamp

to_timestamp('05 Dec 2000',

'DD Mon YYYY')

to_timestamp(double
precision)

timestamp with time

zone

convert Unix epoch to time stamp

to_timestamp(1284352323)

2011 EnterpriseDB. All rights reserved.

175

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

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

2011 EnterpriseDB. All rights reserved.

176

Nested SELECT Statements

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);

2011 EnterpriseDB. All rights reserved.

177

Inner Joins
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;

2011 EnterpriseDB. All rights reserved.

178

Column and Table Aliases

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;

2011 EnterpriseDB. All rights reserved.

179

Outer Joins
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);

2011 EnterpriseDB. All rights reserved.

180

Constraints

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

2011 EnterpriseDB. All rights reserved.

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)
)

181

Dropping or Removing Database Objects

Use the DROP command
To remove a table:
Syntax:
DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE]

2011 EnterpriseDB. All rights reserved.

182

Sequences
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;

2011 EnterpriseDB. All rights reserved.

183

Sequence / Serial Functions

nextval() - Advance sequence and return new value
currval() - Most recently used value for specific sequence
setval() - Set next returned value for a sequence

2011 EnterpriseDB. All rights reserved.

184

Using Sequences
Sequence example:
INSERT INTO departments (department_id, name)
VALUES (nextval('department_id_seq'),'MARKETING');

2011 EnterpriseDB. All rights reserved.

185

Using Views
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;

2011 EnterpriseDB. All rights reserved.

186

Indexes
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
CREATE
CREATE
CREATE

INDEX
INDEX
INDEX
INDEX

2011 EnterpriseDB. All rights reserved.

on
ON
on
ON

<table> (<column>);
<table> USING HASH (<column>);
<table>(expression(<column(s)>));
<table> (<column>) WHERE <where clause>;

187

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
Sequences
Indexes

2011 EnterpriseDB. All rights reserved.

188

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?
SELECT
sal x 12
FROM
2011 EnterpriseDB. All rights reserved.

empno, ename
ANNUAL SALARY
emp;
189

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 managers 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.

2011 EnterpriseDB. All rights reserved.

190

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.

2011 EnterpriseDB. All rights reserved.

191

Lab Exercise - 4
Create the EMP2 table based on the structure of the EMP table. Include only
the EMPLOYEE_ID, NAME, SALARY, and DEPARTMENT_ID columns. Name
the columns in your new table ID, FIRST_NAME, SALARY , and DEPTID,
respectively.
The staff in the HR department wants to hide some of the data in the EMP
table. They want a view called EMPVU based on the employee numbers,
employee names, and department numbers from the EMP table. They want
the heading for the employee name to be EMPLOYEE.
Confirm that the view works. Display the contents of the EMPVU view.
Using your EMPVU view, write a query for the HR department to display all
employee names and department numbers.

2011 EnterpriseDB. All rights reserved.

192

Lab Exercise - 5
You need a sequence that can be used with the primary key
column of the DEPT table. The sequence should start at 60 and
have a maximum value of 90. Have your sequence increment by
10. Name the sequence DEPT_ID_SEQ.
To test your sequence, write a script to insert two rows in the DEPT
table.
Create a index on department_id column of department table.
Create and test a partial index.

2011 EnterpriseDB. All rights reserved.

193

Module 10
Backup, Recovery and PITR

2011 EnterpriseDB. All rights reserved.

194

Objectives
In this module you will learn:

Backup Types
SQL Dump
Cluster Dump
Offline Copy Backup
Continuous Archiving
Point-In Time Recovery
pg_upgrade

2011 EnterpriseDB. All rights reserved.

195

Backup
As with any database, PostgreSQL database should be backed up
regularly.
There are three fundamentally different approaches to backing up
PostgreSQL data:
SQL dump
File system level backup
Continuous Archiving
Let's discuss them in detail.

2011 EnterpriseDB. All rights reserved.

196

Backup SQL Dump

Generate a text file with SQL commands
PostgreSQL provides the utility program pg_dump for this purpose.
pg_dump does not block readers or writers.
pg_dump does not operate with special permissions.
Dumps created by pg_dump are internally consistent, that is, the
dump represents a snapshot of the database as of the time
pg_dump begins running.
Syntax:
pg_dump [options] [dbname]

197

Backup SQL Dump

pg_dump Options
-a Data only. Do not dump the data definitions (schema)
-s Data definitions (schema) only. Do not dump the data
-n <schema> - Dump from the specified schema only
-t <table> - Dump specified table only
-f <path/file name.backup> - Send dump to specified file
-Fp Dump in plain-text SQL script (default)
-Ft Dump in tar format
-Fc Dump in compressed, custom format
-v Verbose option
-o use oids

198

SQL Dump Large Databases

If operating systems have maximum file size limits, it
cause problems when creating large pg_dump output
files.
Standard Unix tools can be used to work around this
potential problem.
You can use your favorite compression program, for example
gzip:
pg_dump dbname | gzip > filename.gz

Also the split command allows you to split the output into
smaller files:
pg_dump dbname | split -b 1m - filename

199

Restore SQL Dump

The text files created by pg_dump are intended to be read in by the psql
program. The general command form to restore a dump is
psql dbname < infile

where infile is what you used as outfile for the pg_dump command. The
database dbname will not be created by this command, so you must create
it yourself.
pg_restore is used to restore a database backed up with pg_dump that was
saved in an archive format i.e., a non-text format
Files are portable across architectures
Syntax:
pg_restore [options] [filename.backup]

200

Restore SQL Dump

Syntax:
psql d template1 < filename.backup
or
psql d template1 f filename.backup
Note: Any database in the cluster can be used for the initial
connection it doesnt have to be template1

201

Restore SQL Dump

pg_restore Options
-d <database name> - Connect to the specified database. Also restores to this
database if C option is omitted
-C Create the database named in the dump file & restore directly into it
-a Restore the data only, not the data definitions (schema)
-s Restore the data definitions (schema) only, not the data
-n <schema> - Restore only objects from specified schema
-t <table> - Restore only specified table
-v Verbose option

202

Entire Cluster SQL Dump

pg_dumpall is used to dump an entire database cluster in plaintext SQL format
Dumps global objects - user, groups, and associated permissions
Use PSQL to restore
Syntax:
pg_dumpall [options] > filename.backup

203

Entire Cluster SQL Dump

pg_dumpall Options
-a, Data only. Do not dump schema.
-s, Data definitions (schema) only.
-g, Dump global objects only - not databases.
-c, Clean (drop) databases before recreating.
-O, Skip restoration of object ownership.
-x, do not dump privileges (grant/revoke)
--disable-triggers, disable triggers during data-only restore
-v Verbose option.

204

Backup - File system level backup

An alternative backup strategy is to directly copy the files that
PostgreSQL uses to store the data in the database.
You can use whatever method you prefer for doing usual file system
backups, for example:
tar -cf backup.tar /usr/local/pgsql/data

The database server must be shut down in order to get a usable

backup.
File system backups only work for complete backup and restoration
of an entire database cluster.
File system snapshots work for live servers.

205

Backup - Continuous Archiving

PostgreSQL maintains WAL files for all transactions in
pg_xlog directory
PostgreSQL automatically maintains the WAL logs
which are full and switched
Continuous archiving can be setup to keep a copy of
switched WAL Logs which can be later used for
recovery.
It also enables online file system backup of a
database cluster.
Requirements:
wal_level must be set to archive
archive_mode must be set to on
archive_command must be set in postgresql.conf which
archives WAL logs and supports PITR
2011 EnterpriseDB. All rights reserved.

206

Backup - Continuous Archiving

Step 1: Edit the postgresql.conf file and set the archive parameters:
wal_level=archive
archive_mode=on
Unix:
archive_command= cp i %p /mnt/server/archivedir/%f </dev/null
Windows:
archive_command= 'copy "%p" c:\\mnt\\server\\archivedir\\"%f"'

%p is the absolute path of WAL otherwise you can define the path
%f is a unique file name which will be created on above path.

207

Online Base File System Level Backup

Step 2: Make a base backup
Connect using psql and issue the command:
SELECT pg_start_backup(any useful label);
Use a standard file system backup utility to back up the /data
subdirectory
Connect using psql and issue the command:
SELECT pg_stop_backup();
Continuously archive the WAL segment files

208

Point-in-Time Recovery
Point-in-time recovery (PITR) is the ability to restore a database
cluster up to the present or to a specified point of time in the past
Uses a full database cluster backup and the write-ahead logs
found in the /pg_xlog subdirectory
Must be configured before it is needed (write-ahead log archiving
must be enabled)

209

Point-in-Time Recovery
Final Step: Recovering the database
Clean out all existing files in the /data directory and subdirectories (be sure
to backup configuration files if you have not already done so)
Restore the database files from the backup dump
Copy any unarchived WAL files into the /pg_xlog directory
Create a recovery.conf file in the /data directory
Restart the database server

210

Point-in-Time Recovery
Settings in the recovery.conf file:
restore_command(string)

Unix:
restore_command = 'cp /mnt/server/archivedir/%f "%p"
Windows:
restore_command = 'copy c:\\mnt\\server\\archivedir\\"%f"
"%p"'
recovery_target_time(timestamp)
recovery_target_xid(string)
recovery_target_inclusive(boolean)

211

pg_upgrade
Typically pg_dump/reload is required for major version upgrades
(e.g. 8.3.X to 8.4.X, or 8.4.X to 9.0.X)
pg_upgrade is used to migrate PostgreSQL data files to a later
PostgreSQL major version without the data dump/reload
pg_upgrade does its best to make sure the old and new clusters
are binary-compatible, e.g. by checking for compatible compiletime settings, including 32/64-bit binaries.
It is important that any external modules are also binary
compatible, though this cannot be checked by pg_upgrade.
pg_upgrade supports upgrades from 8.3.X and later to the
current major release of PostgreSQL.

212

pg_upgrade
You can use pg_upgrade utility for migrating old cluster data
directories to new version.
Syntax:
pg_upgrade [OPTIONS]...

Options:
-b, --old-bindir
-B, --new-bindir
-c, --check
-d, --old-datadir
-D, --new-datadir
-l, --logfile
-p, --old-port
-P, --new-port
-u, --user
-v, --verbose

old cluster executable directory

new cluster executable directory
check clusters only, don't change any data
old cluster data directory
new cluster data directory
log session activity to file
old cluster port number (default 5432)
new cluster port number (default 5432)
clusters superuser (default "postgres")
enable verbose output

213

pg_upgrade
Before running pg_upgrade you must:
create a new database cluster (using the new version of initdb)
shutdown the postmaster servicing the old cluster
shutdown the postmaster servicing the new cluster

When you run pg_upgrade, you must provide the following

information:

the data directory for the old cluster (-d OLDDATADIR)

the data directory for the new cluster (-D NEWDATADIR)
the 'bin' directory for the old version (-b OLDBINDIR)
the 'bin' directory for the new version (-B NEWBINDIR)

For example:
pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin
-B newCluster/bin

214

Summary
In this module we learned:

Backup Types
SQL Dump
Cluster Dump
Offline Copy Backup
Continuous Archiving
Point-In Time Recovery
pg_upgrade

215

Lab Exercise -1
EDBStore website database is all setup and as a DBA you need to
plan a proper backup strategy and implement it.
As the root user, create a folder /PostgreSQL_backup and assign
ownership to PostgreSQL user using chown utility or windows
security tab in folder properties.
Take a full database dump of the edbstore database with the
pg_dump utility. The dump should be in plain text format.
Name the dump file as edbstore_full.sql and store it in the
PostgreSQL_backup folder.

216

Lab Exercise - 2
Take a schema-only dump of the edbstore database and name the
file as edbstore_schema.sql,
Take a data-only dump of the edbstore database, disable all triggers
for faster restore, use the insert command instead of copy, and
name the file as edbstore_data.sql
Take a full dump of only the customers table and name the file as
edbstore_customers.sql

217

Lab Exercise - 3
Take a full database dump of the edbstore in compressed format
using the pg_dump utility, name the file as edbstore_full_fc.dmp
Take a full database cluster dump using pg_dumpall. Remember
pg_dumpall supports only plain text format; name the file
edbdata.sql

218

Lab Exercise - 4
Drop database edbstore.
Create database edbstore with edbstore owner.
Restore the full dump from edbstore_full.sql and verify all the objects
and their ownership.
Drop database edbstore.
Create database edbstore with edbstore owner.
Restore the full dump from compressed file edbstore_full_fc.dmp
and verify all the objects and their ownership.

219

Lab Exercise - 5
Create a directory /opt/arch or c:\arch and give ownership to
PostgreSQL user.
Open postgresql.conf file of your edbdata cluster and configure
your cluster to run in archive mode and archive log location to be /
opt/arch or c:\arch.
Take a full online base backup of your cluster in
PostgreSQL_backup directory.

220

Module 11
Routine Maintenance Tasks

221

Objectives
In this module you will learn:

Explain and Explain Analyze

Table Statistics
Updating Planner Statistics
Vacuuming
Scheduling Auto Vacuum
Preventing Transaction ID Wraparound Failures
Routine Reindexing

222

Explain and Explain Analyze

Explain Show execution plan of the query.
PostgreSQL devises a query plan for each query it is given.
Choosing the right plan to match the query structure and the
properties of the data is absolutely critical for good performance,
so the system includes a complex planner that tries to select
good plans.
You can use the EXPLAIN command to see what query plan the
planner creates for any query.
Syntax:
Explain query;

223

Explain and Explain Analyze

The output of EXPLAIN has one line for each node in the plan
tree, showing the basic node type plus the cost estimates that
the planner made for the execution of that plan node. The first
line (topmost node) has the estimated total execution cost for
the plan; it is this number that the planner seeks to minimize.
Its also important to realize that the cost only reflects things that
the planner cares about. In particular, the cost does not consider
the time spent transmitting result rows to the client, which could
be an important factor in the true elapsed time; but the planner
ignores it because it cannot change it by altering the plan.

224

Explain and Explain Analyze

Example
postgres=# explain select * from emp;

QUERY PLAN
----------------------------------------------------- Seq Scan on emp (cost=0.00..1.14 rows=14 width=135)

The numbers that are quoted by EXPLAIN are:

Estimated start-up cost (Time expended before output scan can start, e.g.,
time to do the sorting in a sort node.)
Estimated total cost (If all rows were to be retrieved, though they might not
be: for example, a query with a LIMIT clause will stop short of paying the
total cost of the Limit plan nodes input node.)
Estimated number of rows output by this plan node (Again, only if executed
to completion.)
Estimated average width (in bytes) of rows output by this plan node

225

Table statistics
As we saw in the previous section, the query planner
needs to estimate the number of rows retrieved by a
query in order to make good choices of query plans.
Here is need of Table statistics.
One component of the statistics is the total number of entries
in each table and index, as well as the number of disk blocks
occupied by each table and index.
This information is kept in the table pg_class, in the columns
reltuples and relpages.

226

Updating Planner Statistics

Table Statistics
Updated when an ANALYZE is run
Stored in pg_class and pg_statistics
You can run the ANALYZE command from psql on specific
tables and just specific columns
Autovacuum will run ANALYZE as needed (on older
versions, run ANALYZE once in a day at low usage time)
Syntax for ANALYZE
Analyze [Table]

227

Vacuuming
An UPDATE or DELETE of a row does not
immediately remove the old version of the row. This
approach is necessary to gain the benefits of MultiVersion Concurrency Control
But eventually, an outdated or deleted row version is
no longer of interest to any currently running
transaction. The space it occupies must be reclaimed
for reuse by new rows, to avoid excessive growth of
disk space requirements.
This is done by running VACUUM.

228

Vacuuming
PostgreSQLs VACUUM command has to run on a regular basis
for several reasons:
1. To recover or reuse disk space occupied by updated or deleted
rows.
2. To protect against loss of very old data due to transaction ID
wraparound.

The standard form of VACUUM can run in parallel with

production database operations. Commands such as SELECT,
INSERT, UPDATE, and DELETE will continue to function as
normal.

229

Vacuuming
There are two variants of the VACUUM command.
The first form is the VACUUM command:
marks dead data in tables and indexes for future reuse
It does not attempt to reclaim the space used by this dead data
unless the space is at the end of the table and an exclusive table
lock can be easily obtained.

The second form is the VACUUM FULL command:

This uses a more aggressive algorithm for reclaiming the space
consumed by dead row versions.
Any space that is freed by VACUUM FULL is immediately returned
to the operating system.
Acquires an exclusive lock on each table while VACUUM FULL is
processing it.
Syntax: VACUUM [ FULL | FREEZE ] [ VERBOSE ] ANALYZE [ table
[ (column [, ...] ) ] ]

230

Autovacuum
PostgreSQL has an optional but highly recommended
feature called autovacuum.
It automates the execution of VACUUM and
ANALYZE commands.
When enabled, autovacuum checks for tables that
have had a large number of inserted, updated or
deleted tuples.
It is controlled by the thresholds and scale factors
which are taken from postgresql.conf

231

Preventing Transaction ID Wraparound Failures

MVCC transaction semantics depend on being able to compare
transaction ID (XID) numbers: a row version with an insertion XID
greater than the current transaction's XID is "in the future" and
should not be visible to the current transaction. But since
transaction IDs have limited size (32 bits at this writing) a cluster
that runs for a long time (more than 4 billion transactions) would
suffer transaction ID wraparound.
To avoid this, every table in the database must be vacuumed at
least once every billion transactions.

232

Routine Reindexing
In some situations it is worthwhile to rebuild indexes periodically
with the REINDEX command.
Index pages that have become completely empty are reclaimed
for re-use.
The potential for bloat is not indefinite at worst there will be
one key per page but it may still be worthwhile to schedule
periodic reindexing for indexes that have such usage patterns.
REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]

233

Summary
In this module you learned:

Explain and Explain Analyze

Table Statistics
Updating Planner Statistics
Vacuuming
Scheduling Auto Vacuum
Preventing Transaction ID Wraparound Failures
Routine Reindexing

234

Lab Exercise - 1
Create an explain plan for following query

Select * from emp where empno = 1

Select * from emp where empno > 1
Select * from emp where empno is not null
What is difference between different explain plans.

235

Lab Exercise - 2
Write a statement to vacuum whole database
including all tables.
The customers table is very heavily used in DML
operations which results in lots of obsolete rows in
the table. Execute a command to remove all such
rows in order to improve performance.

236

Module 12
The PostgreSQL Data Dictionary

237

Objectives
In this module you will learn:
The System Catalog Schema
System Information views/tables
System Information Functions

238

The System Catalog Schema

In addition to public and user-created schemas, each
database contains a pg_catalog schema, which
contains the system tables and all the built-in data
types, functions, and operators.
pg_catalog is always effectively part of the search
path. If it is not named explicitly in the path then it is
implicitly searched before searching the paths
schemas.

239

System Information views/tables

\dS will give you the list of pg_* tables that reside in
pg_catalog schema and gives you system
information. Some of important tables are:
pg_tables list of tables
pg_constraints list of constraints
pg_indexes list of indexes
pg_trigger list of triggers
pg_views list of views

240

System Information Functions

current_database, current_schema, inet_client_addr,
inet_client_port, inet_server_addr, inet_server_port,
pg_postmaster_start_time, version
current_user, user - user used for permission checking,

must be called without ()

session_user - normally user who started the session, but
superusers can change
current_schemas(boolean) - returns array of schemas in
the search path, optionally including implicit schemas

241

System Administration Functions

current_setting, set_config - return or modify configuration
variables

pg_cancel_backend - cancel a backend's current query

pg_terminate_backend terminates backend process.
pg_reload_conf - reload configuration files
pg_rotate_logfile - rotate the server's log file
pg_start_backup, pg_stop_backup - used with Point In
Time Recovery

242

System Administration Functions

pg_*_size - disk space used by a tablespace, database,

relation or total_relation (includes indexes and toasted data)

pg_column_size - bytes used to store a particular value
pg_size_pretty - convert a raw size to something more
human-readable
pg_ls_dir, pg_read_file, pg_stat_file - file operation
functions.
Restricted to superuser use and only on files in the data or log
directories

243

System Administration Functions

pg_stat_activity - details of open connections and running
transactions

pg_locks - list of current locks being held

pg_stat_database - details of databases
pg_stat_user_* - details of tables, indexes and functions

244

Summary
In this module you learned about:
The System Catalog Schema
System Information views/tables
System Information Functions

245

Module 13
Moving Data

246

Objectives
In this module you will learn:
The COPY Command
Examples

247

The COPY Command

COPY TO Command Syntax
COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ HEADER ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ] ]

(See documentation for complete option list)

Note that from PostgreSQL 9.0, a new syntax is used, but the above syntax will still
work.

248

The COPY Command

COPY FROM Command Syntax
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ]
[ BINARY ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ] ]

(See documentation for complete option list)

Note that from PostgreSQL 9.0, a new syntax is used, but the above syntax will
still work.

249

Example of COPY TO

edb=# COPY emp (empno,ename,job,sal,comm,hiredate) TO '/tmp/emp.csv' CSV HEADER;

COPY

edb=# \! cat /tmp/emp.csv

empno,ename,job,sal,comm,hiredate
7369,SMITH,CLERK,800.00,,17-DEC-80 00:00:00
7499,ALLEN,SALESMAN,1600.00,300.00,20-FEB-81 00:00:00
7521,WARD,SALESMAN,1250.00,500.00,22-FEB-81 00:00:00
7566,JONES,MANAGER,2975.00,,02-APR-81 00:00:00
7654,MARTIN,SALESMAN,1250.00,1400.00,28-SEP-81 00:00:00
7698,BLAKE,MANAGER,2850.00,,01-MAY-81 00:00:00
7782,CLARK,MANAGER,2450.00,,09-JUN-81 00:00:00
7788,SCOTT,ANALYST,3000.00,,19-APR-87 00:00:00
7839,KING,PRESIDENT,5000.00,,17-NOV-81 00:00:00
7844,TURNER,SALESMAN,1500.00,0.00,08-SEP-81 00:00:00
7876,ADAMS,CLERK,1100.00,,23-MAY-87 00:00:00
7900,JAMES,CLERK,950.00,,03-DEC-81 00:00:00
7902,FORD,ANALYST,3000.00,,03-DEC-81 00:00:00
7934,MILLER,CLERK,1300.00,,23-JAN-82 00:00:00

250

Example of COPY FROM

edb=# CREATE TEMP TABLE empcsv (LIKE emp);

CREATE TABLE
edb=# COPY empcsv (empno, ename, job, sal, comm, hiredate)
edb-# FROM '/tmp/emp.csv' CSV HEADER;
COPY
edb=# SELECT * FROM empcsv;
empno | ename | job | mgr |
hiredate
| sal | comm | deptno
-------+--------+-----------+-----+--------------------+---------+---------+-------7369 | SMITH | CLERK | | 17-DEC-80 00:00:00 | 800.00 |
|
7499 | ALLEN | SALESMAN | | 20-FEB-81 00:00:00 | 1600.00 | 300.00 |
7521 | WARD | SALESMAN | | 22-FEB-81 00:00:00 | 1250.00 | 500.00 |
7566 | JONES | MANAGER | | 02-APR-81 00:00:00 | 2975.00 |
|
7654 | MARTIN | SALESMAN | | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |
7698 | BLAKE | MANAGER | | 01-MAY-81 00:00:00 | 2850.00 |
|
7782 | CLARK | MANAGER | | 09-JUN-81 00:00:00 | 2450.00 |
|
7788 | SCOTT | ANALYST | | 19-APR-87 00:00:00 | 3000.00 |
|
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 |
|
7844 | TURNER | SALESMAN | | 08-SEP-81 00:00:00 | 1500.00 | 0.00 |
7876 | ADAMS | CLERK | | 23-MAY-87 00:00:00 | 1100.00 |
|
7900 | JAMES | CLERK | | 03-DEC-81 00:00:00 | 950.00 |
|
7902 | FORD | ANALYST | | 03-DEC-81 00:00:00 | 3000.00 |
|
7934 | MILLER | CLERK | | 23-JAN-82 00:00:00 | 1300.00 |
|
(14 rows)

251

Another example
Copy command on remote host using psql
cat emp.csv | ssh 192.168.192.83 psql U edbstore edbstore -c
copy emp from stdin;

252

Summary
In this Module we covered:
The COPY Command
Examples

253

Lab Exercise 1
1.
2.
3.

Unload a table in the edbstore schema to a csv file, with a header

and using a pipe (|) delimiter.
Load the table to a temp tables of similar structure.
Try unloading and loading only using certain columns.

254

Lab Exercise - 2
Write a command to copy the customers table data in CSV format to
a file.

255

Module 14
Postgres Plus Advanced Server

256

What is Postgres Plus Advanced Server ?

Built on PostgreSQL
A super set of PostgreSQL
Enhanced Performance
Oracle Compatibility
Enhanced Management
Available for trial from EnterpriseDB
Download for trial from http://www.enterprisedb.com/
products/download.do

257

Performance Enhancements - DynaTune

DynaTune
Automatically tunes the database based on the server hardware
and user inputs

258

Performance - Infinite Cache

Allows for horizontal scaling of database deployments
Database Server can take advantage of additional memory on local
and remote machines
No changes required to application code (unlike memcached)
Allows for compressed data to be stored in the cache enabling
benefits on a single server
Can see significant performance benefits without additional
servers
Trade of CPU resources for memory resources

259

Performance - Infinite Cache - Continued

Expands available memory for DB cache to memory on remote
machines

260

Performance Enhancements Query Optimizer Hints

Allow Application Developer to influence the SQL Execution of plans
queries
Override PostgreSQLs default optimization strategy
Example: Over-ride sequential scan and use emp_pk index

261

Performance EDB*Loader
A high speed bulk loading utility for basic text files
Can see significant performance benefits over PostgreSQL COPY
command (over 100% in many cases)
If a single row in a file loaded via COPY command is invalid, entire
load is rejected. EDB*Loader logs errors and loads remaining valid
roads.
Bypasses much of the SQL processing as part of a load operation

262

Performance EDB*Loader
Direct path loading for
increased performance

263

Oracle Compatibility
Postgres Plus Advanced Server enables Oracle compatibility
Allows Oracle databases to be replicated to Postgres Plus Advanced
Server
Enables Oracle DBAs and Oracle developers to leverage their skills
Applications can run unchanged on Oracle
Support for Packages, Stored Procedures, Triggers and more

264

Compatibility Details
Oracle compatible data types to define the applications database
tables.
SQL statements that are compatible with Oracle SQL.
Oracle compatible system and built-in functions for use in SQL
statements and procedural logic.
Superset Procedure Language (SPL) to create database server-side
application logic for stored procedures, functions, triggers, and
packages.
SPL is compatible with PL/SQL Stored Procedures

System catalog views that are compatible with Oracles Data

Dictionary
OCI compatibility with EnterpriseDBs Open Client Library (OCL).

265

A Look at EnterpriseDB Solutions

Products and Tools

Advanced database server software
Deep Oracle compatibility
Bundled development and management tools

Technical Support and Services

Expert consulting
High Availability
Replication
Performance Tuning
Disaster Recovery
Around the clock support
Remote management and monitoring

266

Postgres Plus Standard Server

Advanced Security
Built-in SQL firewall
Stored code obfuscation

Advanced Data Management

Advanced replication
Oracle-Postgres Replication
SQL Server-Postgres Replication

Advanced Manageability
Automatic software updates
Effortless patch management

Production Support
24x7, around-the-clock support
Help from the Postgres experts

267

Postgres Plus Advanced Server

Advanced Performance

Oracle Compatibility

Advanced Scalability

Infinite Cache/memory caching

High-speed, parallel loader
Bulk collect/bind
Advanced performance monitoring

PL/SQL compatible
Data dictionary views
Pro*C support
Migration tools

Infinite Cache/memory caching

High-speed, parallel loader
Bulk collect/bind

Advanced Security

Advanced Data Management

Advanced Manageability

Production Support

Built-in SQL firewall

Data auditing
Stored code obfuscation

Advanced replication
Oracle-Postgres Replication
SQL Server-Postgres Replication
Database links

Automatic software updates

Effortless patch management

24x7, around-the-clock support

Help from the Postgres experts

268

Postgres Plus Certifications

EnterpriseDB, Postgres Plus and Dynatune are trademarks of

269
269

Training/Certification Roadmap
Trainings

Certifications

Introduction to PostgreSQL
Administration

Postgres Plus Associate

Certification

Advanced PostgreSQL
Administration

Postgres Plus Professional

Certification

Postgres Plus Advanced Server

Postgres Plus Master

Certification

270

Pre-requisites
I. Postgres Plus Associate Certification
Pre-requisites: No pre-requisites
Pricing:
It can be taken individually at a price of $100.
It is also offered complimentary along with the Introduction to PostgreSQL
Administration class.
Retakes cost $100 per attempt.
II. Postgres Plus Professional Certification
Pre-requisites:
Clearing the Postgres Plus Associate Certification &
Completion of the Advanced PostgreSQL Administration class.
Pricing:
It is offered complimentary along with the Advanced PostgreSQL Administration
class.
Retakes cost $100.00 per attempt.
2011 EnterpriseDB. All rights reserved.

271

Pre-requisites (contd.)
III. Postgres Plus Master Certification
Pre-requisites:
Clearing the Postgres Plus Associate Certification,
Clearing the Postgres Plus Professional Certification &
Completion of the Postgres Plus Advanced Server class.
Pricing:
This is currently under review and not offered at this time. Please
check back in the near future.

272

EnterpriseDB Training Coordinator

For any further queries related to PostgreSQL Trainings/
Certifications, please write to:
trainingcoordinator@enterprisedb.com

273

Thank you for your time!

274

03-PostgreSQL-Database Admin Overview
No ratings yet
03-PostgreSQL-Database Admin Overview
32 pages
Security Best Practices For Postgresql: Whitepaper
No ratings yet
Security Best Practices For Postgresql: Whitepaper
14 pages
PostgreSQL for Schemaless Data
100% (1)
PostgreSQL for Schemaless Data
61 pages
Ora2postgres DF
No ratings yet
Ora2postgres DF
72 pages
PostgreSQL Migration Guide
100% (2)
PostgreSQL Migration Guide
33 pages
Postgre SQL Database Administration
No ratings yet
Postgre SQL Database Administration
3 pages
Oracle Migration 2 Post GR Esq L
No ratings yet
Oracle Migration 2 Post GR Esq L
13 pages
Tuning Your PostgreSQL Server
0% (1)
Tuning Your PostgreSQL Server
12 pages
MySQL Master-Master Replication
No ratings yet
MySQL Master-Master Replication
7 pages
MongoDB Manual Master
No ratings yet
MongoDB Manual Master
1,117 pages
EDB High Availability Scalability v1.0
No ratings yet
EDB High Availability Scalability v1.0
23 pages
Tuning Linux For MongoDB
No ratings yet
Tuning Linux For MongoDB
26 pages
Administration PGSQL
No ratings yet
Administration PGSQL
109 pages
Oracle GoldenGate Course Guide
100% (1)
Oracle GoldenGate Course Guide
5 pages
PostgreSQL Setup & Features Guide
100% (1)
PostgreSQL Setup & Features Guide
54 pages
PostgreSQL Backup Strategies Guide
100% (1)
PostgreSQL Backup Strategies Guide
44 pages
Migrate From Oracle To Postgresql With Azure: Webinar Series
100% (1)
Migrate From Oracle To Postgresql With Azure: Webinar Series
21 pages
PostgreSQL & MySQL DBA Expertise
No ratings yet
PostgreSQL & MySQL DBA Expertise
6 pages
How To Backup and Restore PostgreSQL Database Using PG - Dump and PSQL
No ratings yet
How To Backup and Restore PostgreSQL Database Using PG - Dump and PSQL
9 pages
PGSQL SQLs
No ratings yet
PGSQL SQLs
9 pages
Inside PostgreSQL Shared Memory
100% (3)
Inside PostgreSQL Shared Memory
25 pages
Migration Strategy Oracle To Postgres
No ratings yet
Migration Strategy Oracle To Postgres
13 pages
Oracle Golden Gate Microservices Installation 191 - 220628 - 082404
No ratings yet
Oracle Golden Gate Microservices Installation 191 - 220628 - 082404
16 pages
Postgre SQL
100% (2)
Postgre SQL
85 pages
Postgresql Triggers (Tae 2) : Subject: Dbms Guided By: Prof. Neha Purohit - Submitted By: Varun Dalal (Cse - 4-C-25)
No ratings yet
Postgresql Triggers (Tae 2) : Subject: Dbms Guided By: Prof. Neha Purohit - Submitted By: Varun Dalal (Cse - 4-C-25)
21 pages
Database Health Check
100% (1)
Database Health Check
14 pages
SQL Server High Availability
No ratings yet
SQL Server High Availability
49 pages
PGSQL CheatSheet Mysql2psql
No ratings yet
PGSQL CheatSheet Mysql2psql
7 pages
Oracle Database Internals FAQ
No ratings yet
Oracle Database Internals FAQ
9 pages
Postgresql Dba: Learn Basic Rdbms Terms and Concepts
No ratings yet
Postgresql Dba: Learn Basic Rdbms Terms and Concepts
7 pages
Oracle to PostgreSQL Migration Guide
100% (1)
Oracle to PostgreSQL Migration Guide
19 pages
MSSQL - SQL Server DBA Survival Guide PDF
100% (2)
MSSQL - SQL Server DBA Survival Guide PDF
936 pages
Patroni 3.0.3: PostgreSQL HA Guide
100% (1)
Patroni 3.0.3: PostgreSQL HA Guide
137 pages
Optimize Postgres Query Performance
No ratings yet
Optimize Postgres Query Performance
26 pages
Everything You Need To Know About PostgreSQL EXPLAIN
No ratings yet
Everything You Need To Know About PostgreSQL EXPLAIN
44 pages
Mysql Replication & Cluster
100% (9)
Mysql Replication & Cluster
40 pages
DB2 DBA Checklist
No ratings yet
DB2 DBA Checklist
25 pages
PostgreSQL SQL Queries Guide
No ratings yet
PostgreSQL SQL Queries Guide
1 page
Netezza Commands
No ratings yet
Netezza Commands
1 page
SQL DBA PDF (20 Files Merged)
No ratings yet
SQL DBA PDF (20 Files Merged)
114 pages
Automatic Tools For High Availability in Postgresql: Camilo Andrés Echeverri
No ratings yet
Automatic Tools For High Availability in Postgresql: Camilo Andrés Echeverri
9 pages
PostgreSQL Functions by Example
No ratings yet
PostgreSQL Functions by Example
41 pages
What Is Always On in SQL Server 2012?: Group Supports A Failover Environment For A Discrete Set of User Databases
No ratings yet
What Is Always On in SQL Server 2012?: Group Supports A Failover Environment For A Discrete Set of User Databases
28 pages
PostgreSQL DBA Complete 300 Pages
No ratings yet
PostgreSQL DBA Complete 300 Pages
303 pages
MongoDB Performance Best Practices
No ratings yet
MongoDB Performance Best Practices
15 pages
PostgreSQL Performance & Storage Guide
100% (1)
PostgreSQL Performance & Storage Guide
18 pages
Postgresql Course Material
No ratings yet
Postgresql Course Material
205 pages
Foundations PostgreSQL Administration 13
100% (1)
Foundations PostgreSQL Administration 13
307 pages
PostgreSQL Essentials v16 Student
100% (1)
PostgreSQL Essentials v16 Student
400 pages
Postgre SQL
No ratings yet
Postgre SQL
35 pages
EPAS Essentials v15
No ratings yet
EPAS Essentials v15
432 pages
EPAS Essentials v15
No ratings yet
EPAS Essentials v15
432 pages
Postgresql InterviewQuestion
100% (1)
Postgresql InterviewQuestion
5 pages
PostgreSQL Essentials v16 Student
100% (1)
PostgreSQL Essentials v16 Student
462 pages
Post GRE
No ratings yet
Post GRE
59 pages
CENG301 DBMS - Session-3
100% (1)
CENG301 DBMS - Session-3
13 pages
Making Postgres Central in Your Data Center
No ratings yet
Making Postgres Central in Your Data Center
39 pages
Postgres The First Experience
No ratings yet
Postgres The First Experience
173 pages
Lecture 1
No ratings yet
Lecture 1
35 pages
Postgrre
No ratings yet
Postgrre
14 pages
EDB Global Services Catalogue
No ratings yet
EDB Global Services Catalogue
32 pages
Open Source: The New Data Center Standard
No ratings yet
Open Source: The New Data Center Standard
11 pages
PostgreSQL Server Programming Guide
100% (1)
PostgreSQL Server Programming Guide
55 pages
HA Options For Postgres in Containers
100% (1)
HA Options For Postgres in Containers
13 pages
Edb - Bart - Qs - 7 Quick Start Guide For RHEL-CentOS 7
No ratings yet
Edb - Bart - Qs - 7 Quick Start Guide For RHEL-CentOS 7
7 pages
Hong Kong Oracle Migration EDB Presentation
No ratings yet
Hong Kong Oracle Migration EDB Presentation
25 pages
EDB Postgres Distributed (PGD)
No ratings yet
EDB Postgres Distributed (PGD)
307 pages
Postgresql (Open Source Database) Training Program
No ratings yet
Postgresql (Open Source Database) Training Program
11 pages
Data Sheet - EDB - Data - Sheet - Enterprise - Subscription - 20160126
No ratings yet
Data Sheet - EDB - Data - Sheet - Enterprise - Subscription - 20160126
2 pages
Postgres & Java for Developers
No ratings yet
Postgres & Java for Developers
55 pages
PostgreSQL Cookbook Sample Chapter
No ratings yet
PostgreSQL Cookbook Sample Chapter
24 pages
Edb Pgpool User
No ratings yet
Edb Pgpool User
53 pages
Benchmark Study On Replication Lag in PostgreSQL Using Single Region and Multi-Region Architectures
No ratings yet
Benchmark Study On Replication Lag in PostgreSQL Using Single Region and Multi-Region Architectures
18 pages
Answer Key For Foundations PostgreSQL Administration Labs 13
No ratings yet
Answer Key For Foundations PostgreSQL Administration Labs 13
51 pages
EnterpriseDB Gridsql
No ratings yet
EnterpriseDB Gridsql
2 pages
Gartner Reprint
No ratings yet
Gartner Reprint
42 pages
PGD v5 Documentation
No ratings yet
PGD v5 Documentation
443 pages
Technical Comparison Edb Postgres Enterprise and Oracle Enterprise Ebook
100% (1)
Technical Comparison Edb Postgres Enterprise and Oracle Enterprise Ebook
35 pages
Infor LN Installation Guide: Release 10.7.1
No ratings yet
Infor LN Installation Guide: Release 10.7.1
46 pages
EnterpriseDB Licensing Guide
No ratings yet
EnterpriseDB Licensing Guide
24 pages
Introduction Postgre SQLAdministration V11
No ratings yet
Introduction Postgre SQLAdministration V11
274 pages
06.5 - Devrim Gunduz - PostgreSQLClusteringWithRedHatClusterSuite - LT
No ratings yet
06.5 - Devrim Gunduz - PostgreSQLClusteringWithRedHatClusterSuite - LT
9 pages
Answer Key For PostgreSQL Essential v16
No ratings yet
Answer Key For PostgreSQL Essential v16
46 pages
EDB Postgres Advanced Server EcpgPlus Guide v9.5
No ratings yet
EDB Postgres Advanced Server EcpgPlus Guide v9.5
117 pages
EDB Database Compatibility For Oracle Developers Guide v10
No ratings yet
EDB Database Compatibility For Oracle Developers Guide v10
418 pages
6 Things A Developer Should Know About Postgres
No ratings yet
6 Things A Developer Should Know About Postgres
14 pages
How To Use PostgreSQL With Django - EDB
No ratings yet
How To Use PostgreSQL With Django - EDB
13 pages
EDB Postgres Migration Guide v52.0
No ratings yet
EDB Postgres Migration Guide v52.0
97 pages
Answer Key Advanced EDB Postgres v15
No ratings yet
Answer Key Advanced EDB Postgres v15
13 pages
Pega719 Install Tomcat MSSQL 2
No ratings yet
Pega719 Install Tomcat MSSQL 2
50 pages