Infrastructure at your Service

Tag

PostgreSQL Archives - Page 3 of 37 - Blog dbi services

Daniel Westermann

An introduction into server side programming in PostgreSQL – 3 – PL/pgSQL, procedures

By | Database Administration & Monitoring | No Comments

In the first to posts in this series (An introduction into server side programming in PostgreSQL – 1 – SQL functions, basics , An introduction into server side programming in PostgreSQL – 2 – SQL functions, sets, udts and polymorphic types ) we had a look at SQL functions and how you can use them to return data for everything that is a SQL statement. If you can do it in SQL, you should do…

Read More
Daniel Westermann

An introduction into server side programming in PostgreSQL – 2 – SQL functions, sets, udts and polymorphic types

By | Database Administration & Monitoring | No Comments

This is part two of the little series about server side programming in PostgreSQL. In the last post we had a look at the basics when it comes to SQL functions. We learned how to write those functions, how we can pass parameters to functions, how to name parameters for better readability, how to return a result, and how we can use functions in plain SQL statements. In this post we’ll go a step further…

Read More
Daniel Westermann

An introduction into server side programming in PostgreSQL – 1 – SQL functions, basics

By | Database Administration & Monitoring | No Comments

Over the last years I’ve seen many different applications running against PostgreSQL. The use cases vary from simple applications, which only use basic data types and a few tables and views, to complex applications with custom types, more specific data types like jsonb or range types, that use hundreds of schemas/tables/views/materialized views etc. Surprisingly only a few of them make use of functions and procedures in PostgreSQL. I’ve always told people to process the data…

Read More
Franck Pachot

Google Cloud SQL Insights: ASH, plans and statement tagging

By | Cloud, Postgres | No Comments

By Franck Pachot . Looking at database performance has always been necessary to optimize the response time or throughput, but when it comes to public cloud where you are charged by resource usage, performance tuning is critical for cost optimization. When looking at host metrics, you see only the symptoms and blindly guess at some solutions: add more vCPU if CPU usage is high, more memory if I/O wait is high. And this can be…

Read More
Daniel Westermann

Dealing with German “Umlaute” in PostgreSQL’s full text search

By | Database Administration & Monitoring | No Comments

PostgreSQL comes with build-in Full Text Search and you can do quite amazing stuff with it. A question that popped up during one of the last PostgreSQL for developers workshop was: How can I deal with German “Umlaute” such as “ä”, “ö” and “ü” in such a way, that I can search for e.g. “Schnösel” and the result will give me “Schnösel” as well as “Schnoesel”? One way to deal with that would be to…

Read More
Daniel Westermann

Cluster level encryption for PostgreSQL 14

By | Database Administration & Monitoring | No Comments

The discussions how and why TDE (Transparent data encryption) should be implemented in PostgreSQL goes back several years. You can have a look at these two more recent threads to get an idea on how much discussion happened around that feature: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS) Re: Internal key management system Finally an essentials part for that infrastructure was committed and I am sure, many people have waited for…

Read More
Julia Gugel

Running two Patroni on one host using an existing etcd

By | Database Administration & Monitoring | No Comments

Have you ever asked yourself, how to create a second Patroni PostgreSQL cluster on an existing server using the existing etcd? My first idea was to study the documentation of Patroni, but unfortunately without big success. This post should help to identify the changes you have to do on the hosts to run two parallel Patroni clusters using an existing etcd.

Read More
Daniel Westermann

pg_auto_failover: Failover and switchover scenarios

By | Application integration & Middleware | No Comments

In the last post we had a look at the installation and setup of pg_auto_failover. We currently have one primary cluster and two replicas synchronizing from this primary cluster. But we potentially also have an issue in the setup: The monitor is running beside the primary instance on the same node and if that nodes goes down the monitor is gone. What happens in that case and how can we avoid that? We also did…

Read More
Franck Pachot

Recovery in the ☁ with Google Cloud SQL (PostgreSQL)

By | Cloud, Postgres | One Comment

By Franck Pachot . In a previous post I started this series of “Recovery in the ☁” with the Oracle Autonomous database. My goal is to explain the recovery procedures, especially the Point-In-Time recovery procedures because there is often confusion, which I tried to clarify in What is a database backup (back to the basics). And the terms used in managed cloud services or documentation is not very clear, not always the same, and sometimes…

Read More
Daniel Westermann

Easy failover and switchover with pg_auto_failover

By | Database Administration & Monitoring | No Comments

One the really cool things with PostgreSQL is, that you have plenty of choices when it comes to tooling. For high availability we usually go with Patroni, but there is also pg_auto_failover and this will be the topic of this post. Because of the recent announcement around CentOS we’ll go with Debian this time. What is already prepared is the PostgreSQL installation (version 13.1), but nothing else. We start from scratch to see, if “is…

Read More