PostgreSQL Interview Q&A Part-1

Share via:

Post Views: 0

PostgreSQL Interview Q&A Part -1

PostgreSQL is a free and open-source relational database management system emphasizing extensibility and SQL compliance. The frequently asked questions from PostgreSQL database are given below.

1.Difference between Alert log in Oracle and Error log in PostgreSQL?

Error log to PostgreSQL is same as Alert Log to Oracle. There is a dedicated background process in PostgreSQL which writes the errors in Error log where as in Oracle, all the background processes have access to write the errors in Alert Log.

PostgreSQL Error log does not have the detailing of an error which Alert log has, so you might need to be look at on-screen errors to completely understand the issue.

You can find the PG log in inside the log directory under your $PGDATA.

2.What will happen when any row gets updated every time in PostgreSQL?

PostgreSQL is a delete –insert database. Whenever a row gets updated in PostgreSQL, the original row is deleted and a new row with updated values is inserted in the table.

Due to this reason, it results in lot of Data Fragmentation. Check out the below article for more information on Data Fragmentation:

https://www.ktexperts.com/data-fregmentation-in-postgresql/

3.Anything equivalent to a Control File in PostgreSQL?

There is only one configuration file on which PostgreSQL database cluster will run. There is no concept similar to Control file in PostgreSQL. Postgresql.conf file can be called similar to pfile/spfile in Oracle.

You can look for the file in data directory under you PG_Home.

4.If we kill Postmaster what will happen to the PostgreSQL database? Database will crash?

If you kill Postmaster, it will kill all the other background processes and database will be inaccessible.

Check the below screenshot:

Before killing the Postmaster:

After killing Postmaster:

As you can see, all the processes were killed just by killing the Postmaster.

5.What about the existing connections, if we kill the Postmaster process at server level?

Existing connections will be terminated if the Postmaster is killed at the OS level if you have not set the Postmaster to auto-spawn itself.

Check the screenshot below:

6.When we are doing any maintenance activity at database level. How would we proceed/restrict to avoid connecting to the database?

In order to restrict the transactional connections on the database, you can put the DB in read only mode by altering the parameter “default_transaction_read_only”.

Following command needs to be triggered:

Alter database <db_name> SET default_transaction_read_only = true;

7.Can we stop the Postmaster process to avoid connecting to the PostgreSQL database for any activity?

You can definitely do that by blocking your Postgres Installation port (5432 :default). If it is not accessible from outside, no connections can be made to your PostgreSQL database.

8.Difference of User level Management in PostgreSQL and Oracle?

Some of the differences are highlighted below:

	Oracle	PostgreSQL
1	Users are created at Database Level	Users are created at Cluster Level
2	Users by default have no access to the Database	Users by default have access to connect to any Database in the cluster and Public Schema
3	No Default Role granted to User on Creation	Public Role by default granted to User on Creation

9.Database cluster in PostgreSQL mean?

Database cluster in PostgreSQL is a group of databases in one installation. You can create any number of databases under one software installation in PostgreSQL. Please look at the fig, below to understand what a Cluster means in PostgreSQL.

10.If we have different versions of PostgreSQL in one server. Can we have a different Postmaster port for each version?

Yes, Postgres run on TCP/IP Port. Default port is 5432. IF you already have a PostgreSQL version running in your box and you want to install another PostgreSQL Version, during the time of installation you will have to provide a different Port (5433 eg) , otherwise your installation will fail.

You can find the Port on which your PostgreSQL is running by looking at the postmaster.pid file under your data directory.