Info

Env: https://dbiinsite.dbi-services.com
Pass: MEn52tDj!EVxLAG3O
User: pgdba[01-08]
Pass: pgdba[01-08]
OS user: root
OS pass: install
postgres user: postgres
postgres pass: postgres

https://dbiinsite.dbi-services.com
Laurent Indermühle pgdba03
Giovanni Cangiani pgdba05
Nicolas Borboen pgdba06

Connection to the Participants VM
SSH : Username:postgres Password:postgres
VNC : only click on the machine

Jeudi 27 Mai

Intro

Company founded in 2010, 70 ppl, specialized in the Middkeware Infrastructure, customers in Switzerlan and all over Europe
Base in Nyon, Delemont, …
They do consulting SLA, trainings, License Management.
Mouhamadou Diaw, Senior Cnsultant
Hervé Schweitzer, CTO, Principal consultant, @Herveschweitzer
Product placement (…)
- DMK Management Kit - for free (www.dbi-services.com/DMK)
Insight
- Not only theory
OpenDB applicance, developed by DBI, VMWare templade, Hidory cloud as docker container

Participants

Claudio Spadaro (EPFL) → projet de sortie d'oracle
Didier (EPFL) → Moodle (LMS EPFL), isacademia + migration DB Oracle
Giovanni (EPFL) → Pyhsicien de formation, FSD,
JJ Bercier (Equinox) → Developmenet ISAcademia, migration DB Oracle
Julia P (EPFL) → Manager mais avant admin sys MySQL,SQL,Oracle
Laurent Indermühle (EPFL) → Admin Sys Linux, DBA MySQL
Luc Caya (Equinox) → Nouvelle arrivant (3 mois), DBA Oracle, EDB PostgreSQL, migration ISA de Oracle à Postgre
Nicolas Repond (EPFL) → Réseau EPFL, outils gestion réseau (MySQL, Oracle), évt. migration Postgres de Oracle
Alexandre (EPFL) → DBA Oracle
Nicolas Borboën (EPFL)

Main goals

Architecture
Install et configure
- initdb, pg_ctl, pg_hba.conf
Backup / recovery
- pg_dump, pg_restore,
- Write Ahead Log (WAL) archiving,
- Point in time recovery (PITR),
- Third party solutions
HA, clustering
- Types of replication
- Hot standy DB (replicas)
Vacuum and Statistics
- vacuum for updating the stats
Extensions for new features
Monitoring
- server log
- pg_stat_statements
- pg_badger
- pgcluu
- prometheus
- Postgres Entreprise Manager

History & Timeline

Edgar F. Codd - the relational model for db management (1969)
- Data represented in terms of tuples, grouped into relations
Michael Stonebraker, Berkeley, 1973, origin of PostgreSQL
- Was first called Postgres
- 1986 (started)
- Named after the project Ingress (Integrated Graphics Retrieval System), meaning after it
- seas.upenn.edi/~FUCKIT
- commercialized in 2001
- subset of POSTQUEL replaced with SQL in 1995
- Renamed postgres95
- PostgreSQL began at version 6.0
https://opensource.org/licenses/postgresql
Who controls PostgreSQL → directed by the community of developers
PostgreSQL Global Development Group → company that help development
Timeline: http://www.postgresql.org.es/rdbms-timeline/
Graphical representation of the timeline into: database_timeline.pdf (Files Teams)
Elephant symbole → because the got good memory
PUG
- Swiss PostgreSQL Users Group (https://www.swisspug.org/)
- https://www.postgresql.eu/
Timeline
- 1997 - 2000 : PostgreSQL 6.0 - 7.0
- 2001: PostgreSQL 7.1
- 2005: PostgreSQL 8.0
- 2008: PostgreSQL 8.3 (pg_standby, support for SQL/XML)
- 2010: PostgreSQL 9.0 (Hot standby, Streaming replication, pg_upgrade)
- 2011: PostgreSQL 9.1 (pg_basebackup, synchronous replication, per column collation support)
- 2012: PostgreSQL 9.2 (cascading replication, index only scans, range data types)
- 2013: PostgreSQL 9.3 (Materialized views)
- 2014: PostgreSQL 9.4 (replication slots, logical deconding, jsonb)
- 2016: PostgreSQL 9.5 (BRIN indexes) [Note: https://en.wikipedia.org/wiki/Block_Range_Index, e.g. bucket instead of BTree]
- 2016: PostgreSQL 9.6 (parallel sequential scans, parallel joins, ph_stat_activity)
- 2017: PostgreSQL 10 (WAL logged hash indexes, logical replication, ~zero-downtime upgrades…)
- 2018: PostgreSQL 11 (Just-In-Time (JIT) compilation, hash partitioning, changeable WAL seg. size)
- 2019: PostgreSQL 12 (pg_promote function for promoting replicas, exclude with pg_dumpall, recovery.conf no more needed, included in postgres.conf)
- 2020: PostgreSQL 13 (pg_verifybackup, vacuum improvments, drop db with force)
- 2021: PostgreSQL 14, probably end of September/beginning of October (parallel copy from ?, parallel insert into ?, incremental materialized view maintenance ?)

PostgreSQL is a
non-commercial, all volunteer, free software project
as such there is no formal list of feature requirements required for development
we really do follow the mantra of letting developers scratch their own itches
http://www.postgresql.org/developer/roadmap/

Very good and rapid support by developers and experts
Five years support starting from release date.

Who runs PostgreSQL

Image Not Showing Possible Reasons

The image file may be corrupted
The server hosting the image is unavailable
The image path is incorrect
The image format is not supported

Learn More →

Where does PostgreSQL run on?

Image Not Showing Possible Reasons

The image file may be corrupted
The server hosting the image is unavailable
The image path is incorrect
The image format is not supported

Learn More →

Getting support

The official documentation http://www.postgresql.org/docs/
Mailing lists http://www.postgresql.org/list/
Wiki https://wiki.postgresql.org/wiki/Main_Page
EnterpriseDB http://www.enterprisedb.com/products-services-training/subscriptions
Many others http://www.postgresql.org/support/professional_support/

su - postgres

mkdir -p /u02/pgdata/10/PG1
mkdir -p /u03/pgdata/10/PG1/pg_wal
mkdir -p /u99/pgdata/10/PG1/archived_wal
/u01/app/postgres/product/10/db_0/bin/initdb --pgdata=/u02/pgdata/10/PG1 --waldir=/u03/pgdata/10/PG1/pg_wal --pwprompt --data-checksum --auth=md5

the config file is /u02/pgdata/10/PG1/postgresql.conf and can be overriden by postgresql.auto.conf we use the one from dmk:

cp /u01/app/postgres/local/dmk/templates/postgres/postgresql.auto.conf /u02/pgdata/10/PG1/

In order to start the DB, the data directory is mandatory"

/u01/app/postgres/product/10/db_0/bin/pg_ctl -D /u02/pgdata/10/PG1 start

Vendredi 28 Mai

What needs a setting change to take effect, see https://www.postgresql.org/docs/13/view-pg-settings.html.

select name, context from pg_settings

context	Needs
postmaster	restart
sighup	reload
user	session

Clustering

Multi-master synchronous
Best product: https://github.com/zalando/patroni

Monitoring

Voir https://www.percona.com/doc/percona-monitoring-and-management/2.x/index.html