Обсуждение: Damaged (during upgrade?) table, how to repair?
Hi there, I have a PG 11.12 (was 9.5 or 9.5) upgraded from 9.x (during OS upgrade). One table is now damaged, trying to dump it results in server restart, message is "invalid record length xxxx maximum is yyy" (from memory). Also fails pg_dumpall. How can I (?) repair this table? (for recent data this works OK). Laurent
On Thu, 2021-07-01 at 10:56 +0200, W.P. wrote: > I have a PG 11.12 (was 9.5 or 9.5) upgraded from 9.x (during OS upgrade). > > One table is now damaged, trying to dump it results in server restart, > message is "invalid record length xxxx maximum is yyy" (from memory). > > How can I (?) repair this table? (for recent data this works OK). If you have a backup, take that. If not, hire an expert in data recovery. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
W dniu 01.07.2021 o 16:19, Laurenz Albe pisze: > On Thu, 2021-07-01 at 10:56 +0200, W.P. wrote: >> I have a PG 11.12 (was 9.5 or 9.5) upgraded from 9.x (during OS upgrade). >> >> One table is now damaged, trying to dump it results in server restart, >> message is "invalid record length xxxx maximum is yyy" (from memory). >> >> How can I (?) repair this table? (for recent data this works OK). > If you have a backup, take that. > > If not, hire an expert in data recovery. > > Yours, > Laurenz Albe I have something similar to backup: old database, but it is 9.5, how can I install 9.5 binaries / libs / config on Fedora 30 i386 (with PG11 installed) side-by-side? Laurent
On 7/1/21 12:56 PM, W.P. wrote: > W dniu 01.07.2021 o 16:19, Laurenz Albe pisze: >> On Thu, 2021-07-01 at 10:56 +0200, W.P. wrote: >>> I have a PG 11.12 (was 9.5 or 9.5) upgraded from 9.x (during OS >>> upgrade). >>> >>> One table is now damaged, trying to dump it results in server restart, >>> message is "invalid record length xxxx maximum is yyy" (from memory). >>> >>> How can I (?) repair this table? (for recent data this works OK). >> If you have a backup, take that. >> >> If not, hire an expert in data recovery. >> >> Yours, >> Laurenz Albe > > I have something similar to backup: old database, but it is 9.5, how can > I install 9.5 binaries / libs / config on Fedora 30 i386 (with PG11 > installed) side-by-side? Is the old database on another machine where it can be started and then the data dumped? > > > Laurent > > > -- Adrian Klaver adrian.klaver@aklaver.com
W dniu 01.07.2021 o 22:27, Adrian Klaver pisze: > On 7/1/21 12:56 PM, W.P. wrote: >> W dniu 01.07.2021 o 16:19, Laurenz Albe pisze: >>> On Thu, 2021-07-01 at 10:56 +0200, W.P. wrote: >>>> I have a PG 11.12 (was 9.5 or 9.5) upgraded from 9.x (during OS >>>> upgrade). >>>> >>>> One table is now damaged, trying to dump it results in server restart, >>>> message is "invalid record length xxxx maximum is yyy" (from memory). >>>> >>>> How can I (?) repair this table? (for recent data this works OK). >>> If you have a backup, take that. >>> >>> If not, hire an expert in data recovery. >>> >>> Yours, >>> Laurenz Albe >> >> I have something similar to backup: old database, but it is 9.5, how >> can I install 9.5 binaries / libs / config on Fedora 30 i386 (with >> PG11 installed) side-by-side? > > Is the old database on another machine where it can be started and > then the data dumped? > It is on another disc, with OS that has problems (F24 does not boot beyond single user mode, Network Manager doesn't start) after removing power / battery while in "suspend to RAM". Thats why I think about setting 9.5 on my current machine (F30/PG11 cluster) and then do a dump, only I need to know how to do it (binaries / libs / default cfg + data copy). Laurent
On 7/1/21 9:24 PM, W.P. wrote: > W dniu 01.07.2021 o 22:27, Adrian Klaver pisze: >> On 7/1/21 12:56 PM, W.P. wrote: >>> W dniu 01.07.2021 o 16:19, Laurenz Albe pisze: >>>> On Thu, 2021-07-01 at 10:56 +0200, W.P. wrote: >>>>> I have a PG 11.12 (was 9.5 or 9.5) upgraded from 9.x (during OS >>>>> upgrade). >>>>> >>>>> One table is now damaged, trying to dump it results in server restart, >>>>> message is "invalid record length xxxx maximum is yyy" (from memory). >>>>> >>>>> How can I (?) repair this table? (for recent data this works OK). >>>> If you have a backup, take that. >>>> >>>> If not, hire an expert in data recovery. >>>> >>>> Yours, >>>> Laurenz Albe >>> >>> I have something similar to backup: old database, but it is 9.5, how >>> can I install 9.5 binaries / libs / config on Fedora 30 i386 (with >>> PG11 installed) side-by-side? >> >> Is the old database on another machine where it can be started and >> then the data dumped? >> > It is on another disc, with OS that has problems (F24 does not boot > beyond single user mode, Network Manager doesn't start) after removing > power / battery while in "suspend to RAM". > > Thats why I think about setting 9.5 on my current machine (F30/PG11 > cluster) and then do a dump, only I need to know how to do it (binaries > / libs / default cfg + data copy). > So you have backup of the failed machine's disk stored somewhere else? Otherwise how are you going to get the 9.5 instance to the new machine? > > Laurent > > > > -- Adrian Klaver adrian.klaver@aklaver.com
W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: > On 7/1/21 9:24 PM, W.P. wrote: >> W dniu 01.07.2021 o 22:27, Adrian Klaver pisze: >>> On 7/1/21 12:56 PM, W.P. wrote: >>>> W dniu 01.07.2021 o 16:19, Laurenz Albe pisze: >>>>> On Thu, 2021-07-01 at 10:56 +0200, W.P. wrote: >>>>>> I have a PG 11.12 (was 9.5 or 9.5) upgraded from 9.x (during OS >>>>>> upgrade). >>>>>> >>>>>> One table is now damaged, trying to dump it results in server >>>>>> restart, >>>>>> message is "invalid record length xxxx maximum is yyy" (from memory). >>>>>> >>>>>> How can I (?) repair this table? (for recent data this works OK). >>>>> If you have a backup, take that. >>>>> >>>>> If not, hire an expert in data recovery. >>>>> >>>>> Yours, >>>>> Laurenz Albe >>>> >>>> I have something similar to backup: old database, but it is 9.5, >>>> how can I install 9.5 binaries / libs / config on Fedora 30 i386 >>>> (with PG11 installed) side-by-side? >>> >>> Is the old database on another machine where it can be started and >>> then the data dumped? >>> >> It is on another disc, with OS that has problems (F24 does not boot >> beyond single user mode, Network Manager doesn't start) after >> removing power / battery while in "suspend to RAM". >> >> Thats why I think about setting 9.5 on my current machine (F30/PG11 >> cluster) and then do a dump, only I need to know how to do it >> (binaries / libs / default cfg + data copy). >> > > So you have backup of the failed machine's disk stored somewhere else? No, I have disc from this machine, looks not damaged (random files). Only problem that OS does not boot beyond "emergency mode". > > Otherwise how are you going to get the 9.5 instance to the new machine? > In modern Postgres I've seen there is possibility to have several (different versions) "clusters" on same machine. My question (for now) is how to do it? (I can see PG10 somewhere in /usr/pgsql/postgresql-10/ - bin, lib, share, postgresql-9.6 is empty). Laurent
On 7/2/21 10:18 AM, W.P. wrote: > W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: >> >> So you have backup of the failed machine's disk stored somewhere else? > > > No, I have disc from this machine, looks not damaged (random files). > Only problem that OS does not boot beyond "emergency mode". I would say your second sentence contradicts your first. In any case, we got to this point as the upgrade(more information on how that was done would be nice) from the 9.5 instance to 11.12 failed. Given that there is good chance that was due to corruption of files in the Postgres cluster on the above disc, it is very probable that going back to that disk will repeat the problem. Still see more below. > >> >> Otherwise how are you going to get the 9.5 instance to the new machine? >> > In modern Postgres I've seen there is possibility to have several > (different versions) "clusters" on same machine. > > My question (for now) is how to do it? I'm going to say the easiest way to do this on the new machine would be to build 9.5 from source: https://www.postgresql.org/docs/9.5/installation.html https://www.postgresql.org/ftp/source/v9.5.25/ This will require installing development packages on your new machine. > > (I can see PG10 somewhere in /usr/pgsql/postgresql-10/ - bin, lib, > share, postgresql-9.6 is empty). Where do you see the above, old machine or new machine? > > > Laurent > > > > -- Adrian Klaver adrian.klaver@aklaver.com
W dniu 02.07.2021 o 21:05, Adrian Klaver pisze: > On 7/2/21 10:18 AM, W.P. wrote: >> W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: > >>> >>> So you have backup of the failed machine's disk stored somewhere else? >> >> >> No, I have disc from this machine, looks not damaged (random files). >> Only problem that OS does not boot beyond "emergency mode". > > I would say your second sentence contradicts your first. Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then copied "sector by sector" (and resized partitions, volumes, fs) to 1TB one. This was my "working" disc. For test, now I have put it (500GB) in another laptop, then upgraded F24 to F30 (step by step, once I had to do manually "pg_upgrade", ARAIR this was at PG10->PG11 step). Now I did my crazy move, removed battery and power while "suspend to RAM". OS (F24! @ 1TB) don't start anymore (beyond "emergency"). I use now this upgraded disc (500GB), copying files for daily work from 1TB one attached to OPi4. Will try, if I can achieve PG9.5 run on that "non working" one. If so, and dumpall is OK, then files on this are mainly intact (except for NetworkManager)... > > In any case, we got to this point as the upgrade(more information on > how that was done would be nice) from the 9.5 instance to 11.12 > failed. Given that there is good chance that was due to corruption of > files in the Postgres cluster on the above disc, it is very probable > that going back to that disk will repeat the problem. Still see more > below. > I did a "step-by-step" OS upgrade using dnf, and (once) manual pg_upgrade (when there was no "automatic" upgrade, db failed to start). > >> >>> >>> Otherwise how are you going to get the 9.5 instance to the new machine? >>> >> In modern Postgres I've seen there is possibility to have several >> (different versions) "clusters" on same machine. >> >> My question (for now) is how to do it? > > I'm going to say the easiest way to do this on the new machine would > be to build 9.5 from source: > > https://www.postgresql.org/docs/9.5/installation.html > > https://www.postgresql.org/ftp/source/v9.5.25/ > > This will require installing development packages on your new machine. > Ok, should be easy :) But what PREFIX should I use during configure step? "/usr/lib/postgresql/"? And what about location of config files? >> >> (I can see PG10 somewhere in /usr/pgsql/postgresql-10/ - bin, lib, >> share, postgresql-9.6 is empty). > > Where do you see the above, old machine or new machine? > 500GB disc, after system upgrade. Laurent
On 7/4/21 9:33 AM, W.P. wrote: > W dniu 02.07.2021 o 21:05, Adrian Klaver pisze: >> On 7/2/21 10:18 AM, W.P. wrote: >>> W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: >> >>>> >>>> So you have backup of the failed machine's disk stored somewhere else? >>> >>> >>> No, I have disc from this machine, looks not damaged (random files). >>> Only problem that OS does not boot beyond "emergency mode". >> >> I would say your second sentence contradicts your first. > > Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then > copied "sector by sector" (and resized partitions, volumes, fs) to 1TB > one. This was my "working" disc. To be clear the 1TB disk is working where and with what OS? > > > For test, now I have put it (500GB) in another laptop, then upgraded F24 > to F30 (step by step, once I had to do manually "pg_upgrade", ARAIR this > was at PG10->PG11 step). For future reference you can could have gone from 9.5 -> 11 without the intermediate upgrades. Also where were you installing Postgres from the Fedora or Postgres repos? > > > Now I did my crazy move, removed battery and power while "suspend to > RAM". OS (F24! @ 1TB) don't start anymore (beyond "emergency"). I will reserve comment on that move. Still in future it would be prudent to do a pg_dump and stash the backup somewhere off machine before you do that sort of experiment. > > I use now this upgraded disc (500GB), copying files for daily work from > 1TB one attached to OPi4. What I understand from the above is you are copying files from the 1TB disk to the 500GB disc to work with on the 500GB disk/machine. Am I correct? > > Will try, if I can achieve PG9.5 run on that "non working" one. If so, > and dumpall is OK, then files on this are mainly intact (except for > NetworkManager)... In that case you will be using 9.5 pg_dump(all) to do the dump, Assuming it works it is not guaranteed that it will restore forward to Postgres 11. >> >> This will require installing development packages on your new machine. >> > Ok, should be easy :) > > But what PREFIX should I use during configure step? > "/usr/lib/postgresql/"? And what about location of config files? The default for a source compile install is /usr/local/pgsql(https://www.postgresql.org/docs/11/install-procedure.html). I would stick with that if for no other reason then helping identifying your package and source clusters. > > >>> >>> (I can see PG10 somewhere in /usr/pgsql/postgresql-10/ - bin, lib, >>> share, postgresql-9.6 is empty). >> >> Where do you see the above, old machine or new machine? >> > 500GB disc, after system upgrade. So those where the intermediate updates as you hopped through the OS/Postgres versions. > > > Laurent > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 7/4/21 9:33 AM, W.P. wrote: > W dniu 02.07.2021 o 21:05, Adrian Klaver pisze: >> On 7/2/21 10:18 AM, W.P. wrote: >>> W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: >> >>>> >>>> So you have backup of the failed machine's disk stored somewhere else? >>> >>> >>> No, I have disc from this machine, looks not damaged (random files). >>> Only problem that OS does not boot beyond "emergency mode". >> >> I would say your second sentence contradicts your first. > > Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then > copied "sector by sector" (and resized partitions, volumes, fs) to 1TB > one. This was my "working" disc. > Just dawned on me, why aren't you working directly from the 1TB disk? It has the presumably intact files from before the OS/Postgres upgrades and the power experiment. > Laurent > > > -- Adrian Klaver adrian.klaver@aklaver.com
W dniu 04.07.2021 o 19:48, Adrian Klaver pisze: > On 7/4/21 9:33 AM, W.P. wrote: >> W dniu 02.07.2021 o 21:05, Adrian Klaver pisze: >>> On 7/2/21 10:18 AM, W.P. wrote: >>>> W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: >>> >>>>> >>>>> So you have backup of the failed machine's disk stored somewhere >>>>> else? >>>> >>>> >>>> No, I have disc from this machine, looks not damaged (random >>>> files). Only problem that OS does not boot beyond "emergency mode". >>> >>> I would say your second sentence contradicts your first. >> >> Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then >> copied "sector by sector" (and resized partitions, volumes, fs) to >> 1TB one. This was my "working" disc. >> > > Just dawned on me, why aren't you working directly from the 1TB disk? > > It has the presumably intact files from before the OS/Postgres > upgrades and the power experiment. > "Only problem that OS does not boot beyond "emergency mode"."... But I made some progress: - 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... BTW, pls respond only to list. Laurent
On 7/4/21 11:59 AM, W.P. wrote: > W dniu 04.07.2021 o 19:48, Adrian Klaver pisze: >> On 7/4/21 9:33 AM, W.P. wrote: >>> W dniu 02.07.2021 o 21:05, Adrian Klaver pisze: >>>> On 7/2/21 10:18 AM, W.P. wrote: >>>>> W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: >>>> >>>>>> >>>>>> So you have backup of the failed machine's disk stored somewhere >>>>>> else? >>>>> >>>>> >>>>> No, I have disc from this machine, looks not damaged (random >>>>> files). Only problem that OS does not boot beyond "emergency mode". >>>> >>>> I would say your second sentence contradicts your first. >>> >>> Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then >>> copied "sector by sector" (and resized partitions, volumes, fs) to >>> 1TB one. This was my "working" disc. >>> >> >> Just dawned on me, why aren't you working directly from the 1TB disk? >> >> It has the presumably intact files from before the OS/Postgres >> upgrades and the power experiment. >> > "Only problem that OS does not boot beyond "emergency mode"."... I thought the 1TB disk was copied over before you did any of the upgrades and experimentation? > > But I made some progress: > > - 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? > > > BTW, pls respond only to list. Afraid that will be hard to achieve as I my muscle memory is hard wired to hit Reply All. If you want to eliminate duplicate copies got to here: https://lists.postgresql.org/manage/ and check: Don't receive an extra copy of mails when listed in To or CC fields and then: Save > > > Laurent > > > -- Adrian Klaver adrian.klaver@aklaver.com
>> So you have backup of the failed machine's disk stored somewhere else? >>>> >>>> >>>> No, I have disc from this machine, looks not damaged (random >>>> files). Only problem that OS does not boot beyond "emergency mode". >>> >>> I would say your second sentence contradicts your first. >> >> Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. Then >> copied "sector by sector" (and resized partitions, volumes, fs) to >> 1TB one. This was my "working" disc. > > 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. > >> >> >> For test, now I have put it (500GB) in another laptop, then upgraded >> F24 to F30 (step by step, once I had to do manually "pg_upgrade", >> ARAIR this was at PG10->PG11 step). > > 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. > >> >> >> Now I did my crazy move, removed battery and power while "suspend to >> RAM". OS (F24! @ 1TB) don't start anymore (beyond "emergency"). > > I will reserve comment on that move. Still in future it would be > prudent to do a pg_dump and stash the backup somewhere off machine > before you do that sort of experiment. > > >> >> I use now this upgraded disc (500GB), copying files for daily work >> from 1TB one attached to OPi4. > > What I understand from the above is you are copying files from the 1TB > disk to the 500GB disc to work with on the 500GB disk/machine. Am I > correct? Yes. > >> >> Will try, if I can achieve PG9.5 run on that "non working" one. If >> so, and dumpall is OK, then files on this are mainly intact (except >> for NetworkManager)... > > In that case you will be using 9.5 pg_dump(all) to do the dump, > Assuming it works it is not guaranteed that it will restore forward to > Postgres 11. > > > >>> >>> This will require installing development packages on your new machine. >>> >> Ok, should be easy :) >> >> But what PREFIX should I use during configure step? >> "/usr/lib/postgresql/"? And what about location of config files? > > The default for a source compile install is > /usr/local/pgsql(https://www.postgresql.org/docs/11/install-procedure.html). > I would stick with that if for no other reason then helping > identifying your package and source clusters. > 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. >> >> >>>> >>>> (I can see PG10 somewhere in /usr/pgsql/postgresql-10/ - bin, lib, >>>> share, postgresql-9.6 is empty). >>> >>> Where do you see the above, old machine or new machine? >>> >> 500GB disc, after system upgrade. > > So those where the intermediate updates as you hopped through the > OS/Postgres versions. > >> >> >> Laurent >> >> >> > >
W dniu 04.07.2021 o 21:10, Adrian Klaver pisze: > On 7/4/21 11:59 AM, W.P. wrote: >> W dniu 04.07.2021 o 19:48, Adrian Klaver pisze: >>> On 7/4/21 9:33 AM, W.P. wrote: >>>> W dniu 02.07.2021 o 21:05, Adrian Klaver pisze: >>>>> On 7/2/21 10:18 AM, W.P. wrote: >>>>>> W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: >>>>> >>>>>>> >>>>>>> So you have backup of the failed machine's disk stored somewhere >>>>>>> else? >>>>>> >>>>>> >>>>>> No, I have disc from this machine, looks not damaged (random >>>>>> files). Only problem that OS does not boot beyond "emergency mode". >>>>> >>>>> I would say your second sentence contradicts your first. >>>> >>>> Nope ;). There was 1 500GB disc, with Fedora24 and Postgres 9.5. >>>> Then copied "sector by sector" (and resized partitions, volumes, >>>> fs) to 1TB one. This was my "working" disc. >>>> >>> >>> Just dawned on me, why aren't you working directly from the 1TB disk? >>> >>> It has the presumably intact files from before the OS/Postgres >>> upgrades and the power experiment. >>> >> "Only problem that OS does not boot beyond "emergency mode"."... > > I thought the 1TB disk was copied over before you did any of the > upgrades and experimentation? > >> >> But I made some progress: >> >> - 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. >> >> >> BTW, pls respond only to list. > > Afraid that will be hard to achieve as I my muscle memory is hard > wired to hit Reply All. If you want to eliminate duplicate copies got > to here: > > https://lists.postgresql.org/manage/ > > and check: > > Don't receive an extra copy of mails when listed in To or CC fields > > and then: > > Save THX :) > >> >> >> Laurent >> >> >> > >
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
On Sun, Jul 4, 2021 at 1:20 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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.
There are 9.5 docker images available from dockerhub. Easy enough to run postgres from inside one of those with the postgres data dir mounted inside, I would think. Could even use an 11 image to connect to it for pg_dump.
--sam