Damaged (during upgrade?) table, how to repair?

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.

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 :)

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