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

Hi there,

I had a problem with disconnecting USB disc with PG database on my OPi3.

PG is 11.

Now base refuses to start, with problem reported in log (see attached file).

But I don't have replication configured!

Is it possible to repair this?


Laurent

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
youshould. 

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


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

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



> [BTW my "logical restore" runs already 9h... still making indexes, DB 
> is not very large, on i386 ~50GB]
>
>
> Laurent
>
>
>> 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
>>>
>>>
>>>
>>
>>
>
>
>