Learning PostgreSQL - Configuration files

 As part of Learning PostgreSQL series, today we will discuss configuration files that are essential in administering PostgreSQL server. These files will always reside in PGDATA, data directory of the PostgreSQL cluster.

In some installation, the configuration files might reside in another location like in Ubuntu based distribution, if you installed the database software using package installation type, then there are chances that these files would reside in `/etc/postgresql/<version>/main/`.

Please note that you can always get the location of configuration file by connecting to the database. I have installed the database using package method on a Linux distribution.

Learning PostgreSQL - Database Layouts (Logical and Physical)

As you might already know that I have started writing about my journey in learning of PostgreSQL as posts, which are sort of notes to me and professionals like me. If you are following along with the series, then there are 2 posts which I have already written about Architecture and Installation of PostgreSQL database.

In this post, I will share Logical and Physical structure of the PostgreSQL database. This will be an overview of the database layouts, we will go deep like discussing internal layout of a page in future posts.

Learning PostgreSQL - Installation

 If you have not viewed my last post, then I request you to please go and check it out. The last post is about the architecture of PostgreSQL database, and this post is in continuation of that series.

In this post, we will discuss, different ways of installing a PostgreSQL software. Most of the organizations use Linux to deploy their PostgreSQL database, so we will be discussing ways to install PostgreSQL software on a Linux.

This will be a long post, so I would suggest you to keep a cup of coffee or a tea handy 😉.

Before installation, we will first understand how PostgreSQL versioning works.

Learning PostgreSQL - Architecture

I have started learning a new database, i.e. PostgreSQL, which is a powerful and open source relational database system. 

This is something new that I am trying, so I will start this series where I will try to publish a post every week with what I learned. Basically, these posts are nothing but notes that I am taking during the learning journey of PostgreSQL database.

This can also help those who are also in the process of learning this database system, and to those who already learned PostgreSQL, it can be a refresher.

Without wasting more time in introduction, let's start with the Architecture of the PostgreSQL. For Oracle DBAs (someone like me 😉), I will try to compare it with Oracle terminology so that it may help or speedup the learning.

Recover the Data without Backup !

So I had one interview, wherein got below question which left me confused. As per interviewer there is a solution but at that time I was not aware of the solution.

The question goes like...

There is a database which is having size in TBs and accidentally one of the developer deleted all rows of a business critical table and committed the changes. The table is not huge, it was not having many rows at that time. There is no flashback enabled. Now you've to recover deleted rows. So what would you do to recover the data in minimal time?

Recovering from removed/deleted datafile

In this post will discuss on how to recover from the scenario where someone somehow deleted any datafile. But before proceeding make sure you have a backup of the database, and the archive logs from the backup till date. So to recover from that scenario we must create that scenario.

For creating scenarios follow this:

• Make sure you’ve a Permanent Tablespace which is not that important (In Test Env). If you don’t want to use any existing tablespace then create a new tablespace to take it to this scenario.

SYS@DBACODE > CREATE TABLESPACE USERS DATAFILE '/oracle_11204/oracle/oradata/dbacode/datafile/users01.dbf' SIZE 60M;

Tablespace created.

Create a Linux / Unix Mount Point

In this post we will see how to create a Mount Point in Linux. For this post I'll be using Oracle Linux 6.4 on Virtual Box. This tutorial can be used for any UNIX, but before doing to actually on Live Machine, first use it in Test Machine and if everything is fine do it on Live Machine.

If you are using Linux on VMWare, the step to add storage will be somewhat different but rest of it will be same.

Follow below steps in order to create a mount point on Linux –

• Add new SATA disk(s) in Virtual Machine.
  Select the Machine where the Linux is installed (In this case dbrhel)
  Machine Setting -> Storage -> Controller:SATA -> Add Disk
  Provide the required details.
  

Scenario - Archive Destination Full

This post will discuss what action you can take if your Archive Destination is full. There are many ways to solve this scenario but I found this solution very easy to understand and it is working (in my environment.)

So lets get started. I will now tell you how to simulate so that your archive destination will be full. This is very simple technique, there is nothing complicated in this simulation just follow the steps as described.

rlwrap for Command Line History and Editing in SQL*Plus and RMAN

The rlwrap (readline wrapper) utility provides a command history and editing of keyboard input for any other command. This is comes in handy when we are using sqlplus or rman on linux, which don't come with command line history feature.

This post will explain how to install and configure rlwrap for sqlplus and rman.