12

trying to using pg_dump to backup a postgres db

i connected through the command prompt and here is my command following this tutorial http://www.postgresqltutorial.com/postgresql-backup-database/

pg_dump -U postgres -W -F t lucz_2017 > X:\postgres_backup\lucz_backup.tar

it gives me an error 

Invalid command \postgres_backup. Try \? for help.

what am I doing wrong?

the db name and paths are correct

windows 7 running this from the CMD

ziggy
  • 1,488
  • 5
  • 23
  • 51

5 Answers5

14

You are running pg_dump from psql. Get out of psql and run pg_dump command from Windows Command prompt. pg_dump is its own executable, different from psql.

Julia Leder
  • 786
  • 4
  • 8
  • im not running it from psql...? i never call psql in my command. I call pg_dump – ziggy Jul 28 '17 at 14:47
  • 1
    The error message `Invalid command \postgres_backup. Try \? for help. ` is generated by psql, so you are in it somehow. Can you open a brand new cmd.exe and run the pg_dump command from there? – Julia Leder Jul 28 '17 at 14:49
  • ahh you were right I was already in psql trying to execute this command! – ziggy Jul 28 '17 at 14:56
  • 3
    C:\Program Files\PostgreSQL\9.5\bin>pg_dump -U postgres -F t lucz_2017 > X:\postgres_backup\lucz.tar – ziggy Jul 28 '17 at 14:56
10

This works for me in Windows PowerShell in Windows 10:

.\pg_dump.exe --username "yourUserName" --no-owner "yourDatabasName" >./filename.sql
Spider
  • 1,380
  • 4
  • 22
  • 42
  • 3
    Only if the path is loaded, in my case I just linked directly to it: 'C:\Program Files\PostgreSQL\9.5\bin\pg_dump.exe' – The Coder Jan 13 '20 at 02:48
1

DB Backup

For windows user, You can follow the procedure below,

Run cmd as Admimistrator

Go to pg bin directory using

cd C:\Program Files\PostgreSQL\<VERSION_NUMBER>\bin

Type any of the following 3 commands, all of these commands will give same result.

1. .\pg_dump.exe -Fc -U Username DB_Name > backup_dump.dump
2. .\pg_dump.exe --format=c -U Username -p 5432 DB_Name  > backup_dump.dump
3. .\pg_dump.exe -Fc "host=localhost port=5432  dbname=dbname user=postgres password=***" > mydb_export.backup

DB Restore:

pg_restore -v -U postgres -d backup < backup_dump.dump
four-eyes
  • 10,740
  • 29
  • 111
  • 220
J. Hasan
  • 492
  • 3
  • 14
0

To backup my "DVD_RENTAL_DB" database to a local folder on my computer I had to use the below in the Windows command prompt while running it as an administrator:

Don't use shell redirection (>) on Windows with pg_dump. The shell will helpfully "correct" encoding issues and corrupt your dump. Instead, specify the output filename with the -f option (enter your respective information):

"C:\Program Files\PostgreSQL\14\bin\pg_dump" -U postgres -p 5432 -W -F p -h localhost -f C:\Postgres_DB_Backups\DVD_RENTAL_DB.sql DVD_RENTAL_DB

This worked for me ONLY after I put double quotes around the pg_dump executable file path, before when I was adding the file path without double quotes the back up was not working; probably due to spaces in my file path. The PostgreSQL documentation didn't mention anything about double quotes around the pg_dump executable file path.

To Restore my Database I used the following in the Windows command prompt while running it as an administrator:

  1. Open the Windows Command Prompt as an Administrator and you should be in this directory:
C:\Windows\System32>
  1. Then type the following:
cd C:\Program Files\PostgreSQL\14\bin\
  1. Then you'll be here in this directory:
C:\Program Files\PostgreSQL\14\bin>
  1. Type the following (enter your respective information):
psql -U postgres -d DVD_RENTAL_DB -f C:\Postgres_DB_Backups\DVD_RENTAL_DB.sql
  1. You'll be prompted for your password, then your database will be restored.
JTD2021
  • 127
  • 2
  • 12
  • One person upvoted this solution and one downvoted it, I'm not sure why one person down voted this answer, it does work for me, but different environments can pose different challenges so if it doesn't work for you then dig deeper into why instead of down voting an answer that is working for some people. – JTD2021 Sep 01 '23 at 20:49
-1

Steps to using pg_dump on windows

  1. Access cmd as Admin and type
  2. cd path_to_pg_dump PRESS ENTER
  3. pg_dump --username your_user_name --table=table_name --data-only --column-inserts your_database > my_table_data.sql
    PRESS ENTER
Emeka
  • 51
  • 1
  • 3