pg_restore: error: unrecognized data block type (0) while searching archive while trying to import postgres database to heroku

Question

I was trying to import a local database to Heroku, I got inspired from this StackOverflow question Push database to Heroku: how to use Heroku pg:push. The command I was running was

PGUSER=postgres PGPASSWORD=mypassword heroku pg:push mydatabse_name DATABASE_URL -a myapp

I got this error pg_restore: error: unrecognized data block type (0) while searching archive:

pg_dump: last built-in OID is 16383   
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
...
pg_dump: dumping contents of table "public.user"
pg_restore: creating TABLE "public.alembic_version"
...
pg_restore: processing data for table "public.alembic_version"
pg_restore: error: unrecognized data block type (0) while searching archive
 !    pg_restore errored with 1

I wonder how could I fix this issue?

Answer 1

I have encountered this issue by running this command

pg_dump -f database_output_name --no-owner --no-acl -U user_name name_of_your_local_database

• database_output_name name of the output file you can rename it whatever backup, database ...etc
• user_name: the postgres sql user_name mainly postgres
• name_of_your_local_database: is the name of your database: mydb or whatever the name you gave if you forgot it you can check in PgAdmin
• pg_dump I have export it to global path that is why I am using it like this, other ways you can call it with the absolute path ie: C:\"Program Files"\PostgreSQL\14\bin\pg_dump

the grenerated ourput file shoule look something like this

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.1
-- Dumped by pg_dump version 14.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: alembic_version; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.alembic_version (
    version_num character varying(32) NOT NULL
);

...

Now run this command to create the database on heroku

heroku pg:psql --app heroku_app_name < database_output_name

If you want to reset your database you can run this command

heroku pg:reset -a heroku_app_name

Now you can check the database on heroku by clicking on postgres link

you should see something like this, if the number of rows is 0 you probably have empty database.

Connet to heroku database via PgAdmin

• Open your PgAdmin right click on Servers and create new server let us name it Heroku

• Now click on Connection tab and fill up those information, you can find them in the hroku app under settings scrool to Config Vars reveal vars=> DATABASE_URL

the format of database url is like so: postgres://user:password@host:port/database ex: postgres://unancmnpxrgkmp:ababd6bcfed84a29549c32278204762d41b04be284a8b81750cbb6d356f8bcc7@ec2-54-209-221-231.compute-1.amazonaws.com:5432/d22q7soeu9pv6u So fill up the information like so

• Host name/address: host ie: ec2-54-209-221-231.compute-1.amazonaws.com
• Maintainace database: database ie: d22q7soeu9pv6u
• username: username ie: unancmnpxrgkmp
• password => password ie: ababd6bcfed84a29549c32278204762d41b04be284a8b81750cbb6d356f8bcc7

Click on save now you should see something like this once you open your server. You should scroll down until you find the name of your database d22q7soeu9pv6u above, you can open it and check the tables, if you do not find it refrech, make sure you create everything correctly

Yeeeees, finally ElhamdulilAllah :D