Re: AWS forcing PG upgrade from v9.6 a disaster - Mailing list pgsql-performance
| From | Dean Gibson (DB Administrator) |
|---|---|
| Subject | Re: AWS forcing PG upgrade from v9.6 a disaster |
| Date | |
| Msg-id | a7e9da9d-7433-ee02-9e35-db6b19acdcd4@mailpen.com Whole thread Raw |
| In response to | Re: AWS forcing PG upgrade from v9.6 a disaster ("Dean Gibson (DB Administrator)" <postgresql@mailpen.com>) |
| Responses |
Re: AWS forcing PG upgrade from v9.6 a disaster
(Andrew Dunstan <andrew@dunslane.net>)
Re: AWS forcing PG upgrade from v9.6 a disaster (Joshua Drake <jd@commandprompt.com>) |
| List | pgsql-performance |
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.
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.
pgsql-performance by date: