Part 8- PostgreSQL :What is postgresql.conf configuration file .

hello and welcome to email to skills video tutorial in this video tutorial we will learn how to use post-race equal al confer on both grey and linux environment the first thing is what is suppose Chris equal can't fight so post grades sequel dot Conte file is a parameter file which is available on poor scree server when the the post gray server is installed and and the parameters are read when the when the server starts when the server restart it means where the post Cree server restarts and most of the parameters are global parameters and they are read when the server is starting up and let's let's see some of the effects about the the server parameter file which is post case equal dot count for like what is the location of post grace equal dot confer on Linux environment and how do we change it how do we check so let's let's see a small demo this is my poster a sequel server so I'll just say systemctl status post case equals the version is sick post case equal twelve so you can see the status is active and running so what I can say I'll say SP sequel and I'll be connected to the post gray prompt so by default post grace equal dot conf is located in data directory if you say show date underscore directory it will be available at this location or if you wanted to see the exact location you will have to say show config underscore file so you can write show config underscore five so this will give you the complete location of Postgres equal dot conf file and you can view it using the view command or if you wanted to modify some of its parameter you can see if the changes using VI and some of the parameters require server reboot some of them may not require the server reboot so we'll so that is a very interesting point like how to check which one is requiring the yeah this is my boss case equal conf and how do we check the parameters so it is the parameters are listed in PG underscore settings so if you say select name comma settings from PG and his course settings you will be able to see all the parameters so you can see allow system modes so it is an alphabetical order so I think it is close to some 300 parameters so 314 parameters are there and you can see the values on the right side the parameter name on the left side let's see that how many distinct parameters are there so we'll say select distinct contacts sorry contacts from PG underscore settings so if there is a context as postmaster against any parameter that means when you try to modify that parameter at that time you need to to restart your poster a sequel or incidents so that the changes are reflected so we discussed about postmaster context okay now let's move on to the next slide how to modify post-race equal conf parameter so we checked how to check the parameter and then we will see how to how to modify it so let's let's see some of very basic parameter like if I say show port it will show me what port this post gray is listening so it is listening at 5 4 3 2 port number or or there is a very basic parameter which has max underscore connection so if I say show max underscore connection so there are 100 connections allowed at one particular point of time and if you go beyond this you will not be allowed to connect it so let's let's see how do we make changes to this parameter from 100 to 200 there are two ways one way is either you added the parameter file using bi editor and you restart the instance or you can use there is something called as alter system you just say all the system's set max connection equal to new value so in this case we are trying to increase it from 100 to 200 so I will say alter system set max connection equal to 200 so let's do that so it has changed to 200 but this requires reboot so this will not be reflected as of now so if I say show max connections it will still show hundred so where do we find like it has gone to so let's go to the data directory and show data directory I think we already executed it and so this is the location or Postgres equal dot conf cd1 step back and this is the data directory so if I say LS - LRT so there is a file which is called as post grace equal dot Auto dot conf so if you view this file you will be able to see the the alter commodity which you've just fired so max connection equal to 200 so what happens when you restart your instance when you restart your instance this value is picked up from from here first of all your post race equal dot confess red and then this post crease equal dot Auto dot con parameter will be read so let us restart the instance sudo systemctl restart boost race equal 12 so instances restarted just check the status there is on the running mode now let's login to login to the command prompt psql and we'll say show max underscore connections which we updated using using set parameter alter system so you can set the parameter globally using alter there so there are some parameters which are applicable for global there are some which are at what you say the session level there are some at your your user level so you need to classify them okay moving on to the next one so there are different components which are available in Postgres equal dot corn so there is something called so when you open it or when you view it using the view command or editor using the VI you will be able to see something called as connections and authentication which will have listened address equal to localhost that means this this particular tutorial is not about defining what are the different parameters like but I have this will give you a a kind of glimpse of what is post-race equal confound what if I it where it exists and what all can be done with us so that is in this particular section connection and authentication you have listen and ask or address equal to localhost by default post grade do not allow the remote connections so it is equal to localhost so if you have to allow remote connection you need to change this value from localhost to the IP address of the server in in my case the IP address of my system is 71 192 168 1.71 so in case I have to allow the remote IP address connection so I will have to add the IP address of the server itself your server is running post your server is running port is 5 4 3 2 max connection is equal to hundred at this present time so you can see on the right hand side of each parameter there is change requires restarts if you try to restart change the value of max connection from 100 to 200 you need to or restart the server sorry the mice post free instance and let's let's move on to the next one connection and authentication then you have something called as a resource usage so how much of resource you want wanted to give to to post Cray server so by default for our system where I have 2 GB of RAM I have given 128 MB so when you are tuning such parameters shared buffer you need to set 2 or some minimum value let's say 15 to 20 percent of the RAM then you need to gradually increase till the time you find the optimum usage of the RAM so if you try to update any of the parameter like huge pages or shared before it says the change requires restart so you need to restart the change but there are some parameters you you will not see like change required so we start like a work underscore ma'am is equal to 4 MB so this is what Cadia when some temporary rearrangement has to happen in the memory so you can see it as some 64 Kb and as of now which is 4 MB and if you have to increase the value you don't need to I think this is the scan beat and on the fly the next one as well which is right ahead log that means so whenever any changes made to the database so before write it writing to the database it has to be written to a file which is why well log so you have this replication parameters also mentioned in post case equal confer so you have sending servers you have master server you have send by server we will have a separate session for replication in post case equal dot count for you have something called as query tuning also you can enable the hash join or index scan so by default they are own if you wanted to turn it off you can do that as well so you have lot of parameter some 312 parameters we saw in post case equal conf so based on your requirement you can turn it on or off reporting and logging like let's say what where I wanted to record all all what is happening so you can put it in to wear to log then you have when to log and what to log so you have all the the the flexibility available all over cume is one of the greatest feature in in post scree so any unused space or or any kind of maintenance activity happens by in this auto vacuum so by default it is it is enabled so we have a separate session on auto vacuum client connection or defaults are available then you have lock management is also available like what happens when the deadlock is the instance faces the deadlock situation like how many locks per transaction it can it can support in particular point of time then you have a version and platform compatibility so this is somewhat related to your backward compatibility error handling so when when your instance suffers some kind of error or what it is going what it's supposed to do it it is supposed to exit or restart or what what it has to do config file includes so this will give you the location so this option apply allow settings to be loaded from file other than the default Postgres equal dot cond in case you wanted some non default Postgres equal dot count for so you can do it in config file includes and that is it so just a recap like what we saw is we did a small demo like how to find the location of Postgres equal dot conf and then we we saw if the context is postmaster then in that case you have to restart your your instance and and if you there are parameters which can be changed globally using alter system set command and there are some parameters which are requires restart and when you say older system set and if it requires a restart it goes to post post race equal dot corn for auto file and it is read when your since ten instances coming up so the first one is to be read is supposed to be a sequel dot count and that is the second one in that and there are several other parameters which are available in post race equal dot count let us see how it looks so let's go to the leader directory and as - LRT and this is mostly a sequel conf if I just say VI if I wanted to add it I'll have to do this so this poster is equal dot conf so the file consists of lines in the for name equal to value so there is a parameter and on the right hand side there will be a value so it is mentioned over here that this file is read on server startup and when the server receives a PSYOP signal if you edit the file on a running system you have to sign up the server for the changes to take place run PG underscore CTL reload or execute select PG underscore reload underscore some parameters which are Marvel or requires a server shutdown and restart to take place so you can see there is one parameter section which is file location like what should be the location of data directory hbf I identify external PID then you have connection and authentications listen address is very important if you have to do an even remote connection so I have mentioned the IP address port is by default this one max connection is 100 superuser result connection is 3 so this is under connection and authentication then you have the the second parameter section which is resource usage like you can mention the amount of the RAM which can be dedicated to shared buffer or work memory and so on and so there are a lot of parameters which you can do then you have well log parameter so a lot a lot of things are there in in each section then you have application section where and you can once you have the application set up you can enable the parameters so we'll have a separate session for this then you have something called as query tuning so it is there is this great scope available in Postgres equal that corn then you have reporting and logging like we here to log you know what to log and and like rotation also is available what when and where so everything is available for your distinct then you have process title like what should be the post or a cluster name then you have a statistics section order vacuum is there then you have client connection defaults we are not discussing the individual components then you have lock management you have version and platform compatibility then you have error handling component I'm just going through the different components which we have so there are 312 parameters which are available and and it depends like if parameter has postmaster at as to what that means underscore PG underscore settings then you have to restart the instance otherwise you don't need to do so so I hope that is going to help and thanks for watching

Channel: E-MultiSkills Database Tutorials

Views: 1,780

Keywords: postgresql.conf, postgresql.conf settings, postgresql configuration, postgresql configuration in linux

Id: zXTgN3hbG9c

Length: 18min 52sec (1132 seconds)

Published: Sat Jul 25 2020