PostgreSQL : Introduction

Introduction Installation The psql client Authentication and privileges Backup and restoration
I
Part 1
www.opensourceschool.fr – Licence Creative Commons (CC BY-SA 3.0 FR) – 2/110

Part 1
1 Introduction
2 Installation
3 The psql client
4 Authentication and privileges
5 Backup and restoration

Introduction

What is PostgreSQL
What is PostgreSQL

What is PostgreSQL
What is PostgreSQL ?
Open Source
BSD-like licence
Many commercial derivatives (EnterpriseDB,. . . )
Uncompromised
Slow evolution but rock solid
No for-proﬁt company behind
Goes much further than standards asks for.
Relational
ACID
Object-Relational
Stored procedures
Cross-platform
Linux
Windows
Most *NIX

What is PostgreSQL
History
Evolved from Ingres
Postgres = Ingres + SQL
Postgres released in 1995
1999 : support for real ACID and PL/pgSQL
2005 : optimized enough to become a real contender
2009 : PostgreSQL 8.4

What is PostgreSQL
How does PostgreSQL compare to MySQL ?
Respects the standards strictly.
Strict value checking. Example: MySQL 4.X considers
2012-01-00 a valid date , same for 2012-02-31, MySQL 5 fixes
the later example only.
Really ACID. Exemples
In PostgreSQL NOW() is the start of the transaction not
really the current timestamp.
Transactional DDL : In MySQL, an ALTER will silently COMMIT
any transaction.
In MySQL, foreign key cascades do not fire triggers,API’s
don’t fire triggers

What is PostgreSQL
Maximum sizes of objects
Object PostgreSQL MyISAM InnoDB
Database Unlimited 1 Unlimited2 Adressable space3
Table size 32TB Max file size 4 Max file size 4
Row size 400GB 64kB 5 64kB 5
Field size 1GB 8kB 6 8kB 6
Columns up to 16007 ?8 10009
Indexes unlimited 256 256
1
some 32TB databases exist
2
but databases over 200GB are quite rare, or quite slow
3
innodbPageSize * 224
with default innodbPageSize = 16kB, for all tables
unless innodb file per table=1
4
4TB on typical linux
5
not counting BLOBs
6
not counting BLOBs, VARCHAR and, VARBINARY
7
250 to 1600, depending on field types
8
constraint is row size
9
row size constraint still applies

What is PostgreSQL
How does PostgreSQL compare to MySQL ?
Deeply object-oriented (database creation is a
fork+inheritance, tables can inherit from one another...)
Storage of large objects (BLOB) is automagically ”put aside”
to not clobber tables.
PostgreSQL is renowned in the geographical ﬁeld :
PostGIS
PostgreSQL can be used for datawarehousing :
OpenStreetMap has a 2.4TB database and it’s a small one.
PostgreSQL does not have a query cache : you need an
external tool for that

Basic concepts
Basic concepts

Basic concepts
Cluster
A database cluster is a single PostgreSQL server instance
If you want multiple clusters you will need :
Different config files
A different data directory
A different network port
Usually, servers only have one cluster

Basic concepts
Database
A database is a set of objects that can be used together
Tables
Functions
Custom data types
Views
Triggers
...
Once connected to a database, you stay inside it, and cannot
use objects from other databases
A database cluster can have multiple databases

Basic concepts
Schema
A schema is a namespace within a database
All databases have a default, public schema
It can be used to separate things
It can also help with access control
A database can have multiple schemas

Basic concepts
Tables
Your regular DB table
myschema.mytable : explicit table indication
mytable : look for table in the search path (contains public)

Basic concepts
Tablespaces
The physical location of ﬁles on disk. Your friendly sysadmin
can choose to put one tablespace on a faster medium (SSD)
to increase performance of this tablespace’s tables.
Can be deﬁned per database or per user
Or explicitely on table creation
Any object in the whole cluster can use any tablespace if
permissions allow it

Installation

Installing PostgreSQL

Grabbing the bits
Debian : aptitude install postgresql
RedHat :
yum install postgresql-server
chkconfig postgresql on
service postgresql initdb
service postgresql start
You might want to install postgresql-contrib, which
contains may community-developped tools : benchmark tools,
additional diagnostic tools, . . .

First steps : processes
Look at the running processes
postgres
-D /var/lib/postgresql/8.4/main
-c config file=/etc/.../postgresql.conf
Master database process
postgres: writer process
Writes data blocks back to disk
postgres: wal writer process
Writes transaction logs
postgres: autovacuum launcher process
Database housekeeping
postgres: stats collector process
Stats
postgres: postgres postgres [local] idle
Each connection to the DB spawns a process

First steps : config files
Each cluster has its own set of configuration files and data files
environment : environment variables for the server
pg ctl.conf : startup command-line options
pg hba.conf : authentication
pg ident.conf : ident maps for authentication
postgresql.conf : main config file
start.conf : whether this cluster is auto started or not
When changing the configuration, there is usually no need to
restart, a reload sould be enough

First steps : ﬁles
That -D parameter was the $datadir : by default, everything
is there
On RedHat, everything is there
On Debian, as usual, conf goes to /etc/postgresql and logs
go to /var/log/postgresql
Debian has a version/cluster convention that allows you
to run as many instances of as many versions of
PostgreSQL as you want easily

First steps : ﬁles
base : default tablespace
global : global tablespace
pg tblspc : other tablespaces (can contain symlinks)
pg log : application logs (admin-readable)
pg clog, pg multixact, pg stat tmp, pg subtrans,
pg twophase, pg xlog : various binary logs and technical
directories

Basic administration

su - postgres : required to do any administrative operation
on the database with the default conﬁguration
createuser -P myuser : create a new user
You should answer ”no” to all its questions : access users
should not be able to create anything global
createdb -O myuser mydb : create a database that belongs
to the user, he will have full privileges on it
you can now psql -hlocalhost -Umyuser mydb

The psql client

psql basic usage
Uses unix-socket by default to connect to localhost, check
your ident or adapt pg hba.conf
Connect with psql DBNAME USERNAME -W for interactive
auth
Use psql -h 10.1.2.3 to connect to a remote postgres.
psql uses less as a result pager by default (usefull)

psql basic usage
Once connected,
l : list database (equivalent to MySQL’s SHOW DATABASES)
c : connect to database (equivalent to MySQL’s USE)
dt : list tables in current database (equivalent to MySQL’s
SHOW TABLES).
dt+ : shows more information like ownerships and comments
on each table.
di : show indexes
d table : lists columns of a table (equivalent to MySQL’s
DESC table)
x : Print one column per line

Autocommit
By default, autocommit is set, this means any command you
enter in the psql client will be automatically commited as it it
were a mini-transaction
You can set AUTOCOMMIT off
With autocommit set to oﬀ, even if you do not explicitely
start a transaction with BEGIN, no permanent change will be
made until you COMMIT

Passwords
psql will prompt automatically for a password if required
You cannot give the password on the command line !
.pgpass ﬁle in the homedirectory :
hostname:port:database:username:password
eg : *:*:mydb:myuser:mypw

Authentication and privileges

Authentiﬁcation
Authentiﬁcation

Authentiﬁcation
Authentiﬁcation
PostgreSQL allows you to choose between many authentication
schemes :
md5 : regular user/password based on embedded account info
ident : get identity from local socket (only for local auth !)
krb5
ldap
cert : SSL client auth
gss/sspi/pam : delegate to external framework

Authentification
Authentification
Authentication is configured in pg hba.conf:
# Database a d m i n i s t r a t i v e l o g i n by UNIX s o c k e t s
l o c a l a l l p o s t g r e s i d e n t
# ” l o c a l ” i s f o r Unix domain socket c o n n e c t i o n s only
l o c a l a l l a l l i d e n t
# IPv4 l o c a l c o n n e c t i o n s :
host a l l a l l 1 2 7 . 0 . 0 . 1 / 3 2 md5
# IPv6 l o c a l c o n n e c t i o n s :
host a l l a l l : : 1 / 1 2 8 md5

Authentiﬁcation
Authentiﬁcation
Allowing external connections :
First, change the listen address in postgresql.conf
l i s t e n a d d r e s s e s = ’∗ ’
Then, add in pg hba.conf
# From j u s t one f r o n t s e r v e r
host a l l a l l 172.16.0.54/32 md5
# From a pool of f r o n t s e r v e r s
host a l l a l l 172.16.0.0/24 md5
# From everywhere ( don ’ t )
host a l l a l l 0 . 0 . 0 . 0 / 0 md5

Roles : users and groups

Role management :
CREATE ROLE something; : not very useful
CREATE ROLE foobar LOGIN PASSWORD "toto"; : role can
login
CREATE USER foobar; : same thing
CREATE ROLE foobar CREATEDB; : this role can create
databases
CREATE ROLE foobar CREATEROLE; : this role can admin
roles
CREATE ROLE foobar SUPERUSER; : this role is not
submitted to permission checks
ALTER ROLE, DROP ROLE
GRANT group role TO user; : add a user to a group
SET ROLE group role; : gain the privileges of the group
Role display :
SELECT * FROM pg roles;
du

Privileges
Privileges

Privileges
Privileges
SELECT, INSERT, UPDATE, DELETE
TRUNCATE
REFERENCES, TRIGGER, TEMPORARY: create foreign
keys/triggers/temp tambes
CREATE : create tables, indexes, etc.
CONNECT : allow a user to connect to the database
USAGE, EXECUTE : allow a user to deﬁne and execute functions
ALL/ALL PRIVILEGES : allow everything
rights to DROP and ALTER are not grantable, and given to
object owners and superusers

Privileges
Privileges
GRANT rights ON object TO role;
REVOKE rights ON object FROM role;
dp : display permissions

Ownership
Ownership

Ownership
Ownership
Owners have default privileges
Create any table in a db
read any column in a table
etc..
When using createdb to create a new database for your
application do not forget to set the owner
You can see the owner with dt
You can change the owner with ALTER

Backup and restoration

Backups
Backups

Backups
dumps
pg dump
pg dump is the standard database dump program :
pg dump dbname > outfile (as superuser)
pg dump -t mytable dbname > outfile : just one table
(-b for blobs)
You can use -Fc to produce a optimized, more ﬂexible dump
using an internal format (not SQL)
pg dumpall
pg dumpall dumps all databases
pg dumpall > outfile (as superuser)
Backups will be coherent, and will not lock tables (MVCC) except
if you do a heavy modiﬁcation like ALTER

Restoration
Restoration

Restoration
Restoration
psql < mydump
pg restore -d mydb mydump if your dump is in
PostgreSQL ’s custom format
pg restore mydump converts the dump into SQL format for
later use

Physical backup and PITR

Instead of doing dumps, you can ”freeze” the database and do
a filesystem backup
Then, the WALs are archived
Using the filesystem backup + the WAL, you can restore to
any point in time
This requires a specific, complicated config
Stick to good old dumps if you don’t really need that

Internal Architecture Performance optimization Stats and monitoring Logs Replication
II
Part 2

Part 2
6 Internal Architecture
7 Performance optimization
8 Stats and monitoring
9 Logs
10 Replication

Internal Architecture

Storage engine
Storage engine

Storage engine
Filesystem
The tablespace is a folder that contains oddly-named ﬁles
Those ﬁles are data blocks (multiples of 8k)
They are mapped in memory in various caches

Storage engine
Cache
PostgreSQL uses two levels of cache
Shared buﬀers contain a copy of data blocks
Since PostgreSQL knows what they are used for, it can
invalidate only some of them during certain operations
The OS cache (not PostgreSQL memory) also contains a
copy of the data blocks
PostgreSQL relies on it for performance considerations
(query planner)
Invalidation is controlled by the OS, and not smart from a DB
point of view
It is however, smart from a hardware point of view
Both level are designed to work together

Storage engine
Write-ahead log
The WAL is an essential component
stored in pg xlog
Contains every modiﬁcation of the database
Optimized for write performance
Pre-allocated
sequential writes
Used to guarantee integrity : on COMMIT (or autocommit),
the modiﬁcation is written and fsync-ed to the log
If a crash occurs, the server re-synchronises the data from the
xlog

Storage engine
Checkpoints and background writer
The background writer’s task is to commit dirty buﬀers to the
disk (or OS cache)
Checkpointing is the operation that writes all data back to the
hard drive (bypassing OS cache)
throttled to avoid IO peaks
It occurs automatically
When the transation logs are full
After a certain time

Transactions
Transactions

Transactions
MVCC : Multi Version Concurrency Control
MVCC is a method used in many databases, relational or
others
InnoDB, Oracle Database, Berkeley DB, DB2, Sybase (and
MSSQL)
CouchDB
Subversion
EHcache
and of course, PostgreSQL
rows have a xmin, and xmax value, containing a xid, or
transaction ID
Every transaction increments the current xid,
Is is an internal column, but you can display it with
SELCT txid current();
SELECT *,xmin,xmax from mytable;

Transactions
MVCC and vaccuuming
UPDATE does not modify rows, it creates a new row with a
xmin set to the current transaction and sets xmax to the
current transaction on the old row
DELETE does not delete rows, it sets the row’s xmax to the
current transaction
The operation that removes unneeded rows is VACCUUM
regular VACCUUM simply removes unneeded rows by marking
them free in the Free Space Map (FSM)
VACUUM FULL actually disallocates unneeded rows from the
tablespace, it is a heavy operation, but can help of very active
tables (sessions)
regular VACCUUM is automatically performed by the
autovaccuum process

Transactions
Transaction isolation level
PostgreSQL automatically manages row-level locking as
needed
READ COMMITED (default)
In the same transaction, you can see a diﬀerent image of the
DB depending if another transaction has been validated in the
meantime
You will never, however, see a transaction that has not been
commited yet
But you can lock lines explicitely with SELECT FOR UPDATE
SERIALIZABLE
If a transaction detects that a value was modiﬁed since the
beginning of the transaction, it will fail
The other standard levels (READ UNCOMMITED, and
REPEATABLE READ) are mapped to the closest level of those
two

Performance optimization

Memory
Memory

Memory
shared buﬀers
The main memory pool used by PostgreSQL
Usually : shared buffers = totalram/4
more isn’t better
SHM : need to tweak sysctl.conf
kernel.shmmax : maximum size (in bytes) of a memory
segment
kernel.shmall : maximum size (in pages) of all memory
segments
getconf PAGE SIZE
effective cache size : give it the actual cache size,
including OS cache (50 − 70% of memory) to help the query
planner. This will not allocate anything, it’s just helping the
query planner make the right decisions.

Memory
work memory
work mem : memory for sorts, hash tables, etc.
The more work mem, the less you need to store temporary
tables to disk
Any single session can run multiple operations concurrently
(complex queries)
so don’t put too much there, or you’ll run out of memory on
high loads
maintenance work mem : memory available to maintenance
operation (INDEX, VACUUM, ALTER, . . . )
You can give more than work mem, not many maintenance
operations will occur simultaneously
a good value is totalram/20

Disk
Disk

Disk
Disk
Checkpoints are the main source of IO
checkpoint segments : number of 16Mb log ﬁles to keep
before ckecpointing
recommended value : 10
checkpoint timeout : maximum time between two
checkpoint
Default : 5min, but you can put more if you need to
checkpoint completion target : helps spread the IO load
default : 0.5
you can try : 0.9 or something in-between if you want to
spread the load more
wal buffers
a good value is 16Mb, which is the size of a WAL

Network
Network

Network
Network
max connections : maximum amount of connections
superuser reserved connections : maximum amount of
connections for the superuser
If you use PHP, you’ll need
max connections =
frontends
maxclients+superuser reserved connections
If you have connection pools :
max connections =
frontends
poolsize+superuser reserved connections

Query planner
Query planner

Query planner
Query planner
The query planner is used by PostgreSQL to find the most
efficient way to process a request
Can we use indexes
Which one is the best
In which order do we execute complex statements
Can we reformulate the statement in a more efficient one
(SELECT WHERE IN becomes an JOIN, etc.)
It uses a cost-based model
And predictions from ANALYZE (autoanalyze is a part of
autovaccuum)
Sometimes it is wrong (but not very often!)
Don’t try to configure it or you will shoot yourself in the foot
You cant, however, hint it not to use certain operations (no
full scans, no indexes, etc.) and see what happens

Query planner
What it looks like
EXPLAIN will yield :
A tree of actions
Indication on
estimated cost for first row and for all rows
amount of rows expected to retrieve
estimated size of a row
EXPLAIN ANALYZE will actually execute the request, yield the
query planner info, and :
time it took to produce first row and all rows (but actual time
wihtout the profiler would be much faster)
actual amount of rows returned
how many times the node was executed

Query planner
Sequential scans and index scans
# A query using a t a b l e scan ( bad )
root=> EXPLAIN SELECT ∗ FROM customer WHERE a c t i v e =0;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Seq Scan on customer ( c o s t =0.00..16.49 rows=15 width =70)
F i l t e r : ( a c t i v e = 0)
# A query using an index ( good )
root=> EXPLAIN SELECT ∗ FROM customer WHERE last name =’SMITH ’ ;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Index Scan using i d x l a s t n a m e on customer ( c o s t = 0 . 0 0 . . 8 . 2 7 rows=1 width =70)
Index Cond : (( last name ) : : t e x t = ’SMITH ’ : : t e x t )

Query planner
Query optimization
root=> EXPLAIN SELECT DISTRICT FROM a dd r es s WHERE a d d r e s s i d IN
(SELECT a d d r e s s i d FROM customer WHERE a c t i v e =0);
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Hash Semi Join ( c o s t =16.68..32.45 rows=15 width =9)
Hash Cond : ( a dd r es s . a d d r e s s i d = customer . a d d r e s s i d )
−> Seq Scan on a dd r es s ( c o s t =0.00..14.03 rows=603 width =13)
−> Hash ( c o s t =16.49..16.49 rows=15 width =2)
−> Seq Scan on customer ( c o s t =0.00..16.49 rows=15 width =2)
F i l t e r : ( a c t i v e = 0)
root=> EXPLAIN SELECT a dd r es s . d i s t r i c t FROM address , customer WHERE
a dd r es s . a d d r e s s i d=customer . a d d r e s s i d AND customer . a c t i v e =0;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Hash Join ( c o s t =16.68..33.12 rows=15 width =9)
Hash Cond : ( a dd r es s . a d d r e s s i d = customer . a d d r e s s i d )
F i l t e r : ( a c t i v e = 0)

Query planner
Complex queries
root=> EXPLAIN SELECT COUNT(∗) , ad d re ss . d i s t r i c t FROM address , customer
WHERE a d dr e ss . a d d r e s s i d=customer . a d d r e s s i d AND customer . a c t i v e=1
GROUP BY a dd r es s . d i s t r i c t ;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
HashAggregate ( c o s t =49.01..53.73 rows=378 width =9)
−> Hash Join ( c o s t =21.57..46.09 rows=584 width =9)
Hash Cond : ( customer . a d d r e s s i d = a d dr e ss . a d d r e s s i d )
F i l t e r : ( a c t i v e = 1)

Query planner
Complex queries (for real)
root=> EXPLAIN ANALYZE SELECT COUNT(∗) , ad d re s s . d i s t r i c t FROM address , customer
WHERE a d dr e ss . a d d r e s s i d=customer . a d d r e s s i d AND customer . a c t i v e=1
GROUP BY a dd r es s . d i s t r i c t ;
QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
HashAggregate ( c o s t =49.01..53.73 rows=378 width =9)
( a c t u a l time =4.939..5.384 rows=369 l o o p s =1)
−> Hash Join ( c o s t =21.57..46.09 rows=584 width =9)
Hash Cond : ( customer . a d d r e s s i d = a d dr e ss . a d d r e s s i d )
F i l t e r : ( a c t i v e = 1)
Total runtime : 5.883 ms

Stats and monitoring

Table stats
Table stats

Table stats
IO stats
SELECT * from pg statio user tables
Show you the amount of cache reads and page reads
Show how eﬃcient the buﬀer pool is
Does not show information about the OS cache
SELECT * from pg statio user indexes
Same with indexes

Table stats
IO stats example
SELECT relname , h e a p b l k s r e a d , h e a p b l k s h i t , i d x b l k s r e a d , i d x b l k s h i t
FROM p g s t a t i o u s e r t a b l e s ;
relname | h e a p b l k s r e a d | h e a p b l k s h i t | i d x b l k s r e a d | i d x b l k s h i t
−−−−−−−−−−−+−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−
payment | 52 | 297 | 54 | 28123
f i l m | 58 | 170 | 34 | 8646
i n v e n t o r y | 27 | 71 | 35 | 17552
SELECT relname ,
c a s t ( h e a p b l k s h i t as numeric )/( h e a p b l k s h i t+h e a p b l k s r e a d ) AS h i t r a t e
FROM p g s t a t i o u s e r t a b l e s
WHERE ( h e a p b l k s h i t+h e a p b l k s r e a d )>0 ;
relname | h i t r a t e
−−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−
country | 0.50000000000000000000
payment p2007 04 | 0.85100286532951289398
f i l m | 0.74561403508771929825
payment p2007 02 | 0.83898305084745762712

Table stats
Table sizes
SELECT schemaname , relname ,
p g s i z e p r e t t y ( p g r e l a t i o n s i z e ( r e l i d )) AS s i z e ,
p g s i z e p r e t t y ( p g t o t a l r e l a t i o n s i z e ( r e l i d )) AS t o t a l s i z e
FROM p g s t a t u s e r t a b l e s ;
or dt+

Activity
Activity

Locks
Locks

Locks
Finding locks
Let’s see which queries are blocked
SELECT ∗ FROM p g s t a t a c t i v i t y WHERE w a i t i n g=t r u e ;
−[ RECORD 1 ]−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
d a t i d | 16392
datname | root
procpid | 1530
u s e s y s i d | 16390
usename | root
c u r r e n t q u e r y | SELECT i FROM t WHERE s=1 FOR UPDATE;
w a i t i n g | t
x a c t s t a r t | 2012−02−27 14:21:34.723872+01
q u e r y s t a r t | 2012−02−27 14:21:36.195808+01
b a c k e n d s t a r t | 2012−02−27 14:16:49.203923+01
c l i e n t a d d r |
c l i e n t p o r t | −1
PID 1530 is waiting for something

System monitoring
System monitoring

System monitoring
Munin

Logs

Error logs
Error logs

Error logs
Error logs
You can ﬁnd error logs in /var/log/postgresql
syntax errors, server errors, etc.

Finding slow queries

log min duration statement : deﬁne which queries should
be logged
−1 (default) : Do not log anything
0 : log all queries (aka: ruin your HDD)
500 : log queries that run for longer than 500ms
Set it globally in postgresql.conf then reload
Or set it just for one user :
alter role ’myuser’ set log min duration statement
= 500;
Will only start logging next time myuser opens a session
Then, disable it with :
alter role ’myuser’ set log min duration statement
= DEFAULT;

Replication

Basics
Basics

Basics
Basics
PostgreSQL replication is asynchronous by default
synchronous replication is possible, but not usually
recommended
log shipping : copy transaction log ﬁles to the server and
replay them
Delay can be high
You can set a timeout to make sure you get a new log every X
minutes
You have to setup the copy (using rsync, NFS, etc.)
streaming : direct connection to the master server (like
MySQL)

Basics
Basics
warm standby : the replica is not usable, but can be rapidly
enabled during failover
hot standby : the replica can be used for reads
beware of conﬂicts due to locking
must compromise between the ability to run long queries and
the freshness of data
On 8.4 : log shipping, warm standby
On 9.1 : log shipping, streaming, hot standby
Many 3rd party products : slony, etc.

Log Shipping
Log Shipping

Log Shipping
Log Shipping: master
Setup SSH keys, a NFS shared server, or any other mean to
share the logs
In postgresql.conf, enable archiving :
wal level = archive
archive mode = on
archive command = ’rsync -a %p host:/dir/%f’
archive timeout = 60 (if you want one)

Log Shipping
Log Shipping: initializing the replication
Do a hot backup on the master :
SELECT pg start backup(’label’);
rsync -av --delete $datadir slave:$datadir
SELECT pg stop backup();

Log Shipping
Log Shipping: slave
create a recovery.conf ﬁle in the datadir
restore command = ’/ usr / l i b / p o s t g r e s q l /8.4/ bin / pg standby −d −t
/tmp/ p g s q l s t o p s t a n d b y / var / l i b / p o s t g r e s q l /8.4/ main/ a r c h i v e l o g %f %p %r
2>>/var / log / p o s t g r e s q l / standby . log ’
recovery end command = ’rm −f /tmp/ p gsq l st op st and by ’

Log Shipping
Log Shipping: failing over
remove the trigger ﬁle /tmp/pgsql stopstandby to ﬁnish
restoration
recovery.conf is renamed to recovery.done
You can now access the slave

Streaming
Streaming

Streaming
Streaming: master
You can combine streaming and log shipping
Enable WAL senders :
max wal senders = 2
You should make sure to have a few old log ﬁles if you don’t
use archiving :
wal keep segments = 10
Create a REPLICATION account :
CREATE ROLE myuser REPLICATION LOGIN PASSWORD
’mypass’
In pg hba.conf
host replication myuser slave/32 md5

Streaming
Streaming: slave
standby mode = ’on’
primary conninfo = ’host=pgmaster port=5432
user=X password=Y’

Streaming
Streaming: failing over
pg ctlcluster 9.1 main promote

Hot standby
Hot standby

Hot standby
Hot standby
hot standby = on
wal level = hot standby
max standby archive delay = 30
max standby streaming delay = 30
It’s the time we allow queries running on the slave to block
WAL replay

PostgreSQL : Introduction

Recommended

More Related Content

What's hot (20)

Similar to PostgreSQL : Introduction (20)

More from Open Source School (13)

Recently uploaded (20)

PostgreSQL : Introduction