postgresql db take backup and restore with data

Question

i would like to take backup and restore of my postgresql database automatically.

take backup automatically from postgresql with cron trigger in windows pc
later i will do restore when ever it required.

i have below bat commands to take back up of my database

F:
cd F:\softwares\postgresql-12.1-3-windows-x64-binaries\pgsql\bin
pg_dump.exe -U postgres -s fuelman > E:\fuel_man_prod_backup\prod.sql
cmd /k

but i am getting

pg_dump: error: too many command-line arguments (first is "-s")

also i need to take both schema structure with data.

Edit :- removed password -W

-s means schema only. And when only have a backup of the schema, you don't have the data. And it looks like a \ is missing in your command, but I'm not familiar with Windows — Frank Heikens
– Frank Heikens, Commented Dec 29, 2022 at 13:59

gile · Accepted Answer · 2022-12-29 14:13:17Z

1

"Too many command line" probably is due to -W option; the string "postgres" after -W is interpreted as db name, so following -s gives error. Anyway, when running pg_dump from a script you must not use -W; use .pgpass file or set PGPASSWORD environment variable (look at pg_dump: too many command line arguments for more details).
As for Frank Heikens comment, if you need to dump both object definition and data, avoid -s option. pg_dump documentation is quite clear.

answered Dec 29, 2022 at 14:13

gile

6,0341 gold badge30 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pappu_kutty Over a year ago

yeah i removed -w and now it takes dump of object definition, but i need to enter password manually, is there a ways to take password input from cmd automatically?

pappu_kutty Over a year ago

ok got it .pgpass would work..

gile Over a year ago

As already written: use .pgpass file or set PGPASSWORD environment variable

Collectives™ on Stack Overflow

postgresql db take backup and restore with data

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related