Backup Restore PostgreSQL database
Backup File:
-
Open command prompt and go to location of bin folder in pgsql. As shown in figure below:
-
location: C:\aiv_postgreNOV2\pgsql\bin
-
aiv_postgreNOV2: name of installed aiv in local machine.
- Take backup using below command:
pg_dump -U postgres -W -F t aiv > C:/AIV/backup.tar
-
postgres: stand for default username.
-
aiv: stand for default database name.
-
AIVHUB: stand for default password.
-
Insert postgreSQL password after adding the command, as shown in figure below:
-
As per 2nd point, the default password of PostgreSQL is “AIVHUB”. Add password and hit enter button. it will look as figure below:
-
Check the backup file at the specified location.
Restore backup file:
-
open file location of postgres in command prompt, as shown in figure below:
-
Login to postgreSQL using command:
psql –d [database name] –U [username]
-
Insert Password, hit enter button. it will login into postgres as shown in figure below:
Note: Default password: AIVHUB
-
The password may not be visible while typing in command prompt. as shown in figure below:
-
After login, add command to create database as follows: Command line: create database postgre1;
-
The command prompt will show the CREATE DATABASE alert as shown in figure below:
-
To see the available list of databases in AIV, user needs to insert command as follows:
-
command: \list
-
Hit enter after adding command.
-
Now press “ctrl + z” to logout from postgres.
-
Now, add command to Restore the dump using command as follows: Command:
psql -h localhost -p 6432 -U [Postgres username] -f "[C:\path location\filename.tar]" [database name]Example:psql -h localhost -p 6432 -U postgres -f "C:\backup\postbackup.tar" postgre1
-
6432: Port number, the default number is 6432, it’s different from the below figure.
-
postgres: Default username of PostgreSQL
-
C:\backup\postbackup.tar: Example location of the dump file.
-
postgre1: Database name which is created by user.
