pg_dump windows command prompt invalid command

Question

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

Shearin

Which OS are you on and from where are you running this command? – Th

Try putting your directory location in single quotes. – Pond

Answer 1

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.

Th

im not running it from psql...? i never call psql in my command. I call pg_dump – Shearin

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? – Th

ahh you were right I was already in psql trying to execute this command! – Shearin

C:\Program Files\PostgreSQL\9.5\bin>pg_dump -U postgres -F t lucz_2017 > X:\postgres_backup\lucz.tar – Shearin

Answer 2

This works for me in Windows PowerShell in Windows 10:

.\pg_dump.exe --username "yourUserName" --no-owner "yourDatabasName" >./filename.sql

Fm

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' – Cogswell

Answer 3

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

Frisbee

Answer 4

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>

2. Then type the following:

cd C:\Program Files\PostgreSQL\14\bin\

3. Then you'll be here in this directory:

C:\Program Files\PostgreSQL\14\bin>

4. Type the following (enter your respective information):

psql -U postgres -d DVD_RENTAL_DB -f C:\Postgres_DB_Backups\DVD_RENTAL_DB.sql 

5. You'll be prompted for your password, then your database will be restored.

Accent

Answer 5

If you want use pg_dump in cmd you can put the path in environment variables:

C:\Program Files\PostgreSQL\16\bin OR C:\Program Files\PostgreSQL\15\bin

Like this:

Close and open CMD and put pg_dump

Now it's a command usable in CMD :D

Oh, if you don't know where are the environment variables just search pressing windows button and searching environment variables

Foreordain

Answer 6

We can navigate to the path where postgres has been installed using Command Prompt and execute the below command -

C:\Program Files\PostgreSQL\14\bin>pg_dump -U postgres -d lucz_2017 -n my_schema -f ~/Downloads/postgres_backup.sql
Password:

If you wish to reduce the backup generation time, use the -n option to specify the schema. This will reduce the backup size as well.

PS - I tried to run this using git bash, but it never prompts for Password

Clover

Answer 7

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

Umbilication