Monday, June 26

The rds_superuser role isn’t that super

Andrew's PlanetPostgreSQL
The Amazon RDS documentation blithely contains this statement: "When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is similar to the PostgreSQL superuser role (customarily named postgres in local instances) but with some restrictions." But just how super is it?One of the things I came up against recently was that, unlike the usual postgres superuser, this role has no access other than what is explicitly granted to objects owned by other users. From a table and function privileges point of view, it's just an ordinary user.So if you're using more than one user in your RDS database, even if one or even all of them are rds_superusers, you're going to become very familiar with the GRANT command if y...

Barman 2.1 and the new –archive option

Giulio's PlanetPostgreSQL
Barman 2.1 Version 2.1 of Barman, backup and recovery manager for PostgreSQL, was released Thursday, Jan. 5.The new release, along with several bugfixes, introduces preliminary support for the upcoming PostgreSQL 10, and adds the --archive option to the switch-xlog command. switch-xlog --archive The new --archive option is especially useful when setting up a new server.Until now, the switch-xlog command used to force the PostgreSQL server to switch to a different transaction log file. Now, Barman also gives the --archive option, which triggers WAL archiving after the xlog switch, and forces Barman to wait for the archival of the closed WAL file.By default Barman expects to receive the WAL in 30 seconds, the amount of seconds to wait can be changed using the --archive-timeout...

Managed Database Services – pros and cons

Andrew's PlanetPostgreSQL
Using a managed service is a very attractive proposition. You are offloading a heck of a lot of worry, especially when it comes to something as complicated and, let's face it, specialized as a database. Someone else will set it up for you, and back it up, and keep it running, without you having to worry too much about it. However, there are downsides. You can only get what the manager is offering. Often that's good enough. But needs change, and I have often seen people start with managed services, only to find that they want more than they can get that way.Just yesterday I received a complaint that the Redis Foreign Data Wrapper (which I have done a lot of work on) is not available on Amazon RDS. And that's completely understandable. Amazon only provides a limited number of extensions,...

PostgreSQL’s Popularity Goes Up Again

2ndQuadrant, Simon's PlanetPostgreSQL
Mirror mirror on the wall, Who is the fairest Database of all?A frequently asked question, certainly.DB-Engines recently announced it's DBMS of the Year. Maybe the cool thing is that PostgreSQL is in 3rd Place. Yee-ha, an open source project is up there!Let's look closely about what this means.PostgreSQL.org's agreed response was this... "It's great to see the continued success of PostgreSQL being reflected in DB-Engines rankings. It's clear that the strength of the following for the World's Most Advanced Open Source Database is enough to outweigh the largest software companies as people continue to choose to move away from commercial databases." though because of commercial sensitivity this was toned down to this "It's great to see the continued success of PostgreSQL b
PGDay.IT 2016 in Prato, Italy

PGDay.IT 2016 in Prato, Italy

2ndQuadrant, Jonathan's PlanetPostgreSQL
This year, the ITPUG (Italian Postgres User Group) organized PGDay in Prato, my hometown and city where the association was born, although most of the members are from outside of the Tuscany region.It was my pleasure to contribute to the community by sharing my professional experience. I gave a talk about PostgreSQL on Network File System, a research based on crash tests to prove reliability using PostgreSQL on NFS. Sharing my PostgreSQL experience with the participants was useful and pleasant.During the event, I had the chance to attend the talk about tools for upgrading Postgres between major releases given by Denis Gasparin; and the one about ULSS's (Regional Healthcare System) migration to Open Source, given by Giorgio Roncolato, proving that more institutions than we thought a

CHAR16 Conf Panel Video

2ndQuadrant, Simon's PlanetPostgreSQL
CHAR(16) Conference was a live conference in NYC held on 6 Dec 2016, focusing on Scalability for Business.The Speaker Panel was recorded, so you can find out what happened here: https://youtu.be/eLjBoZr4dTc (1 hour)Eric David Benari hosted an hour long "chat show" format, featuringMason Sharp Petr Jelinek Peter Eisentraut Simon Riggs Jonathan KatzOther videos? No, sorry. Obviously, if you have to pay to attend, but we post the videos for free, then fewer people will attend. So next time please come along for the conference and find out what's happening, ask questions and enjoy chatting with the attendees.Hope to see you next year.

The PostgreSQL License – What Does It Mean For My Business?

Umair's PlanetPostgreSQL
When I talk to various people about PostgreSQL, based on where they are in the process of analyzing it, I get asked many similar questions. One of the most frequently asked questions is: What is the PostgreSQL licensing model? Here is my attempt to address this question from the perspective of what the PostgreSQL license means for your business. Go Ahead, Read It - It’s Really Not That Scary Let’s be honest with ourselves, we are really not in the habit of reading license agreements - they are best left to the legal department to decipher. And seriously, licenses have become so complex and so detailed that even if I try really really hard to read just a few clauses, my head starts to hurt.The PostgreSQL License is different though.I have reproduced the license in full at the e

CHAR(16) Scalability for Business

2ndQuadrant, Simon's PlanetPostgreSQL
CHAR(16) Conference on Scalability for Business went very well in the heart of New York City.Hotel Pennsylvania was a great venue; convenient for travel and a great conference venue with coffee, food and evening drinks.Jonathan Katz was great as MC for the conference, while Eric David Benari did a great job of hosting the Speaker Panel and keeping everybody on their toes with surprising and revealing questions. We'll be posting the video for that sometime soon.All the presentations were well received and the room was full all day long. Corridor feedback was that the speakers were good and the content was "dense" and very much in line with the specialised nature of the conference. Many good detailed questions after each talk, always a good sign people are tuned in.Thanks to

Avoiding Disasters in New York

2ndQuadrant, Simon's PlanetPostgreSQL
What to do when disaster strikes... (various topics)King Kong attacks! Lure up tall building and attack with biplanesGodzilla’s eggs hatch Team up with European Special Forces operative to escape, then call in airstrike on Madison Square GardensWatch the Rangers lose at home (same as above)Global Warming descends quickly Stay holed up in Public Library, burn copies of “MySQL In 32 Days” to stay warmAssault on Precinct 13 Write memo to boss about false economy of staying in cheaper neighbourhoodsAlien mothership hovers over city Rousing speech about the dangers of NoSQL... "...won't go quietly into the night...", "...fight them on the beaches..." etc; wait for aliens to die from fatal flaws in their technologyLose database servers in both Availability Zones

On the impact of full-page writes

2ndQuadrant, PostgreSQL, Tomas' PlanetPostgreSQL
While tweaking postgresql.conf, you might have noticed there's an option called full_page_writes. The comment next to it says something about partial page writes, and people generally leave it set to on - which is a good thing, as I'll explain later in this post. It's however useful to understand what full page writes do, because the impact on performance may be quite significant.Unlike my previous post on checkpoint tuning, this is not a guide how to tune the server. There's not much you can tweak, really, but I'll show you how some application-level decisions (e.g. choice of data types) may interact with full page writes.(more…)