Postgresql – pg_restore: error: could not read from input file: end of file

backuppg-dumppostgresql

I do try to create a dump of a table, that is stored inside of my database.
The table is called

webapp_product

In order to create the dump, I do use following commands:

docker-compose exec db1 pg_dump -F tar -d db -U user -t webapp_product > /home/backup.tar

Then, on the other machine:

sudo mv backup.tar ../pgdata
docker-compose exec db1 sh
pg_restore -Ft -C -U user -d db /var/lib/postgresql/data/pgdata/backup.tar

Unfortunately, it always ends up with the same scenario. I do receive following error log:

pg_restore: error: could not read from input file: end of file

I have also tried the other way. After moving the backup file, I have been trying to use:

cat /var/lib/postgresql/data/pgdata/backup.sql | psql -d db -U user

After invoking that command, I have been receiving an error saying that I am unable to create duplicates (IDs of the records were the same.)
In order to go further, I have tried to turn off all of the constrains, but without any expected effects.

@Update1

The backup.tar file consists of a following lines:

-- NOTE:
--
-- File paths need to be edited. Search for $$PATH$$ and
-- replace it with the path to the directory containing
-- the extracted data files.
--
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.1 (Debian 12.1-1.pgdg100+1)
-- Dumped by pg_dump version 12.1 (Debian 12.1-1.pgdg100+1)

Can it affect the process of restoring the data?

and has a following ending:



--
-- PostgreSQL database dump complete
--

Best Answer

You really have two questions here. First you have a corrupted tar file. I'd blame that on your usage of docker. If you run tar -tf ... on the docker side before moving the file, was it corrupted there?

Second, you don't want to restore data, you want to merge it. pg_dump/pg_restore doesn't do that. You will have to figure out what you want to do (overwrite, aggregate, assign new PKs and just insert them as new rows) and then implement that. You likely want to use COPY or \copy to get the data out of the first system so the second one can see it. After that, it is really up to you what you do with it. You can implement the merging in python or Perl or SQL (after \copy into staging tables) or whatever your favorite tool is.

Related Question