Error restoring Postgresql db

Question

I have a problem restoring a PostgreSQL DB which I have backup early using the following command:

pg_dump -i -h localhost -p 5432 -U Mark -F c -b -v -f "C:\Users\mchan\Desktop\MyDB\DBBackup.backup" mydb

Now When I try to restore this DB backup on (another machine) I got the following error:

pg_restore: [archiver] directory "C:\Users\mark\Desktop\MCHANBackups\DBBackup.backup" does not appear to be a valid archive ("toc.dat" does not exist)

Below is the command I used to restore the backup:

pg_restore -i -h localhost -p 5432 -U Mark -d mydb -v "C:\Users\mark\Desktop\MCHANBackups\DBBackup.backup"

Can someone please help me and tell me what I am doing wrong here?

I think you have used custom format and the backup is sent to normal text file. For restoring from text files, use psql command: psql -e -d template1 -f "C:\Users\mchan\Desktop\MyDB\DBBackup.backup" Refer to this: postgresql.org/message-id/… — r3ap3r
– r3ap3r, Commented Sep 10, 2013 at 10:29
@r3ap3r I tried the psql command but it kept asking me about the Windows user (mchan) password, when I entered the password it gave me error (psql: FATAL: password authentication failed for user "mchan")...any idea why it is checking the windows password? — MChan
– MChan, Commented Sep 10, 2013 at 11:17
@MChan Not sure why psql is asking for Windows User password. You can disable this authentication by replacing "md5" with "trust" in pg_hba.conf file under "/data" folder. For this modification to take effect , you will have to restart the "postgres service". Once this is done, you can retry the "psql" command. It should not ask for any password. — r3ap3r
– r3ap3r, Commented Sep 10, 2013 at 11:50
@MChan use psql -U username -d databaseName . Refer to this link for more documentation on "psql" command :postgresql.org/docs/9.2/static/app-psql.html . You will have to use a role that is defined in the database such as postgres or any other role that you might have created. — r3ap3r
– r3ap3r, Commented Sep 10, 2013 at 12:05

r3ap3r · Accepted Answer · 2013-09-10 12:48:21Z

2

I think you have used custom format and the backup is sent to normal text file. For restoring from text files, use psql command: psql -e -d template1 -f "C:\Users\mchan\Desktop\MyDB\DBBackup.backup" Refer to this post on Postgresql's Community page.

Use the psql command as follows:

psql -e -U username -d databaseName -f "C:\Users\mchan\Desktop\MyDB\DBBackup.backup"

Username should be a role defined in the database. Ex: postgres

If the psql command prompts for a password and you don't know the password for the user, replace "md5" with "trust" in pg_hba.conf file under "/data" folder and restart "postgres service" for this change to take effect.

Once this change is done psql will not prompt for the password.

For more documentation on psql and additional options , refer to psql-doc.

answered Sep 10, 2013 at 12:48

r3ap3r

2,8232 gold badges20 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Error restoring Postgresql db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related