CODEWITHSUNDEEP
postgresqlpg-dump
ziggy
ziggy

Reputation: 1538

pg_dump windows command prompt invalid command

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

Upvotes: 12

Views: 56291

Answers (7)

ForeverLearner
ForeverLearner

Reputation: 2113

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

Upvotes: 0

Roel Leal
Roel Leal

Reputation: 679

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: path environment

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

Upvotes: 0

JTD2021
JTD2021

Reputation: 166

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.

Upvotes: 0

J. Hasan
J. Hasan

Reputation: 512

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

Upvotes: 2

Emeka
Emeka

Reputation: 61

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

Upvotes: -1

Spider
Spider

Reputation: 1470

This works for me in Windows PowerShell in Windows 10:

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

Upvotes: 10

Julia Leder
Julia Leder

Reputation: 816

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.

Upvotes: 16

Related Questions