Jump to content
Search In
  • More options...
Find results that contain...
Find results in...
matrix07012

I need help fixing a database dump

Recommended Posts

Posted · Original PosterOP

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;
 

 


Spoiler

Quiet Whirl | CPU: AMD Ryzen 7 3700X Cooler: Noctua NH-D15 Mobo: MSI B450 TOMAHAWK MAX RAM: HyperX Fury RGB 32GB (2x16GB) DDR4 3200 Mhz Graphics card: MSI GeForce RTX 2070 SUPER GAMING X TRIO PSU: Corsair RMx Series RM550x Case: Be quiet! Pure Base 600

 

Buffed HPHP ProBook 430 G4 | CPU: Intel Core i3-7100U RAM: 4GB DDR4 2133Mhz GPU: Intel HD 620 SSD: Some 128GB M.2 SATA

 

Retired:

Melting plastic | Lenovo IdeaPad Z580 | CPU: Intel Core i7-3630QM RAM: 8GB DDR3 GPU: nVidia GeForce GTX 640M HDD: Western Digital 1TB

The Roaring Beast | CPU: Intel Core i5 4690 (BCLK @ 104MHz = 4,05GHz) Cooler: Akasa X3 Motherboard: Gigabyte GA-Z97-D3H RAM: Kingston 16GB DDR3 (2x8GB) Graphics card: Gigabyte GTX 970 4GB (Core: +130MHz, Mem: +230MHz) SSHD: Seagate 1TB SSD: Samsung 850 Evo 500GB HHD: WD Red 4TB PSU: Fractal Design Essence 500W Case: Zalman Z11 Plus

 

Link to post
Share on other sites
Posted · Original PosterOP
1 hour ago, SupaKomputa said:

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.


Spoiler

Quiet Whirl | CPU: AMD Ryzen 7 3700X Cooler: Noctua NH-D15 Mobo: MSI B450 TOMAHAWK MAX RAM: HyperX Fury RGB 32GB (2x16GB) DDR4 3200 Mhz Graphics card: MSI GeForce RTX 2070 SUPER GAMING X TRIO PSU: Corsair RMx Series RM550x Case: Be quiet! Pure Base 600

 

Buffed HPHP ProBook 430 G4 | CPU: Intel Core i3-7100U RAM: 4GB DDR4 2133Mhz GPU: Intel HD 620 SSD: Some 128GB M.2 SATA

 

Retired:

Melting plastic | Lenovo IdeaPad Z580 | CPU: Intel Core i7-3630QM RAM: 8GB DDR3 GPU: nVidia GeForce GTX 640M HDD: Western Digital 1TB

The Roaring Beast | CPU: Intel Core i5 4690 (BCLK @ 104MHz = 4,05GHz) Cooler: Akasa X3 Motherboard: Gigabyte GA-Z97-D3H RAM: Kingston 16GB DDR3 (2x8GB) Graphics card: Gigabyte GTX 970 4GB (Core: +130MHz, Mem: +230MHz) SSHD: Seagate 1TB SSD: Samsung 850 Evo 500GB HHD: WD Red 4TB PSU: Fractal Design Essence 500W Case: Zalman Z11 Plus

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×