Error when using pg_restore to restore database in docker compose

Question

I ran the following commands to create the dump file

sudo docker-compose up -d db
sudo docker exec –i container_name pg_dump –username username database_name > desired_path/dump.sql

And then I added the file to an existing volume in the docker-compose.override.yml file

version: '3.7'  
services:  
  db:  
    container_name: seqdb-db-container  
    volumes:  
      - ./dump.sql:path_to_volume_used_by_container  
    ports:  
      - 5432:5432  

Finally I run

sudo docker exec –i container_name psql –username username database_name < path_to_dump_file

and I get the following error:

pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

I want to use docker exec pg_restore without installing psql. Is there a solution to this or did I mount my volume incorrectly?

Answer 1

From the pgdump docs:

-F format
--format=format
Selects the format of the output. format can be one of the following:

p
plain
Output a plain-text SQL script file (the default).

So the default output format for pg_dump is a plain-text SQL file (which it appears you expect because you call the output dump.sql)

Now from the docs for pg_restore

pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats.

So the error, input file appears to be a text format dump. Please use psql, should be expected based on your actions. pg_restore does not support restoring plain-text dumps - to restore those you should use psql (as per the error).

The quick solution is to request a non-plain-text format when using pg_dump (e.g. --format=custom). However I am a little confused by your reluctance to use psql?