PostgreSQL: Database backup and Recovery?

Question

What is the procedure to take database backup and recovery in PostgreSQL 9.3?

I am using SQL Dump for the same, but getting an error near pg_dump.

--Example

pg_dump testdb > backupfile

--Error occurred

ERROR:  syntax error at or near "pg_dump"  
LINE 1: pg_dump testdb > backupfile

Are you taking backup of local database or remote database ? — Satyam Koyani, Mar 25 '14 at 04:03
As your error pointing to some syntax error.Check your database name and dbuser. — Satyam Koyani, Mar 25 '14 at 04:07
pg_dump is a utility program. You should run this from a command prompt, not a psql monitor or pgAdmin III. — Patrick, Mar 25 '14 at 04:16
Y'know, the first Google hit is [How to back up a postgresql database from within psql?](http://stackoverflow.com/questions/7265704/how-to-back-up-a-postgresql-database-from-within-psql) ... — Craig Ringer, Mar 25 '14 at 04:30
@Patrick, After you said. I am trying the same by using command prompt but getting FATAL error of authentication fail. I tried with all passwords. — Sarfaraz Makandar, Mar 25 '14 at 04:45

score 1 · Accepted Answer · edited Mar 25 '14 at 07:38

1

Use below command to take Database backup locally

pg_dump -U postgres -h 127.0.0.1 -p 5432 -Fc -f FileName.backup -w DBName

If you want to take database backup remotely Than use Remote IPAddress in place of 127.0.0.1.

edited Mar 25 '14 at 07:38

max taldykin

answered Mar 25 '14 at 06:48

Ilesh Patel

1 Answers1