Archivi categoria: PostgreSQL

PostgreSQL performance tuning for faster query execution

PostgreSQL is a reliable open source database available in many modern distribution’s repository. The ease of use, the ability to use extensions and the stability it provides all add to it’s popularity. While providing the base functionality, like answering to SQL queries, store inserted data consistently, handling transactions, etc. most mature database solutions provide tools and know-hows on how to tune the database, identify possible bottlenecks, and be able to solve performance problems bound to happen as the system powered by the given solution grows.

Image result for postgresql images

PostgreSQL is no exception, and in this guide we’ll use the built in tool explain to make a slow-running query complete faster. It is far from a real world database, but one can take the hint on the usage of the built in tools. We’ll use a PostgreSQL server version 9.2 on Red Hat Linux 7.5, but the tools shown in this guide are present in much older database and operating system versions as well.

How to create a hot standby with PostgreSQL

PostgreSQL is an open source RDBMS (Relational DataBase Management System), and with any databases, the need may arise to scale and provide HA (High Availability). A single system providing a service is always a possible single point of failure – and even with virtual systems, there may be a time when you can’t add more resources to a single machine to cope with the ever-increasing load. There also may be a need to another copy of the database contents that can be queried for long-running analytics, that are not fit to be run on the highly transaction-intensive production database. This copy could be a simple restore from the most recent backup on another machine, but the data would be outdated as soon as it is restored.

Image result for postgresql images

PostgreSQL 10 Released

The PostgreSQL Global Development Group today announced the release of PostgreSQL 10, the latest version of the world’s most advanced open source database.

Risultati immagini per postgresql logo png

This release also marks the change of the versioning scheme for PostgreSQL to a “x.y” format. This means the next minor release of PostgreSQL will be 10.1 and the next major release will be 11.

Logical replication extends the current replication features of PostgreSQL with the ability to send modifications on a per-database and per-table level to different PostgreSQL databases. Users can now fine-tune the data replicated to various database clusters and will have the ability to perform zero-downtime upgrades to future major PostgreSQL versions.

“We have been heavily using PostgreSQL since 9.3 and are very excited about version 10 since it brings basis for long-awaited partitioning and built-in logical replication. It will allow us to use PostgreSQL in even more services,” said Vladimir Borodin, DBA Team Lead at Yandex.

Declarative Table Partitioning – Convenience in dividing your data

Table partitioning has existed for years in PostgreSQL but required a user to maintain a nontrivial set of rules and triggers for the partitioning to work. PostgreSQL 10 introduces a table partitioning syntax that lets users easily create and maintain range and list partitioned tables. The addition of the partitioning syntax is the first step in a series of planned features to provide a robust partitioning framework within PostgreSQL.

Improved Query Parallelism – Quickly conquer your analysis

PostgreSQL 10 provides better support for parallelized queries by allowing more parts of the query execution process to be parallelized. Improvements include additional types of data scans that are parallelized as well as optimizations when the data is recombined, such as pre-sorting. These enhancements allow results to be returned more quickly.

Quorum Commit for Synchronous Replication – Distribute data with confidence

PostgreSQL 10 introduces quorum commit for synchronous replication, which allows for flexibility in how a primary database receives acknowledgement that changes were successfully written to remote replicas. An administrator can now specify that if any number of replicas has acknowledged that a change to the database has been made, then the data can be considered safely written.

“Quorum commit for synchronous replication in PostgreSQL 10 gives more options to extend our ability to promote database infrastructure with nearly zero downtime from the application perspective. This allows us to continuously deploy and update our database infrastructure without incurring long maintenance windows,” said Curt Micol, Staff Infrastructure Engineer at Simple Finance.

SCRAM-SHA-256 authentication – Secure your data access

The Salted Challenge Response Authentication Mechanism (SCRAM) defined in RFC5802 defines a protocol to improve upon the secure storage and transmission of passwords by providing a framework for strong password negotiation. PostgreSQL 10 introduces the SCRAM-SHA-256 authentication method, defined in RFC7677, to provide better security than the existing MD5-based password authentication method.



Progetto automazione LABijk. Revisione 1.

Il progetto intende promuovere tecnologie open source ed open electronics.

Si supponga di voler gestire sensori ed attuatori.

Poniamo al centro un microserver, con sistema operativo Linux Server, eventualmente connesso ad internet ed a reti locali.

Questa macchina archivia un resoconto di tutto quello che viene fatto e comunica con tablet, telefoni ed altri sistemi anche attraverso internet. Abbiamo già dato alcune indicazioni sulle possibili scelte in articoli precedenti.

Distinguiamo tre livelli, per l’appunto i, j e k di aggregazione dispositivi.

Alla radice poniamo il microserver.

I primi nodi, di livello i, siano microcontroller Arduino SMD Uno o Ethernet, connessi al microserver via USB o ethernet. Le schede Arduino siano dotate  dispositivi adattatori seriali RS485.

Il secondo livello, detto j,  è costituito da dispositivi Arduino SMD UNO con shield Grove e connessi al padre mediante seriale.

Al terzo livello, detto k, poniamo attuatori e sensori connessi ai padre mediante cavi Grove, oppure cavi multipolari a 4 capi, schermati o non, telefonici o di rete. Lo standard Grove può essere convertito da adattatori nel formato RJ13 per cavi telefonici o RJ45 per usuali cavi ethernet.

Ogni dispositivo è individuato da coordinate i.0.0 oppure i.j.0 oppure i.j.k

I comandi sono del tipo i.j.k:<comando:valore,…> 

Il microserver è dotato di servizio che interroga costantemente in pool i dispositivi di livello i, mediante USB. Un secondo servizio che è simile al precedente ma per i dispostivi connessi mediante rete.

I dispostivi Arduino di coordinate i sono in ascolto verso il microserver, quindi USB o socket, ed anche interrogano il pool dei figli connessi mediante seriale.

I dispositvi di livello j sono in ascolto verso i dispositivi padri ed eseguono l’interrogazione in pool dei sensori, o meglio le entrate,  oppure cambiano lo stato delle uscite.

Nel microserver esiste un database “automazione” con le tabelle

  • “comandi”, contenente record corrispondente a comandi da eseguire
  • “stati” che contiene lo stato attuale di ogni dispositivo
  • “cambio_stati” che contiene cambiamenti di stati eventualmente da elaborare
  • “pianificazioni” che contiene operazioni pianificate

Il microserver esegue un servizio che interroga  in pool i dispositivi di livello i e riporta comandi e cambiamenti di stato nelle tabelle corrispondenti.

Un secondo servizio legge i cambiamenti di stato, consulta le tabelle di correlazione e scrive i comandi da eseguire nella tabella comandi.

Un terzo legge la tabella comandi e instrada il comando nel ramo corretto.

Tutto è riportato in tabelle e viene a costituire in sistema three-tier architecture, con pilastri di appoggio quali PostgreSQL, Apache, OpenSSH, Postfix, …

Precisiamo che

  • i cambiamenti di stato sono riportati nella tabella “cambio_stati”
  • i comandi con destinazione di coordinate valide sono instradati verso i destinatari
  • le risposte ritornano al microserver che chiude il comando ed aggiunge eventuali cambio stati.

Consideriamo un semplice esempio.

Supponiamo di avere un sistema di dimensione 5.3.5 ovvero al microserver sono connessi 5 dispositivi con il ruolo di HUB RS485, ad ognuno di questi sono connessi al più 3 dispositivi di livello j con HUB Grove, ad ognuno di questi al più 5 sensori o attuatori.

I dispositivi di livello j sono dotati di firmware che permette di sapere e gestire perfettamente i sensori od attuatori ad essi connessi. Inoltre questi dispositivi sono in pool rispetto i sensori ed attuatori.

Consideriamo il dispositivo di livello j di coordinate 3.2.

Supponiamo che abbia 2 attuatori ed un sensore di coordinate 3.2.3.

Il sensore sia un interrutore bistabile (entrata a due stati).

Ora se l’interrutore viene azionato e cambia stato, il dispositvo 3.2.0 costruisce il cambio stato 3.2.3:on (ma meglio introdurre la notazione definitiva  3.2.1:1 ) ed instrada il cambio stato al padre, il quale lo riporta al microserver che lo scrive nella tabella cambio stato.

Il servizio predisposto, nel microserver, rielabora secondo tabelle di correlazione il cambio stato, che viene elaborato, ovvero chiuso e crea gli opportuni comandi.

Mel nostro esempio il comando è 3.2.1:on  ma meglio  3.2.1:1

Quindi il servizio legge il comando e lo instada al dispositivo 3.0.0 che lo gira a 3.2.0

A questo punto il dispositivo 3.2.0 elabora il comando alzando il livello all’uscita corrispondente.


Il progetto infine prevede la creazione di una GUI  che gestisce le tabelle nel RDBMS del microserver.

La GUI permette di descrivere l’intero impianto mediante griglie di correlazione.

Quindi il programma prevede l’opzione di creare al volo ed in modo completamente automatico il firmware per ogni Arduino ed aggiornarlo via USB.

Perciò ogni Arduino deve venire correttamente etichettato mediante le coordinate i.j.k e connesso mediante USB al computer con la GUI di gestione per il travaso del firmware. L’operazione precedente deve essere rifatta nel caso che cambino i figli.


Il progetto si conclude con la realizzazione di App per Ubuntu Phone e Tablet, Android e iPhone di gestione ordinaria. L’obiettivo più ambizioso vorrebbe creare piante dei sistemi SVG e sovrapporre dei widgets dinamici visuali che rendano visuale il comportamento del sistema.

L’applicazione più importante sarà il controllo del CED quindi di una stanza con molti server. L’obiettivo è di controllare la linea di rete, le temperature, i tasti di accensione, reset di ogni server strategico. Le App mostreranno visualmente lo stato di ogni server e permetteranno operazioni quali spegnimento ed accesione “fisica”. Verranno gestiti i tempi di sostegno degli UPS e lo stato dei gateway verso l’esterno.