Installation Instructions for PostgreSQL:
Introduction
PostgreSQL is a popular open source database server. Mark the use of the word server. Unlike SQLite which you used in class, PostgreSQL is a much more feature rich database management system. With PostgreSQL you have two components, the server and the client. This isn't very different from your web server-browser model where the browser is your client. The web server services requests for fetching web pages whereas a database server services SQL queries on a database. Extending the analogy, just as a web browser helps you make requests to a web server and displays the results of the request viz. a web page, similarly a database client helps you fire queries at a database server, PostgreSQL in our case, and displays the results that the database server sends over from processing those queries. The two most common clients that you will come across when using PostgreSQL are "psql" which is a command-line client and "pgAdmin" which is a graphical client.
This document will guide you through the process of setting up PostgreSQL on your machine. What this means is you will have a locally running instance of the PostgreSQL server on your machine instead of connecting to the edlab machines which also have PostgreSQL servers running on them.
Mac
The easiest and quickest way of getting PostgreSQL server running on your Mac is by using the Postgres.app.
- Go to http://postgresapp.com/.
- Download the latest released version (not the prerelease!).
- Extract the file you just downloaded. Typically the downloaded file should be in your Downloads folder.
- The previous step will extract the Postgres application, typically in the same folder. Look for a file with a blue elephant icon.
- Double click on this file and your very own PostgreSQL server should be up and running. You will see an elephant icon show up in your menu bar at the top.
- Click on "Slonik" in the menu bar. In the menu that that shows up, select "Open Postgres". This should open a new window with a command prompt that should look something like this <your_user_name>=#. This is the psql client that was mentioned earlier and where you will enter SQL commands to interact with the database server.
- To stop the server simply quit the application by clicking on the elephant icon in your menu bar and selecting Quit from the menu that shows up underneath the icon.
A bit of related trivia: The PostgreSQL logo elephant is named "Slonik". "Slonik" means "little elephant" and the name comes from "slony" (plural) or "slon" (singular) which, in turn come from the Russian word "слоны" meaning "elephants" or "elephant" respectively. source
Ubuntu
- Open up a terminal. If you're on a PC, this should typically be achieved by pressing Ctrl-Alt-T together.
- Run this command at the command prompt-
sudo apt-get install postgresql. You may be asked to enter your password. Go ahead and enter it. You may also be prompted for confirmation asking whether or not to continue installing the packages. Be positive and go ahead and say Y. - Then enter these commands in sequence at the command prompt:
$ sudo -u postgres createuser --superuser $USER
$ createdb $USER - Typing
$ psql at the the command prompt now should bring up the psql client prompt which should look something like this- <your_user_name>=#. This is the psql client that was mentioned at the beginning of the document where you can type queries to send to the database server. Entering \q at this prompt will exit psql. - Refer to trivia above for free(as in speech) knowledge!
Windows
- Download the installer from http://www.postgresql.org/download/windows/ from the download link under the section "One click installer".
- Double click on the downloaded file. A window will show up that will guide you through the installation.
- The installer wizard will ask you to specify a directory where PostgreSQL should be installed. It is all right if you stick with the default option. So just click on "Next>".
- It will then ask you to specify a data directory. Again, all right to stick with the default option and click on "Next>".
- You will next be prompted to enter a password for the superuser "postgres". Enter one and make a note of it.
- Next, you will be asked to enter a port number for the server to listen on. The default option should be 5432. Not a bad idea to leave it at that.
- Next, leave the locale selection at "Default Locale".
- The next screen will ask you if you want to install Stack Builder. You can check the box to install it, however you don't need Stack Builder right now and possibly not for the duration of this course. Click on "Finish". This will complete your installation of PostgreSQL.
- To open psql now, which is the client we will use to interact with the database server, go to the command prompt and type this command-
$ psql -Upostgres It will ask your for the password for the user postgres. Enter the password that you had selected during the setup.- If the command 'psql' is not found, then it needs to be added to the system PATH. Hit the Windows key, and search for the option to "Edit the system environment variables". You should be able to find this option quickly by just typing "path".
- Go to "Environment Variables"->"System variables"->Double click "Path"->"New"->Check your installation, and add the directory of the binaries and libraries to the path. As of this writing, by default, it should be "C:\Program Files\PostgreSQL\14\bin" and "C:\Program Files\PostgreSQL\14\lib", respectively. Afterwards, click "OK", and restart your command prompt. The command should work now.
- On executing the previous command, the prompt should change. It should now look like this "postgres=#".
- You are now in the psql program where you can enter queries to interact with the PostgreSQL server. Entering \q at this prompt should exit the psql program.
- Refer to trivia above for free(as in speech) knowledge!