Kaarel Moppel: Version 1.6 of pgwatch2 PostgreSQL monitoring tool released
12 hours ago, postgresql

After almost half a year since last updates I’m glad to announce that another set of useful features and improvements have found their way to pgwatch2 – our Open Source PostgreSQL monitoring tool of choice. New version is incremented to 1.6 but continuing the naming tradition I’m also calling it “Feature Pack 5” as it’s mostly a feature release (with a couple of bugfixes as well). So here a quick overview on changes – for the “full monty” and when updating from a previous version please turn to ...

Michael Paquier: Postgres 12 highlight - More progress reporting
13 hours ago, postgresql

Since Postgres 11, it is possible to monitor progress activity of running manual VACUUM and even autovacuum using a dedicated system catalog called pg_stat_progress_vacuum. This is vital for operators when doing some long-running operations, because it is possible to not blindly wait for an operation to finish. When doing performance workload analysis, this is also proving to be helpful in evaluating VACUUM job progressing for tuning system-level parameters or even relation-level once depending...

Joe Conway: PostgreSQL Deep Dive: PostgreSQL Defaults and Impact on Security - Part 1
1 day ago, postgresql

Recently I gave a "deep dive" talk on the topic of PostgreSQL security, and I wanted to capture one part of that content into a blog since this format is both better for making that content stand on its own and for expanding on it a bit. Specifically, in this two-part blog, we will cover a PostgreSQL extension that I wrote called crunchy_check_access -- the motivation behind it and what it does -- and then use that extension to probe and understand the consequences of the default, out of the bo...

Mark Wong: PDXPUG July Meetup at OSCON: New things coming to Postgres 12
1 day ago, postgresql

Note location for the July meeting is at OSCON! When: 7-9pm Wednesday July 17, 2019 Where: Oregon Convention Center Room D135/136 New versions of PostgreSQL usually get released every fall with lots of improvements and new features. Come hear about some of the highlighted changes expected to come in the next new release! This special edition meetup also welcomes any OSCON attendee.  Also come and meet the local Portland PostgreSQL user group to ask any PostgreSQL related questions!...

Jobin Augustine: Managing Multiple PostgreSQL Instances on Ubuntu/Debian
2 days ago, postgresql

Introduction Those DBAs who are experts in one database system look for other database systems to have “similar features”. It is a human tendency to look at any new technology and compare it with a world they are familiar with. Most of the time, I keep addressing Oracle DBAs who are looking for similar features or setup in PostgreSQL, but this time it is for MySQL DBA. MySQL historically ships mysqld_multi to manage multiple instances of MySQL on a server. Even though this is not a core feature...

Bruce Momjian: Postgres 12 Features Presentation
3 days ago, postgresql

Now that I have given a presentation about Postgres 12 features in Ibiza, I have made my slides available online....

Avinash Kumar: Hypothetical Indexes in PostgreSQL
5 days ago, postgresql

At recent conferences, we have received a curious question from users who have used Oracle in the past and are now using PostgreSQL: “Do we have hypothetical indexes in PostgreSQL ?“. The answer to that question is YES. The true meaning of hypothetical is imaginary but not real. We can try creating indexes that are imaginary for the PostgreSQL optimizer which don’t exist in reality. Let’s look at some detailed examples. How are hypothetical indexes helpful? The execution time of an SQL in most ...

Vasilis Ventirozos: CVE-2019-10164 Who's affected and how to protect your systems.
5 days ago, postgresql

Yesterday, 20th of June Postgres community released minor version updates for all supported versions. (11.4, 10.9, 9.6.14, 9.5.18, 9.4.23, 12Beta2) As with any minor version, it is recommended to upgrade and keep your database to the latest minor version. But this release is a bit more important  than others because it includes a fix about a recently discovered CVE (CVE-2019-10164)  From the release notes: "This release is made outside of the normal update release schedule as the security vulner...

Michael Paquier: Postgres 12 highlight - SQL/JSON path
5 days ago, postgresql

Postgres ships in-core data types for JSON with specific functions and operators (json since 9.2, and jsonb which is a binary representation since 9.4). The upcoming Postgres 12 is becoming more complaint with the SQL specifications by introducing SQL/JSON path language, introduced mainly by the following commit: commit: 72b6460336e86ad5cafd3426af6013c7d8457367 author: Alexander Korotkov <akorotkov@postgresql.org> date: Sat, 16 Mar 2019 12:15:37 +0300 Partial implementation of SQL/JSON path ...

Jonathan Katz: Explaining CVE-2019-10164 + PostgreSQL Security Best Practices
5 days ago, postgresql

The PostgreSQL Global Development Group provided an out-of-cycle update release for all supported  to provide a fix for the CVE-2019-10164 vulnerability. This vulnerability only affects people running PostgreSQL 10, 11 or the 12 beta, and it is effectively remediated by simply upgrading all of your PostgreSQL installations to the latest versions. What follows is some more insight about what this vulnerability is, the impact it can have in your environment, how to ensure you have patched all of y...

Hubert 'depesz' Lubaczewski: Changes on explain.depesz.com
1 week ago, postgresql

Recently got two bug reports: plans with “COSTS OFF" do not parse, and error out (bugreport by Marc Dean Jr) WorkTable Scan is not properly parsed (bugreport by Ivan Vergiliev) Additionally, I was kinda upset because plans that include trigger calls did not display properly. All of this has been fixed today: First, I fixed … Continue reading "Changes on explain.depesz.com"...

Granthana Biswas: Install PostgreSQL 9.6 with Transparent Data Encryption
1 week ago, postgresql

Cluster encryption can be used if the DBA can not or does not rely on the file system in terms of confidentiality. If this feature is enabled, PostgreSQL encrypts data  (both relations and write-ahead log) when writing to disk, and decrypts it when reading. The encryption is transparent, so the applications see no difference between the encrypted and unencrypted clusters. PostgreSQL 9.6 with TDE on Ubuntu In this blog, we go through the basic steps used to install PostgreSQL 9.6 with Transparen...

pgCMH - Columbus, OH: What’s new in pgBouncer
1 week ago, postgresql

The June meeting will be held at 18:00 EST on Tues, the 25th. Once again, we will be holding the meeting in the community space at CoverMyMeds. Please RSVP on MeetUp so we have an idea on the amount of food needed. What CoverMyMeds’ very own CJ will be presenting this month. This month’s meeting will be CJ telling us about what’s new and improved in pgBouncer as well as how to get it up and running. Discussion will include real life examples from its use at CMM. pgBouncer is the lightweight conn...

Avinash Kumar: Bloom Indexes in PostgreSQL
1 week ago, postgresql

There is a wide variety of indexes available in PostgreSQL. While most are common in almost all databases, there are some types of indexes that are more specific to PostgreSQL. For example, GIN indexes are helpful to speed up the search for element values within documents. GIN and GiST indexes could both be used for making full-text searches faster, whereas BRIN indexes are more useful when dealing with large tables, as it only stores the summary information of a page. We will look at these ind...

Luca Ferrari: A recursive CTE to get information about partitions
2 weeks ago, postgresql

I was wondering about writing a function that provides a quick status about partitioning. But wait, PostgreSQL has recursive CTEs! A recursive CTE to get information about partitions I’m used to partitioning, it allows me to quickly and precisely split data across different tables. PostgreSQL 10 introduced the native partitioning, and since that I’m using native partitioning over inheritance whenever it is possible. But how to get a quick overview of the partition status? I mean, knowing which p...

Jeff McCormick: What's New in Crunchy PostgreSQL Operator 4.0
2 weeks ago, postgresql

Crunchy Data is pleased to release PostgreSQL Operator 4.0. ...

Hans-Juergen Schoenig: Tech preview: How PostgreSQL 12 handles prepared plans
2 weeks ago, postgresql

PostgreSQL 12 is just around the corner and therefore we already want to present some of the new features we like. One important new feature gives users and devops the chance to control the behavior of the PostgreSQL optimizer. Prepared plans are always a major concern (especially people moving from Oracle seem to be most concerned) and therefore it makes sense to discuss the way plans are handled in PostgreSQL 12. Firing up a PostgreSQL test database To start I will create a simple table consis...

Luca Ferrari: Checking the sequences status on a single pass
2 weeks ago, postgresql

It is quite simple to wrap a couple of queries in a function to have a glance at all the sequences and their cycling status. Checking the sequences status on a single pass The catalog pg_sequence keeps track about the definition of a single sequence, including the increment value and boundaries. Combined with pg_class and a few other functions it is possible to create a very simple administrative function to keep track about the overall sequences status. I’ve created a seq_check() function that...

Next