Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote:

Is it possible to repair this?

Not unless you tell us what exactly you did, command by command.

Yours,
Laurenz Albe

W dniu 06.04.2022 o 12:37, Laurenz Albe pisze:

On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote:

Is it possible to repair this?

Not unless you tell us what exactly you did, command by command.

Yours,
Laurenz Albe

That was not a "command". PG was working ok, DB on encrypted (LUKS on
LVM2, ext4) USB drive.

Then simply I disconnected cable by accident.

After OPi restart, with filesystem on USB mounted again and fsck'ed
first, PG refuses to start. With a message about replication error in log.

But I did not configured relication.

Thats all.

Laurent

On 4/6/22 11:41, W.P. wrote:

W dniu 06.04.2022 o 12:37, Laurenz Albe pisze:

On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote:

Is it possible to repair this?

Not unless you tell us what exactly you did, command by command.

Yours,
Laurenz Albe

That was not a "command". PG was working ok, DB on encrypted (LUKS on
LVM2, ext4) USB drive.

Then simply I disconnected cable by accident.

After OPi restart, with filesystem on USB mounted again and fsck'ed first,
PG refuses to start. With a message about replication error in log.

But I did not configured relication.

The disk got corrupted, and one part that got corrupted is where
Postgresql's replication (or not) is configured.

Delete the data/ directory and restore from backups (including config files).

--
Angular momentum makes the world go 'round.

On Wednesday, April 6th, 2022 at 17:41, W.P. <laurentp@wp.pl> wrote:

That was not a "command". PG was working ok, DB on encrypted (LUKS on
LVM2, ext4) USB drive.

I mean, why ?

If there was ever a "setting yourself up to fail" scenario, it would be this. Just because you can do it, doesn't mean you should.

I suspect you will have to file this under "learnt the hard way"

W dniu 06.04.2022 o 18:54, Ron pisze:

On 4/6/22 11:41, W.P. wrote:

W dniu 06.04.2022 o 12:37, Laurenz Albe pisze:

On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote:

Is it possible to repair this?

Not unless you tell us what exactly you did, command by command.

Yours,
Laurenz Albe

That was not a "command". PG was working ok, DB on encrypted (LUKS on
LVM2, ext4) USB drive.

Then simply I disconnected cable by accident.

After OPi restart, with filesystem on USB mounted again and fsck'ed
first, PG refuses to start. With a message about replication error in
log.

But I did not configured relication.

The disk got corrupted, and one part that got corrupted is where
Postgresql's replication (or not) is configured.

Delete the data/ directory and restore from backups (including config
files).

I know about restoring backup. I just don't have one (at least with
reasonnable age), as it is experimental installation. (Didn't ever been
thinking about locating production DB on USB drive :) ).

If it is not possible to get rid ("somehow") of this "replication
error", I will do a "logic restore" -> I have similar database on my
laptop, so I'll get a pg_dumpall from this one, then recreate fresh DB
on OPi and restore from SQL.

It looks that DATA in PG DB is protected by various mechanisms very
well, but "dictionary data" (in this case replication data) is not (as
much). And in my case ext4 recovery mechanisms failed.

This makes me logical replication more appealing.

Laurent

On 06/04/2022 18:20, Laura Smith wrote:

On Wednesday, April 6th, 2022 at 17:41, W.P. <laurentp@wp.pl> wrote:

That was not a "command". PG was working ok, DB on encrypted (LUKS
on LVM2, ext4) USB drive.

I mean, why ?

If there was ever a "setting yourself up to fail" scenario, it would
be this. Just because you can do it, doesn't mean you should.

That was my thought too, but not being an expert I didn't want to jump
straight in... The OP might have a good reason for doing this, but
trying to run a database off a USB stick strikes me as asking for trouble.

I suspect you will have to file this under "learnt the hard way"

Heh - I have a lot of stuff filed there myself. :-)

Ray.

--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

On 4/6/22 11:04 AM, W.P. wrote:

W dniu 06.04.2022 o 18:54, Ron pisze:

I know about restoring backup. I just don't have one (at least with
reasonnable age), as it is experimental installation. (Didn't ever been
thinking about locating production DB on USB drive :) ).

If it is experimental you could try pg_resetwal:

https://www.postgresql.org/docs/11/app-pgresetwal.html

Read all the way through for the ifs/ands/buts.

If it is not possible to get rid ("somehow") of this "replication
error", I will do a "logic restore" -> I have similar database on my
laptop, so I'll get a pg_dumpall from this one, then recreate fresh DB
on OPi and restore from SQL.

It looks that DATA in PG DB is protected by various mechanisms very
well, but "dictionary data" (in this case replication data) is not (as
much). And in my case ext4 recovery mechanisms failed.

This makes me logical replication more appealing.

Laurent

--
Adrian Klaver
adrian.klaver@aklaver.com

W dniu 06.04.2022 o 20:09, Adrian Klaver pisze:

On 4/6/22 11:04 AM, W.P. wrote:

W dniu 06.04.2022 o 18:54, Ron pisze:

I know about restoring backup. I just don't have one (at least with
reasonnable age), as it is experimental installation. (Didn't ever
been thinking about locating production DB on USB drive :) ).

If it is experimental you could try pg_resetwal:

https://www.postgresql.org/docs/11/app-pgresetwal.html

Found this, had to use -f (program hint), but no luck. Same error.

Is there anything else?

[BTW my "logical restore" runs already 9h... still making indexes, DB is
not very large, on i386 ~50GB]

Laurent

On Wed, 2022-04-06 at 18:41 +0200, W.P. wrote:

W dniu 06.04.2022 o 12:37, Laurenz Albe pisze:

On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote:

Is it possible to repair this?

Not unless you tell us what exactly you did, command by command.

Yours,
Laurenz Albe

That was not a "command". PG was working ok, DB on encrypted (LUKS on
LVM2, ext4) USB drive.

Then simply I disconnected cable by accident.

After OPi restart, with filesystem on USB mounted again and fsck'ed
first, PG refuses to start. With a message about replication error in log.

But I did not configured relication.

I see. If you have no backup, you will need an expert to try and recover what
is possible. Even so, there are chances that enough data are lost that
nothing can be salvaged.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

If it is experimental you could try pg_resetwal:

https://www.postgresql.org/docs/11/app-pgresetwal.html

Found this, had to use -f (program hint), but no luck. Same error.

Is there anything else?

Problem stille here: during restore (from pg_dumpall on 11 on i386)
server on OPi crashed during CREATE INDEX phase. Some indexes and
triggers are missing.

SQL file has 16GB...

How can I prevent this in future (1), and (2) how to repair (maybe doing
dump/restore again?) the "slave" database?

I'd like to have a replica of my DB on laptop, especially after seeing
unrecoverable crash on OPi...

Laurent