PostgreSQL #1 - Introduction

PostgreSQL #1 - Introduction
Hi, My name is Mariel Cherkassky and I'm going to write in this blog all the things that I have learned so far about postgresql . I will try to explain anything from what it is to the most complicated things that I handled. Hopefully it will be usefull for some people someday. 


?So what is PostgreSQL
PostgreSQL is a relational database (data stored in tables). PostgreSQL has many advantages in compare to other relational databases but you should remember that what makes it so unique is that it is an open source database. In other words the PostgreSQL project`s code is visible to the community. Every one can reach that code and help make this project better. As a result of that there are many helpfull tools in the field that can make our life easier whether you are developer,system administrator, dba or just someone that is trying postgresql.

This huge attribute led to the creation of many PostgreSQL`s forks. In other words, people took the source code of PostgreSQl and changed it to their need. As a result of that, we can find many forks that can support different kind of problems. There are forks that are free and there are some that cost money. You should choose your fork according to your need. If you just starting I recommend you to start from the official free version that you can find on the official website of the project. On the other hand, if you are trying to migrate system that are based on oracle database the EDB fork will make your life much easier but it will cost you some $ . Another example for a a usefull fork is a PostgreSQL fork that its data located on the HDFS of hadoop. This allows you to enjoy the best of two worlds. You can find more forks on this list.

What you will find in this blog ?
In general the purpose of this blog is to share some knowledge that I accuired on PostgreSQL. I entered to the PostgreSQL`s world from a point of view of an oracle dba. I think that this world is huge and unlimited and sometimes you can get lost. Therefore I would like to help guys that chose to take the same path as I did. I will try to post many hands on trials and many problems that I handled during the last year. The topics that I will focus on are : 

-Installation from source code.
-Architecture
-Objects in PostgreSQL - tables,views,indexes,users,schemas...
-Important parameters
-How to manage you db
-Replication - Replication streaming / repmgr
-Load balancing - pg_pool
-Maintenance
-Performance 
-Monitoring
-Usefull extensions - oracle_fdw,ora2pg,orafce

Some usefull sites : 

Right now I'm the only one working on this blog. I believe that it will take me some time to publish posts on all the topics that I mentioned. I will try to publish a post once a week or two . If you have questions you are more then welcomed to ask.


תגובות

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

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

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...
המשך לקרוא