Having now successfully migrated from PostgreSQL v9.6 to v13.2 in
Amazon RDS, I wondered, why I am paying AWS for an RDS-based
version, when I was forced by their POLICY to go through the effort
I did? I'm not one of the crowd who thinks, "It works OK, so I
don't update anything". I'm usually one who is VERY quick to apply
upgrades, especially when there is a fallback ability. However, the
initial failure to successfully upgrade from v9.6 to any more recent
major version, put me in a time-limited box that I really don't like
to be in.
If I'm going to have to deal with maintenance issues, like I easily
did when I ran native PostgreSQL, why not go back to that? So, I've
ported my database back to native PostgreSQL v13.3 on an AWS EC2
instance. It looks like I will save about 40% of the cost, which is
in accord with this article:
https://www.iobasis.com/Strategies-to-reduce-Amazon-RDS-Costs/
Why am I mentioning this here? Because there were minor issues
& benefits in porting back to native PostgreSQL, that may be of
interest here:
First, pg_dumpall (v13.3) errors out, because on RDS, you cannot be
a superuser, & it tries to dump protected stuff. If there is a
way around that, I'd like to know it, even though it's not an issue
now. pg_dump works OK, but of course you don't get the roles
dumped. Fortunately, I kept script files that have all the database
setup, so I just ran them to create all the relationships, &
then used the pg_dump output. Worked flawlessly.
Second, I noticed that the compressed ("-Z6" level) output from
pg-dump is less than one-tenth of the disk size of the restored
database. That's LOT less than the size of the backups that AWS was
charging me for.
Third, once you increase your disk size in RDS, you can never
decrease it, unless you go through the above port to a brand new
instance (RDS or native PostgreSQL). RDS backups must be restored
to the same size volume (or larger) that they were created for. A
VACUUM FULL ANALYZE on RDS requires more than doubling the required
disk size (I tried with less several times). This is easily dealt
with on an EC2 Linux instance, requiring only a couple minutes of DB
downtime.
Fourth, while AWS is forcing customers to upgrade from v9.6, but the
only PostgreSQL client tools that AWS currently provides in their
standard repository are for v9.6!!! That means when you want to use
any of their client tools on newer versions, you have problems.
psql gives you a warning on each startup, & pg_dump simply
(& correctly) won't back up a newer DB. If you add their
"optional" repository, you can use v12.6 tools, but v13.3 is only
available by hand-editing the repo file to include v13 (which I
did). For this level of support, I pay extra? I don't think so.
Finally, the AWS support forums are effectively "write-only." Most
of the questions asked there, never get ANY response from other
users, & AWS only uses them to post announcements, from what I
can tell. I got a LOT more help here in this thread, & last I
looked, I don't pay anyone here.
