Author Topic: Postresql backup  (Read 378 times)

Gabriele

  • Newbie
  • *
  • Thank You
  • -Given: 2
  • -Receive: 2
  • Posts: 45
    • View Profile
Postresql backup
« on: February 07, 2018, 09:29:23 am »
Hello,

I'm trying to run the "pg_dumpall" command from the postgresql server package, but while "pg_dump" command works fine, when I run "pg_dumpall" I get the following error:

  [C:\Programs\pgsql\bin]rem pg_dump -Fc -b biblioteca | gzip -9 > dumpsql_biblioteca.tar.gz
  [C:\Programs\pgsql\bin]pg_dumpall   | gzip -9  1> dumpall.gz
  C:/usr/bin/sh.exe: 1: cannot create nul:: Directory nonexistent
  fgets failure: No such file or directory
  The program "pg_dump" is needed by pg_dumpall but was not found in the same directory as "C:/Programs/pgsql/bin/pg_dumpall".
  Check your installation.


Obviously there is something wrong with the line

"The program "pg_dump" is needed by pg_dumpall but was not found in the same directory as "C:/Programs/pgsql/bin/pg_dumpall". ":
the directory should be "C:/Programs/pgsql/bin", not "C:/Programs/pgsql/bin/pg_dumpall"... I'm sure it is something trivial related to sh, but I don't understant what it is... the environment for the command is:

  set beginlibpath=c:\programs\pgsql\bin;c:\programs\pgsql\lib;
  SET USE_HOSTS_FIRSTS=Y
  set LANG=it_IT
  set PGDATA=D:\var\postgres9\data

Thanks for any hint!



Greg Pringle

  • Jr. Member
  • **
  • Thank You
  • -Given: 3
  • -Receive: 2
  • Posts: 96
    • View Profile
Re: Postresql backup
« Reply #1 on: February 07, 2018, 05:40:56 pm »
If the end result is to save the data then there is another solution.

for each table needed do this from the command line:
psql -c "copy eforward.ccbox to '/out/ccbox'" user pwd;

replace user with the database owner and pwd with the password.

fix the path as needed.

To restore do this. Recreate an empty database using the method used to create the original.
Then

psql -c "copy eforward.ccbox from '/out/ccbox'" user pwd;

This is fast and reliable.

Gabriele

  • Newbie
  • *
  • Thank You
  • -Given: 2
  • -Receive: 2
  • Posts: 45
    • View Profile
Re: Postresql backup
« Reply #2 on: February 14, 2018, 09:21:37 am »
Thank you Greg, I'll give psql a try. What puzzles me is that for a long time pg_dumpall worked flawlessly. Shame on me that upgraded something without realizing that I was breaking it :-)

Greg Pringle

  • Jr. Member
  • **
  • Thank You
  • -Given: 3
  • -Receive: 2
  • Posts: 96
    • View Profile
Re: Postresql backup
« Reply #3 on: February 15, 2018, 05:24:06 pm »
Also you might check the logs for errors. You might not like what you see.
There are some known issues. If I get time I will be looking at correcting a few.

The method of back up / restore I mention has the benefit of correctly restoring some characters that can be broken by the other method.
« Last Edit: February 15, 2018, 05:25:40 pm by Greg Pringle »