Backup Restore PostgreSQL database in Windows

This document contains steps to perform backup and restore operations of aiv 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:

   

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

• aiv4: name of installed aiv in local machine.

2. Take backup using below command:

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

• postgres: stand for default username.

• aiv: stand for default database name.

• aiv: is default password.

• 6432: is port number

  

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

   

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

   

5. 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:

   

2. Login to postgreSQL using command:

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

   

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

   

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

   

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

   Command line: create database postgre1;

   

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

   

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

• command: \list

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

  

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

   

9. 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:\aiv4\backup.tar

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

• postgres: Default username of PostgreSQL

• C:/aiv4/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.

  

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

    

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..