PostgreSQL Interview Q&A Part-2
Before going into the article, please check our previous article on PostgreSQL Q&A Part-1
1. What are template0 & template1 databases?
As the name suggests, these are template databases that come by default with the Installation. In a fresh installation, both the template databases are exactly the same but template1 is editable while template0 is read-only.
You can create new databases using any of these template databases as the base database. The reason for having 2 template databases is that template1 is editable and if for some reason it is messed up you still have template0 which you can use.
Also, remember you cannot drop a template database. In order to figure out which all databases are template databases, run the following command. All the “t” ones are template databases.
2. What are the purpose and use of tablespaces in PostgreSQL?
In PostgreSQL, the Tablespace concept is diluted since it does not represent the logical storage of data. Tablespaces are common to complete cluster and it does not belong to only 1 database. There are 2 tablespaces available by default and it is always advisable to use the default tablespaces.
3. We don’t need to add any datafiles in PostgreSQL?
PostgreSQL is a filesystem-level database. You don’t have to manage/maintain any data-files for tablespaces. Space at the mount point is used by the available tablespaces and the only thing you would need to make sure is there is always enough space available on the mount point.
If you create a user-defined tablespace and would like to know the mount point it is created on, run the following command.
Here, the Location tab will give the mount point details. For default tablespace, it is always blank.
4. How will we manage Database growth in PostgreSQL as we don’t have a tablespace concept?
Again, you would need to do capacity planning for your database’s future growth. Based on the planning, you will have to make sure there is sufficient space on the mount points which your database cluster is using.
5. How User and Schema are different in PostgreSQL?
Schema is a logical separation of the data in the database. Your user can create any no of schemas in the database and store the data.
The user is at the cluster level and is common for all the databases and has by default access to all the databases in the cluster.
Schema is at the database level and can store data pertaining to only that particular database.
6. Can we also control databases with different ports? Can we give multiple ports for one database?
PostgreSQL Cluster works on 1 and only 1 TCP port. So the entire cluster will be getting connected to one port. You cannot have multiple ports for multiple databases in one cluster.
Eg: All the below databases are part of 1 cluster and all of them will get connected to 5432 port.
7. Can we see the PostgreSQL conf file?
Yes, the Postgresql. the conf file is in a readable format and you can view and edit the contents on the file. It is placed under the data directory of your PG home. You can make changes to the file, bounce the service and again connect to the database for the changes to take effect.
8. What is the difference between reload/restart of PostgreSQL Services?
Restart means you will stop your services, all the processes will go down including the Postmaster. Once you start all the processes will start.
Reload means your processes will not go down and only your Configuration file will be refreshed and any changes that you did will take effect.
9. Can we restart Postmaster? Is it the same as Listener in Oracle?
Yes, you can stop and start Postmaster. It is the same as Listener in Oracle.
10. Dynamically change parameter values in PostgreSQL as same as Oracle?
Yes, in PostgreSQL you have dynamic and static parameters. You have cluster and database level parameters. For dynamic parameters, you can directly change them from the DB prompt and no service restart is needed.
For Static parameters, you can either change them from the configuration file or at the DB prompt but changes will take effect only after you reload the services.
Author : Ankit Goyal
LinkedIn : https://www.linkedin.com/in/ankit-goyal-0a72999a
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 : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
