24

I am new to Postgres database. I have to get the backup from Production Server (pgAdmin Version is 9.2.4) & restore it on my local machine (I have pgAdmin Version 9.4). I tried to get backup by right clicking on database -> Backup to get the .backup file. Like shown in below image:

enter image description here

But when I try to restore the backup file, I get many errors.

I also want to know whether having different ports at both system can also create issues while restoring backups. As When I tried to restore backup of same system had no problems.

Improve this question
2
  • 4
    I personally find using the command line tools directly much easier than the pgAdmin UI (which I find highly confusing). Btw: there is no "pgAdmin version 9.4". It's Postgres that has the version is 9.4 Commented Jan 20, 2015 at 15:03
  • 1
    What kind of errors you get? Commented Jan 20, 2015 at 17:51

3 Answers 3

Reset to default
52

To backup a database you can use pg_dump.exe:

  1. Open Powershell

  2. Go to Postgres bin folder. For example:

    cd "C:\Program Files\PostgreSQL\9.6\bin"

  3. Enter the command to dump your database. For example:

    ./pg_dump.exe -U postgres -d my_database_name -f D:\Backup\<backup-file-name>.sql

  4. Type password for your postgres user

To restore a database you can use psql.exe. (Note, the following is extracted from Alexandr Omelchenko's helpful answer which has been deleted for reasons not clear to me.)

  1. Open Powershell

  2. Go to Postgres bin folder. For example:

    cd "C:\ProgramFiles\PostgreSQL\9.6\bin"

  3. Enter the command to restore your database. For example:

    ./psql.exe -U postgres -d my_database_name -f D:\Backup\<backup-file-name>.sql

  4. Type password for your postgres user

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

1 Comment

you can also provide the password throught the PGPASSWORD variable. SET PGPASSWORD=mypassword
5

I was stuck here when creating the database dump file due to version mismatch. So I follow the below command to get the backup and restore.

pg_dump -h localhost -U postgres -p 5432 YourDbName > BackupFileName.dump
Improve this answer

Comments

0

As an alternative, you can try below cmd code. If you trouble with restoring from pgAdmin4, you can try this. First you should find the path pg_restore.exe. It is generally in

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

run the below code after changing database name and backup path

pg_restore.exe --verbose --clean -U "postgres" --dbname databaseName c:\database.backup
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.