Question 113816 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – pg_restore is not working – input file does not appear to be a valid archive

I am doing pg_dump using –

pg_dump -U <username> -h <host> <database> > backup.sql

pg_dump is working fine.
I am trying to do pg_restore doing –

pg_restore -U <username> -h <host> -d <databse> backup.sql

Then it is showing pg_restore: error: input file does not appear to be a valid archive

I have checked many StackOverflow answers about this, but I could not figure out anything. Please help me. Thanks in advance.

Update : As per comments we can not use pg_restore for .sql files. Actually I have an restriction that I must have to restore the database using pg_restore command. Can you please give the pg_dump command using which I can restore that using pg_restore?

Tags:

2

Answers


  1. 0

    You created a plain format dump, which is an SQL file. You have to restore plain-format dumps with psql:

    psql -U <username> -h <host> -d <databse> -f backup.sql

    pg_restore is used to restore dumps in all other formats. You get dumps in other formats by using the appropriate -F option with pg_dump: for example, -F c produces a custom format dump.

    If you want to restore a plain format dump with a client other than psql, you have to create it with the option --inserts.

    Login or Signup to reply.
  2. 0

    In my case, I was outputting pg_dump with nohup because it was time-consuming, which confused the redirector and mixed unnecessary data in the file, causing the error (pg_restore: error: input file does not appear to be a valid archive).

    nohup pg_dump *** > output.dump > nohup.out &

    pg_dump: DEBUG: rehashing catalog cache id 41 for pg_partitioned_table; 65 tups, 32 buckets
    pg_dump: DEBUG: rehashing catalog cache id 41 for pg_partitioned_table; 129 tups, 64 buckets
    pg_dump: DEBUG: rehashing catalog cache id 41 for pg_partitioned_table; 257 tups, 128 buckets
    pg_dump: DEBUG: rehashing catalog cache id 59 for pg_statistic; 257 tups, 128 buckets
    pg_dump: DEBUG: rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search