All language subtitles for 2. Debugging restoration issues

In this video, we are going to cover some of the issues that students face while restoring a database.
1

There are two main reasons of facing issue.
2

The first reason is that the students do not create new database while restoring and try to restore
3

a database within an old database.
4

And the second reason occurs when Postgress team brings an update to the software or launches a new
5

version of the software so many times the newer version is unable to restore files created in the older
6

version or there are some other restoration issues with the newer version.
7

For example, this is one of the issues which is usually seen in the 13th version of PostgreSQL.
8

So if you see an error like this, that there is a location and at the end there is this file
9

pg_restore.exe, this file is not found and it says that we have to correct the binary path
10

in the preferences dialogue.
11

So the reason for this issue is that the pg_restore.exe file, which is required to restore
12

any database.
13

This file is actually located at some other location and the location that pgAdmin4 has for
14

that file is something else.
15

So this location written here, this is the location available with our pgAdmin software.
16

But if you go and search for the file on your system, this file will be located at some other location.
17

So we have to change the location of this file in pgAdmin so that this error does not occur.
18

So to do that, we have to first find the file on our pc
19

Let me show you where this file is on my PC.
20

So this is the postgress installation folder on my PC C program files postgreSQL and I'm using the
21

12th version.
22

And here there is a folder called bin. If I open this folder.
23

And I scroll down, you will see that here's the file pg_restore.exe
24

So this is an application file.
25

So Dot exe, this file is used to restore the database and the location of this file is C program files.
26

PostgreSQL 12 bin
27

Now where do I have to put this location in pgAdmin.
28

Let's see that.
29

So in pgAdmin.
30

You have to open this file menu, go to the preferences option.
31

Here there is an option called binary parts on the left, click on it, and here you will see this option
32

of both postgreSQL binary path.
33

So this is the path that you have to change.
34

For me, it is taking the path at runtime and it is taking the right path.
35

But for you, if it is not taking the right path, you have to paste that path here.
36

So the path is this C program files.
37

PostgreSQL\12\bin
38

Do not add pg_restore.exe after that.
39

Just copy this folder location and paste it here.
40

Save it and once you have done it try restoring again and you will not see this issue coming up.
41

Another very common issue faced during restoration is failed with exit code 1.
42

Now, again, there can be two reasons for this.
43

One is we are not using a new DB.
44

So always make sure that when you are restoring, always create a new database and to create a new database,
45

you have to.
46

Right.
47

Click on databases, go to create and click on database.
48

Name the database, and only in that new database, you have to restore the file, do not restore the
49

files given in an older database because that in most of the cases will give you this error.
50

Now, if you're sure that you're using a new database, then the next thing is you have to select the
51

tar file to restore.
52

Now, I've given you two versions of tar files.
53

The issue here is that if I created the backup tar file in 9th version of PostgreSQL you are using,
54

say, the 12th version or the 13th version, you will see an error.
55

So if the file is created in some other version and you are using it to restore database in some other
56

version, it usually gives up this error.
57

So I have given you tar files for two versions.
58

Try it out with both the tar files.
59

If it still does not work out, I have given you another file which has the extension.backup.
60

So this file also has the same data set inside it, only that the format is not tar. The format
61

is dot backup.
62

The process for selecting the backup file is also same.
63

So you have to create a new database.
64

So I have created this database with the name backup file here, right, click on it, go to restore.
65

Now, in the file name, I'll have to go and select the backup file.
66

So you can select the supermart_db.backup file and click on this select button,
67

click on Restored and it will restore the same backup data from the dot backup file.
68

Now, if that backup file option also does not work for you, so in future, if in some version of this
69

backup file also does not work out, there is another option of this dot SQL file
70

Try restoring the database using this dot SQL file.
71

Apart from this, I have given you another option of restoring using a directory, so let me show you
72

how restoration is done using a directory.
73

So if you are restoring from our directory, let's create a new database.
74

From directory.
75

Now, when I select restored here, instead of having the format as custom or tar, select directory here
76

and in the file name, so you do not have to click on any of the files, just select this folder, which
77

is titled restore directory inside this folder.
78

We have restoration files.
79

You do not have to select individual files.
80

Just select this folder and click on restore.
81

And now, if you refresh the database.
82

Go to schemas.
83

Look at the tables, you'll see that the three tables have been created, so these are all the ways
84

in which you can restore the database.
85

We have tar files, we have backup file, we have SQL file, and we have a restoration directory.
86

Try all of these out.
87

And once you restore the database, refresh it to check if the tables are created or not.
88

Sometimes this failure code is shown, but the tables are still created.
89

So always after restoring, refresh the database and check if the tables are created or not.
90

Lastly, if nothing else works, the last option, which always works is creating the tables individually
91

and loading the data from CSV files.
92

So we have provided the CSV files also.
93

And in the next video, I'll show you how to create those three individual tables and restore the CSV
94

files.