How to upgrade psql on Amazon ec2?

Question

I'm aware of how one can upgrade the PostgreSQL version on an RDS instance by using the management console's point-and-click. However, doing so leaves me with mismatched client and server psql versions. I want to upgrade the psql client on my ec2 instance. How does one do so?

Thermos

Answer 1

You can also use amazon-linux-extras command to install new major versions of postgresql, for example:

$ sudo amazon-linux-extras install postgresql13

$ psql --version
psql (PostgreSQL) 13.3

Note that previous versions will remain enabled, but you can explicitly disable them (even though there's no uninstall, try yum to get rid of packages):

$ sudo amazon-linux-extras disable postgresql11

Tested on:

$ hostnamectl | grep -E -w 'Operating|Kernel|Architecture'
Operating System: Amazon Linux 2
Kernel: Linux 4.14.243-185.433.amzn2.aarch64
Architecture: arm64

Krys

running amazon-linux-extras install postgresql13 will overwrite the previous version of postgres, If you have a running DB this can be a problem. – Lorianne

That's true since the postgresqlXX package doesn't distinguish between client and server. Since the original poster explicitly mentioned the use of AWS managed RDS database service, and only wanted to update the client on an EC2 machine, there was most probably no risk since the server was running remotely. But I agree that it's safer to state the client-server dependency explicitly. – Krys

Answer 2

The exact solution is going to vary depending on your AMI, but here's what worked for me on Amazon Linux:

sudo yum erase postgresql92 -y
sudo yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-ami201503-96-9.6-2.noarch.rpm -y
sudo yum install postgresql96 postgresql96-devel -y
sudo ln -sf /usr/pgsql-9.6/bin/pg_config /usr/bin/

You might not need the last step unless you're trying to get the pg gem working in Ruby on Rails.

Child

I tried this but I keep getting this messages - Loaded plugins: priorities, update-motd, upgrade-helper Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast – Dogfish

And it keeps trying all mirrors and then fails . Any ideas would be appreciated. – Dogfish

Did you try yum makecache fast? – Child

I did and I see the same message again as I mentioned above. – Dogfish