Skip to content

PostgreSQL

PostgreSQL from installation to production: configuration, streaming replication, high availability with Patroni, backups with pgBackRest, performance tuning, extensions, upgrades and migrations from other database engines.

Handling PostgreSQL installations from packages

[vc_row type=”in_container” full_screen_row_position=”middle” scene_position=”center” text_color=”dark” text_align=”left” overlay_strength=”0.3″ shape_divider_position=”bottom” bg_image_animation=”none”][vc_column column_padding=”no-extra-padding” column_padding_position=”all” background_color_opacity=”1″ background_hover_color_opacity=”1″ column_link_target=”_self” column_shadow=”none” column_border_radius=”none” width=”1/1″ tablet_width_inherit=”default” tablet_text_alignment=”default” phone_text_alignment=”default” column_border_width=”none” column_border_style=”solid” bg_image_animation=”none”][vc_column_text]In this blog I will show how to handle a PostgreSQL installation with a customized PGDATA using the packages provided by the PostgreSQL community. One issue with the packages is the hard coded PGDATA, which will be overwritten… 

Patroni Operations – Changing Parameters

Sooner or later all of us have to change a parameter on the database. But how is this put into execution when using a Patroni cluster? Of course there are some specifics you have to consider. This post will give you a short introduction into this topic. When you want to change a parameter on a Patroni cluster you have… 

pgconf.eu – Welcome to the community

On tuesday I started my journey to Milan to attend my first pgconf.eu, which was also my first big conference. I was really excited what will come up to me. How will it be, to become a visible part of the community. How will it be, to give my first presentation in front of so many people? The conference started… 

Patroni Operations – switchover and failover

In this post we will have a look at switchover and failover of a Patroni cluster. As well as a look at the maintenance mode Patroni offers, which gives the opportunity to prevent from an automatic failover. Switchover There are two possibilities to run a switchover, either in scheduled mode or immediately. 1. Scheduled Switchover postgres@patroni1:/home/postgres/ [PG1] patronictl switchover Master… 

PostgreSQL 12: New option –exclude-database for pg_dumpall

By Mouhamadou Diaw Up to PostgreSQL 11 pg_dump all was used to dump all PostgreSQL databases of a cluster into one script file. The script file then contains SQL commands that can be used as input to psql to restore the databases. It does this by calling pg_dump for each database in the cluster. PostgreSQL 12 is under development but… 

PostgreSQL : barman rsync method vs streaming method

By Mouhamadou Diaw Barman is a tool to perform backup and recovery for PostgreSQL databases. It can do backup using two methods: -rsync/ssh -streaming In this blog I am going to explain how to use these two methods to backup a PostgreSQL database. Generally it is a good practice to dedicate a server for barman instead of installing it on… 

PostgreSQL: When wal_level to logical

By Mouhamadou Diaw wal_level determines the quantity of information written to the WAL. With PostgreSQL 11 the parameter wal_level can have 3 values: -minimal : only information needed to recover from a crash or an immediate shutdown -replica : enough data to support WAL archiving and replication -logical : enough information to support logical decoding. If we want to use… 

EDB Failover Manager 3.3 With Virtual Ip Address

By Mouhamadou Diaw In a previous blog , I talked about installing and configuring Edb Failover Manager with PostgreSQL. The configuration of a Virtual Ip Address was not discussed in this previous article.In this blog I am going to show how we can implement a virtual IP with EFM. We suppose that -The standby is already built -EFM is configured… 

PAM Authentication With PostgreSQL

By Mouhamadou Diaw PostgreSQl supports many authentication methods. The PAM authentication method operates similarly to password except that it uses PAM (Pluggable Authentication Modules) as the authentication mechanism. The user must exist in the database before PAM can be used for authentication. In this blog I will configure PAM authentication for a PostgreSQL cluster 11 running on a centos 7.… 

PostgreSQL 12 : New option –socketdir for pg_upgrade

By Mouhamadou Diaw PostgreSQL 12 is under development but tests can be done. Steps to install this version can be found in this dbi blog . Many thanks to Daniel. While reading the documentation I found that there is a new parameter for pg_upgrade. This new parameter is –socketdir. Why this parameter? In fact the path name of a UNIX… 

From Oracle to Postgres with the EDB Postgres Migration Portal

EnterpriseDB is a valuable actor in PostgreSQL’s world. In addition to provide support, they also deliver very useful tools to manage easily your Postgres environments. Among these we can mention EDB Enterprise Manager, EDB Backup & Recovery Tool, EDB Failover Manager, aso… With this post I will present one of the last in the family, EDB Postgres Migration Portal, a… 

Masking Data With PostgreSQL

By Mouhamadou Diaw I was searching a tool for anonymizing data in a PostgreSQL database and I have tested the extension pg_anonymizer. PostgreSQL_anonymizer is a set of SQL functions that remove personally identifiable values from a PostgreSQL table and replace them with random-but-plausible values. The goal is to avoid any identification from the data record while remaining suitable for testing,…