PostgreSQL: pg_restore: [archiver] input file does not appear to be a valid archive

Question

I'm working through the Postgres DVD tutorial and am running into issues importing their sample database.

Running pg_restore -U postgres -d dvdrental ~[filepath]/dvd-database.tar.gz gives me pg_restore: [archiver] input file does not appear to be a valid archive.

My process so far has been the following:

1. Download the dvdrental.zip file
2. Extract it to a .tar using tar czf dvd-database.tar.gz dvdrental.zip (I've also tried extracting the zip to a folder first with the same result, as well as dropping the .gz)
3. Running pg_restore -U postgres -d dvdrental ~[filepath]/dvd-database.tar as stated above.

I'm currently using Postgres 9.5, which may be causing the issue. Looking for an answer that points out where I'm going wrong in this process or how to update an archive of a Postgres database to 9.5.

Denysedenzil

I don't think pg_restore can handle .tar.gz. Did you try just unzipping the archive unzip dvdrental.zip and then feed the .tar file to pg_restore – Subterfuge

Was just about to edit this – I was using tar incorrectly and outputting a gzipped file when I didn't mean to. However, now that I am providing pg_restore with an uncompressed tar, I'm getting pg_restore: [tar archiver] could not find header for file "toc.dat" in tar archive although the toc.dat is present when the .tar is unpacked. – Denysedenzil

then try to unpack the tar and point pg_restore to the directory – Subterfuge

Thank you that worked. Was clearly overthinking this/need to read up on the documentation. – Denysedenzil

Answer 1

Per comments above, the solution was simple.

Extracting the dvdrental.zip file to an uncompressed .tar is not necessary as suggested in the tutorial instructions. pg_restore will work if pointed to the directory where the database dump was extracted:

pg_restore -U <username> -d dvdrental <your/path/to/extracted/dir>/dvdrental

Denysedenzil

If you are on mac having postgres.app run below and restart terminal. Then execute command on this post pointing extracted folder. sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp ref: postgresapp.com/documentation/cli-tools.html – Hangnail

Answer 2

My problem was that when installing postgresql, I chose a different name for the superuser (root for example, not postgres). And the dvdrental/restore.sql file uses the postgres role. So to solve this problem you need to create another superuser named postgres.

createuser --superuser postgres

Then create server (PosgreSQL) with role postgres and db dvdrental with role postgres. After u can succesfuly restore db from extracted folder

Schmit

Answer 3

To restore an SQL text file into a PostgreSQL database, you can use the psql command-line tool, which is commonly used for interacting with PostgreSQL databases. Here are the steps to restore the SQL text file:

1. Open Command Prompt or Terminal: Open a command prompt or terminal window on your computer.

2. Navigate to the Directory: Use the cd command to navigate to the directory where your SQL text file is located.

   cd /path/to/directory
   

3. Log into PostgreSQL: Use the psql command to log into your PostgreSQL database. You'll need to provide the username and database name as arguments. You may also need to enter your password.

   psql -U your_username -d your_database
   

   Replace your_username with your PostgreSQL username and your_database with the name of the database you want to restore the SQL file into.

4. Execute SQL File: Once you're logged in, you can execute SQL commands from your text file using the \i command followed by the file path:

   \i /path/to/your_sql_file.sql
   

   Replace /path/to/your_sql_file.sql with the full path to your SQL text file.

5. Exit psql: After the SQL file has been executed, you can exit psql by typing:

   \q
   

   This will return you to the command prompt.

That's it! Your SQL text file has been restored into your PostgreSQL database. Make sure that your SQL file contains valid SQL statements and is compatible with your PostgreSQL database version.

Pettifer

meta.https://mcmap.net/q/36206/-asp-net-site-default-document-in-subfolder/11107541 – Engeddi

Answer 4

After trying for hours which worked for me is the below command. Since PgAdmin 4 it's very hard to work with.

/Library/PostgreSQL/10/bin/pg_restore -U postgres -p 5433  -d dvdrental /pg-db/dvdrental/

Just extract the zip into a folder and execute the pg_restore command with these options in my case Mac os where /pg-db/dvdrental/ is the zip extracted directory.

Addition

Answer 5

If you are running your postgres on Docker then make sure to pass hostname and port,

pg_restore -U postgres -h localhost -p 5432 -d dvdrental ./Downloads/dvdrental

Here, ./Downloads/dvdrental is extracted folder from dvdrental.zip

Kingdom

Answer 6

Don't know the reason but this is how it worked for me.

Im using ubuntu 20.04, pgadmin4 version 5.7, postgres version 12.8.

In the "restore" dialog box, "custome and tar", "..." in filename, click upload to upload the tar file, select the uploaded file, then selected the three options: pre-data, data and post-data. Was getting error: failed with exit code 1.

Deleted the database, again created the database (with the same name), did exactly the same way as before.

This 2nd time it succeeded. In the logs it executed the following cmd:

/usr/bin/pg_restore --host "localhost" --port "5432" --username "postgres" --no-password --dbname "mydb" 
--section=pre-data --section=data --section=post-data --verbose "/var/lib/pgadmin/storage/my_email.com/restore_file.tar"

Claudianus

Answer 7

I got the same error below:

pg_restore: error: input file does not appear to be a valid archive

Because I exported but did not archive apple database to backup.sql with pg_dump as shown below:

pg_dump -U john apple > backup.sql

Or:

pg_dump -U john -Fp apple > backup.sql

Then, I tried to import non-archive backup.sql to orange database with pg_restore which must be used to import archive files:

pg_restore -U john -d orange < backup.sql

So, I did it with psql which must be used to import non-archive files, then I could solve the error. *My answer explains how to export a database and my answer explains how to import a database:

psql -U john -f backup.sql orange

Hubble

Answer 8

It will be easier to use pgAdmin 4.

While restoring the tar file onto the database through pgAdmin you will get this error thrown at you. We usually get stuck at this point and forget to check the schema.

Ignore the error. The data has already been loaded and restored.

Laborer