Basic Introduction

HISTORY, code, etc

  • reference to basics
  • focus on administration
  • optimization

This is not meant to be a SQL tutorial but to cover all the basics, administration things and optimization solutions.

Check the current version:

$ SELECT version();
PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

TOPICS:

Replication and High-availability

Replication is the process of copying data from one to another dabase server for replication. When the master server fails, one of the replicators take over.

With High-availability, the replicators are already ready to take over. Generally there are two flavours:

  • Single-Master Replication (SMR) or unidirectional; changes to table rows in a designated master database server are replicated to one or more replica servers. The replicated tables in the replica database are not permitted to accept any changes (except from the master). But even if they do, changes are not replicated back to the master server.

  • Multi-Master Replication (MMR) or bidirectional; changes to table rows in more than one designated master database are replicated to their counterpart tables in every other master database. In this model conflict resolution schemes are often employed to avoid problems like duplicate primary keys.

There are two modes in PostgreSQL: Asynchronous and Synchronous Mode. this defines if the replicas are allowed to be out-of-sync for a while.

Write-Ahead Logging (WAL)

All changes made by a transaction are first saved in a log file (the write ahead log), and then the result of the transaction is sent to the initiating client. To prevent data loss in any case.

  • Data Maintenance: Vacuum and Background Write
  • Write-Ahead Log (WAL), Continuous Archiving /Point-In-Time Recovery (PITR) (pgBackRest or barman)
  • Backups
  • Monitoring

Resources

COMMAND LINE

An overview of basic psql commmands:

  • \l: list all databases
  • l+_: list databases with extra info
  • \c: connect to a database
  • \d: display columns
  • \du: display user roles