I need help fixing a database dump

matrix07012 · October 27, 2019

I run a Mastodon instance and I'm trying to restore my database backup after a failed update, however I'm unable to do so and have no clue how to fix it. It appear my database was broken long before and I didn't notice, so all my backups have this error.

Log:

Spoiler

2019-10-27 08:21:14.299 EDT [1584] postgres@mastodon_production ERROR: invalid byte sequence for encoding "UTF8": 0xe3 0xb6 0x01
2019-10-27 08:21:14.299 EDT [1584] postgres@mastodon_production CONTEXT: COPY statuses, line 7
2019-10-27 08:21:14.299 EDT [1584] postgres@mastodon_production STATEMENT: COPY public.statuses (id, uri, text, created_at, updated_at, in_reply_to_id, reblog_of_id, url, sensitive, visibility, spoiler_text, reply, language, conversation_id, local, account_id, application_id, in_reply_to_account_id, poll_id, tsv) FROM stdin;

2019-10-27 08:21:29.291 EDT [1590] ERROR: canceling autovacuum task
2019-10-27 08:21:29.291 EDT [1590] CONTEXT: automatic analyze of table "mastodon_production.public.favourites"
2019-10-27 08:21:38.680 EDT [455] LOG: checkpoints are occurring too frequently (24 seconds apart)
2019-10-27 08:21:38.680 EDT [455] HINT: Consider increasing the configuration parameter "max_wal_size".
2019-10-27 08:21:41.858 EDT [1594] ERROR: canceling autovacuum task
2019-10-27 08:21:41.858 EDT [1594] CONTEXT: automatic analyze of table "mastodon_production.public.conversations"
2019-10-27 08:22:53.646 EDT [1594] ERROR: canceling autovacuum task
2019-10-27 08:22:53.646 EDT [1594] CONTEXT: automatic analyze of table "mastodon_production.public.mentions"
2019-10-27 08:23:03.218 EDT [455] LOG: checkpoints are occurring too frequently (8 seconds apart)
2019-10-27 08:23:03.218 EDT [455] HINT: Consider increasing the configuration parameter "max_wal_size".
2019-10-27 08:23:07.471 EDT [455] LOG: checkpoints are occurring too frequently (4 seconds apart)
2019-10-27 08:23:07.471 EDT [455] HINT: Consider increasing the configuration parameter "max_wal_size".
2019-10-27 08:23:17.756 EDT [1618] ERROR: canceling autovacuum task
2019-10-27 08:23:17.756 EDT [1618] CONTEXT: automatic analyze of table "mastodon_production.public.statuses_tags"
2019-10-27 08:23:24.559 EDT [455] LOG: checkpoints are occurring too frequently (17 seconds apart)
2019-10-27 08:23:24.559 EDT [455] HINT: Consider increasing the configuration parameter "max_wal_size".
2019-10-27 08:23:26.599 EDT [1618] ERROR: canceling autovacuum task
2019-10-27 08:23:26.599 EDT [1618] CONTEXT: automatic analyze of table "mastodon_production.public.stream_entries"
2019-10-27 08:23:28.103 EDT [1618] ERROR: canceling autovacuum task
2019-10-27 08:23:28.103 EDT [1618] CONTEXT: automatic analyze of table "mastodon_production.public.tags"
2019-10-27 08:23:30.270 EDT [1682] ERROR: canceling autovacuum task
2019-10-27 08:23:30.270 EDT [1682] CONTEXT: automatic analyze of table "mastodon_production.public.conversations"
2019-10-27 08:23:30.664 EDT [1594] ERROR: canceling autovacuum task
2019-10-27 08:23:30.664 EDT [1594] CONTEXT: automatic analyze of table "mastodon_production.public.media_attachments"
2019-10-27 08:23:32.582 EDT [1583] postgres@mastodon_production ERROR: insert or update on table "favourites" violates foreign key constraint "fk_b0e856845e"
2019-10-27 08:23:32.582 EDT [1583] postgres@mastodon_production DETAIL: Key (status_id)=(100967933924687083) is not present in table "statuses".
2019-10-27 08:23:32.582 EDT [1583] postgres@mastodon_production STATEMENT: ALTER TABLE ONLY public.favourites
   ADD CONSTRAINT fk_b0e856845e FOREIGN KEY (status_id) REFERENCES public.statuses(id) ON DELETE CASCADE;



2019-10-27 08:23:32.585 EDT [1583] postgres@mastodon_production ERROR: insert or update on table "polls" violates foreign key constraint "fk_rails_3e0d9f1115"
2019-10-27 08:23:32.585 EDT [1583] postgres@mastodon_production DETAIL: Key (status_id)=(101908674135469982) is not present in table "statuses".
2019-10-27 08:23:32.585 EDT [1583] postgres@mastodon_production STATEMENT: ALTER TABLE ONLY public.polls
   ADD CONSTRAINT fk_rails_3e0d9f1115 FOREIGN KEY (status_id) REFERENCES public.statuses(id) ON DELETE CASCADE;



2019-10-27 08:23:32.586 EDT [1583] postgres@mastodon_production ERROR: insert or update on table "media_attachments" violates foreign key constraint "fk_rails_3ec0cfdd70"
2019-10-27 08:23:32.586 EDT [1583] postgres@mastodon_production DETAIL: Key (status_id)=(100911531061395636) is not present in table "statuses".
2019-10-27 08:23:32.586 EDT [1583] postgres@mastodon_production STATEMENT: ALTER TABLE ONLY public.media_attachments
   ADD CONSTRAINT fk_rails_3ec0cfdd70 FOREIGN KEY (status_id) REFERENCES public.statuses(id) ON DELETE SET NULL;



2019-10-27 08:23:32.587 EDT [1583] postgres@mastodon_production ERROR: insert or update on table "status_stats" violates foreign key constraint "fk_rails_4a247aac42"
2019-10-27 08:23:32.587 EDT [1583] postgres@mastodon_production DETAIL: Key (status_id)=(100912764324433780) is not present in table "statuses".
2019-10-27 08:23:32.587 EDT [1583] postgres@mastodon_production STATEMENT: ALTER TABLE ONLY public.status_stats
   ADD CONSTRAINT fk_rails_4a247aac42 FOREIGN KEY (status_id) REFERENCES public.statuses(id) ON DELETE CASCADE;



2019-10-27 08:23:32.588 EDT [1583] postgres@mastodon_production ERROR: insert or update on table "status_pins" violates foreign key constraint "fk_rails_65c05552f1"
2019-10-27 08:23:32.588 EDT [1583] postgres@mastodon_production DETAIL: Key (status_id)=(102478578309050590) is not present in table "statuses".
2019-10-27 08:23:32.588 EDT [1583] postgres@mastodon_production STATEMENT: ALTER TABLE ONLY public.status_pins
   ADD CONSTRAINT fk_rails_65c05552f1 FOREIGN KEY (status_id) REFERENCES public.statuses(id) ON DELETE CASCADE;



2019-10-27 08:23:32.591 EDT [1583] postgres@mastodon_production ERROR: insert or update on table "statuses_tags" violates foreign key constraint "fk_rails_df0fe11427"
2019-10-27 08:23:32.591 EDT [1583] postgres@mastodon_production DETAIL: Key (status_id)=(98006329962985452) is not present in table "statuses".
2019-10-27 08:23:32.591 EDT [1583] postgres@mastodon_production STATEMENT: ALTER TABLE ONLY public.statuses_tags
   ADD CONSTRAINT fk_rails_df0fe11427 FOREIGN KEY (status_id) REFERENCES public.statuses(id) ON DELETE CASCADE;



2019-10-27 08:23:32.651 EDT [1682] ERROR: canceling autovacuum task
2019-10-27 08:23:32.651 EDT [1682] CONTEXT: automatic analyze of table "mastodon_production.public.mentions"
2019-10-27 08:23:33.930 EDT [1584] postgres@mastodon_production ERROR: insert or update on table "mentions" violates foreign key constraint "fk_rails_59edbe2887"
2019-10-27 08:23:33.930 EDT [1584] postgres@mastodon_production DETAIL: Key (status_id)=(100990749820636542) is not present in table "statuses".
2019-10-27 08:23:33.930 EDT [1584] postgres@mastodon_production STATEMENT: ALTER TABLE ONLY public.mentions
   ADD CONSTRAINT fk_rails_59edbe2887 FOREIGN KEY (status_id) REFERENCES public.statuses(id) ON DELETE CASCADE;

SupaKomputa · October 27, 2019

invalid byte sequence for encoding "UTF8"

https://dba.stackexchange.com/questions/4777/how-to-solve-utf8-invalid-byte-sequence-copy-errors-on-a-restore-when-the-sourc

Consider increasing the configuration parameter "max_wal_size".

postgresql.conf -> increase to something like 5gb.

matrix07012 · October 27, 2019

1 hour ago, SupaKomputa said:

invalid byte sequence for encoding "UTF8"

https://dba.stackexchange.com/questions/4777/how-to-solve-utf8-invalid-byte-sequence-copy-errors-on-a-restore-when-the-sourc

I tried that, but my db backup is a binary dump and when I try to turn it to a plain text dump, it ends up as ASCII, so converting that breaks, at minimum, tsvector.