--- title: "Pros and cons of different Postgres upgrade methods" publication: "Postgres Weekly" issue: 322 published_at: "2019-09-11" canonical_url: "https://postgresweekly.com/issues/322" --- The latest Postgres news, articles, tools, and projects. #322 — September 11, 2019 [Read on the Web](https://postgresweekly.com/issues/322) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/v1568155380/jqkp0qfoibvzfmh6l31h.png)](https://www.cybertec-postgresql.com/en/a-primer-on-postgresql-upgrade-methods/) [A Primer on Postgres Upgrade Methods](https://www.cybertec-postgresql.com/en/a-primer-on-postgresql-upgrade-methods/) — Postgres 12 is just around the corner, so it’s worth revisiting what’s involved in upgrading your Postgres installation. Kaarel looks at the pros and cons of three different approaches. Kaarel Moppel [![\](https://copm.s3.amazonaws.com/d022bff6.png)](https://info.enterprisedb.com/HighAvailabilityOptionsforPostgresinContainerspaper.html?utm_source=Cooperpress&utm_medium=ad) [A Discussion of High Availability Options for Postgres in Containers](https://info.enterprisedb.com/HighAvailabilityOptionsforPostgresinContainerspaper.html?utm_source=Cooperpress&utm_medium=ad) — Clustered containers running Postgres require a controller to monitor and manage the cluster. Find the right one for your organization. EnterpriseDB sponsor [Managing Postgres Users and Roles](https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/) — This is a few months old but we missed it at the time. That’s a shame, because it’s a really neat introduction to working with users and roles in Postgres. Yaser Raja [Vacuum Improvements in Postgres 12](https://www.2ndquadrant.com/en/blog/pg-phriday-postgres-12-revs-up-vacuum/) — Postgres users sometimes complain about VACUUM maintenance tasks. Well Postgres 12 is making these just a bit less painful, and easier to use frequently. Shaun Thomas [What is an SQL 'Relation'?](https://theartofpostgresql.com/blog/2019-09-sql-relations/) — This is a short but sweet introduction to the concept of what a ‘relation’ is. It’s not just the persistent tables you’re familiar with but _“the truth is that any SQL query defines a new relation.”_ Dmitri has also released some related articles this week reintroducing us to [joins](https://theartofpostgresql.com/blog/2019-09-sql-joins/) and [aggregates.](https://theartofpostgresql.com/blog/2019-09-sql-aggregates/) Dimitri Fontaine [Get More Out of Your Postgres Database](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklySecondary) — pganalyze summarized their learnings on how to get a 3x performance improvement on their Postgres database. Lots of helpful best-practices. pganalyze sponsor [Tuning DB Parameters for Postgres 12 in Amazon RDS](http://jkshah.blogspot.com/2019/09/tuning-db-parameters-for-postgresql-12.html) — Last week, Jignesh showed us [how to use the beta of Postgres 12 on Amazon RDS](http://jkshah.blogspot.com/2019/08/setting-up-postgresql-12-beta-3-for.html), now he looks at how to tweak its settings for better performance. Jignesh Shah [How to Deploy and Manage Postgres on OpenShift using the ROBIN Operator](https://blog.openshift.com/how-to-deploy-and-manage-postgresql-on-openshift-using-the-robin-operator/) Ankur Desai [Installing pgAdmin 4 on Debian 10](https://linuxhint.com/install_pgadmin4_debian_10/) Shahriar Shovon [Replicating PostgreSQL into MemSQL's Columnstore](https://www.memsql.com/blog/replicating-postgresql-into-memsqls-columnstore/) — How to get the high performance of MemSQL’s columnstore for queries while keeping transaction data in PostgreSQL for updates. Oryan Moshe supported by [![Chaos Conf\](https://res.cloudinary.com/cpress/image/upload/v1566213097/i9xqivqj9c5fxawufvch.png)](https://chaosconf.io/cp) 💡**Tip of the Week** Turning query results into JSON Did you know that Postgres includes a function for turning query result rows into JSON? It's called `row_to_json`. Let's create a basic table containing records of two people, along with an array column of 'interests', to play with: CREATE TABLE people (name text, age int, interests text[]); INSERT INTO people (name, age, interests) VALUES ('Jon', 12, ARRAY ['Golf', 'Food']), ('Jane', 45, ARRAY ['Art']); The most basic way to use `row_to_json` is by using the `ROW` row constructor like so: SELECT row_to_json(ROW(name, age, interests)) FROM people; row_to_json ----------------------------------- {"f1":"Jon","f2":12,"f3":["Golf","Food"]} {"f1":"Jane","f2":45,"f3":["Art"]} We can see that the different types of data in the table (strings, integers, arrays) are reflected in the output, but the column names aren't! Luckily there's a better way to tackle this by using a subquery instead: SELECT row_to_json(q1) FROM (SELECT * FROM people LIMIT 1) q1; row_to_json ----------------------------------- {"name":"Jon","age":12,"interests":["Golf","Food"]} It has no relevance to our example here, but `row_to_json` has an optional second argument which 'prettifies' the output in certain circumstances – to enable it, you'd use `row_to_json(q1, true)` in the example query above instead. This tip of the week is brought to you by [Chaos Conf](https://chaosconf.io/cp), the flagship event for all things Chaos Engineering and resilience. Learn more at [chaosconf.io](https://chaosconf.io/cp). 🗓**Upcoming Events** - [PostgresConf Silicon Valley 2019](https://postgresconf.org/conferences/SV2019) (September 18-20 in San Jose) — The schedule (including training) has now been published. - [PostgresConf South Africa 2019](https://postgresconf.org/conferences/SouthAfrica2019) (October 8-9 in Johannesburg) — An opportunity for the database administration and developer community who use Postgres to get to know each other. - [PostgreSQL Conference Europe 2019](https://2019.pgconf.eu/) (October 15-18 in Milan, Italy) Curated by [Cooperpress](https://cooperpress.com/), [Peter Cooper](https://twitter.com/peterc), and [Craig Kerstiens](https://twitter.com/craigkerstiens). [Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) © Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom