PostgreSQL #2 - Installation

PostgreSQL #2 - Installation

In this post I'm going to explain how to install PostgreSQL database server. I will try to make it clear as possible. You will find in the post the prerequisites, on what environment I'm working on and step by step instructions on how to install postgresql via yum or from source code.

So, Lets begin :)

Prerequisites
You can find all the prerequisites in the official documentation. To be honest I looked at the prerequisites maybe once. If you are trying to create a production enivronment you should check the prerequisites. If you are just want to learn or create a test env just follow the post instructions and you'll be ok.

My environment
I dont have any server therefore I downloaded oracle virtualbox. I created a virtual machine on my laptop and installed centos 7 on it. You need to configure the machine so that it can access the internet. In other word if you can ping outside of your network you are good to go. If you are working in a private network/vpn you should make sure that you meet the prerequisites. During the installation of centos 7 you will need to configure your mount points. Please make sure that you have the next 2 mount points :


PostgreSQL repository
1.The benefits of working in an environment that can access the internet is that you can use the postgresql repository. This repository contains all the rpms that are needed for the installation and many more that are connected to PostgreSQL. I will install PostgreSQL version 9.6 on Centos 7 and therefore I downloaded the suitable rpm for me. Please make sure to download the suitable rpm for your os and for the version that you are going to install. 
2.Copy the rpm to the server and install it with the command :
 rpm -hiv pgdg-centos96-9.6-3.noarch.rpm
3.Check that it worked with the command :
yum repolist
You should get a list of all the repositories you have including the the postgresql repository :



 Installation
1..In this part we need to install the postgresql database. I prefer the installation from source code because then I have more control on the locations of the installation. On the other hand, installation with yum is faster and easier.

Installation via yum
2a.We can skip the download part and install postgresql immediatly via yum :
yum install postgresql96-server.x86_64

Installation from source code
2b.We can download the source code from the official site, compile it and install it manualy. Lets do that :
2b.0.1.Create a directory of the version you want to install in /PostgreSQL :
mkdir /PostgreSQL/9.6.3
2b.1.Download from the File browser the version you want(I want to install version 9.6.3 therefore I'll download the file postgresql-9.6.3.tar.gz).
2b.2.Copy the tar file to the server and put it in the directory /PostgreSQL/9.6.3/
2b.3.Use the command gunzip postgresql-9.6.3.tar.gz to unzip the gzip file. You will see that your file doesnt have the suffix gz anymore.
2b.4.Use the command tar xf postgresql-9.6.3.tar to open the tar file
2b.5.Now you should have a new directory in the current directory, please cd inside :
cd /PostgreSQL/9.6.3/postgresql-9.6.3
2b.6.Now you need to make sure that the $PATH variable includes the directory that contains the libgcc file. With the command which libgcc you can find the location of the gcc file. If you dont have gcc installed install it with yum install libgcc . Now check that the directory that contains the libgcc is part of the $PATH variable. In my CENTOS machine the libgcc is located in /usr/lib64 directory.If you want to add the directory to the PATH variable you should use the next command : export PATH=$PATH:/usr/lib64


2b.7.Before you install postgresql please make sure that you have the next packages installed and if not please install them : readline-devel,gcc,zlib-devel.
yum install readline-devel
yum install gcc
yum install zlib-devel
2b.8.Now lets move to the directory that was created after we opened the tar file. If you followed my directories structure you should cd to /PostgreSQL/9.6.3/postgresql-9.6.3
2b.9.The next command wil make sure that you have all of the dependencies for the rest of the build and install process. If you are missing some packages you will get some errors. You should set the prefix to be the path of the directory that will contain the installation:
./comfigure --prefix=/PostgreSQL/9.6.3/
2b.10.The next command will build postgresql files,a few important modules and the documentation : make world.  After it finishes you will see the output :
"PostgreSQL,contrib, and documentation successfully made. Ready to install"
 Now it is time to install postgresql, run the command :  make install-world .It will copy all the relevant files to the directory that you mentioned in step 2b.9 as the installation destination and install postgresql. At the end of the installation you should see the output  :
"PostgreSQL, contrib, and documentation installation complete."
2b.11.The command make check will check if postgresql is suitable to run on your machine. It will create a temporary instance and a few databases in order to perform regression checks.
2b.12.Now, please run make clean. It will delete all the object files that were compiled.
2b.13.If you run ls -ltr in the directory that was configured as the installation destination you will see the next output :
The 4 first directories were created as a result of the previous steps. The last directory (the green one) is the directory that was created when we opened the tar. We dont need it anymore.
2b.14.Create the data directory in the same path :
mkdir /PostgreSQL/9.6.3/data

Create postgres user and initialize the instance: 
1.Create the linux user that will manage your postgresql instance. I chose to create a linux user that is called postgres and set it home to be /PostgreSQL/9.6.3. After that I configured a password for the user :
2.Now let set postgres as the user of all the directories inside /PostgreSQL :
chown -R postgres:postgres /PostgreSQL/
3.We could do it when we created the user but I forgot. So, lets edit the shell of the postgresql user in the file /etc/passwd and set it to be bash.
4..Now login to the postgres user and create a .bash_profile file in the home directory. Please fill the next varaibles according to the pathes that you configured during the installation steps :
su - postgres
vi ~/.bash_profile
export LD_LIBRARY_PATH=/PostgreSQL/9.6.3/lib:/usr/lib64
export PATH=$PATH:/PostgreSQL/9.6.3/bin
export MANPATH=$MANPATH:/PostgreSQL/9.6.3/share/man
export PGDATA=/PostgreSQL/9.6.3/data
5.Now lets initiate our first PostgreSQL instance :
/PostgreSQL/9.6.3/initdb -d /PostgreSQL/9.6.3/data
You will see a lot of debug messages, do not panic its fine. It will take a few minutes and in the end you will see the output
"Success . You can now start the database server using : pg_ctl start -D /PostgreSQL/9.6.3/data -l logfile start"

So do you want to start the database server ? Lets do it.. run the command ...








6.Now try connecting via : psql -d postgres -U postgres -p 5432


This is all for now. In the next post I will explain about the basics of PostgreSQL .  If you have any questions please post them in the comments and I will try to answer you as soon as possible.

תגובות

הוסף רשומת תגובה

פוסטים פופולריים מהבלוג הזה

PostgreSQL #7 - Tuning PART 1 - Where to start ?

תמונה
PostgreSQL #7- Tuning Part 1 It`s been a while since my last post (to be accurate half a year) but I'm back and I got a lot of things to talk about. In the next few posts I decided to focus on Tuning. Performance issues are something that everyone has encountered in some way. It can be a very slow application, a query that is running for a long time and even a very slow server. I think that in the next chapters I will focus on some advanced topics.  If you are asking yourself some of the next questions then I think that this post and the next ones might be useful for you :  -Where should we start from ? -Is it a database issue or an application issue ?  -On what part of the database should we focus ? -What is an execution plan and how can I understand it ? -What are indexes and when to use them ? What type of indexes there are ? -Why the database doesn't use an index that I created ? -Does the order of the columns in the index has any meanin...
המשך לקרוא

PostgreSQL #8- Tuning Part 2 - Indexes

תמונה
PostgreSQL #8- Tuning Part 2 - Indexes In the last post I described how we can gather data from the database to identify some performance issues. In this post I I will focus mostly on indexes. In this post I'm going to answer the next questions :  -What is an index ? -How does an index look like ? -How does index saved in disk ? -Why using an index is sometimes faster then reading the table directly ? -How the database decides whether to use an index or not ?  -What are seq_page_cost and random_page_cost? -What is an index only scan/ bitmap index scan ? -How can I force the database to use a specific scan ? -How to find tables that need an index ? As I already said in the previous posts, you dont need to be a professional Data engineer to understand what I'm talking about. I'm trying to write those posts in way that most of the people can understand so just try to read it :) So lets start :  So what is an index ? A lot of pe...
המשך לקרוא

PostgreSQL #6 - Backup & Restore

תמונה
PostgreSQL #6- Backup and Restore What do you do if your data is lost ? Are you ready for this kind of scenario ? Every database that exists and even PostgreSQL in sepecific might crush(power outage,physical damage to the server and so on.). As a result of that you might lose crucial/non crucial data . In both cases, if you dont have any backup of your database you have a BIG problem. If your PostgreSQL cluster is after a crush and you start the instance it will try to recover the data with the transaction log or with the wal files. The wal files are the files that contains all the changes since the last checkpoint and they are saved in the pg_xlog directory(pg_wals in PostgreSQL 10). The instance will try to roll forward the wals in order to save/restore the data that was lost. When the database start rolling forward the wals it start point is the last checkpoint. I'm pretty sure that I talked about it in the last posts but think about it as the last saving point tha...
המשך לקרוא