Neues vom PostgreSQL Planet
Stored procedures are widely used in commercial relational databases. You write most of your application logic in PL/SQL and achieve notable performance gains by pushing this logic into the database. As a result, customers who are looking to migrate from other databases to PostgreSQL usually make heavy use of stored procedures.
pgBackRest is a well-known powerful backup and restore tool. It offers a lot of possibilities.
In this post, we’ll see how to setup a dedicated repository host to backup a PostgreSQL 3-nodes cluster.
The repository host will be called backup-srv and the 3 PostgreSQL nodes in Streaming Replication: pg1-srv, pg2-srv, pg3-srv. All the nodes will be running on CentOS 7.
This week Apple started delivering Macs using their own Apple Silicon chips, starting with a Mac SOC named the M1. M1 uses the ARM instruction set and claims some amazing acceleration for media workloads. I wanted to know how it would do running PostgreSQL, an app that's been running on various ARM systems for years. The results are great!
This is a guest post from Raúl Marín, a core PostGIS contributor and a former colleague of mine at Carto. Raúl is an amazing systems engineer and has been cruising through the PostGIS code base making things faster and more efficient. You can find the original of this post at his new personal tech blog. – Paul
Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER.
The PostGIS Team is pleased to release the third alpha of upcoming PostGIS 3.1.0 release. This version is exposes some of the new performance and feature enhancements in not yet relesed GEOS 3.9 as well as numerous speed enhancements not requiring newer GEOS. Requires GEOS 3.6+ and PostgreSQL 9.6+. To use MVT you will need proto-buf 1.2.1 or higher.
Best served with
PostgreSQL 13.1, GEOS 3.7 or higher is recommended.
I am on the phone with Eric Ridge of ZomboDB and PGX fame. We chat often on the People, Postgres, Data Discord server (yes you should join) and we have unofficial “we are human so we get on the phone” calls about twice a month. The calls are generally about PostgreSQL and the awesome Open Source projects he is building around our famed database. However, on this call I got a question I don’t normally get: how good is your SQL?
We recently enabled unattended-upgrades on our Ubuntu 18.04 LTS (Bionic) servers, including our PostgreSQL hosts. By default, unattended-upgrades will ignore PGDG packages (where all of our PostgreSQL packages come from), so I had assumed we wouldn’t have any interruptions due to apt installing updates and triggering a database restart. One evening, however, I received an alert that the nightly backup job had failed. Upon investigation I saw that the PostgreSQL 10 database had been restarted (not ideal in production), and then noticed that it had been upgraded!
Open source developers sometimes have a hard time figuring out what feature to focus on to generate the greatest value for end users. As a result, they will often default to performance.
As you probably know, pg_timetable is the advanced PostgreSQL cron compatible scheduler already released!
plpgsql_check version 1.14.0 was just released and brings some improvement for performance diagnostic.
Thanks a lot to Pavel Stěhule for the awesome plpgsql_check extension and the help for implementing the queryid reporting in v1.14!plpgsql_check: static code analysis and more
PostgreSQL supports procedural code for many languages, the most popular one probably being plpgsql.
Say you want to run a query a specific number of times and then stop... trivial right? Not as much as I first thought. Below is a recreation of how I thought this was supposed to work (I've subbed in select now(); for my actual query, as it's a bit more illustrative.) ``` pagila=# select now(); \g 10now
2020-11-14 17:37:50.694618-05 (1 row)
pagila=# select now();now
2020-11-14 17:37:56.045518-05 (1 row)
2020-11-14 17:37:58.133626-05 (1 row)