News for hackers. Stuff that matters.
Re:PostgreSQL upgrades are hard by 411111111111111 on Saturday June 25, 2022 @06:22AM (#31874050)
In not the person you've responded to but I've never been able to get `pg_dump -Fc` and restore to work across versions. It always errors out for me, saying it's an invalid/broken backup. Super annoying because now the workflow is 1. Start old version just to restore, remove old version and start new version for upgrade.
Re:PostgreSQL upgrades are hard by castorp on Saturday June 25, 2022 @06:39AM (#31874128)
You need to use pg_dump from the new version, not from the old version.
Re:PostgreSQL upgrades are hard by jasfi on Saturday June 25, 2022 @09:30AM (#31875160)
I think you found a common stumbling block. The tutorial people follow may say to run pg_dump and many people would assume you're running it from the old version.
Re:PostgreSQL upgrades are hard by rapind on Saturday June 25, 2022 @11:59AM (#31876287)
Wait, what? Explain please.

OK upon further thought, what you're saying is to use the pg_dump tool that comes with the pg version you are upgrading to.

I read it as "use pg_dump instead of pg_restore"...

Re:PostgreSQL upgrades are hard by mh- on Saturday June 25, 2022 @02:31PM (#31877557)
Yes. Put concisely: Use the client tools from the newest version involved for all steps.
Re:PostgreSQL upgrades are hard by oblio on Saturday June 25, 2022 @02:48PM (#31877675)
Isn't that impractical for most Linux distros? You'd need to manually install the new client.
Re:PostgreSQL upgrades are hard by BGZq7 on Saturday June 25, 2022 @03:14PM (#31877870)
On Debian (and probably most of its derivatives), the package manager will not remove any running PostgreSQL versions and will let you have multiple versions side by side. It also has its own tool pg_upgradecluster, which can perform the upgrade using pg_dump+pg_restore or pg_upgrade (optionally with link or clone options).
Re:PostgreSQL upgrades are hard by mh- on Saturday June 25, 2022 @02:52PM (#31877700)
Probably? Certainly if you rely on distro packaging for it, yes.

I was just trying to simplify the guidance here. :)

Re:PostgreSQL upgrades are hard by autra on Monday June 27, 2022 @07:28AM (#31893073)
Yes, and usually, it means you need to use the full binary path unfortunately (because debian wrappers embedded in the package select the version from your 5432 cluster).
Re:PostgreSQL upgrades are hard by forinti on Saturday June 25, 2022 @08:49AM (#31874871)
I've used dumps as backups and never experienced this issue, although they would stop prematurely from time to time. The solution was just to check the string at the end and repeat the dump if it wasn't there.
Re:PostgreSQL upgrades are hard by 411111111111111 on Saturday June 25, 2022 @09:14AM (#31875041)
Considering that -Fc is a binary format, how would i check a string at the end of it?
Re:PostgreSQL upgrades are hard by forinti on Saturday June 25, 2022 @09:57AM (#31875339)
If you do an SQL dump, it will end with "-- PostgreSQL database dump complete".

If you use -Fc, you can make an SQL file from the dump using pg_restore like this:

  pg_restore -f db.sql db.dmp
Or straight to stdout like this:

  pg_restore -f - db.dmp | grep "database dump complete"
Although I guess if it's corrupted pg_restore won't work.

It is really fast, but might not be practical if your database is too big.

Re:PostgreSQL upgrades are hard by 411111111111111 on Saturday June 25, 2022 @10:28AM (#31875566)
My issue was indeed the mishmash of versions, as the restore succeeded with the mentioned steps (installing old version, restoring, upgrading).

nonetheless, interesting way to validate a backup, though it would probably take too long on most dumps