Tag: PostgreSQL

Feed: Planet PostgreSQL. Sun, Jan 23, 2022 2-minute read Typically when discussing having “read-only” connections to a PostgreSQL database, it is in the context of connecting to a replica. There are a variety of methods available to route connections with known read-only queries (i.e. queries with SELECT statements…that are not calling VOLATILE functions that modify data). This includes connection proxy software like Pgpool-II or framework mechanisms such as Django’s database router. However, there are situations where you might need to force read-only connections to your primary (read-write) Postgres instance. Some examples include putting your application into a degraded state to ... Read More

Feed: Recent Announcements. Following the announcement of updates to the PostgreSQL database, we have added support in Amazon Relational Database Service (Amazon RDS) for PostgreSQL minor versions 13.5, 12.9, 11.14, 10.19, and 9.6.24. We have also added support in Amazon RDS on Outposts for PostgreSQL minor versions 13.5 and 12.9. This release closes security vulnerabilities in PostgreSQL and contains bug fixes and improvements done by the PostgreSQL community. This also includes the final release of PostgreSQL 9.6 ... Read More

Feed: Recent Announcements. AWS Migration Hub Strategy Recommendations helps you build a migration and modernization strategy for your applications running on premises or in AWS. Strategy Recommendations automates the manual process of analyzing your servers - and the applications and databases running on those servers - to reduce the time and effort spent on planning application migration and modernization, and accelerate your business transformation on AWS. With the additional recommendation for Babelfish for Aurora PostgreSQL, customers can plan on migrating and replatforming their legacy SQL Server databases on PostgreSQL without needing to make changes to the application itself. With Babelfish ... Read More

Feed: Planet PostgreSQL. On 17th of January 2022, Michael Paquier committed patch: Introduce log_destination=jsonlog   "jsonlog" is a new value that can be added to log_destination to provide logs in the JSON format, with its output written to a file, making it the third type of destination of this kind, after "stderr" and "csvlog". The format is convenient to feed logs to other applications. There is also a plugin external to core that provided this feature using the hook in elog.c, but this had to overwrite the output of "stderr" to work, so being able to do both at the ... Read More

Feed: Planet PostgreSQL. Failover of the logical replication slot has always been the pain point while using the logical replication in PostgreSQL. This lack of feature undermined the use of logical replication and acted as one of the biggest deterrents. The stake and impact were so high that many organizations had to discard their plans around logical replication, and it affected many plans for migrations to PostgreSQL. It was painful to see that many had to opt for proprietary/vendor-specific solutions instead. At Percona, we have written about this in the past: Missing Piece: Failover of the Logical Replication Slot.  In ... Read More

Feed: Planet PostgreSQL. I have done a few previous blog posts on who has contributed to PostgreSQL, but I did not do one last year. A couple people mentioned to me that they missed it, so I decided to do one this year, and I decided to gather statistics, using basically the same methodology that I have in the past, for both 2020 and 2021. As always, it is important to remember, first, that many people contribute to the project in ways that these statistics don't capture, and second, that the statistics themselves are prone to mistakes (since there is ... Read More

Feed: Planet PostgreSQL. A customer recently asked if our Percona Distribution for PostgreSQL Operator supports the deployment of a standby cluster, which they need as part of their Disaster Recovery (DR) strategy. The answer is yes – as long as you are making use of an object storage system for backups, such as AWS S3 or GCP Cloud Storage buckets, that can be accessed by the standby cluster. In a nutshell, it works like this: The primary cluster is configured with pgBackRest to take backups and store them alongside archived WAL files in a remote repository; The standby cluster is ... Read More

Feed: Planet PostgreSQL. On behalf of the PostgreSQL community and the team at MigOps, we wish all the readers a very Happy New Year - 2022. It is no wonder that PostgreSQL is the only Open Source database with a consistent increase in its adoption every year. The Year 2021 has been another milestone in the PostgreSQL world. Every user of PostgreSQL has either directly or indirectly created a positive impact to the PostgreSQL adoption through - patch submissions or reviews, feature requests, blogs and articles, speaking at PostgreSQL conferences, discussions in postgres mailing lists about proposed features, etc. In ... Read More

Feed: Planet PostgreSQL. In a previous post I stored tags and groups as arrays in the posts table. I created GIN indexes on them, but without the possibility to add the timestamp to it. To optimize queries on a list of tags, or groups, within a range of date, I added secondary tables, automatically maintained by triggers. This was much more efficient in YugabyteDB because this additional index is like an index (tables are stored as LSM Trees rather than heap tables) and because, without this, the Rows Removed by Filter from the GIN index are very expensive in a ... Read More

Feed: Planet PostgreSQL. In parts 6 and of the pipeline series we took a different path in the pipe/tunnel and explored PostgreSQL and Apache Superset, mainly from a functional perspective—how can you get JSON data into PostgreSQL from Apache Kafka Connect, and what does it look like in Superset. In part 8, we ran some initial load tests and found out how the capacity of the original Elasticsearch pipeline compared with the PostgreSQL variant. These results were surprising, so in true “Myth Busters” style we had another attempt to make them more comparable (Part 9). In this blog, we sum ... Read More