Re: Damaged (during upgrade?) table, how to repair? - Mailing list pgsql-general

On 7/4/21 12:29 PM, W.P. wrote:
>  >> So you have backup of the failed machine's disk stored somewhere else?
>>>>>

>> To be clear the 1TB disk is working where and with what OS?
> 1TB DISC is connected to OrangePi4 (as I have copied disc "sector by 
> sector" from 500GB to 1TB, some months ago, I can't connect both of them 
> to one machine -> same UUIDs, LVM does crazy). And then I copy single 
> files using rsync.

So you have been keeping the 500GB and 1TB disks in sync since the 
original copy was done?


>> For future reference you can could have gone from 9.5 -> 11 without 
>> the intermediate upgrades.
>>
> That is not true for Fedora OS.... Tested both ways, only "step by step" 
> looks working (but is time consuming).
>> Also where were you installing Postgres from the Fedora or Postgres 
>> repos?
>  From Fedora's, during OS upgrade.

pg_upgrade can skip versions, so what you saw was Fedora just doing 
pg_upgrade as it upgraded its version of Postgres as it upgraded its own 
version. One work around would have been, if possible, to install a 
version of Postgres 11 on another machine, the use the pg_dump from that 
version to dump your 9.5 instance to a safe location. Then uninstall 
Postgres from the Fedora 24 and do the OS upgrade. Then reinstall 
Postgres which would be version 11 and restore the dump file to the 
database.



> At 9.5 "era" there ware single directories (/bin /lib etc) for single 
> machine, now as I can see, there are "single" but one for each PG 
> version installed, with dispatcher(s) located in PATH (/usr/(local)/bin.
> 
> So I want to replicate this doing my PG upgrade.
> 

 From your previous post:
 > - booted up into single user, bring up Ethernet, now CAN start 
Postgres but only using pg_ctl directly, does NOT work using 
systemctl... So problem is (possibly) with systemd.
 >
 > Dumped base, pg_dump worked fine, dump gzipped is < 600MB, so I 
assume that somehow Postgres recovered from my (stupid) move...

 > Dumped from what Postgres instance 9.5 or 11?
 >
9.5, the only one on 1TB.

At this point I am thoroughly confused as to where you are working the 
OrangePi4 or the laptop?

In any case I don't see you getting a 9.5 version on the laptop in the 
package directories. Pretty sure the Fedora 30 repos will not have 9.5 
and the Postgres repos don't go back to Fedora 30. So if you want a 9.5 
instance you will need to build it from source in order to get a server 
that works long enough to restore the 9.5 dump to so you can then use 
the 11 instance pg_dump to dump in order to move to the 11 instance.


-- 
Adrian Klaver
adrian.klaver@aklaver.com