Peter Bengtsson: Best way to count distinct indexed things in PostgreSQL
12 hours ago, postgresql

`SELECT COUNT(*) FROM (SELECT DISTINCT my_not_unique_indexed_column FROM my_table) t`...

Yogesh Sharma: PostgreSQL Zero to Hero: Getting Started with RPMs -Part 1
1 day ago, postgresql

One of the most important things to using PostgreSQL successfully in your development and production environments is simply getting started! One of the most popular ways to install PostgreSQL is by using RPM packages. The PostgreSQL RPM packages work across many Linux distributions, including, RedHat Enterprise Linux (RHEL), CentOS, Fedora, Scientific Linux, and more, and the PostgreSQL community provides installers for these distributions. This guide will help you get started with installing an...

Craig Kerstiens: How to evaluate your database
1 day ago, postgresql

Choosing a database isn’t something you do every day. You generally choose it once for a project, then don’t look back. If you experience years of success with your application you one day have to migrate to a new database, but that occurs years down the line. In choosing a database there are a few key things to consider. Here is your checklist, and spoiler alert, Postgres checks out strongly in each of these categories. Does your database solve your problem? There are a lot of new databases tha...

Christophe Pettus: “Look It Up: Practical PostgreSQL Indexing” at Nordic PGDay 2019
1 day ago, postgresql

The slides from my presentation at PGDay Nordic 2019 are now available....

Hubert 'depesz' Lubaczewski: Migrating simple table to partitioned. How?
2 days ago, postgresql

Recently someone asked, on irc, how to make table partitioned. The thing is that it was supposed to be done with new partitioning, and not the old way. The problem is that while we can create table that will be seen as partitioned – we can't alter table to become partitioned. So. Is it possible? … Continue reading "Migrating simple table to partitioned. How?"...

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 12 – Partial implementation of SQL/JSON path language
2 days ago, postgresql

On 16th of March 2019, Alexander Korotkov committed patch: Partial implementation of SQL/JSON path language   SQL 2016 standards among other things contains set of SQL/JSON features for JSON processing inside of relational database. The core of SQL/JSON is JSON path language, allowing access parts of JSON documents and make computations over them. This commit … Continue reading "Waiting for PostgreSQL 12 – Partial implementation of SQL/JSON path language"...

Hans-Juergen Schoenig: Foreign data wrapper for PostgreSQL: Performance Tuning
2 days ago, postgresql

Foreign data wrappers have been around for quite a while and are one of the most widely used feature in PostgreSQL. People simply like foreign data wrappers and we can expect that the community will add even more features as we speak. As far as the postgres_fdw is concerned there are some hidden tuning options, which are not widely known by users. So let us see how we can speed up the PostgreSQL foreign data wrapper. Foreign data wrappers: Creating a “database link” To show how things can be imp...

Tim Colles: PostgreSQL Roles are Cluster-Wide
3 days ago, postgresql

A role in PostgreSQL is common to all databases in the cluster. This seems to be the result of a design decision made when the former user and group handling was unified under role. Follow these links for reference: https://www.postgresql.org/message-id/flat/20050628161358.GH24207%40ns.snowman.net#fd1e062c263589cf67b68850a92c5b06 https://www.postgresql.org/message-id/flat/20050627062949.GU24207%40ns.snowman.net https://www.postgresql.org/message-id/flat/CA%2BTgmobcQOVCDuOx3bSkYYZ%2BeTdTXFSnp...

Avinash Kumar: PostgreSQL Upgrade Using pg_dumpall
3 days ago, postgresql

There are several approaches to assess when you need to upgrade PostgreSQL. In this blog post, we look at the option for upgrading a postgres database using pg_dumpall. As this tool can also be used to back up PostgreSQL clusters, then it is a valid option for upgrading a cluster too. We consider the advantages and disadvantages of this approach, and show you the steps needed to achieve the upgrade. This is the first of our Upgrading or Migrating Your Legacy PostgreSQL to Newer PostgreSQL Versi...

Venkata Nagothi: Configuring PostgreSQL for Business Continuity
3 days ago, postgresql

Business Continuity for Databases Business continuity for databases means databases must be continuously operational even during the disasters. It is imperative to ensure that production databases are available for the applications all the time even during the disasters, otherwise, could end-up being an expensive deal. DBAs, Architects would need to ensure that database environments can sustain disasters and are disaster recovery SLA compliant. To ensure disasters does not affect database avai...

elein mustain: Having Group By Clauses elein’s GeneralBits
4 days ago, postgresql

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 produces only one row for each matching grouping from the input. This allows you to make sophisticated calculations via ordinary SQL. Fruit Example: We have some fruit: item | source | amt |...

Venkata Nagothi: How to Replicate PostgreSQL Data to Remote Sites
6 days ago, postgresql

In a busy database environment with larger size databases, the need for real-time data replication is a common occurrence. Applications often need the production data to be replicated in real-time to remote sites for analytics and other critical business operations needs. DBAs also need to ensure that the data is replicated continuously to the remote sites to meet various requirements. These requirements, though, may not always be to replicate the whole database; there can also be a need to re...

Baron Schwartz: New Book: DevOps for the Database
1 week ago, postgresql

I’ve written a new 65-page ebook, titled DevOps for the Database. Drawn from what I’ve observed over the last 15 or so years in my own experience as well as that of many others, it tries to answer why and how some organizations deliver value to customers fast, run data-intensive applications with high quality, and have great engineering cultures—and why some don’t....

Craig Kerstiens: Fun with SQL: Text and system functions
1 week ago, postgresql

SQL by itself is great and powerful, and Postgres supports a broad array of more modern SQL including things like window functions and common table expressions. But rarely do I write a query where I don’t want to tweak or format the data I’m getting back out of the database. Thankfully Postgres has a rich array of functions to help with converting or formatting data. These built-in functions save me from having to do the logic elsewhere or write my own functions, in other words I have to do le...

Michael Banck: Integrating Patroni into Debian
1 week ago, postgresql

Integrating Patroni into Debian Patroni is a PostgreSQL high availability solution with a focus on containers and Kubernetes. Until recently, the available Debian packages had to be configured manually and did not integrate well with the rest of the distribution. For the upcoming Debian 10 "Buster" release, the Patroni packages... 13-03 Michael Banck ...

Hans-Juergen Schoenig: unaccent: Getting rid of umlauts, accents and special characters
1 week ago, postgresql

Database people dealing with natural languages are all painfully aware of the fact that encodings, special characters, accents and alike are usually hard to deal with. This is especially true if you want to implement search in a user friendly way. Consider the following example in PostgreSQL: My name contains a couple of of super inconvenient special characters, which will cause issues for people around the globe. The correct spelling of my surname is “Schönig”, which is pretty hard to type on m...

Kristi Anderson: Managing High Availability in PostgreSQL – Part II
1 week ago, postgresql

Are you deploying PostgreSQL in the cloud and want to understand your options for achieving high availability? In our previous blog post, Managing High Availability in PostgreSQL – Part I, we discussed the capabilities and functioning of PostgreSQL Automatic Failover (PAF) by ClusterLabs. In Part II, we’re introducing you to an alternative open source tool, Replication Manager from 2ndQuadrant, to be closely followed by Part III where we dive into our third alternative, Patroni by Zalando. Repli...

Regina Obe: PostGIS 2.5.2, 2.4.7, 2.3.9 Released
1 week ago, postgresql

The PostGIS development team is pleased to provide bug fix 2.5.2, 2.4.7, and 2.3.9 for the 2.5, 2.4, and 2.3 stable branches. These are the first versions to be able to compile against Proj 6.0.0, You must upgrade to these if you are using Proj 6. 2.5.2 This release supports PostgreSQL 9.3-11 (will compile against PostgreSQL 12, but not pass tests. Use only for pg_upgrade. You are encouraged to use the PostGIS 3.0 unreleased branch with PostgreSQL 12 , which has features specifically designed to...

Bruce Momjian: The High Value of Data
1 week ago, postgresql

There was a time when every piece of software had to be purchased: operating systems, compilers, middleware, text editors. Those days are mostly gone, though there are a few holdouts (e.g., MS Windows, vertical applications). What happened is that open source software has come to dominate most uses, and software selection is rarely based on cost requirements. One of the final holdouts for purchased software is databases. You might think that is because database software is complex, but so is the...

Michael Paquier: Postgres 12 highlight - Connection slots and WAL senders
1 week ago, postgresql

The maximum number of connections a PostgreSQL can accept is configured using max_connections. When attempting to connect to a server already at full capacity, logically the server complains: $ psql psql: FATAL: sorry, too many clients already It is possible to define connection policies, for example at database level with CREATE DATABASE or ALTER DATABASE, and even have superuser-only connection slots using superuser_reserved_connections, so as a superuser has a reserved space to be able ...

Next