THE FABULOUS DESTINY OF 0000000200000008000000BB FOSDEM 2018-02-03 - - PowerPoint PPT Presentation

▶
the fabulous destiny of 0000000200000008000000bb fosdem
SMART_READER_LITE
LIVE PREVIEW

THE FABULOUS DESTINY OF 0000000200000008000000BB FOSDEM 2018-02-03 - - PowerPoint PPT Presentation

Dec 22, 2022 21 likes •642 views

LOXODATA THE FABULOUS DESTINY OF 0000000200000008000000BB FOSDEM 2018-02-03 Patrick Francelle Loxodata LOXODATA WHO Patrick Francelle PostgreSQL consultant and trainer First contact with PostgreSQL in 1999 never stopped using it

slide-1
SLIDE 1

LOXODATA

THE FABULOUS DESTINY OF 0000000200000008000000BB FOSDEM

2018-02-03

Patrick Francelle Loxodata

slide-2
SLIDE 2

LOXODATA

WHO

Patrick Francelle PostgreSQL consultant and trainer First contact with PostgreSQL in 1999 never stopped using it

@pharrek

slide-3
SLIDE 3

LOXODATA

LOXODATA

Company built on 3 essential pillars PostgreSQL DevOps Cloud

slide-4
SLIDE 4

LOXODATA

WHAT

The many possible lives of a WAL

slide-5
SLIDE 5

LOXODATA

HI !

slide-6
SLIDE 6

LOXODATA

SOON, I WILL BE A WAL FILE

slide-7
SLIDE 7

LOXODATA

WHAT AM I ?

transaction log REDO log Write Ahead Log

slide-8
SLIDE 8

LOXODATA

WAL ?

record data changes ASAP bring data consistency help restore data be the pillar of replication

slide-9
SLIDE 9

LOXODATA

SOME THEORY

ACID Atomicity Consistency Isolation Durability

slide-10
SLIDE 10

LOXODATA

SOME THEORY

Atomicity

Atomicity requires that each transaction be "all or nothing": if one part of the transaction fails, then the entire transaction fails, and the database state is left unchanged.

slide-11
SLIDE 11

LOXODATA

SOME THEORY

Consistency

The consistency property ensures that any transaction will bring the database from one valid state to another.

slide-12
SLIDE 12

LOXODATA

SOME THEORY

Isolation

The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed sequentially, i.e., one after the other.

slide-13
SLIDE 13

LOXODATA

SOME THEORY

Durability

(All quotes from Wikipedia)

The durability property ensures that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors.

slide-14
SLIDE 14

LOXODATA

SOME THEORY

slide-15
SLIDE 15

LOXODATA

SOME THEORY

slide-16
SLIDE 16

LOXODATA

SOME THEORY

slide-17
SLIDE 17

LOXODATA

SOME THEORY

slide-18
SLIDE 18

LOXODATA

SOME THEORY

slide-19
SLIDE 19

LOXODATA

SOME THEORY

slide-20
SLIDE 20

LOXODATA

SOME THEORY

slide-21
SLIDE 21

LOXODATA

MY VISION OF "TIME"

not human time depends on activity "soon" : microseconds to years

slide-22
SLIDE 22

LOXODATA

MY LIFE WISHES

reach the end of le travel! not be involved in a "disaster" not end up in "cryo chamber"

slide-23
SLIDE 23

LOXODATA

MY DESTINY

no choice all my goals may be achievable

slide-24
SLIDE 24

LOXODATA

LIFE

slide-25
SLIDE 25

LOXODATA

"BIRTH"

preallocated recycled allocated on demand

slide-26
SLIDE 26

LOXODATA

JOB

record "events" data changes replication events checkpoints writings in append-only mode except for metadata

slide-27
SLIDE 27

LOXODATA

WORK TIME

after switch from previous WAL le until switch to next WAL le continuation of other's work

slide-28
SLIDE 28

LOXODATA

THE SWITCH

normal switch at EOF manual switch with pg_wal_switch() special PITR / promote

slide-29
SLIDE 29

LOXODATA

CRYONICS

at the end of working period may be copied to another location this is called archiving

slide-30
SLIDE 30

LOXODATA

ARCHIVING

external command in archive_command enable with archive_mode = on may retain WAL longer than expected

slide-31
SLIDE 31

LOXODATA

ARCHIVES

many possible destinations local or remote lesystem tape band or permanent storage would likely never been used again

slide-32
SLIDE 32

LOXODATA

"DEATH"

checkpointer process : deleted or recycled "death" may be delayed in some cases manual (human) action: ERROR (not) Schrödinger paradox

slide-33
SLIDE 33

LOXODATA

DEFROST

  • nly when recovering

copied from archives fully read to REDO transactions

slide-34
SLIDE 34

LOXODATA

IDENTITY

slide-35
SLIDE 35

LOXODATA

MY NAME

00000002 00000008 000000BB It's made of 3 parts, 8 digits each. TimeLine ID starts at 1 Logical le ID starts at 0 Physical le ID from 00 to FF First of all WAL: 000000010000000000000001

slide-36
SLIDE 36

LOXODATA

MY NAME

00000002 00000008 000000BB (unofcial) nickname: 8/BB the 0xBBth segment in the 0x8th logical le all my bytes have an address LSN: 8/BB3CB0D2 is my byte 3 977 426

slide-37
SLIDE 37

LOXODATA

TIMELINE ?

Something horrible happened. That's why the TLID is 2 and not 1 A part of the family was abandonned. Some informations in le 00000002.history

slide-38
SLIDE 38

LOXODATA

VERSION

I am tied to the PostgreSQL version my internals may differ from one major version to another

slide-39
SLIDE 39

LOXODATA

SIZE

default size: divided into blocks, by default 8 kB each full size when allocated 16 MB

slide-40
SLIDE 40

LOXODATA

WAL LEVEL

3 different levels available wal_level in conguration allows different life opportunities might change over time

slide-41
SLIDE 41

LOXODATA

LEVEL "MINIMAL"

recovery, only from "crash" data consistency short life :'(

slide-42
SLIDE 42

LOXODATA

LEVEL "REPLICA"

archiving physical replication (travel!) read-only queries on standby more informations stored

slide-43
SLIDE 43

LOXODATA

LEVEL "LOGICAL"

logical decoding logical replication even more informations stored

slide-44
SLIDE 44

LOXODATA

MY PLACE

pg_wal directory in $PGDATA

  • r any directory symlinked as pg_wal
slide-45
SLIDE 45

LOXODATA

PASSPORT

date of birth ? date of issue/expiration ? photo ? Not a human passport

slide-46
SLIDE 46

LOXODATA

DISASTER MANAGEMENT

slide-47
SLIDE 47

LOXODATA

DISASTER

restore a backup replay work after that backup maybe stop at some point go back in production

slide-48
SLIDE 48

LOXODATA

AUTOMATIC RECOVERY

after a brutal stop no need to restore a backup last checkpoint lookup transactions replay

slide-49
SLIDE 49

LOXODATA

DELIBERATE RECOVERY

start from a physical backup write le recovery.conf restore_command to fetch WAL same as automatic recovery timeline change

slide-50
SLIDE 50

LOXODATA

PITR

point-in-time recovery deliberate recovery specify end of recovery end of recovery action

slide-51
SLIDE 51

LOXODATA

TIMELINE CHANGE

last WAL of recovery copied name differs by TLID content differs from recovery point subsequent WAL in old timeline abandonned

slide-52
SLIDE 52

LOXODATA

TRAVELLING

slide-53
SLIDE 53

LOXODATA

TRIP

  • rigin, source

primary, provider, publisher destinations standby, subscriber transport method via archives streaming replication

slide-54
SLIDE 54

LOXODATA

CONCEPT

continuously up-to-date clone of data copy data, then replay transactions who's the best at recording transactions ?

slide-55
SLIDE 55

LOXODATA

PHYSICAL REPLICATION

duplication of WAL le from one cluster to another streaming replication

slide-56
SLIDE 56

LOXODATA

LOGICAL REPLICATION

decoded on publisher side information transformed sent to feed another WAL out there no travel

slide-57
SLIDE 57

LOXODATA

WAL SENDER

gets replication connections runs replication protocol commands sends WAL content

slide-58
SLIDE 58

LOXODATA

WAL RECEIVER

fetchs data permits REDO events sends feedback

slide-59
SLIDE 59

LOXODATA

PG_RECEIVEWAL

special receiver process collects and stores (no REDO) streamed archive

slide-60
SLIDE 60

LOXODATA

REPLICATION SLOTS

client dedicated resource stores replication status forbids deletion until replicated

slide-61
SLIDE 61

LOXODATA

TIME TO GET TO WORK

slide-62
SLIDE 62

LOXODATA

THANK YOU FOR YOUR ATTENTION