Solved "pg_restore: error: input file appears to be a text format dump"

Also Read: How to Install Mojo on Ubuntu 20.04 LTS (Focal Fossa)

Now coming back to the error again, usually when you try to restore database using pg_restore utility from the dump you have taken using pg_dump utility then you might see "pg_restore: error: input file appears to be a text format dump. Please use psql" as it happened with me as you can see below.

postgres@ubuntu:~$ pg_restore -d cyberithub_db cyberithub.dump
pg_restore: error: input file appears to be a text format dump. Please use psql.

While above error could occur due to different reasons but most of the time it is because you have not specified the correct format while trying to take the dump of your database using pg_dump utility. For example, in my case this error happened because I forgot to use -F c with pg_dump command while trying to take the dump of my cyberithub_db database as you can see below.

postgres@ubuntu:~$ pg_dump cyberithub_db > cyberithub.dump

If you are also facing above error then to fix it you can think of using below two working solutions depending on your use case scenario.

Solution 1: Use psql to restore

When you do not use the correct format while taking the dump of your database using pg_dump utility then by default it takes the dump in plain text SQL file irrespective of the format you are using in the dump file name. So when you try to restore or rebuild your database from that dump, it would think it is a plain text SQL file and hence it will ask to restore using psql utility as pg_restore won't able to detect the magic number to restore. So basically to solve this problem, you need to restore simply by running psql -f cyberithub.dump command as shown below.

postgres@ubuntu:~$ psql -f cyberithub.dump
SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET

You can also specify the username by using -U <user_name> with psql command as shown below.

postgres@ubuntu:~$ psql -U postgres -f cyberithub.dump
SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET

Solution 2: Use pg_dump with correct format

Another solution that you can follow to fix this problem is that you can retake the dump by specifying correct option with pg_dump utility. For example if you need to take the dump in .dump format then you can use -F c as shown below.

postgres@ubuntu:~$ pg_dump -F c cyberithub_db > cyberithub_db.dump

Similarly, if you would like to take dump in .tar format then you need to use -F t as shown below.

postgres@ubuntu:~$ pg_dump -F t cyberithub_db > cyberithub_db.tar

Now if you try to restore or rebuild the database from the dump you took into an existing database called cyberithub_db then it should work fine as expected. For example, in my case when I tried to restore from cyberithub_db.dump into cyberithub_db database using pg_restore utility then it worked fine as expected.

postgres@ubuntu:~$ pg_restore -d cyberithub_db cyberithub_db.dump

If in case, you don't have a database to restore or would like to recreate the database from dump then you need to use -C option as well with above pg_restore command. So now the command will look like below.

postgres@ubuntu:~$ pg_restore -C -d cyberithub_db cyberithub_db.dump

Similarly, if you try to restore the database from .tar dump into cyberithub_db database then you need to run like below. It should work fine without showing any error.

postgres@ubuntu:~$ pg_restore -d cyberithub_db cyberithub_db.tar

To recreate database from dump, just like as shown above, here also you need to add -C option with pg_restore command as shown below.

postgres@ubuntu:~$ pg_restore -C -d cyberithub_db cyberithub_db.tar

Hope above solution would be enough to solve your "pg_restore: error: input file appears to be a text format dump. Please use psql" as well. Please let me know for any feedback or suggestions in the comment box.

Cyberithub