4

So I regularly backup and restore databases and schema's using pgadmin4. I would like to do it with a batch file using commands as pg_dump and pg_restore. I however always fail to succeed in this and could use some help. The way I try to dump one schema (with data) is the following: 

pg_dump -U postgres -F c -d database -n schema > mw2

Then I try to restore it with pg_restore:

pg_restore -U postgres -d otherdatabase -n schema mw2

First I tried to use .dump in stead of tar but the result stays the same; which is an empty schema in my database.

Or the following error message: 

pg_restore: [archiver] input file does not appear to be a valid archive
Chris Stryczynski
  • 30,145
  • 48
  • 175
  • 286
Zuenie
  • 963
  • 2
  • 11
  • 30

1 Answers1

5

https://www.postgresql.org/docs/current/static/app-pgrestore.html

--format=format Specify format of the archive. It is not necessary to specify the format, since pg_restore will determine the format automatically. If specified, it can be one of the following:

custom, directory and tar

https://www.postgresql.org/docs/current/static/app-pgdump.html

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

others are custom, directory and tar

in short - you used defualt plain format, which is meant for using with psql, not pg_restore. So either specify different format with pg_dump or use your file as 

psql -f  mw2.tar
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • Trying it out now by adding -F t in the dump – Zuenie Feb 12 '18 at 12:47
  • It does execute now; but the resulting schema stays empty. Still doesnt work; ill update my code – Zuenie Feb 12 '18 at 12:51
  • Looking into psql -f mw2.tar now, but I first need to select the right database and schema there. – Zuenie Feb 12 '18 at 12:54
  • Please don't mess the POST. If you have different problem now - try finding the solution between existing posts, you there's none - ask a new question – Vao Tsun Feb 12 '18 at 12:54
  • 1
    @Zuenie yes - surely, like `psql -U postgres -d otherdatabase -f mw2`. I mentioned only `-f` key as it is different – Vao Tsun Feb 12 '18 at 12:55
  • it just adds a new schema in your existing database. I thought I had to select a schema in the database so that psql knows where to restore it. Got it now; working like a charm! – Zuenie Feb 12 '18 at 13:06