0

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
--


4
  • "I have been receiving an error saying that I am unable to create duplicates (IDs of the records were the same.)" You are trying to load data over the top of existing data. What do you want to happen when you try that? Should the existing data be dropped? – jjanes Feb 8 '20 at 16:44
  • What you show for the leading lines of the tar file is not what a real tar file looks like. What happens if you do tar -tf backup.tar on it? – jjanes Feb 8 '20 at 16:45
  • @jjanes the result of using command : tar -tf backup.tar. toc.dat. tar: Skipping to next header. tar: A lone zero block at 390. tar: Exiting with failure status due to previous errors. – D Komo Feb 8 '20 at 16:48
  • @jjanes basically I just want to update records of a table that is stored on the second server – D Komo Feb 8 '20 at 16:52
0

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.

0

@jjanes was right. Docker was making the causing the errors

Docker was causing the error. File, that I have created using Docker was corrupted. Once I have exported it straight from Docker, using shell & run command:

tar -tf backup.tar

the file has been defined as a valid one.

Moreover, @jjanes was also right about the second case. It was pointless to use pg_dump. In my case, the best solution was to use built-it functions of Django:

dumpdata
loaddata

It has worked great!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.