Skip to main content

Backup/Restore using PostgreSQL database in Windows

This document contains steps to perform backup and restore operations of OPNBI Database with PostgreSQL database in Windows platform.

There are 2 ways we can perform this,

  1. Using command prompt

  2. PgAdmin application

1. Using Command prompt​

Backup​

List of commands used to perform backup and restore operation are marked below,

  1. Open command prompt and go to location of bin folder in pgsql. As shown in figure below:

    Docusaurus Slash Introduction

  • location: (your_directory)\OPNBI4\pgsql\bin

  • OPNBI4: name of installed OPNBI in local machine.

  1. Take backup using below command:

    pg_dump -U postgres -p 6432 -W -F t opnbi > C:/OPNBI4/backup.tar

  • postgres: stand for default username.

  • opnbi: stand for default database name.

  • OPNBI: is default password.

  • 6432: is port number

    Docusaurus Slash Introduction

  1. Enter postgreSQL password after adding the command, as shown in figure below:

    Docusaurus Slash Introduction

  2. As per 2nd point, the default password of PostgreSQL is OPNBI. Add password and hit enter button. it will look as figure below:

    Docusaurus Slash Introduction

  3. Check the backup file at the specified location.

Restore backup file​

  1. Open command prompt and go to location of bin folder in pgsql. As shown in figure below:

    Docusaurus Slash Introduction

  2. Login to postgreSQL using command:

    psql –d [database name] –U [username] -p 6432

    Docusaurus Slash Introduction

  3. Insert Password, hit enter button. it will login into postgres as shown in figure below:

    Docusaurus Slash Introduction

    info

    Note: Default password: OPNBI. The password may not be visible while typing in command prompt.

    Here 6432 is database port number. if your database port number is different you may change it before executing this command

  4. If credentials are valid you will see screen as shown in figure below:

    Docusaurus Slash Introduction

  5. After login, add command to create database as follows:

    Command line: create database postgre1;

    info

    This step is added here to restore backup data in a new database. if you want to backup data in existing database then skip this step.

    Docusaurus Slash Introduction

  6. The command prompt will show the CREATE DATABASE alert as shown in figure below:

    Docusaurus Slash Introduction

  7. To see the available list of databases in OPNBI, user needs to insert command as follows:

  • command: \list

  • Hit enter after adding command. you can see opnbi1 database in the list.

    Docusaurus Slash Introduction

  1. Now press ctrl + z and hit enter to logout from postgres.

    Docusaurus Slash Introduction

  2. Now, add command to Restore the dump using command as follows:

  • Command: pg_restore -h localhost -p 6432 -U user_name --dbname=backup_db_name --verbose (your_directory)\backup_file_name.tar

  • Example: pg_restore -h localhost -p 6432 -U postgres --dbname=postgre1 --verbose C:\OPNBI4\backup.tar

  • 6432: Port number, the default number is 6432, it’s different from the below figure.

  • postgres: Default username of PostgreSQL

  • C:/OPNBI4/backup.tar: backup file location location of the dump file.

  • postgre1: Database name which is created by user. or existing database name where you want to restore.

    Docusaurus Slash Introduction

  1. By running above command database will be restore and you can see scrpt running in cmd.

    Docusaurus Slash Introduction

    info

    If you find error with this command, check file path or user name or port number or uername-password once and try to run again. Make sure database in which you are restoring is available.

2. pgAdmin application​

Other way of performing backup and restore in PostgreSQL is by pgAdmin utility. To know more about how to perform backup and restore please go through official documentation of postgres.follow this link..