From: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
Date: Wed, 9 Jun 2021 18:50:38 -0700
Message-ID: <a7e9da9d-7433-ee02-9e35-db6b19acdcd4@mailpen.com> (raw)
In-Reply-To: <a38c45cf-3bbd-b43a-0f8a-11539c1c6dc1@mailpen.com>
References: <88dec70b-eb63-0b72-0b5d-253851ef127b@mailpen.com>
<92cc8563-195f-8727-d887-5f2bb63f231f@mailpen.com>
<20210601041635.GQ2082@telsasoft.com>
<a38c45cf-3bbd-b43a-0f8a-11539c1c6dc1@mailpen.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: postgresql@mailpen.com, pgsql-performance@lists.postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
In-Reply-To: <a7e9da9d-7433-ee02-9e35-db6b19acdcd4@mailpen.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox