Invalid byte sequence for encoding "UTF8": 0xe3 0xb6 0x01 trying to restore PostgreSQL database

I’m trying to restore a backup of my database, but I can’t do so without errors.


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;

Why are you using pgrestore to restore SQL dump? Is the target database empty?

Yes it is empty. I made it with createdb -T template0 mastodon_production

You seem to be having database/dump encoding issues. What encoding is your dump in? How it was generated?

(for the future: please copy text into the forum - images are pretty useless)

Dump is in binary, but the database was in UTF-8. I used command pg_dump -Fc mastodon_production > backup.sql

Sorry for that. I’m gonna look into the log if it’s there and copy it.

Please use pg_dump -E utf8 mastodon_production > backup.sql and see if the UTF-8 characters look okay in the file.

Yeah…that’s gonna be an issue. I don’t have the database, only the backup.

In that case convert it to plain SQL with pgrestore and check what kind of encoding is there… then convert.

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.