--- tags: PostgreSQL title: WorkShop PostgreSQL --- # 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 * Click here to join the meeting: https://teams.microsoft.com/l/meetup-join/19%3aJOixY7jQ3v_VeocxjDiueuno7qK1-3InevmnnDseC-81%40thread.tacv2/1621965714274?context=%7b%22Tid%22%3a%22b9c70613-b1ea-47b7-9fdd-e6f3a67ce75c%22%2c%22Oid%22%3a%225b8fe158-ee26-45e9-8404-5860fab40907%22%7d * View group files https://outlook.office365.com/owa/PostgreSQLworkshop@dbi-services.com/groupsubscription.ashx?source=EscalatedMessage&action=files&GuestId=3fac382e-f817-4387-aed8-49d0742c1987 # 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](https://twitter.com/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 ![](https://i.imgur.com/HyzCyWo.png) ### Where does PostgreSQL run on? ![](https://i.imgur.com/j8z2xX4.png) ### 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](https://www.postgresql.org/docs/13/view-pg-settings.html). ```sql 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](https://github.com/zalando/patroni) ## Monitoring Voir [https://www.percona.com/doc/percona-monitoring-and-management/2.x/index.html](https://www.percona.com/doc/percona-monitoring-and-management/2.x/index.html)