Hi every body, I want to upgrade postgres from 11 to 12.15 But I don’t know, how to analyze impact with rails app, my app use ruby 3 and rails 6
1 Like
Hello there, Before making any changes you should create a backup of your database to ensure data safety after that Install PostgreSQL 12.15.
Dump your PostgreSQL 11 database using the pg_dump command.
Also Restore the data into PostgreSQL 12.15
In your Rails application, update the gem 'pg' line in your Gemfile to use the latest version of the pg gem compatible with PostgreSQL 12.
Run bundle update pg to update the gem.
In your config/database.yml file, update the database connection settings to point to the new PostgreSQL 12.15 database.
Run rails db:migrate to ensure your database schema is up to date.
Thoroughly test your Rails application to ensure it works correctly with PostgreSQL 12.15.
Keep an eye on your application’s performance and monitor the PostgreSQL logs for any issues. You may need to optimize queries or configurations if necessary.
Once you are confident that your application is running smoothly with PostgreSQL 12.15, you can safely remove the old PostgreSQL 11 installation and associated data.
I hope the above information is helpful to you.
1 Like
We recently (07 Nov 2023) upgraded the PG version from 11 to 12.16, and noticed a huge performance drop.
Rails 6.1.7, Ruby 3.1.3
+1 tysonzach - That’s how we tested postgres upgrade when we’re testing it against our app. Additionally using pg_hero gem helped identifying long running queries and how much impact did the upgrade had on them.
Hey Akhil, how did you measured the performance drop ? I’ve noticed in the past that it’s most likely unoptimized queries like not using an index etc causes such issues. Have you also checked locks in your application ? Locking can lead to starvation sometimes which can slow down performance.
we’ve observed a significant performance impact, including a doubling of CPU usage and negative effects on various other metrics.
@akhilgkrishnan I would suggest to check long running queries using pg hero gem and then figure out which queries are causing the issue. Doubling of CPU might imply starvation because of locking, queries waiting on acquiring locks can use up CPU cycles. I am assuming you mean application CPU and metrics and not postgres instance, if it’s indeed the postgres instance metrics then it might be some config issue.
I would start by investigating reporting or analytics if your application has that, seeing that the CPU cycle doubled I would presume that your application is heavily using SQL.
It’ll also be helpful to look into changelog in Postgres 12: https://www.enterprisedb.com/blog/8-major-improvements-postgresql-12 or release notes: PostgreSQL: Release Notes. Using that one can figure the reason behind such issues.
Hope the info helps.