Foundations PostgreSQL

Administration
Your name
 Course Agenda
• Introduction
• System Architecture
• Installation and Database Clusters
• Configuration
• Creating and Managing Databases
• User Tools – Command Line and Graphical Interfaces
• Security
• SQL Primer
• Backup, Recovery and Point in Time Recovery
• Routine Maintenance Tasks
• Data Dictionary
• Loading and Moving Data

2 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 1
Introduction
 Module Objectives

• EDB Postgres Platform

• History of PostgreSQL
• Major Features
• Architectural Overview
• General Database Limits
• Common Database Object Names

4 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 EDB Postgres Platform
PostgreSQL EDB Postgres Advanced Server
Software
EDB Postgres Tools*

PostgreSQL Cloud Remote Technical

Support Technical Support DBA Service DBA Service Account Manager

Getting Started PostgreSQL Optimization Enterprise Strategy Custom Services

Services Quick Deploy Performance Tuning PostgreSQL Strategy Implementation

Solution Design Automation Services Security Assessment Embedded Expert
Migration Assessment Monitoring Best Practices Enterprise Architecture Training
Migration Assistance Backup Best Practices

* Postgres Enterprise Manager, Backup and Recovery Tool, Failover Manager, Replication Server, Containers,
Kubernetes Operator, PostGIS, Pgpool, PgBouncer, Connectors, Foreign Data Wrappers, Migration Toolkit

5 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 PostgreSQL Server

EDB supercharges PostgreSQL

• Performance - Handles enterprise workloads
• Extensibility - A wide array of extensions and
data models
• Scalability - Multiple options for operating at
scale
• Community-driven - Multiple companies
contribute

6 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 EDB Postgres Advanced Server

EDB Postgres Advanced Server

• Security - Password policy management, session tag auditing, data redaction, SQL injection
protection, and procedural language code obfuscation

• Performance - Query optimizer hints, SQL session/system wait diagnostics

• Developer Productivity - Over 200 pre-packaged utility functions, user-defined object

types, autonomous transactions, nested tables, synonyms, advanced queueing
• DBA Productivity - Throttle CPU and I/O at the process level, over 55 extended catalog
views to profile all the objects and processing that occurs in the database
• Oracle Compatibility - Offers compatibility for schemas, data types, indexes, users, roles,
partitioning, packages, views, PL/SQL triggers, stored procedures, functions, and utilities

7 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Facts about PostgreSQL

• The world’s most advanced open source database

• Designed for extensibility and customization
• ANSI/ISO compliant SQL support
• Actively developed for more than 20 years
• University Postgres (1986-1993)
• Postgres95 (1994-1995)
• PostgreSQL (1996-current)

8 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 PostgreSQL Community
• Community mailing lists
• https://www.postgresql.org/list/
• Commercial SLAs
• https://www.enterprisedb.com/postgres-plans-pricing-
subscriptions-for-services-and-support

9 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 PostgreSQL Lineage

10 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 PostgreSQL Version History

PostgreSQL PostgreSQL 9
UCB [1996] [2010]
Postgres • V 6.0, Open • Streaming PostgreSQL 11 PostgreSQL 13
[1986] Source Replication [2018] [2020]

UCB PostgreSQL 8 PostgreSQL 10 PostgreSQL 12

Postgres95 [2005] [2017] [2019]
• Logical Replication
[1995]

11 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Major Features of PostgreSQL
• Portable:
• Written in ANSI C
• Supports Windows, Linux, Mac OS/X and major UNIX platforms
• Reliable:
• ACID Compliant
• Supports Transactions and Savepoints
• Uses Write Ahead Logging (WAL)
• Scalable:
• Uses Multi-version Concurrency Control
• Table Partitioning and Tablespaces
• Parallel Sequential Scans
• Parallel Table and Index Creation

12 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Major Features of PostgreSQL (continued)
• Secure:
• Employs Host-Based Access Control
• Provides Object-Level Permissions and Row Level Security
• Supports SSL
• Connections and Statement Logging

• Recovery and Availability:

• Streaming Replication, Logical Replication and Replication Slots
• Replication Slots, Sync or Async Options
• Supports Hot-Backup, pg_basebackup and Point-in-Time Recovery

• Advanced:
• Supports Triggers, Functions and Procedures
• Supports Custom Procedural Languages
• Upgrade using pg_upgrade
• Unlogged Tables and Materialized Views
• Just-in-Time (JIT) Compilation

13 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Postgres for Big Data

• Postgres enables you to support a wider range of workloads

with your relational database
• An Object-relational design and decades of proven reliability make
Postgres the most flexible, extensible and performant database
available
• Document store capabilities: XML, JSON, PLV8; HStore (key-value
store); non-durable storage; full text indexing
• Foreign Data Wrappers enable read/write integration with other
database technologies

14 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Architectural Overview
Connectors

PERL DBI
NODE.JS

PYTHON
LIBPQ

ODBC
ECPG
JDBC

.NET

TCL
PostgreSQL

Background OS Kernel
User Process Shared Memory Storage
Processes Cache

15 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 General Database Limits
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

16 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Common Database Object Names

Industry Term PostgreSQL Term

Table or Index Relation

Row Tuple

Column Attribute

Data Block Page (when block is on disk)

Page Buffer (when block is in memory)

17 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Setup Guidelines

• All the instructor demos and labs are based on Linux

• CentOS 7 machine or virtual machine with at least 1 GB
RAM and 10 GB HDD is recommended
• Participants using Linux must follow instructor during the
installation module and install PostgreSQL
• Lab Exercise 1 of Installation Module covers PostgreSQL
installation on Windows Platform

18 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary

• EDB Postgres Platform

• History of PostgreSQL
• Major Features
• Architectural Overview
• General Database Limits
• Common Database Object Names

19 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 2
System Architecture
 Module Objectives

• Architectural Summary • Commit and Checkpoint

• Process and Memory Architecture • Statement Processing
• Utility Processes • Physical Database Architecture
• Connection Request-Response • Data Directory Layout
• Disk Read Buffering • Installation Directory Layout
• Disk Write Buffering • Page Layout
• Background Writer Cleaning Scan

21 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Architectural Summary

• PostgreSQL uses processes, not threads

• The postmaster process acts as a supervisor
• Several utility processes perform background work
• postmaster starts them, restarts them if they die

• One back end process per user session

• postmaster listens for new connections

22 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Process and Memory Architecture
Postmaster

Shared Memory

Shared Buffers WAL Buffers Process Array

BACKGROUND STATS
WRITER COLLECTOR WAL Archived
Data Files Segments WAL
CHECKPOINTER ARCHIVER

AUTOVACUUM LOGGER
Error Log
LOGICAL Files
WALWriter
REPLICATION

23 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Utility Processes
• Background writer
• Writes dirty data blocks to disk
• WAL writer
• Flushes write-ahead log to disk
• Checkpointer
• Automatically performs a checkpoint based on config parameters
• Autovacuum launcher
• Starts Autovacuum workers as needed

• Autovacuum workers
• Recover free space for reuse

24 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 More Utility Process

• Logger
• Routes log messages to syslog, eventlog, or log files
• Stats collector
• Collects usage statistics by relation and block
• Archiver
• Archives write-ahead log files
• Logical replication launcher
• Starts logical replication apply process for logical replication

25 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Postmaster as Listener

• Postmaster is master
process called postgres Client requests a
connection
• Listens on 1, and only 1,
tcp port Postmaster

• Receives client
connection requests
Shared Memory

26 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 User Back End Process

• Master process postgres spawns Postmaster

a new server process for each
connection request detected
• Communication is done using
semaphores and shared memory
work_mem
• Authentication - IP, user and Postgres
password
• Authorization - Verify
Shared Memory
Permissions

27
27 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.
 Respond to Client

• User back end process Postmaster

called postgres
• Callback to client
work_mem
• Waits for SQL Postgres

• Query is transmitted
Shared Memory
using plain text

28 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Disk Read Buffering

• PostgreSQL buffer Postgres Postgres Postgres

cache (shared_buffers)
reduces OS reads. Shared (data) Buffers
• Read the block once,
Shared Memory
then examine it many
times in cache.
Stable Databases

29 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Disk Write Buffering

• Blocks are written to Postgres Postgres Postgres

disk only when needed:

Shared (data) Buffers
• To make room for new
blocks Shared Memory

• At checkpoint time
CHECKPOINT
Stable Databases

30 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Background Writer Cleaning Scan

• User process performs Postgres Postgres Postgres

writes using shared

buffers
Shared (data) Buffers
• Background writer scan
ensures adequate Shared Memory
supply of clean buffers BGWRITER

Stable Databases

31 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Write Ahead Logging (WAL)

• User process writes Postgres Postgres Postgres

data to WAL buffers Shared Memory

• WAL Writer flushes WAL Shared (data) Buffers

WAL
Buffers
buffers periodically, on
commit, or when buffers WAL
are full Writer
Transaction
Log
• Group commit Stable Databases

32 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Transaction Log Archiving
Postgres Postgres Postgres
• Archiver spawns a task
Shared Memory
to copy away pg_wal
Shared (data) WAL
log files when full Buffers Buffers

Transaction
Log
Archive Stable Databases
Command
Archiver

33 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Commit and Checkpoint

• Before commit
• Uncommitted updates are in memory

• After commit
• WAL buffers are written to the disk (write-ahead log file) and shared
buffers are marked as committed

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

34 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Statement Processing

• Check Syntax Optimize

• Call Traffic Cop
• Identify Query Type • Execute Query based
• Command Processor if • Planner generate Plan on query plan
needed • Uses Database Statistics
• Break Query in Tokens • Query Cost Calculation
• Choose best plan
Parse Execute

35 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Physical Database Architecture

• A cluster is a collection of databases managed by a one

server instance
• Each cluster has a separate
• Data directory
• TCP port
• Set of processes
• A cluster can contain multiple databases

36 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Installation Directory Layout

• Default Installation Directory Location:

• Linux - /usr/pgsql-13
• bin – Programs

• lib – Libraries

• share – Shared data

• Default Data directory - /var/lib/pgsql/13/data

37 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Database Cluster Data Directory Layout

Data

global pg_tblsc pg_wal log Status Configuration Postmaster

base
Directories Files Info Files

Contains Symbolic link to Write ahead Startup and Starts with postgresql.conf, postmaster.pid
Cluster wide
Databases tablespaces logs Error logs pg_ pg_hba.conf, postmaster.opts
database
pg_ident.conf,
objects
postgresql.auto.conf

38 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Physical Database Architecture
• File-per-table, file-per-index
• A table-space is a directory
• Each database that uses that table-space gets a subdirectory
• Each relation using that table-space/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)

39 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Sample - Data Directory Layout
14297

14300
14307
14405
Database OID
Base
14498
14312
Data
pg_tblsc
16650
14302
Tablespace OID
/storage 14301
/pg_tab 14307 16700

16701
16651

40 © Copyright EnterpriseDB Corporation, 2020. 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

41 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Page Structure
Page
Item Item Item
Header

8K

Tuple

Tuple Tuple Special

42 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary

• Architectural Summary • Commit and Checkpoint

• Process and Memory Architecture • Statement Processing
• Utility Processes • Physical Database Architecture
• Connection Request-Response • Data Directory Layout
• Disk Read Buffering • Installation Directory Layout
• Disk Write Buffering • Page Layout
• Background Writer Cleaning Scan

43 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 3
PostgreSQL Installation
 Module Objectives
• OS User and Permissions
• Installation Options
• Installation of PostgreSQL
• Initializing Database Instance
• Database Cluster Defaults
• Starting Database Instance
• Connecting to a Database
• Setting Environmental Variables

45 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 OS User and Permissions

• PostgreSQL runs as a daemon (Unix / Linux) or service

(Windows)
• The PostgreSQL GUI Installer needs superuser/admin access
• All processes and data files must be owned by a user in the OS
• During installation a postgres locked user will be created on
Linux
• On windows a password is required

46 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 The postgres User Account
• It is advised to run PostgreSQL under a separate user account
• This user account should only own the data directory that is managed
by the server
• The useradd or adduser Unix command can be used to add a user
• The user account named postgres is used throughout this training
[root@pgsrv1 ~]# useradd postgres
[root@pgsrv1 ~]# passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

47 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Installation Options for PostgreSQL

• Operating system package

• RPM/YUM
• Debian/Ubuntu DEB
• FreeBSD port
• Solaris package
• Source code
• Wizard Installer for Microsoft Windows and Mac OS

48 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Download the PostgreSQL Repository RPM

• PostgreSQL can be installed using yum repo:

• https://www.postgresql.org/download/linux/redhat/
• On this page, select the version and the platform on
which PostgreSQL needs to be installed
• It will provide you with repository location and the post
installation steps to be performed for setup of the initial
database cluster

49 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example – Download the PostgreSQL Repository RPM

50 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Install the PostgreSQL Repository RPM

• Download and install the repository rpm using yum command:

yum install –y
[root@pgsrv1 ~]#
https://download.postgresql.org/pub/repos/yum/repor
pms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

• pgdg-redhat-all.repo is created in /etc/yum.repos.d

location

51 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Install PostgreSQL Client and Server

• After installing the repository rpm, install PostgreSQL

client and server packages using the yum command
• yum install –y postgresql13
• yum install –y postgresql13-server

52 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Initialize Database Cluster
• After installing the PostgreSQL client and server packages, initialize a database cluster
• postgresql-13-setup file contains initialization and upgrade information for the initial
cluster setup
• It creates a default data directory at /var/lib/pgsql/13/data
• The default data directory location can be changed by changing PGDATA environmental
variable in the postgresql-13.service file located at /usr/lib/systemd/system
• Use postgresql13-setup file with initdb option to initialize a new database cluster

[root@pgsrv1 ~]# /usr/pgsql-13/bin/postgresql-13-setup initdb

Initializing database ... OK

53 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Start postgresql-13 service

• After initialization, use systemctl to enable and start

postgresql-13 service
[root@pgsrv1 ~]# systemctl enable postgresql-13
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-
13.service to /usr/lib/systemd/system/postgresql-13.service.
[root@pgsrv1 ~]# systemctl start postgresql-13

54 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Database Cluster Defaults

• Data directory – /var/lib/pgsql/13/data

• Default authentication – peer and scram-sha-256
• Default database superuser – postgres
• Default password of database superuser – blank
• Default port – 5432

55 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Connecting to a Database
• Connect to the default database using psql and change password of the superuser postgres by using
\password
[root@pgsrv1 ~]# su - postgres
[postgres@pgsrv1 ~]$ /usr/pgsql-13/bin/psql -d postgres -U postgres
postgres=# ALTER USER postgres PASSWORD 'postgres';
ALTER ROLE
postgres=# \q

• Change the authentication method to scram-sha-256 in pg_hba.conf file and reload the server

[postgres@pgsrv1 ~]$ vi /var/lib/pgsql/13/data/pg_hba.conf

local all all scram-sha-256
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256

[postgres@pgsrv1 ~]$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ reload

server signaled

56 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Setting Environmental Variables
• Setting environment variables is very important for trouble free
startup/shutdown of the database server
• PATH – should point to correct bin directory
• PGDATA – should point to correct data cluster directory
• PGPORT – should point to correct port on which database cluster is running
• PGUSER – specifies the default database user name
• PGDATABASE – specify the default database
• PGPASSWORD – specify default password
• Edit .profile or .bash_profile to set the variables
• In Windows set these variables using my computer properties page

57 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example – Environmental Variables setup
[postgres@pgsrv1 ~]$ vi .bash_profile
Edit User Profile
PATH=/usr/pgsql-13/bin/:$PATH:$HOME/.local/bin:$HOME/bin

export PATH
export PGDATA=/var/lib/pgsql/13/data/
Logoff and Login
export PGUSER=postgres
export PGPORT=5432
export PGDATABASE=postgres

[postgres@pgsrv1 ~]$ exit

logout
[root@pgsrv1 ~]# su - postgres Verify
Environmental
[postgres@pgsrv1 ~]$ which psql Settings
/usr/pgsql-13/bin/psql

[postgres@pgsrv1 ~]$ pg_ctl status

pg_ctl: server is running (PID: 1663)
/usr/pgsql-13/bin/postgres "-D" "/var/lib/pgsql/13/data/"

58 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary
• OS User and Permissions
• Installation Options
• Installation of PostgreSQL
• Initializing Database Instance
• Database Cluster Defaults
• Starting Database Instance
• Connecting to a Database
• Setting Environmental Variables

59 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 1

• In this lab exercise you can practice installing PostgreSQL

on Windows
1. Download the PostgreSQL installer from the EnterpriseDB website
for the windows platform
2. Install PostgreSQL
3. Connect to PostgreSQL using psql

60 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 4
Database Clusters
 Module Objectives

• Database Clusters
• Creating a Database Cluster
• Starting and Stopping the Server (pg_ctl)
• Connect to the Server Using psql

62 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Database Clusters
• A Database Cluster is a collection of databases managed by a single
server instance
• Database Clusters are comprised of:
• Data directory
• Port
• Default databases are created named:
• template0
• template1
• postgres

63 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Creating a Database Cluster

• Choose the data directory location for the new cluster

• Initialize the database cluster storage area (data directory)
using initdb utility
• initdb will create the data directory if it doesn’t exist
• You must have permissions on the parent directory so that
initdb can create the data directory
• Data directory can be created manually using the superuser
access and ownership can be given to the postgres user

64 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 initdb Utility
$ initdb [OPTION]... [DATADIR]
• Options:
-D, --pgdata location for this database cluster
-E, --encoding set default encoding for new databases
-U, --username database superuser name
-W, --pwprompt prompt for a password for the new superuser
-g, --allow-group-access allow group read/execute on data directory
-X, --waldir=WALDIR location for the write-ahead log directory
--wal-segsize=SIZE size of WAL segments, in megabytes
-k, --data-checksums use data page checksums
-V, --version output version information, then exit
-A --auth default authentication method
-?, --help show this help, then exit

• If the data directory is not specified, the environment variable PGDATA is used

65 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example - initdb
[root@pgsrv1 ~]# mkdir /edbstore
[root@pgsrv1 ~]# chown postgres:postgres /edbstore/
[root@pgsrv1 ~]# su - postgres

[postgres@pgsrv1 ~]$ initdb -D /edbstore/ -W

• In the above example the database system will be owned by user postgres
• The postgres user is the database superuser
• The default server config file will be created in /edbstore named
postgresql.conf
• -W is used to force initdb to prompt for the superuser password

66 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Starting a Database Cluster

• Choose a unique port for • Syntax:

postmaster in postgresql.conf
$ pg_ctl start options
• $ vi /edbstore/postgresql.conf
• port = 5434 • Common options:
-D, --pgdata=DATADIR
• pg_ctl utility can be used to
start a cluster -t, --timeout=SECS

• Example: -w, --wait

• $ pg_ctl -D /edbstore -l -W, --no-wait

startlog5434 start

67 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Connecting to a Database Cluster

• The psql and pgAdmin4 clients can be used for

connections
• The edb-psql and PEM clients can be used for EDB
Postgres Advanced Server

68 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Reload a Database Cluster

• Some configuration parameter changes do not require a

restart
• Changes can be reloaded using the pg_ctl utility
• Syntax:
pg_ctl -D <data directory> reload

• Changes can also be reloaded using pg_reload_conf()

69 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Stopping a Database Cluster
• pg_ctl can be used to stop a database cluster
• pg_ctl supports three modes of shutdown
-mf Fast Mode, quit directly, with proper shutdown (default)

-mi Immediate Mode, quit without complete shutdown; will lead to recovery

-ms Smart Mode, quit after all clients have disconnected

• Syntax:
pg_ctl stop [-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]

• Example:

70 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 View Cluster Control Information

• pg_controldata can be used to view the control information for a database

cluster
• Syntax:
• pg_controldata [DATADIR]
[postgres@pgsrv1 ~]$ pg_controldata /edbstore/
pg_control version number: 1300
Catalog version number: 202005171
Database system identifier: 6855489020932905497
Database cluster state: in production
pg_control last modified: Fri 25 Sep 2020 04:01:13 AM UTC
Latest checkpoint location: 0/1626FB8
Latest checkpoint's REDO location: 0/1626FB8
Latest checkpoint's REDO WAL file: 000000010000000000000001
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:486
Latest checkpoint's NextOID: 14175

71 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary

• Database Clusters
• Creating a Database Cluster
• Starting and Stopping the Server (pg_ctl)
• Connect to the Server Using psql

72 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 1

• A new website is to be developed for an online music store

1. Create a new cluster with data directory /edbdata and
ownership of postgres user
2. Start your edbdata cluster
3. Reload your cluster with pg_ctl utility and using
pg_reload_conf() function
4. Stop your edbdata cluster with fast mode

73 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 5
Configuration
 Module Objectives

• Server Parameter File - postgresql.conf

• Viewing and Changing Server Parameters
• Configuration Parameters - Security, Resources and WAL
• Configuration Parameters - Error Logging, Planner and
Maintenance
• Viewing Compilation Settings
• Using File Includes

75 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Setting Server 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 five types:
• boolean
• integer
• floating point
• string
• enum

• One way to set these parameters is to edit the file postgresql.conf, which is normally
kept in the data directory

76 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 The Server Parameter File - postgresql.conf
• Holds parameters used by a cluster
• Parameters are case-insensitive
• Normally stored in data directory
• initdb installs default copy
• Some parameters only take effect on server restart (pg_ctl restart)
• # used for comments
• One parameter per line
• Use include directive to read and process another file
• Can also be set using the command-line option

77 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Viewing and Changing Server Parameters

Configuration parameters can

be viewed using:
• SHOW command Configuration parameters can be
• pg_settings modified for:
• pg_file_settings
• Single session using the SET command
• Database user using ALTER USER
• Single database using ALTER DATABASE

78 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Changing Configuration Parameter at Cluster Level

Use ALTER SYSTEM command to edit cluster level

[postgres@pgsrv1 ~] psql edb postgres settings without editing postgresql.conf
edb=# ALTER SYSTEM SET work_mem=20480;
ALTER SYSTEM
edb=# SELECT pg_reload_conf(); ALTER SYSTEM writes new setting to
postgresql.auto.conf file which is read at last during
edb=# ALTER SYSTEM RESET work_mem; server reload/restarts
ALTER SYSTEM
edb=# SELECT pg_reload_conf();
Parameters can be modified using ALTER SYSTEM when
required

79 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Connection Settings
• listen_addresses (default *) - 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
• unix_socket_permissions (default 0777) - access permissions of the Unix-domain socket

80 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Security and Authentication Settings

• authentication_timeout (default is 1 minute) – Maximum time to

complete client authentication, in seconds
• row_security (default is on) – Controls row security policy behavior
• password_encryption (default scram-sha-256) – Determines the
algorithm to use to encrypt password
• ssl (default: off) - Enables SSL connections

81 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 SSL Settings
• ssl_ca_file - Specifies the name of the file containing the SSL server
certificate authority (CA)
• ssl_cert_file - Specifies the name of the file containing the SSL server
certificate
• ssl_key_file - Specifies the name of the file containing the SSL server
private key
• ssl_ciphers - List of SSL ciphers that may be used for secure connections
• ssl_dh_params_file – Specifies file name for custom OpenSSL DH
paramters

82 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Memory Settings

maintenance_ autovacuum
shared_buffers temp_buffers work_mem temp_file_limit
work_mem _work_mem

Amount of Amount of
Amount of Amount of
Size of shared memory used memory used Amount of disk
memory used memory used by
buffer pool for a caching sorting and space used for
for maintenance autovacuum
cluster temporary hashing temporary files
commands worker
tables operations

Server Session

83 © Copyright EnterpriseDB Corporation, 2020. 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 4GB) - Used to estimate the cost of an index
scan. Rule of thumb is 75% of system memory
• plan_cache_mode (default auto) – Controls custom or generic plan
execution for prepared statements. Can be set to auto, force_custom_plan
and force_generic_plan

84 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Write Ahead Log Settings
• wal_level (default replica) - Determines how much information is written to the WAL. Other
values are minimal and logical
• fsync (default on) – Force WAL buffer flush at each commit, Turning this off can cause lead
to arbitrary corruption in case of a system crash
• wal_buffers (default -1, autotune) - The amount of memory used in shared memory for WAL
data. The default setting of -1 selects a size equal to 1/32nd (about 3%) of shared_buffers
• min_wal_size (default 80 MB) – The WAL size to start recycling the WAL files
• max_wal_size (default 1GB) – The WAL size to start checkpoint. Controls the number of WAL
Segments(16MB each) after which checkpoint is forced
• checkpoint_timeout (default 5 minutes) - Maximum time between checkpoints
• wal_compression (default off) – The WAL of Full Page write will be compressed and written

85 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Where To Log
log_destination Controls logging type for a database cluster.

Can be set to stderr, csvlog, syslog, and eventlog

logging_collector Enables logger process to capture stderr and csv logging messages

These messages can be redirected based on configuration settings

log_directory - Directory where log files are written

Log File and
log_filename - Format of log file name (e.g. postgresql-%Y-%m-%d_%H%M%S.log)
Directory log_file_mode - permissions for log files
Settings log_rotation_age - Used for file age based log rotation
log_rotation_size - Used for file size based log rotation

86 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 When To Log
log_min_messages Messages of this severity level or above are sent to the
Duration server log

and log_min_error_statement When a message of this severity or higher is written to

sampling the server log, the statement that caused it is logged
along with it
log_min_duration_statement When a statement runs for at least this long, it is written
to the server log

log_autovacuum_min_duration Logs any Autovacuum activity running for at least this long

log_statement_sample_rate Percentage of queries(above

log_autovacuum_min_duration) to be logged

log_transaction_sample_rate Sample a percentage of transactions by logging

statements

87 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 What To Log
log_connections Log successful connections to the server log

log_disconnections Log some information each time a session disconnects, including the duration of the session

log_temp_files Log temporary files of this size or larger, in kilobytes

log_checkpoints Causes checkpoints and restart points to be logged in the server log

log_lock_waits Log information if a session is waits longer then deadlock_timeout to acquire a lock

log_error_verbosity How detailed the logged message is. Can be set to default, terse or verbose

log_line_prefix Additional details to log with each line. Default is '%m [%p] ‘ which logs a timestamp and the process ID

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

88 © Copyright EnterpriseDB Corporation, 2020. 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

89 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Statement Behavior
• search_path - This parameter specifies the order in which schemas are searched.
The default value for this parameter is "$user", public
• default_tablespace - Name of the tablespace in which objects are created by
default
• temp_tablespaces - Tablespaces name(s) in which temporary objects are created
• statement_timeout - Postgres will abort any statement that takes over the
specified number of milliseconds A value of zero (the default) turns this off
• idle_in_transaction_session_timeout – Terminates any session with an open
transaction that has been idle for longer than the specified duration in
milliseconds
90 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.
 Parallel Query Scan Settings
• Advanced Server supports parallel execution of read-only queries
• Can be enabled and configured by using configuration parameters
• max_parallel_workers_per_gather (default 2): Enables parallel query scan
• parallel_tuple_cost (default 0.1): Estimated cost of transferring one tuple from a parallel worker
process to another process
• parallel_setup_cost (default 1000): Estimates cost of launching parallel worker processes
• min_parallel_table_scan_size (default 8MB): Sets minimum amount of table data that must be
scanned in order for a parallel scan
• min_parallel_index_scan_size (default 512 KB): Sets the minimum amount of index data that must
be scanned in order for a parallel scan
• force_parallel_mode (default off): Useful when testing parallel query scan even when there is no
performance benefit

91 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Parallel Maintenance Settings

• PostgreSQL supports parallel processes for creating an index

• Currently this feature is only available for btree index type
• max_parallel_maintenance_workers (default 2): Enables
parallel index creation

92 © Copyright EnterpriseDB Corporation, 2020. 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 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

93 © Copyright EnterpriseDB Corporation, 2020. 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
• autovacuum_work_mem (default -1, to use maintenance_work_mem) -
Maximum amount of memory used by each autovacuum worker

94 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Read-only Parameters

• Postgres sources are compiled using various setting.

• Various read-only configuration parameters can be used to
view build settings
block_size
wal_block_size
segment_size
wal_segment_size
data_checksums
data_directory_mode
server_encoding
lc_collate
max_function_args
max_index_keys
ssl_library

95 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Configuration File Includes

• The postgresql.conf file can now contain include

directives
• Allows configuration file to be divided in separate files
• Usage in postgresql.conf file:
• include ‘filename’
• include_dir ‘directory name’

96 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary

• Server Parameter File - postgresql.conf

• Viewing and Changing Server Parameters
• Configuration Parameters - Security, Resources and WAL
• Configuration Parameters - Error Logging, Planner and
Maintenance
• Viewing Compilation Settings
• Using File Includes

97 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 1

1. You are working as a DBA. It is recommended to keep a backup

copy of the postgresql.conf file before making any changes.
Make the necessary changes in the server parameter file for
following settings:
• Allow up to 200 connected users
• Reserve 10 connection slots for DBA
• Set the maximum time to complete client authentication to be 10
seconds

98 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 2

1. 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 the server parameters that control
logging and implement the following:
• Save all the error message in a file inside the log folder in your cluster data directory
• Log all queries which are taking more than 5 seconds to execute, and their time
• Log the users who are connecting to the database cluster
• Make the above changes and verify them

99 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 3

1. Perform the following changes recommended by a senior

DBA and verify them. Set:
• Shared buffer to 256MB
• Effective cache for indexes to 512MB
• Maintenance memory to 64MB
• Temporary memory to 8MB

100 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 4

1. Vacuuming is an important maintenance activity and needs to

be properly configured. Change the following autovacuum
parameters on the production server. Set:
• Autovacuum workers to 6
• Autovacuum threshold to 100
• Autovacuum scale factor to 0.3
• Auto analyze threshold to 100
• Autovacuum cost limit to 100

101 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 6
Creating and Managing Databases
 Module Objectives

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

103 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Object Hierarchy
Database
Cluster

Users/Groups
Database Tablespaces
(Roles)

Catalogs Schema Extensions

Table View Sequence Functions Event Triggers

104 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 What is a Database

• A running PostgreSQL server can manage many databases

• 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
• Databases are 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)

105 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Creating Databases
• Database can be created using:
1. createdb utility program
2. CREATE DATABASE SQL command
• SQL Command syntax:
CREATE DATABASE name [ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]

106 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example – Creating Databases

107 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Accessing a Database

• pgAdmin4 or psql can be used to access a database

• To use psql, open the command prompt or a terminal and
execute:
$ psql –U postgres –d edbstore

108 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Database Users
• Are global across a database cluster
• Are not the operating system users
• Are used for connecting to a database
• Must have an unique name which cannot start with
pg_
Database • postgres is a predefined superuser in the default
Users database cluster

109 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Creating Users using psql

• Users can be added using CREATE USER sql command

• Syntax:
=# CREATE USER name [ [ WITH ] option [ ... ]
where option can be:
SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE |
NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION |
NOREPLICATION | BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'

• Example:

110 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Creating Users Using createuser

• The createuser utility can also be used to create a user

• Syntax:
$ createuser [OPTION]... [ROLENAME]
• Use --help option to view the full list of options available
• Example:

111 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Roles
• Role is a collection of privileges
• Role makes it easier to manage multiple privileges
• Role can be created using the CREATE ROLE statement
• You can grant a privilege to a role using the GRANT statement
• Role can be assigned to a user or a group using the GRANT
statement
• If the privileges for a role are modified, all the users who are granted
the role acquire the modified privileges

112 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Default Roles

• Default pg_* roles can be used to provide access to certain

administrative capabilities
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_signal_backend
pg_read_server_files
pg_write_server_files
pg_execute_server_program
pg_monitor

113 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Privileges
• Cluster level
• Granted to an user during CREATE or later using ALTER USER
• These privileges are granted by the database superuser
• Object Level
• Granted to an user using GRANT command
• These privileges allow the database user to perform particular action on a
database object, such as table, view, sequence etc.
• Can be granted by Owner, superuser or someone who has been given
permission to grant privilege (WITH GRANT OPTION)

114 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 GRANT Statement

• GRANT can be used for granting object level privileges to

database users, groups or roles
• Privileges can be granted on a tablespace, database, schema,
table, sequence, domain and function
• GRANT is also used to grant a role to a user
• Syntax:
• Type \h GRANT in psql terminal to view the entire syntax and
available privileges that can be granted on different objects

115 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example – GRANT Statement

116 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 REVOKE Statement
• REVOKE can be used for revoking object level privileges to database
users, groups or roles
• Privileges can be revoked on a tablespace, database, schema, table,
sequence, domain and function
• REVOKE [ GRANT OPTION FOR ] can be used to revoke only
the grant option without revoking the actual privilege
• Syntax:
• Type \h REVOKE in psql terminal to view the entire syntax and
available privileges that can be revoked on different objects

117 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example - REVOKE Statement

118 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 What is a Schema
SCHEMA

Tables Views

Sequences Functions Owns

USER
Domains

119 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Benefits of Schemas

• A database can contain one or more named schemas

• By default, all databases contain a public schema
• 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

120 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Creating Schemas
• Schemas can be added using the CREATE SCHEMA SQL command
• Syntax:
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION
role_specification ]
• Example:

121 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 What is a Schema Search Path

• The schema search path determines which schemas are

searched for matching table names
• Search path is used when fully qualified object names are
not used in a query
• Example:
 SELECT * FROM employee;

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

122 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Determine the Schema Search Path

• To show the current search path, use the following

command:
• => SHOW search_path;

• Default search_path is "$user",public

• Search path can be changed using SET command:
• => SET search_path TO myschema, public;

123 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Object Ownership

Database
Cluster
Owner

Users/Groups
Database Tablespaces
(Roles)

Catalogs Schema Extensions

Event
Table View Sequence Functions
Triggers

124 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary

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

125 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 1

1. A new website is to be developed for an online music store.

• Create a database user edbuser in your existing cluster
• Create an edbstore database with ownership of edbuser user
• Login to the edbstore database using the edbuser user and
create the edbuser schema
• Logoff from psql

126 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 2

1. An e-music online store website application developer wants to

add an online buy/sell facility and has asked you to separate all
tables used in online transactions. Here you have suggested to
use schemas. Implement the following suggested options:
• Create an ebuy user with password ‘lion’
• Create an ebuy schema which can be used by user ebuy
• Login as the ebuy user, create a table sample1 and check
whether that table belongs to the ebuy schema or not

127 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 3

1. EnterpriseDB provided an edbstore.sql file with your training

material and this script file can be installed in the newly created
edbstore database
• Download edbstore.sql and place it in a directory which is accessible
to the postgres user. Make sure the file is also owned by the
postgres user
• Run the psql command with the –f option to execute the edbstore.sql
file and install all the sample objects required for this training

128 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 4

1. Retrieve a list of databases using a SQL query

2. Retrieve a list of databases using the psql meta command
3. Retrieve a list of tables in the edbstore database and
check which schema and owner they have

129 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 7
User Tools - Command Line Interfaces
 Module Objectives
• Introduction to psql
• Conventions
• Connecting to Database
• psql Command Line Parameters
• Entering psql Commands
• psql Meta-Commands
• psql SET Parameters
• Conditional Commands
• Information Commands

131 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Introduction to psql

• psql is a command line interface (CLI) to PostgreSQL

• Can be used to execute SQL queries and psql meta
commands
[postgres@pgsrv1 ~]$ which psql
/usr/pgsql-XX/bin/psql
[postgres@pgsrv1 ~]$ psql
Password for user postgres:
Type "help" for help.
postgres=# \q
[postgres@pgsrv1 ~]$

132 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Connecting to a Database

psql Connection Options:

• -d <Database Name>
• -h <Hostname>
• -p <Database Port>
• -U <Database Username>

Environmental Variables

• PGDATABASE, PGHOST, PGPORT and PGUSER

133 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Conventions

• psql has it's own set of commands, all of which start with
a backslash (\).
• 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

134 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 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

135 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 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

136 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 History and Query Buffer

• \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 and then execute it
• \w FILENAME will save the query buffer to FILENAME

137 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Controlling Output

• psql -o FILENAME or meta command \o FILENAME

will send query output (excluding STDERR) to FILENAME
• \g FILENAME executes the query buffer sending output
to FILENAME
• \watch <seconds> can be used to run previous query
repeatedly

138 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Advanced Features - Variables

• psql provides variable substitution

• Variables are simply name/value pairs
• Use \set meta command to set a variable
=# \set city Edmonton
=# \echo :city
Edmonton
• Use \unset to delete a variable
=# \unset city

139 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Advanced Features - Special Variables

• Settings can be changed at runtime by altering special variables

• Some important special variables include:
• AUTOCOMMIT, ENCODING, HISTFILE, ON_ERROR_ROLLBACK,
ON_ERROR_STOP, PROMPT1 and VERBOSITY

• Example:
=# \set AUTOCOMMIT off
• Once AUTOCOMMIT is set to off use COMMIT/ROLLBACK to complete the
running transaction

140 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Conditional Commands

• Conditional commands primarily helpful for scripting

• \if EXPR begin conditional block
• \elif EXPR alternative within current conditional block
• \else final alternative within current conditional block
• \endif end conditional block

141 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Information Commands

• \d[(i|s|t|v|b|S)][+] [pattern]
• List of objects (indexes, sequences, tables, views, tablespaces and
dictionaries)
• \d[+] [pattern]
• Describe structure details of an object
• \l[ist][+]
• Lists of databases in a database cluster

142 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Information Commands (continued)

• \dn+ [pattern]
• Lists schemas (namespaces)
• + adds permissions and description to output
• \df[+] [pattern]
• Lists functions
• + adds owner, language, source code and description to
output

143 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Common psql Meta Commands
• \q or ^d or quit or exit
• 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)

144 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Help
• \conninfo
• Current connection information
• \?
• 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

145 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary
• Introduction to psql
• Conventions
• Connecting to Database
• PSQL Command Line Parameters
• Entering PSQL Commands
• PSQL Meta-Commands
• PSQL SET Parameters
• Conditional Commands
• Information Commands

146 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise – 1
1. Connect to a database using psql

2. Switch databases
3. Describe the customers table
4. Describe the customers table including description
5. List all databases
6. List all schemas
7. List all tablespaces

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

9. Do the same thing, just saving data, not the column headers
10. Create a script via another method, and execute from psql

11. Turn on the expanded table formatting mode

12. Lists tables, views and sequences with their associated access privileges. Which meta command displays the SQL text for a function?
13. View the current working directory

147 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 8
GUI Tools
 Module Objectives

• Introduction to pgAdmin • Schemas

• Registering a server • Database Objects
• Viewing and Editing Data • Maintenance
• Query Tool • Tablespaces
• Databases • Roles
• Languages

149 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Introduction to pgAdmin

• pgAdmin is an open source graphical user interface for PostgreSQL

• pgAdmin can be used to create, manage and maintain database
objects in PostgreSQL
• pgAdmin is web based and requires Apache HTTP server
• pgAdmin is available for download from
https://www.pgadmin.org/download/
• It can also be installed using pgadmin packages which are available
in PostgreSQL yum repository: https://yum.postgresql.org/

150 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Installing pgAdmin on Linux

• Use the yum command to install pgadmin package:

• yum install -y pgadmin4

• Run post-install script to configure the Apache-HTTP:

• /usr/pgadmin4/bin/pgadmin4-web-setup.sh

151 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 First Look - pgAdmin

152 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Registering a Server

• Right Click on the server to add a

server

153 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 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 port 5432 may
not be open on database server 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.

154 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Query Tool

Click on
Query Tool

Click on a
Database

155 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Query Tool - Data Output

Type SQL
Query Click on
Execute Button

View Results

156 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Databases

• The databases menu allows you to create a new database

• 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
• Perform maintenance
• Backup or Restore
• Modify the database properties

157 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Creating a Database

158 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Backup and Restore

159 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Schemas

160 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Schemas - Grant Wizard

161 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Domains

162 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Sequences

163 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Tables

164 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Tables - Indexes

165 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Tables - Maintenance

166 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Rules
• Rules can be applied to tables or views

167 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Triggers
• Create a trigger function before creating a trigger

168 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Views

169 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Create Tablespaces

170 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Roles

171 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Dashboard

• Server Sessions
• Transaction per second
• Tuples in
• Tuples out
• Block I/O
• Server activity - sessions

172 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Postgres Enterprise Manager
GUI tool for monitoring, management and tuning databases

• Aggregates performance and status data -

Collects from DBs, OS, and jobs
• Monitors overall system health - Alerts thru
charts and dashboards, email, or SNMP
• Runs performance diagnostics - Execute
SQL, tune queries, run backups, and deploy
updates
• Provides 200+ built-in alerts - Monitor
bloat, memory utilization, server status,
and more

173 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Why Postgres Enterprise Manager

Manage everything Optimize database Monitor multiple Reduce admin

from one GUI performance scenarios burden
Create tables and stored Locate poorly-running Create dashboards to Accomplish bulk changes
procedures, and manage SQL code to improve collect data from and routine tasks
schemas database performance multiple sources

Operate Postgres at scale across your organization

174 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary

• Schemas • Introduction to pgAdmin

• Database Objects • Registering a server

• Maintenance • Viewing and Editing Data

• Tablespaces • Query Tool

• Databases
• Roles
• Languages

175 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 1

1. Open the pgAdmin interface and connect to the default

PostgreSQL database cluster
• Create a user named pguser
• Create a database named pgdb owned by pguser
• After creating the pgdb database, change its connection limit to 4
• Create a schema named pguser inside the pgdb database
The schema owner should be pguser

176 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 2
1. You have created the pgdb database with the pguser schema.
Create following objects in the pguser schema:
• Table - Teams with columns TeamID, TeamName, TeamRatings
• Sequence - seq_teamid start value - 1 increment by 1
• Columns - Change the default value for the TeamID column to seq_teamid
• Constraint - TeamRatings must be between 1 and 10
• Index - Primary Key TeamID
• View - Display all teams in ascending order of their ratings. Name the view as
vw_top_teams

177 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 3

1. View all the rows present in the Teams table.

2. Using the Edit data window you just opened in the previous
step, insert the following rows into the Teams table:

TeamID TeamName TeamRatings

Auto generated Oilers 1
Auto generated Rangers 6
Auto generated Canucks 8
Auto generated Blackhawks 5
Auto generated Bruins 2

178 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 4

1. Connect to the pgdb database using the query tool

2. Using the graphical query builder retrieve all the rows
present in the Teams table
3. Using the graphical query builder retrieve all the rows
present in the view vw_top_teams

179 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 9
Security
 Module Objectives

• Authentication and Authorization

• Levels of Security
• pg_hba.conf File
• Row Level Security
• Object Ownership
• Application Access Parameters

181 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 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

182 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Levels of Security

Server and • Check Client IP

Application • pg_hba.conf

• User/Password
Database • Connect Privilege
• Schema Permissions

• Table Level Privileges

Object
• Grant/Revoke

183 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 pg_hba.conf – Access Control
• Host based access control file
• Located in the cluster data directory
• Read at startup, any change requires reload
• Contain set of records, one per line
• Each record specify connection type, database name, user name , client IP and method of
authentication
• Top to bottom read
• Hostnames, IPv6 and IPv4 supported
• Authentication methods - trust, reject, md5, password, gss, sspi, krb5, ident, peer, pam, ldap,
radius, bsd, scram or cert

184 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 pg_hba.conf Example
# TYPE DATABASE USER ADDRESS METHOD
# “local” is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256

185 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Authentication Problems
FATAL: no pg_hba.conf entry for host "192.168.10.23", user “edbstore", database “edbuser“
FATAL: password authentication failed for user "edbuser"
FATAL: user "edbuser" does not exist
FATAL: database "edbstore" does not exist

• Self explanatory message is displayed

• Verify database name, username and Client IP in pg_hba.conf
• Reload Cluster after changing pg_hba.conf
• Check server log for more information

186 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Row Level Security (RLS)

• GRANT and REVOKE can be used at table level

• PostgreSQL supports security policies for limiting
access at row level
• By default, all rows of a table are visible
• Once RLS is enabled on a table, all queries must
go through the security policy
• Security policies are controlled by DBA rather
than application
• RLS offers stronger security as it is enforced by
the database

187 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example - Row Level Security

• For example, to enable row level security for the table accounts :
• Create the table first
=> CREATE TABLE accounts (manager text, company
text,contact_email text);
=> ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

• Syntax:
CREATE POLICY name ON table_name
[ AS { PERMISSIVE | RESTRICTIVE } ]
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO{ role_name | PUBLIC | CURRENT_USER | SESSION_USER}[,...] ]
[ USING ( using_expression ) ]
[ WITH CHECK ( check_expression ) ]

188 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example - Row Level Security (continued)

• To create a policy on the accounts table to allow the

managers role to view the rows of their accounts, the
CREATE POLICY command can be used:
=> CREATE POLICY account_managers ON accounts TO managers
USING (manager = current_user);

• To allow all users to view their own row in a user table, a

simple policy can be used:
=> CREATE POLICY user_policy ON users USING (user =
current_user);

189 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 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

190 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 EDB Postgres Security Enhancements

Password policy management Audit compliance for SOX Code protection

DBA managed password profiles, Track and analyze database Protects sensitive IP,
compatible with Oracle profiles activities and user connections algorithms or financial policies
by obfuscates DB source code

EDB/SQL protect Data redaction Virtual private databases

SQL firewall installed directly in Hide/remove data to protect Fine grained access control
DB server, screens queries for sensitive information for GDPR, limits user views of data
common attack profiles PCI and HIPAA compliance records in one table

191 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary

• Authentication and Authorization

• Levels of Security
• pg_hba.conf File
• Row Level Security
• Object Ownership
• Application Access Parameters

192 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 1

1. You are working as a PostgreSQL DBA. Your server box has 2

network cards with ip addresses 192.168.30.10 and 10.4.2.10.
192.168.30.10 is used for the internal LAN and 10.4.2.10 is used
by the web server to connect users from an 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.

193 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 2

1. A new developer has joined the team, and his ID number is

89.
• Create a new user by name dev89 and password password89.
• Then assign the necessary privileges to dev89 so that he can
connect to the edbstore database and view all tables.

194 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 3

1. A new developer joins e-music corp. He has the IP address of

192.168.30.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
2. Configure your server so that the new developer can connect
from his machine.

195 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 10
SQL Primer
 Module Objectives

• Data Types • Sequences

• Structured Query Language • Domains
(SQL)
• SQL Joins
• DDL, DML and DCL Statements
• Using SQL Functions
• Transaction Control
• SQL Format Functions
Statements
• Tables and Constraints • Quoting in PostgreSQL

• Views and Materialized Views • Indexes

197 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Data Types

• Common Data Types:

Numeric Types Character Types Date/Time Types Other Types Advanced Server

TIMESTAMP BYTEA CLOB

NUMERIC CHAR
BOOL
BLOB
DATE MONEY
INTEGER VARCHAR VARCHAR2
XML
TIME
NUMBER
JSON
SERIAL TEXT
INTERVAL JSONB XMLTYPE

198 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Structured Query Language

Data Definition Data Manipulation Data Control Transaction Control

Language (DDL) Language (DML) Language (DCL) Language (TCL)
• CREATE • INSERT • GRANT • COMMIT
• ALTER • UPDATE • REVOKE • ROLLBACK
• DROP • DELETE • SAVEPOINT
• TRUNCATE • SELECT • SET TRANSACTION

199 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 DDL Statements
Statement Syntax
CREATE TABLE CREATE [TEMPORARY][UNLOGGED] TABLE table_name
( [column_name data_type [ column_constraint] )
[ INHERITS ( parent_table) ]
[ TABLESPACE tablespace_name ]
[ USING INDEX TABLESPACE tablespace_name ]

ALTER TABLE ALTER TABLE [IF EXISTS] [ONLY] name [*] action [,…]

DROP TABLE DROP TABLE [ IF EXISTS ] name [, …] [ CASCADE | RESTRICT ]

TRUNCATE TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ….]

TABLE

200 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 DML Statements
Statement Syntax

INSERT INSERT INTO table_name [ ( column_name [, ...] ) ]

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

UPDATE UPDATE [ ONLY ] table_name

SET column_name = { expression | DEFAULT }
[ WHERE condition]

DELETE DELETE FROM [ ONLY ] table_name

[ WHERE condition ]

SELECT SELECT [ ALL | DISTINCT ] [ * | expression ]

[FROM table [,.. ]

201 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 DCL Statements
Statement Syntax

GRANT GRANT { { SELECT | INSERT | UPDATE ……} [, … ] | ALL [PRIVILEGES ] }

ON { [ TABLE ] table_name [, …] | ALL TABLES IN SCHEMA schema_name [ ,…] }
TO role_specification [, …] [ WITH GRANT OPTION ]

REVOKE REVOKE [ GRANT OPTION FOR ]

{ { SELECT | INSERT | UPDATE ……} [, … ] | ALL [PRIVILEGES ] }
ON { [ TABLE ] table_name [, …] | ALL TABLES IN SCHEMA schema_name [ ,…] }
FROM { [ GROUP ] role_name | PUBLIC } [, …]

202 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Transaction Control Language
Statement Syntax

COMMIT COMMIT [ WORK | TRANSACTION ]

ROLLBACK ROLLBACK [ WORK | TRANSACTION ]

SAVEPOINT SAVEPOINT savepoint_name

SET TRANSACTION SET TRANSACTION transaction_mode [, …]

203 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Database Objects
Object Description
TABLE Named collection of rows
VIEW Virtual table, can be used to hide complex queries
SEQUENCE Used to automatically generate integer values that follow a pattern

INDEX A common way to enhance query performance

DOMAIN A data type with optional constraints

204 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Tables
• A table is a named collection of rows
• Each table row has same set of columns
• Each column has a data type
• Tables can be created using the CREATE TABLE statement
• Syntax:

205 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Types of Constraints
• Constraints are used to enforce data integrity
• PostgreSQL supports different types of constraints:
• NOT NULL
• CHECK
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY

• Constraints can be defined at the column level or table level

• Constraints can be added to an existing table using the ALTER TABLE statement
• Constraints can be declared DEFERRABLE or NOT DEFERRABLE
• Constraints prevent the deletion of a table if there are dependencies

206 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Views
• A View is a Virtual Table and can be used to hide complex queries
• Can also be used to represent a selected view of data
• Simple views are automatically updatable
• Allow views with updatable and non-updatable columns
• Views with updatable and non-updatable columns
• Views can be created using the CREATE VIEW statement
• Materialized View can be created using CREATE MATERIALIZED VIEW Statement
• Syntax:
=> CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ]
[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
AS query

207 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Sequences
• A sequence is used to automatically generate integer values that follow a
pattern.
• A sequence has a name, start point and an end point.
• Sequence values can be cached for performance.
• Sequence can be used using CURRVAL and NEXTVAL functions.
• Syntax:
=> CREATE SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue] [ MAXVALUE maxvalue]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]

208 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Domains
• A domain is a data type with optional constraints
• Domains can be used to create a data type which allows a selected list of
values
Table: emp
Column: cityname
Data Type: city

Domain: city Table: shop

Allowed Values: Edmonton, Column: shoplocation

Calgary, Red Deer Data Type: city

Table: clients
Column: res_city
Data Type: city
209 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.
 Types of JOINS
Type Description

INNER JOIN Returns all matching rows from both tables

LEFT OUTER JOIN Returns all matching rows and rows from left-hand table even if there is no
corresponding row in the joined table

RIGHT OUTER JOIN Returns all matching rows and rows from right-hand table even if there is no
corresponding row in the joined table

FULL OUTER JOIN Returns all matching as well as not matching rows from both tables

CROSS JOIN Returns all rows of both tables with Cartesian product on number of rows

210 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Using SQL Functions
• Can be used in SELECT statements and WHERE clauses
• Includes
• String Functions
• Format Functions
• Date and Time Functions
• Aggregate Functions
• Example:
=> SELECT lower(name)FROM departments;
=> SELECT * FROM departments
WHERE lower(name) = 'development';

211 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 SQL Format Functions
Function Return Type Description Example
convert time stamp to to_char(current_timestamp,
to_char(timestamp, text) text
string 'HH12:MI:SS')
to_char(interval '15h 2m 12s',
to_char(interval, text) text convert interval to string
'HH24:MI:SS')
to_char(int, text) text convert integer to string to_char(125, '999')
to_char(double real/double precision to
text to_char(125.8::real, '999D9')
precision, text) strconvert ing

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

timestamp with convert string to time to_timestamp('05 Dec 2000',

to_timestamp(text, text)
time zone stamp 'DD Mon YYYY')
to_timestamp(double timestamp with convert Unix epoch to time
to_timestamp(1284352323)
precision) time zone stamp

212 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Quoting
• Single quotes and dollar quotes are used to specify non-numeric
values
• Example:
'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
• Example:
SELECT * FROM "select“
CREATE TABLE "HelloWorld" ...
SELECT * FROM "Hi everyone and everything"

213 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Indexes

• Indexes are a common way to enhance performance

• EDB Postgres Advanced Server supports several index types:
B-tree (default)

Hash

Block Range Index (BRIN)

GIN

GIST

SP-GiST Indexes

Index on Expressions

214 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example Index
• Syntax:
• CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [
ONLY ] table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC |
DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( column_name [, ...] ) ]
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]

• Example:

215 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary

• Data Types • Sequences

• Structured Query Language • Domains
(SQL)
• SQL Joins
• DDL, DML and DCL Statements
• Transaction Control
• Using SQL Functions
Statements • SQL Format Functions
• Tables and Constraints • Quoting in PostgreSQL
• Views and Materialized Views • Indexes

216 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 1
• Test your knowledge:
• Initiate a psql session
• psql commands access the database True/False
• The following SELECT statement executes successfully: True/False
=> SELECT ename, job, sal AS Salary FROM emp;
• The following SELECT statement executes successfully: True/False
=> SELECT * FROM emp;
• There are coding errors in the following statement. Can you identify them?
=> SELECT empno, ename, sal * 12 ANNUAL SALARY FROM emp;

217 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 2

1. 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.
2. Confirm that the view works. Display the contents of the EMPVU view.
3. Using your EMPVU view, write a query for the SALES department to
display all employee names and department numbers.

218 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 3

1. 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 200. Have your sequence increment by 10. Name the
sequence dept_id_seq.
2. To test your sequence, write a script to insert two rows in the dept
table.

219 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 11
Backup, Recovery and PITR
 Module Objectives
• Backup Types
• Database SQL Dumps
• Restoring SQL Dumps
• Offline Physical Backups
• Continuous Archiving
• Online Physical Backups Using pg_basebackup
• Point-in-time Recovery
• Recovery Settings

221 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Types of Backup
• As with any database, PostgreSQL databases should be backed up regularly

Database SQL Dumps

Logical  pg_dump
Database Cluster SQL Dump
Backups  pg_dumpall

Offline File System Level Backups

 Copy using OS commands
Physical Backups Online File System Level Backups
 Low Level API
 pg_basebackup

222 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Logical Backups
 Database 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
• 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]

224 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 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 <file name> - Send dump to specified file. Filename can be specified using absolute or relative location
-Fp - Dump in plain-text SQL script (default)
-Ft - Dump in tar format
-Fc - Dump in compressed, custom format
-Fd - Dump in directory format
-j njobs - dump in parallel by dumping n jobs tables simultaneously. Only supported with –Fd
-B, --no-blobs - Excludes large objects in dump
-v - Verbose option

225 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 SQL Dump – Large Databases

• If operating systems have maximum file size limits, it causes

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

226 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Restore – SQL Dump

• Backups taken using pg_dump with plain text

format(Fp) psql client
• Backups taken using pg_dumpall

• Backup taken using pg_dump with custom(Fc),

tar(Ft) or director(Fd) formats
• Supports parallel jobs for during restore pg_restore utility
• Selected objects can be restored

227 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 pg_restore Options
-l - Display TOC of the archive file

-F [c|d|t] - Backup file format

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

-N <schema> - do not restore objects in this schema

-t <table> - Restore only specified table

-v - Verbose option

228 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Entire Cluster – SQL Dump
• pg_dumpall is used to dump an entire database cluster
in plain-text SQL format
• Dumps global objects - user, groups, and associated
permissions
• Use psql to restore
• Syntax:
$ pg_dumpall [options…] > filename.backup

229 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 pg_dumpall Options
-a - Data only. Do not dump schema
-s - Data definitions (schema) only
-g - Dump global objects only - not databases
-r - Dump only roles
-c - Clean (drop) databases before recreating
-O - Skip restoration of object ownership
-x - do not dump privileges (grant/revoke)
-v - Verbose option
--disable-triggers - disable triggers during data-only restore
--no-role-passwords - do not dump passwords for roles. This allows use of pg_dumpall by non-superusers
--exclude-database -exclude database whose name match with given pattern

230 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Physical Backups
 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 or in backup mode in order to get a usable backup
• File system backups only work for complete backup and restoration of an entire database
cluster
• Two types of File system backup
• Offline backups
• Online backups

232 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 File System Backups
• Taken using OS Copy command
• Database Server must be shutdown
• Complete Backups
Offline Backups • Used to restore data

• Continuous archiving must be enabled

• Database server start/end backup mode
• Complete backups
Online Backups • Used to recover data
• Two methods - Low Level API &
pg_basebackup

233 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Continuous Archiving
• PostgreSQL maintains WAL files for all transactions in pg_wal
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 replica
- archive_mode must be set to on (can be set to always)
- archive_command must be set in postgresql.conf which archives WAL
logs and supports PITR

234 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Continuous Archiving Methods
• Parameters in postgresql.conf file
• wal_level = replica
• archive_mode = on
Archiver Process • archive_command = ‘cp -i %p /pgsql/archive/%f’
• Restart the database server
• Archive files are generated after every log switch

•Parameters in postgresql.conf file

•wal_level = replica
•archive_mode = on
Streaming WAL •max_wal_senders = 3
•Restart the database server
•pg_receivewal –h localhost –D /pgsql/archive
•Transactions are streamed and written to archive files

235 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Base Backup Using pg_basebackup Tool

• pg_basebackup can take an online base backup of a

database cluster
• This backup can be used for PITR or Streaming
Replication
• pg_basebackup makes a binary copy of the database
cluster files
• System is automatically put in and out of backup mode

236 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 pg_basebackup - Online Backup
• Steps require to take Base Backup:
• Modify pg_hba.conf
host replication postgres [Ipv4 address of client]/32 md5

• Modify postgresql.conf
wal_level = replica
archive_command = 'cp -i %p /users/postgres/archive/%f‘
archive_mode = on
max_wal_senders = 3
wal_keep_size = 512

• Backup Command:
$ pg_basebackup [options] ..

237 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Options for pg_basebackup command
-D <directory name> - Location of backup
-F <p or t> - Backup files format. Plain(p) or tar(t)
-R - write standby.signal and append postgresql.auto.conf
-T OLDDIR=NEWDIR - relocate tablespace in OLDDIR to NEWDIR
--waldir - Write ahead logs location
-z - enable gzip compression for files
-Z level - Compression level
-P - Progress Reporting
-h host - host on which cluster is running
-p port - cluster port

• To create a base backup of the server at localhost and store it in the local directory
/usr/local/pgsql/backup
$ pg_basebackup -h localhost -D /usr/local/pgsql/backup

238 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Verify Base Backups
• Verify backup taken by pg_basebackup using pg_verifybackup utility
• Backup is verified against a backup_manifest generated by the server at
the time of the backup
• Only plain format backups can be verified

239 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 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_wal subdirectory
• Must be configured before it is needed (write-ahead log
archiving must be enabled)

240 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Performing Point-in-Time Recovery
Prepare Restore Configure Recover
Stop the server Copy data cluster Configure Start the server
Take a file system files and folders recovery settings using service or
level backup if from backup in pg_ctl utility
possible location to the postgresql.conf
Check error log
data directory file
Clean the data for any issue
directory Use cp -rp to Create
recovery.signal
preserve recovery.signal
file is removed
privileges file in the data
automatically
directory
after recovery

241 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Point-in-Time Recovery Settings
• Restoring archived WAL using restore_command
parameter:
• Unix:
restore_command = 'cp /mnt/server/archivedir/%f "%p"'
• Windows:
restore_command = 'copy c:\\mnt\\server\\archivedir\\"%f" "%p"'

• Recovery target settings:

• recovery_target_name
• recovery_target_time
• recovery_target_xid
• recovery_target_action

242 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Backup and Recovery Tool
Safeguard business data and ensure trouble-free recovery

• Centralized catalog for all backup

data
• Configures retention policies for
multiple backups
• Restores the database from
system-wide catalog when
disaster strikes

243 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Why Backup and Recovery Tool
Disaster recovery for PostgreSQL

Ensure trouble-free Get one solution for Reduce restore Take control of
backup management all PostgreSQL time recovery process

Point-and-click or Command Applies to multi-cloud and Provides full and Point-in-time recovery to
Line Interface (CLI) on-premises deployments block-level incremental a specified transaction ID
backups or timestamp

244 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary
• Backup Types
• Database SQL Dumps
• Restoring SQL Dumps
• Offline Physical Backups
• Continuous Archiving
• Online Physical Backups Using pg_basebackup
• Point-in-time Recovery
• Recovery Settings

245 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 1

1. The 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 /pgbackup and assign
ownership to the Postgres user using the chown utility or the
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
/pgbackup directory

246 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 2
1. Take a dump of the edbuser schema from the edbstore database
and name the file as edbstore_schema.sql

2. Take a data-only dump of the edbstore database, disable all triggers

for a faster restore, use the INSERT command instead of COPY, and
name the file as edbstore_data.sql

3. Take a full dump of customers table and name the file as

edbstore_customers.sql

247 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 3

1. Take a full database dump of edbstore in compressed

format using the pg_dump utility, name the file as
edbstore_full_fc.dmp

2. Take a full database cluster dump using pg_dumpall.

Remember pg_dumpall supports only plain text format;
name the file edbdata.sql

248 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 4
In this exercise you will demonstrate your ability to restore a database.
1. Drop database edbstore.
2. Create database edbstore with owner edbuser.
3. Restore the full dump from edbstore_full.sql and verify all the objects
and their ownership.
4. Drop database edbstore.
5. Create database edbstore with edbuser owner.
6. Restore the full dump from the compressed file edbstore_full_fc.dmp
and verify all the objects and their ownership.

249 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 5

1. Create a directory /opt/arch or c:\arch and give

ownership to the Postgres user.

2. Configure your cluster to run in archive mode and set the

archive log location to be /opt/arch or c:\arch.

3. Take a full online base backup of your cluster in the

/pgbackup directory using the pg_basebackup utility.

250 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 12
Routine Maintenance Tasks
 Module Objectives

• Updating Optimizer Statistics

• Handling Data Fragmentation using Routine Vacuuming
• Preventing Transaction ID Wraparound Failures
• Automatic Maintenance using Autovacuum
• Re-indexing in Postgres

252 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Database Maintenance

• Data files become fragmented as data is modified and deleted

• Database maintenance helps reconstruct the data files
• If done on time nobody notices but when not done everyone
knows
• Must be done before you need it
• Improves performance of the database
• Saves database from transaction ID wraparound failures
253 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.
 Maintenance Tools
• Maintenance thresholds can be configured using the PEM Client and PEM Server
• PostgreSQL maintenance thresholds can be configured in postgresql.conf
• Manual scripts can be written watch stat tables like pg_stat_user_tables
• Maintenance commands:
• ANALYZE
• VACUUM
• CLUSTER
• Maintenance command vacuumdb can be run from OS prompt
• Autovacuum can help in automatic database maintenance

254 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Optimizer Statistics
• Optimizer statistics play a vital role in query planning
• Not updated in real time
• Collect information for the relations including size, row counts,
average row size and the row sampling
• Stored permanently in the catalog tables
• The maintenance command ANALYZE updates the statistics
• Thresholds can be set using the PEM Client to alert you when the
statistics are not collected on time

255 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example - Updating Statistics

256 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Data Fragmentation and Bloat

• Data is stored in data file pages

• An update or delete of a row does not immediately
remove the row from the disk page
• Eventually this row space becomes obsolete and causes
fragmentation and bloating
• Set PEM Alert for notifications

257 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Routine Vacuuming

• Obsoleted rows can be removed or reused using vacuuming

• Helps in shrinking data file size when required
• Vacuuming can be automated using autovacuum
• The VACUUM command locks tables in access exclusive mode
• Long running transactions may block vacuuming thus it should
be done during low usage times

258 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Vacuuming Commands

• When executed, the VACUUM command:

• Can recover or reuse disk space occupied by obsolete rows
• Updates data statistics
• Updates the visibility map, which speeds up index-only scans
• Protects against loss of very old data due to transaction ID wraparound
• The VACUUM command can be run in two modes:
- VACUUM
- VACUUM FULL

259 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Vacuum and Vacuum Full
• VACUUM
• Removes dead rows and marks the space available for future reuse
• Does not return the space to the operating system
• Space is reclaimed if obsolete rows are at the end of a table

• VACUUM FULL
• More aggressive algorithm compared to VACUUM
• Compacts tables by writing a complete new version of the table file with no dead space
• Takes more time
• Requires extra disk space for the new copy of the table, until the operation completes

260 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 VACUUM Syntax
• VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
where option can be one of:
• FULL [ boolean ]
• FREEZE [ boolean ]
• VERBOSE [ boolean ]
• ANALYZE [ boolean ]
• DISABLE_PAGE_SKIPPING [ boolean ]
• SKIP_LOCKED [ boolean ]
• INDEX_CLEANUP [ boolean ]
• TRUNCATE [ boolean ]
• PARALLEL integer

261 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example - Vacuuming

262 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example – Vacuuming (continued)

263 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Preventing Transaction ID Wraparound Failures

• MVCC depends on transaction ID numbers

• 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
• This causes a catastrophic data loss
• To avoid this, every table in the database must be vacuumed at
least once for every two billion transactions

264 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Vacuum Freeze
• VACUUM FREEZE will mark rows as frozen
• Postgres reserves a special XID, FrozenTransactionId
• FrozenTransactionId is always considered older than every normal XID
• VACUUM FREEZE replaces the transaction IDs with FrozenTransactionId, thus
rows will appear to be “in the past”
• vacuum_freeze_min_age controls when a row will be frozen
• VACUUM normally skips the pages without dead row versions but some rows may need
FREEZE
• vacuum_freeze_table_age controls when the whole table must be scanned

265 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 The Visibility Map

• Each heap relation has a Visibility Map which keeps track of

which pages contain only tuples
• Stored at <relfilenode>_vm
• Helps vacuum to determine whether pages contain dead rows
• Can also be used by index-only scans to answer queries
• VACUUM command updates the visibility map
• The visibility map is vastly smaller, so can be cached easily

266 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Vacuumdb Utility

• The VACUUM command has a command-line executable

wrapper called vacuumdb
• vacuumdb can VACUUM all databases using a single command
• Syntax:
• vacuumdb [OPTION]... [DBNAME]
• Available options can be listed using:
• vacuumdb --help

267 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Autovacuuming
• Highly recommended feature of Postgres
• It automates the execution of VACUUM, FREEZE and ANALYZE commands
• Autovacuum consists of a launcher and many worker processes
• A maximum of autovacuum_max_workers worker processes are allowed
• Launcher will start one worker within each database every autovacuum_naptime
seconds
• Workers check for inserts, updates and deletes and execute VACUUM and/or ANALYZE
as needed
• track_counts must be set to on as autovacuum depends on statistics
• Temporary tables cannot be accessed by autovacuum

268 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Autovacuuming Parameters
Autovacuum Launcher Process
•autovacuum
Autovacuum Worker Processes
•autovacuum_max_workers
•autovacuum_naptime
Vacuuming Thresholds
•autovacuum_vacuum_scale_factor
•autovacuum_vacuum_threshold
•autovacuum_analyze_scale_factor
•autovacuum_analyze_threshold
•autovacuum_vacuum_insert_scale_threshold
•autovacuum_vacuum_insert_threshold
•autovacuum_freeze_max_age

269 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Per-Table Thresholds
• Autovacuum workers are resource intensive
• Table-by-table autovacuum parameters can be configured for large tables
• Configure the following parameters using ALTER TABLE or CREATE TABLE:
• autovacuum_enabled
• autovacuum_vacuum_threshold
• autovacuum_vacuum_scale_factor
• autovacuum_analyze_threshold
• autovacuum_analyze_scale_factor
• autovacuum_vacuum_insert_scale_threshold
• autovacuum_vacuum_insert_threshold
• autovacuum_freeze_max_age

270 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Routine Reindexing
• Indexes are used for faster data access
• UPDATE and DELETE on a table modify underlying index entries
• Indexes are stored on data pages and become fragmented over time
• REINDEX rebuilds an index using the data stored in the index's table
• Time required depends on:
• Number of indexes
• Size of indexes
• Load on server when running command

271 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 When to Reindex

• There are several reasons to use REINDEX:

• An index has become "bloated", meaning it contains many empty or
nearly-empty pages
• You have altered a storage parameter (such as fillfactor) for an index
• An index built with the CONCURRENTLY option failed, leaving an
"invalid" index
• Syntax:
REINDEX [(VERBOSE)] {INDEX|TABLE|SCHEMA|DATABASE|SYSTEM}[CONCURRENTLY] name

272 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary

• Updating Optimizer Statistics

• Handling Data Fragmentation using Routine Vacuuming
• Preventing Transaction ID Wraparound Failures
• Automatic Maintenance using Autovacuum
• Re-indexing in Postgres

273 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 1
1. While monitoring the table statistics on the edbstore database, you found
out that some tables are not automatically maintained by autovacuum and
decided to perform the manual maintenance. Write a SQL script to
perform the following maintenance:
• Reclaim obsolete row space from the customers table.
• Update statistics for the emp and dept tables.
• Mark all the obsolete rows in the orders table for reuse.
2. Execute the newly created maintenance script on the edbstore
database.

274 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise – 2

1. The composite index named ix_orderlines_orderid

on (orderid, orderlineid) columns of the
orderlines table is performing very slowly. Write a
statement to reindex this index for better performance.

275 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 13
Data Dictionary
 Module Objectives

• The System Catalog Schema

• System Information Tables
• System Information Functions
• System Administration Functions
• System Information Views
• Oracle-like Dictionaries in EDB Postgres

277 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 The System Catalog Schema

• Store information about table and other objects

• Created and maintained automatically in pg_catalog
schema
• pg_catalog is always effectively part of the search_path
• Contains:
• System Tables like pg_class etc.
• System Function like pg_database_size() etc.
• System Views like pg_stat_activity

278 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 System Information Tables

• \dS in psql prompt will give you the list of pg_* tables and
views
• This list is from pg_catalog schema

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

279 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 More System Information Tables

• Provides summary of the contents of the server

pg_file_settings configuration file

pg_policy • Stores row level security for tables

• Provides access to useful information about each

pg_policies row-level security policy in the database

280 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 System Information Functions

current_database() current_schema[()] pg_postmaster_start_time() version()

current_user current_schemas(boolean) pg_current_logfile() txid_status()

pg_conf_load_time() pg_jit_available()

281 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 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

pg_ls_logdir() •Returns the name, size, and last modified time of each file in the log directory

pg_ls_waldir() •Returns the name, size, and last modified time of each file in the WAL directory

282 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 More System Administration Functions
• Disk space used by a tablespace, database, relation or
pg_*_size 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

• File operation functions. Restricted to superuser use and

pg_ls_dir, pg_read_file only on files in the data or log directories

pg_blocking_pids() • Function to reliably identify which sessions block others

283 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 System Information Views
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

pg_stat_archiver • status of the archiver process

pg_stat_progress_basebackup • view pg_basebackup progress

pg_stat_progress_vacuum • provides progress reporting for VACUUM operations

pg_stat_progress_analyze • provides progress details for ANALYZE operations

• provides a summary of the contents of the client authentication
pg_hba_file_rules
configuration file, pg_hba.conf

284 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 EDB Postgres Advanced Server Oracle-Like Dictionaries

• The sys schema contains View Description

Oracle compatible catalog user_* User’s view (what is in your schema;

what you own)
views
all_* Expanded user’s view (what you can
• EDB Postgres Advanced access)

Server provides DBA_, ALL_ dba_* Database administrator’s view (what

is in everyone’s schemas)
and USER_ dictionary views
to view database object edb$ Performance-related data

information

285 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary

• The System Catalog Schema

• System Information Tables
• System Information Functions
• System Administration Functions
• System Information Views
• Oracle-like Dictionaries in EDB Postgres

286 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 1

1. You are working with different schemas in a database. You

need to determine all the schemas in your search path.
Write a query to find the list of schemas currently in your
search path.

287 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 2

1. You need to determine the names and definitions of all of

the views in your schema. Create a report that retrieves
view information - the view name and definition text.

288 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 3

1. Create report of all the users who are currently

connected. The report must display total session time of
all connected users.

2. You found that a user has connected to the server for a

very long time and have decided to gracefully kill its
connection. Write a statement to perform this task.

289 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise - 4

1. Write a query to display the name and size of all the

databases in your cluster. Size must be displayed using a
meaningful unit.

290 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Module 14
Moving Data
 Module Objectives

• Loading flat files

• Import and export data using COPY
• Examples of COPY Command
• Using COPY FREEZE for performance
• Introduction to EDB*Loader for EDB Postgres Advanced
Server

292 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Loading Flat Files into Database Tables
• A "flat file" is a plain text or mixed text file which usually contains
one record per line
• PostgreSQL offers the following option to load flat files into a
database table:
• COPY Command

• EDB Postgres Advanced Server offers two options to load flat files
into a database table:
• EDB*Loader

• COPY Command

293293 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 The COPY Command

• COPY moves data between PostgreSQL tables and

standard file-system files
• COPY TO copies the contents of a table or a query to a file
• COPY FROM copies data from a file to a table
• The file must be accessible to the server

294 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 COPY Command Syntax
Copy From:
•COPY table_name [(column list)] FROM 'filename'|PROGRAM 'command'|STDIN [options][WHERE cond.]

Copy To:
•COPY table_name[(column list])|(query) TO 'filename'|PROGRAM'command'|STDOUT [options]

Copy Command Options

•FORMAT format_name
•OIDS [ boolean ]
•FREEZE [ boolean ]
•DELIMITER 'delimiter_character'
•NULL 'null_string'
•HEADER [ boolean ]
•QUOTE 'quote_character'
•ESCAPE 'escape_character'
•FORCE_QUOTE { ( column_name [, ...] ) | * }
•FORCE_NOT_NULL ( column_name [, ...] )
•FORCE_NULL ( column_name [, ...] )
•ENCODING 'encoding_name'

295 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example Export to File
edbstore=# COPY emp (empno,ename,job,sal,comm,hiredate) TO '/tmp/emp.csv' CSV HEADER;
edbstore=# \! 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

296 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example Import from File
edbstore=# CREATE TEMP TABLE empcsv (LIKE emp);
CREATE TABLE
edbstore=# COPY empcsv (empno, ename, job, sal, comm, hiredate)
edbstore-# FROM '/tmp/emp.csv' CSV HEADER;
COPY
edbstore=# 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)

297 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Example - COPY Command on Remote Host

• COPY command on remote host using psql

$ cat emp.csv | ssh 192.168.192.83 “psql –U
edbstore edbstore -c ‘copy emp from stdin;’”

298 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 COPY FREEZE

• FREEZE is a new option in the COPY statement

• Adds rows to a newly created table and freezes them
• Table must be created or truncated in current subtransaction
• Improves performance of initial bulk load
• Does violate normal rules of MVCC
• Usage:
=> COPY tablename FROMfilename FREEZE;

299 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 EDB*Loader

• EDB*Loader is a high-performance bulk data loader

• Supports Oracle SQL*Loader data loading methods -
• Conventional Control Discard
File File
• Direct
• Parallel Data File EDB*Loader Log File

Bad File
Param File
Advanced
Server
Table

300300 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Data Loading Methods
• Conventional path loading uses basic insert processing to add rows to the table
• Constraints, Indexes and triggers are enforced during Conventional path data loading
• Direct path loading is faster than conventional path loading, but is non-recoverable
• Direct path loading also requires removal of constraints and triggers from the table
• Conventional path data loading is slower than Direct path loading, but is fully
recoverable
• A Parallel direct path load provides even greater performance

301 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Module Summary

• Loading flat files

• Import and export data using COPY
• Examples of COPY Command
• Using COPY FREEZE for performance
• Introduction to EDB*Loader for EDB Postgres Advanced
Server AS

302 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Lab Exercise – 1

1. Unload the emp table from the edbstore schema to a csv file,
with column headers.

2. Create a copyemp table with the same structure as the emp

table.

3. Load the csv file (from step 1) into the copyemp table.

303 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

Conclusion and Next Steps
 Course Summary
• Introduction
• System Architecture
• Installation and Database Clusters
• Configuration
• Creating and Managing Databases
• User Tools - CUI and GUI
• Security
• SQL Primer
• Backup, Recovery and PITR
• Routine Maintenance Tasks
• Data Dictionary
• Loading and Moving Data

305 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

 Next Steps
• Certify your Postgres skills with EDB Certifications for Postgres
• Continue your skills development with the following classes:
• PostgreSQL Advanced Database Administration
• Monitoring and Alerting with Postgres Enterprise Manager
• Tuning and Maintenance
• See the Training Portal for the full library of Postgres training classes
• Get familiar with the EDB Tools available as part of the EDB Postgres Platform
• For any questions related to EDB Postgres Trainings and Certifications,
or for additional information, write to:
• [email protected]

306 © Copyright EnterpriseDB Corporation, 2020. All rights reserved.

THANK YOU

[email protected]
www.enterprisedb.com