setup of postgresql, pgadmin and importing data• the two most common clients that you will come...

1

SetupofPostgreSQL,pgAdminandimportingdata

CS3200 Databasedesign(sp18 s2)https://course.ccs.neu.edu/cs3200sp18s2/Version2/9/2018

2

Overview

Thisdocumentcovers2issues:

1)HowtoinstallPostgreSQL:• PostgreSQLisapopularopensourcedatabaseserver.UnlikeSQLite,PostgreSQLisamuch

morefeaturerichdatabasemanagementsystem.WithPostgreSQL,youhave2components,theserverandtheclient.Thisisn'tverydifferentfromyourwebserver-browsermodelwherethebrowserisyourclient:ThewebserverservicesrequestsforfetchingwebpageswhereasadatabaseserverservicesSQLqueriesonadatabase.

• ThisdocumentwillguideyouthroughtheprocessofsettingupPostgreSQLonyourmachine.WhatthismeansisyouwillhavealocallyrunninginstanceofthePostgreSQLserveronyourmachine.

3

Overview• Justasawebbrowserhelpsmakerequeststoawebserveranddisplaystheresultsofthe

requestviz.awebpage,similarlyadatabaseclienthelpsyoufirequeriesatadatabaseserver(PostgreSQLinourcase),anddisplaystheresultsthatthedatabaseserversendsoverfromprocessingthosequeries.

• ThetwomostcommonclientsthatyouwillcomeacrosswhenusingPostgreSQLare"psql"whichisacommand-lineclientand"pgAdmin"whichisagraphicalclient.

2)HowtoimporttheIMDBdata:• DownloadthebigZIPfilefromouronlinedirectory.Itcontainsalargecollectionofdata

fromtheIMDBmoviewebsite.Thiswillcreateafoldernamedimdb2015,containing6.txtfiles.Youwillusethemtocreateadatabase.

4

1. Setup PostgreSQL (for MAC)

5

PostgreSQL

Goto:http://postgresapp.com/

• Gotohttp://postgresapp.com/.• Downloadthelatestreleasedversion(nottheprerelease!).

1)ClickHere toDownload

6

2)Extractthefileyoujustdownloaded.Typicallythedownloadedfileshouldbeinyourdownloadedfolder.

3)Thepreviousstepwillextractthe“Postgres”application,typicallyinthesamefolder.Lookforafilewithablueelephanticon.

4)DoubleclickonthisfileandPostgreSQLservershouldbeupandrunning.

7

5)WhilethePostgreSQLserverisupandrunning,youwillseeaniconshowupinyourmenubaratthetop.

6)Clickonthe“Slonik”inthemenubar.

7)Inthemenuthatshowsup,select“OpenPostgres”.

Iflateron,somethingisnotworking,verifythattheserverisrunning

8

9)Afteryou“OpenPostgres”,thiswindowprovidesyouwithallyourcreateddatabases.

10)Double-clickonthedatabaseyouwanttoworkwith.

9

11)Afterclickingonthedatabaseyouwanttoworkwith,thePostgrescommandpromptshouldopen,thatshouldlooklike<your_user_name>=#

12)Thisisthepsql clientthatwasmentionedearlierandwhereyouwillenterSQLcommandstointeractwiththedatabaseserver.

13)Tostoptheserver,simplyquittheapplicationbyclickingontheelephanticoninyourmenubarandselectingQuitfromthemenubarthatshowsupunderneaththeicon.

10

CommandlineCheatsheet

\l listexistingdatabases\c connecttoadatabase\d listtablesindatabase\q disconnectfrompsql

\d<tablename> viewdetailsofatablecreatedatabase<dbname> createDB

Alternatively,startfromterminalwithcommand"psql"

11

2. Setup PostgreSQL (for Windows)

12

• Goto http://www.postgresql.org/download/windows/.

1)Clickon“DownloadtheInstaller”

• Double click on the downloaded file. A window will show up that will guide you through the installation.

13

2)Doubleclickonthedownloadedfile.

14

3)AwindowwillshowupthatwillguideyouthroughthesetupofPostgreSQL.

4)Click“Next”tocontinuethesetup.

15

5)TheinstallerwizardwillaskyoutospecifyadirectorywherePostgreSQLshouldbeinstalled.Itisrightifyoustickwiththedefaultoption.Sojustclicknext.

6)Click“Next”tocontinuethesetup.

16

7)Theinstallerwizardwillthenaskyoutospecifyadatadirectory.Again,itisalrighttostickwiththedefaultoption.

8)Click“Next”tocontinuethesetup.

17

10)Click“Next”tocontinuethesetup.

9)Youwillnextbepromptedtoenterapasswordforthesuperuser “postgres”.EnterthepasswordandMAKEANOTEOFITasitwillberequiredeverytimeyouwanttoworkwithpostgres.

18

11)Youwillnextbeaskedtoenteraportnumberfortheservertolistenon.Thedefaultoptionshouldbe5432.Itisalrighttokeepitasitis.

12)Click“Next”tocontinuethesetup.

19

13)Next,leavethelocaleselectionat“Defaultlocale”

14)Click“Next”tocontinuethesetup.

20

15)Next,itwillaskyouifyouwanttoinstallStackBuilder.Youcanchecktheboxtoinstallit,howeveryoudon’tneedStackBuilderrightnowandpossiblynotforthedurationofthiscourse.So,youuncheckthebox.

14)Click“Finish”tocompletethesetup.

21

• Toopenpsql now(whichistheclientwewillusetocreateourdatabaseandinteractwiththedatabaseserver)gotothecommandpromptanddothefollowing:

17)Typethiscommand,asitis.

18)ItwillthenaskforthepasswordyousetduringthePostgreSQLsetup.Note:thatthepasswordyouenterwillnotbevisibletoyou,sojustkeeptypingitCORRECTLY!

19)Onexecutingthepreviouscommand,thepromptshouldchangeandshouldnowlooklike“postgres=#”

22

• Youarenowinthepsql programwhereyoucanenterqueriestointeractwiththePostgreSQLserver.

• Entering“\q”atthispromptshouldexitthepsql program.Example:postgres=#\q

23

3. Setup PostgreSQL (for Linux)

24

Step1:InstallPostgreSQL

HerearetheinstallationstepsonUbuntu(thisinstallationwillalsoworkonanyDebian-baseddistribution):1. Openaterminalwindow.2. Issuethecommandsudo apt-getsintall postgresql.3. Typethesudo passwordnecessarytogiveyouadminrightsandhitEnter.4. Allowapttopickupanynecessarydependencies.

Step2:Changethedefaultuserpassword

Ifyoudon’tfollowthisstep,youwillnotbeabletoadddatabasesandadministerPostgreSQL,andthedatabasewillnotbesecure.

25

• Here’showtochangethepasswordforthedefaultuser.Theuserinquestionispostgres,andthepasswordischangedlikeso:

1. Openaterminalwindow.2. Issuethecommandsudo passwd postgres.3. Type(andconfirm)thepasswordtobeusedforthisuser.

• Thepostgres userwillbetheonlyuseronyoursystemthatcanopenthePostgreSQLpromptwithoutdefiningadatabase,whichmeanspostgres istheonlyuserwhocanadministerPostgreSQL.

• Totestthis,changetothepostgres userwiththecommandsu - postgres andthenenterthecommandpsql.YoushouldnowbeatthePostgresprompt,whichlookslike:

postgres=#• Allotherusersgainaccesstothepromptlikeso:

psql DB_NAMEWhere,DB_NAMEisthenameofanexistingdatabase.

26

Step3:ChangethePostgresadminpassword

Theadministratorpasswordmustbeset;otherwise,externalapplicationswillnotbeabletocommunicatewiththedatabase.TochangetheadminpasswordforPostgres,followthesesteps:

1. Openaterminalwindow.2. Changetothepostgres user.3. Logintothepostgres prompt.4. Issuethecommand \passwordpostgres.5. Enter(andverify)thenewpassword.6. Exitthepromptwiththecommand \q.

27

Step4:Createyourfirstdatabase

Thisiswhereitgetsexciting.Let'screateanewdatabasecalledtestdb.Todothis,followthesesteps:

1. Openaterminalwindow.2. Changetothepostgres user.3. Logintothepostgres prompt.4. Issuethecommand CREATEDATABASEACTOR;.

28

4. Importing the data

29

1)Gotooursql directoryonourwebsiteandapagewithlotsoffileswillpop-up.

2)Downloadimdb-cs3200.zipandextractit.

30

3)Thiswillcreateafoldernamedimdb2015,containing6.txtfiles.

4)Now,inordertocreateadatabase,openPostgres,makesurethattheserverisrunningandconnecttopsql.

31

5)Oncetheserverisrunningandconnectedtopsql,youshouldseesomethinglike<your_user_name>=#asyourprompt.

6)Tocreateanewdatabase,enterthefollowingcommands:priyalmittal=#CREATEDATABASEimdb;priyalmittal=#\cimdb

Yourpromptwillthenswitchtoimdb=#

32

7)Youcannowusethefollowingcommandtocreateatable:imdb=#CREATETABLEtable_name(……);

YouwillhavetodecideontheappropriatedatatypesJ

8)Chooseappropriatetypesforeachcolumndspecifyallkeyconstraintsasdescribedhere.(partofyourfirsthomework)

33

9)Oncetablesarecreated,youcanusethepsql command\copy,toimportdatafromthecorrespondingtextfiles.

10)Thiscommandimportsdatafromthe“actor.txt”file,whichistobementionedalongwithitscompletepath,intotheACTORtable.

11)Thedelimiterspecifiesowthefieldsareseparated,whereasthelastpartofthestatementspecifiesthattheemptystringshouldbeassignedasaNULLvalue.

NOTE:The\copycommandmaytakeafewminutestocompleteforsometables(likelylongerthan10minforcasts).Theexactspeedoftheseoperationsmayvarydependingonyourmachine.

34

NOTE:Youmaygetanerrorwhenrunningthecopycommandthatlookssimilartothis:

ERROR:invalidbytesequenceforencoding"UTF8":0xc30x7c.

Thatmeansthatpsql issettoanencodingthatdoesnotmatchthefile.Trychangingtheencoding,andtrycopyingagain.

imdb=# set client_encoding to 'latin1';

35

5. Creating the .sql files

36

3)Pastethequeriesintoatexteditor,ASITIS,withoutmakinganychangestothefont,color,size,etc.andsavethefilewiththeextensionof.sql

2)Youmayaddcommentsintothefileasmentionedhere.

1)Copythequeriesyouwroteinthecommandpromptasitis,WITHOUTthe“imdb=#”.

37

PLEASENOTE:Addinganyextrasymbolsintoyour.sql file,suchas/ attheendofeachlineoranywhereelse,#,$,etc.mayresultintoerrorswhichwouldn’tletyourfilegetexecuted.Theonlyaddedtextpermittedisanylinethatispreceeded by"--":-- isusedtoaddcommentstothefile.

38

6. Environment Variables Setup

39

• Ifyouencountertheerror:“psql notrecognizedasaninternalorexternalcommand”issueforPostgreSql inWindows,thenyouaresupposedtofollowthesesteps:

2)Clickon“EnvironmentVariables”.

1)Whenyousearchyourmachinewith“Editthesystemenvironmentvariable”,thiswindowshowsup.

40

3)Double-clickonthehighlightedpath.

41

4)Clickon”New”andaddthehighlightedpathbelow.Ifyoufollowedthedefault,itshouldbethesame.Otherwise,youcancheckforthebin folderinthePostgreSQL folderandcopyitspath.

5)Clickon“OK”andrestartcommandprompt.Thisshouldhopefullysolvetheissue.

42

7. How to import an SQL file

43

• Youcanrunthechinooksql fileusingthefollowingcommandinpostgres prompt:

\i ‘319- Chinook– PostgreSql.sql’;

• \i filename.sql isthecommand.• Also,usethecompletepathofthefile

Example:

\i ‘/Users/……./foldername/319– Chinook– PostgreSql.sql’;

• YoucanalsojustcopyandpastethefileintoPgAdmin.

44

8. PgAdmin (version 3 or 4)

45

• pgAdmin isavisualclientforyourpostgres database• Youcaneitherusethelatestversion4ortheolderversion3• Noticethat– accordingtomanyusers– version4ismorecumbersomethanversion3.Wethusrecommendthatyoudownloadversion3insteadofversion4:https://www.pgadmin.org/download/

• Youmaygetsomeerrorswhenfirstinstallingwhenworkingwithpostgres 10,buttheyshouldnotpreventyoufromusingit

46

ConnectingtoPostgreSQLdatabasewithpgAdmin 3(afterpgAdmin 3issuccessfullyinstalled)

1)OpenPostgres,suchthattheslonik iconshouldappearonthetopofyourdesktop.

2)Doubleclickontheparticulardatabase,forwhomtheconnectionwithpgAdmin 3istobeestablished,toopenitscommandprompt.

47

3)Afterclickingonthedatabase,itscorrespondingcommandpromptshouldopenup.

4)WritethisstatementasitiswiththePASSWORDofyourchoice.

NOTE:(i)Please,notethepasswordsomewhereasyouwillneedtoenteritintopgAdmin,everytimeyouwanttorunqueriesforadatabase.

(ii)REMEMBERTOADDTHESEMI-COLON.

48

Now,tosetupanewserveconnectioninpgAdmin,openpgAdmin and:

5)GotoFile->AddServer

6)EnsurethattheNameandHostissettolocalhostandthePortto5432(unlessyouhaveconfigurePostgreSQLforadifferentport)

7)EnterthePasswordyoujustsetforthedatabaseinthePostgrescommandprompt.

RESTALLREMAINSTHESAME!

49

Toconnecttotheserver: 8)Double-clickontheserveritem.

9)Providethepasswordwhenprompted.Youprobablywanttostoreit

50

10)ClickOKeverytimeyougetapop-up.YouwillhavetoclickOKseveraltimes.

51

11)TochecktheTablesintheDatabase,expandtheparticularDatabase.

12)ExpandtheTablestocheckwhatalltablesarepresentinthedatabase.

RightclickonDatabasesinordertocreateandaddanewdatabase

52

13)ToopentheSQLwindowtorunqueriesoveraparticulardatabase,clickonthedatabase.

14)ThenclickonthisSQLbutton.

53

15)Thisiswhereyoucanrunqueriesforaparticulardatabase.

ThisindicatestheDATABASEthatyouarecurrentlyworkingon.

Afterwritingaquery,pressthisRUNbuttontoexecutethequery.OrjusthitF5