Where and when you need a root.crt file

This is something people seem to get confused about quite often. A root.crt file is used to validate a TLS (a.k.a. SSL) certificate presented by the other end of a connection. It is usually the public certificate of the Certificate Authority (CA) that signed the presented certificate, and is used to validate that signature. If a non-root […]

Having Group By Clauses — elein’s GeneralBits

Some people go to great lengths to avoid GROUP BY and HAVING clauses in their queries. The error messages are fussy but they are usually right. GROUP BY and HAVING key words are essential for good SQL reporting. The primary reason for GROUP BY is to reduce the number of rows, usually by aggregation. It […]

JOIN LATERAL

 LATERAL The primary feature of LATERAL JOIN is to enable access elements of a main query in a subquery which can be very powerful. Several common uses of LATERAL are to: denormalize arrays into parent child tables aggregation across several tables row or action generation. Note, however, that the subquery will execute for each main […]

Geo-redundancy of PostgreSQL database backups with Barman

Barman 2.6 introduces support for geo-redundancy, meaning that Barman can now copy from another Barman instance, not just a PostgreSQL database.

PostgreSQL with passphrase-protected SSL keys under systemd

PostgreSQL supports SSL, and SSL private keys can be protected by a passphrase. Many people choose not to use passphrases with their SSL keys, and that’s perhaps fine. This blog post is about what happens when you do have a passphrase. If you have SSL enabled and a key with a passphrase and you start […]

Webinar: Banking on Postgres – Financial Application Considerations [Follow up]

The demand for PostgreSQL within the financial industry has been rapidly increasing in the recent years; mainly due to reduction in licensing costs, better choice of open source tools, and the robust enterprise features that PostgreSQL provides. 2ndQuadrant hosted the “Banking on Postgres” webinar to discuss attributes of financial databases based on Postgres, configuration processes, […]

PG Phriday: Terrific Throughput Tracking

Postgres has a lot of built-in information functions that most people don’t know about. Some of these are critical components to identifying replication lag, but what if we could use them for something else, like throughput? This man’s one simple trick can track actual database throughput; DBAs hate him! Everybody Knows Let’s take a look […]

Maintaining feature branches and submitting patches with Git

I have developed a particular Git workflow for maintaining PostgreSQL feature branches and submitting patches to the pgsql-hackers mailing list and commit fests. Perhaps it’s also useful to others. This workflow is useful for features that take a long time to develop, will be submitted for review several times, and will require a significant amount of changes over time. […]

PG Phriday: PgBouncer or Bust

What is the role of PgBouncer in a Postgres High Availability stack? What even is PgBouncer at the end of the day? Is it a glorified traffic cop, or an integral component critical to the long-term survival of a Postgres deployment? When we talk about Postgres High Availability, a lot of terms might spring to […]