PostgreSQL Interview Q&A Part-3

Share via:

PostgreSQL Interview Q&A Part-3

Before going into the article, please check our previous article on PostgreSQL Q&A Part-2

1. Can we have different data directories for PostgreSQL Cluster?

Under One PostgreSQL installation, you can create multiple Clusters.  Each Cluster will have its own data directory. One Cluster cannot have multiple data Directories.

Data Directory path would be under the $PGHOME.

2. How do we manage transaction load on PostgreSQL?

PostgreSQL is a delete –insert database. Pre-dominantly used for OLAP loads in Enterprise markets and used as OLTP in SMB’s ( Small & Medium Business).

If you are using Postgres for OLTP, you would need to manage data fragmentation on your high transactional tables. To keep up with the performance, Tables would be needed to be Vacuumed periodically.

Check out the below article for more information on Data Fragmentation:

3. Can we restrict no. of connections at the database level?

Yes, You can restrict no of connections at the database and the User level.

In the above example, we have restricted the connections on Database db6. If you want to revert it back, set the ALLLOW_CONNECTIONS to true.

You can also use the CONNECTION_LIMIT Parameter to limit the connections to the database.

4. Can we do a white list of IP’s to allow the database connection?

Yes, for any remote connection to happen to your database cluster, you would need to make an entry in the pg_hba.conf file.

This file is available under your Data directory. Check the screenshot below for the sample entry.

5. What is the data directories’ ownership after installation?

Since the Installation will happen through the root user, all the directories will be owned by the root. Only the data directory will be owned by Postgres users.

You don’t have to create the Postgres user separately, it will be created along with the installation.

6. How to do remote connectivity to your database server from another server?

You can connect to the database from a remote server using the psql command itself.

Along with the command, you will have to mention the -h <hostname> as the option. Look at the screenshot below:

Here, you have to give IP/Hostname in place of localhost.

7. Can we change/transfer ownership of the database?

Yes. In Postgres, you can change the ownership of the database whenever you want. The only thing that has to be kept in mind is only Superuser has the privileges to do that.

See the below example :

Here, the owner of the db6 database was a Postgres user. It was changed to u1.

8. List some shortcut commands in PostgreSQL

Postgres is a relational database and understand SQL language. You can also use some of the shortcuts to query the database.

  • /l , /l+ ( List of databases)
  • /du, /du+ ( List of Roles)
  • /dn, /dn+ ( List of Schemas)
  • /db, /db+ ( List of Tablespaces)

9. Do we have resource configuration at the PostgreSQL level?

There are 2 types of resource configuration in PostgreSQL.

  1. Cluster Level
  2. DB level.

You can make the changes in the PostgreSQL.conf file for the cluster level changes and for the database level parameter changes, you can run them at the database prompt level.

10. Can we create a database with different tablespace?

Yes, by default if you don’t mention anything, pg_default will be the default tablespace for the database.

If you want to create a database with a non-default tablespace, you would need to mention the name of the tablespace at the end of the create database command.

See the eg below :

Here, ts2 is a user-defined tablespace that I had created before.

Author    : Ankit Goyal
LinkedIn :

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTExperts is always active on social media platforms.

Facebook  :
LinkedIn    :
Twitter       :
YouTube   :
Instagram  :

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)

Add Comment