postgres write errors after Mac crash from failed drive

Posted on
Sun Apr 08, 2018 8:03 am
siclark offline
Posts: 373
Joined: Jun 13, 2017
Location: UK

postgres write errors after Mac crash from failed drive

If anyone is still using sql lite I cannot (again) recommend enough migrating to postgres with Karl's excellent utility, or just starting again.

I have had issues in the past recovering from crashes with sql lite but postgres has been very forgiving of hard power off in the middle of use if the mac just siezes up.
However a days ago the drive failed and I had to do a full timemachine recovery of the drive. All was good except 3 device tables where I was getting a read error.

SQL Logger Error exception trying to insert row into table device_history_612976019 ("Kitchen UnifiAP") -- unable to modify table
SQL Logger Error ERROR: index "device_history_612976019_pkey" contains unexpected zero page at block 8596

SQL Logger Error INSERT INTO device_history_612976019 ("mac", "channel_2", "channel_5", "created", "displaystatus", "essid_2", "essid_5", "groupmember", "hostname", "ipnumber", "laststatuschange", "model", "nstations_2", "nstations_5", "radio_2", "radio_5", "status", "tx_power_2", "tx_power_5", "updownsetting", "upsince", "vendor") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
SQL Logger Error exception trying to insert row into table device_history_1588585220 ("Landing UnifiAP") -- unable to modify table
SQL Logger Error ERROR: index "device_history_1588585220_pkey" contains unexpected zero page at block 7285

I use pgAdmin4 and was getting the same error there when trying to read the whole table, however I realised that I could export it all, which I did, and couldnt see anything wrong in Excel.
I then did a table export, truncated the table to delete all rows, leaving column setup, and then imported the file I had exported. I guess it refreshed some indicies or something? but those tables then started working properly.

Best guess is that I would have lost the tables in sqllite, if not the whole database.

ps I did also have to delete the postmaster.pid file as postgres refused to start initially on the new machine, but after that it was all good.

Posted on
Sun Apr 08, 2018 8:46 am
kw123 offline
User avatar
Posts: 5599
Joined: May 12, 2013
Location: Dallas, TX

Re: postgres write errors after Mac crash from failed drive

interesting, we should keep this available on the announcements..: how to fix postgres errors..

What I do: create an dump every night. if needed that can always be restored.. time machine does not do good backups of large databases.. the copy process takes too long and then files are out of sync. A dump creates a consistent importable setup .Then do a time machine backup of the dump ( dump -->zipped ) file)

Karl

Posted on
Sun Apr 08, 2018 12:27 pm
siclark offline
Posts: 373
Joined: Jun 13, 2017
Location: UK

Re: postgres write errors after Mac crash from failed drive

Thanks... only just seen the postgres dump option in your utilities. Thats a great idea.

Trying to use it, but I get the following.

utilities /Library/PostgreSQL/10/bin/pg_dump indigo_history postgres > /Users/xxxx/Documents/indigoUtilities/postgresBackup.dump &
utilities postgres backup dump finished after 2 seconds

and the dump file created is zero bytes. The file location of pg_dump is correct, is the indigo_history postgres the db name and user name?

Posted on
Sun Apr 08, 2018 3:19 pm
kw123 offline
User avatar
Posts: 5599
Joined: May 12, 2013
Location: Dallas, TX

Re: postgres write errors after Mac crash from failed drive

my command string is:
Code: Select all
'/Applications/Postgres.app/Contents/Versions/9.6/bin/pg_dump'  indigo_history -U postgres > /Users/myuserid/indigo/indigoUtilities/postgresBackup.dump &


please try your command string in a terminal to check what works.

Karl

ps
you can simply make this an external script in a scheduled action.

and you some how use /document/ path for the output file... with the new osx version those directories are used to sync devices. suggest you switch to /indigo/

that directory was switched in some time ago in previous versions... please checkout the latest version

Posted on
Sun Apr 08, 2018 3:29 pm
kw123 offline
User avatar
Posts: 5599
Joined: May 12, 2013
Location: Dallas, TX

Re: postgres write errors after Mac crash from failed drive

You might have the -U
Missing


Sent from my iPhone using Tapatalk

ps and indigoutility uses the command string you give in config (with .../psql ) and replaces psql with pg_dump

Posted on
Mon Apr 09, 2018 2:23 pm
siclark offline
Posts: 373
Joined: Jun 13, 2017
Location: UK

Re: postgres write errors after Mac crash from failed drive

Cheers.. yes the missing -U solved it. So it created a ~500MB zip file which unzipped to 6.5GB. I am guessing thats everything I need?

So do I ensure that Time Machine is backing that up, and exclude this folder from Timemachine
/Users/xxxxx/Library/Application Support/Postgres/var-10

Yes, I was on an old version, so have updated and its now going to /indigo/

Posted on
Mon Apr 09, 2018 4:21 pm
kw123 offline
User avatar
Posts: 5599
Joined: May 12, 2013
Location: Dallas, TX

Re: postgres write errors after Mac crash from failed drive

You should include ~/indigo/indigoutilities in time machine backup.


Sent from my iPhone using Tapatalk

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 1 guest