postgres write errors after Mac crash from failed drive

Posted on
Sun Apr 08, 2018 8:03 am
siclark offline
Posts: 1960
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: 8333
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: 1960
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: 8333
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: 8333
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: 1960
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: 8333
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

Posted on
Wed Sep 26, 2018 12:59 pm
hamw offline
Posts: 1212
Joined: Mar 31, 2008

Re: postgres write errors after Mac crash from failed drive

Following a power failure and reboot I got an error saying that I have an old "postmaster.pid file" that needs to be removed before I can start Postgres.

I tried to find the postmaster.pid file and afaik it is not on my computer? Any ideas?

Posted on
Wed Sep 26, 2018 2:25 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: postgres write errors after Mac crash from failed drive

in your user directory /Library/Application Support/Postgres/var-10
replace var-10 with your version number
Code: Select all
ls './Library/Application Support/Postgres/var-10'
PG_VERSION      pg_hba.conf      pg_replslot      pg_subtrans      postgresql.auto.conf
base         pg_ident.conf      pg_serial      pg_tblspc      postgresql.conf
global         pg_logical      pg_snapshots      pg_twophase      postgresql.log
pg_commit_ts      pg_multixact      pg_stat         pg_wal         postmaster.opts
pg_dynshmem      pg_notify      pg_stat_tmp      pg_xact         >>>postmaster.pid<<<

or :
Code: Select all
 find ~ -name postmaster.*
/Users/karlwachs/Library/Application Support/Postgres/var-10/postmaster.opts
/Users/karlwachs/Library/Application Support/Postgres/var-10/postmaster.pid

Karl

Posted on
Fri Sep 28, 2018 3:21 pm
hamw offline
Posts: 1212
Joined: Mar 31, 2008

Re: postgres write errors after Mac crash from failed drive

The user/library/application support is a hidden file on my system. I used command-shift-period to find it, and then found the two files and deleted them. It appears to be starting normally now.

The problem may have started after the crash and then Postgres suggested I update to a new version.

Thanks very much!

Posted on
Fri Sep 28, 2018 5:07 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: postgres write errors after Mac crash from failed drive

when you update make sure that the time zone is correct.

mine switched to canada east cost when I installed v10

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 1 guest