PostgreSQL - MC

PostgreSQL #5 - Roles&Schemas In the last post I described the physical layer(files on disk) of PostgreSQL db. From now on everything that we will talk about can be explained because you already now the physical and logical structure of PostgreSQL. In this post I'm gonna focus on roles and schemas. The topics that I am going to focus on this post are:  -What are roles and schemas? -How can I create each one of them ? -How can I manage them ? -What is search path and how we configure it ? - Priviledges -Important schemas Roles The official and most clear defenition of a role is that a role is an entity that can own database objects and has database priviledges (- PostgreSQL 9.6 documentation ). A role can be a specific user or a group or even sometimes it can both, depends on how you use it. Roles are defined on the cluster level and therefore they are available in all the databases in our cluster.  You can get a list of all the roles in ...

PostgreSQL #4 - Physical Layer - Files In the last post in the blog I described most of PostgreSQL`s architecture. If you didnt read that post I suggest you to do it in order to help you  better   understand the things that I'm going to describe in this post and in the next posts. In the title of the post I mentioned "Physical Layer". One might ask - what does it mean ? In this post I'm going to talk about PostgreSQL`s file system layer.  I'm going to explain the importance and the meaning of the directories and the files that were created during the installation (check it out in the second post in the blog). I'm going to focus mainly on the data directory - pg_hba.conf,postgresql.conf, pg_xlog,base,pg_tblspc.   Installation directory When you installed PostgreSQL you had the option to choose where to install it. If you installed it via yum or you didnt manualy  choose  the installation directory it is likely that it was instal...

PostgreSQL #3 - Architecture In the last post in the blog I installed PostgreSQL version 9.6.3. If you still didnt install postgresql on your machine I recommend you to do that. I think that hands on is the best way to understand a new technology. In order to understand how PostgreSQL really works it is importand to understand its architecture. When you will realize and understand its architecture it will be much easier to explain and understand many postgresql mechanisms. When you start PostgreSQL`s instance (via pg_ctl start) the main process of the databases is started. In the past versions it was known as the postmaster process and now days it is called postgres. When this processes is started it allocates memory for two main things : shared memory and other background processes. The shared memory is a memory that the database uses mainly for cashing. Its main goal is to reduce the I/O operations and as a result of that improve the performance of the database. The ...

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. I...