This is part 2 of the series . Please read Part1 by clicking this URL https://klouddb.io/postgresql-tips-and-tricks-part-1/
1.Log rotation
Postgres log is automatically rotated depending on config like log_rotation_age, log_rotation_size, etc… What if you want to manually rotate the log ? Lets say you are running a performance test and you want to generate a fresh log , how do you achieve that ?
You can use “select * from pg_rotate_logfile() ;” to rotate postgres log . But depending on your configuration this rotation may or may not work . Below is a comparison of two different settings .When log_filename is set to generate one log per day(postgresql-%a.log) , rotation will not work . Although the function returns ‘true(t)’ , it does not rotate the log. I think better message should be given for the case on the right side (diagram below) . Users might think that rotation is successful since it returned true (t).
2.Template databases
Template databases are often misunderstood . By default there are two template databases – template0 and template1
What is the purpose of template1 ?
When you issue “CREATE DATABASE dbname” to create a new database , structure and objects are copied from template1 . Lets say you want pg_buffercache extension in all of your databases , you can include that in template1 . In below screenshot you can see that we created pg_buffercache extension in template1 . Once it is created in template1 , we created a new database called testtwo and pg_buffercache is propagated to all new databases going forward
How to designate an existing database as template db ?
Lets say you dont want to use template1 as your template database ; You want to designate an existing db as your template – You can use below steps to achieve that . In below example we are designating ‘testtwo’ database as template
After that we create a new database ‘emp’ using “create database emp template testwo”
What is the purpose of template0 ?
Template0 can be used to recreate template1 (if template1 is dropped) . Also when you try to create a database with custom encoding ‘create database’ might fail as shown below . In such cases you need to use template0
3.OOM Killer
You are executing a query or script and your query is aborted with just one word “Killed” . You will not be able to find any related info in your postgres logs(depending on your log settings). After scratching your head and spending some time you might find related information in system log (if you are connecting from client machine , this might be a memory issue with your client machine)
psql -d dbname postgres=# \i testquery1.sql Killed
You might see something like below in your system log . “Out of memory” message here indicates a memory issue
oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/user.slice,task=psql,pid=22726,uid=0 kernel: Out of memory: Killed process 22726 (psql) total-vm:1301624kB, anon-rss:998712kB, file-rss:0kB, shmem-rss:0kB, UID:0 pgtables:2228kB oom_score_adj:0
4. Create table like in Postgres
You want to create a new table which is similar to existing table . People use “create table like” to clone the structure of an existing table
Using this method may or may not work depending on the command and approach you choose For example , If you just use below command without any options it may not copy indices,triggers etc
“Create table temp like emp ;”
Instead you can try “Create table temp(LIKE empty INCLUDING ALL)” This will copy indices as well . Better strategy is to use pg_dump –schema-only for that table .So next time you use “create table like” in postgres make sure you compare both tables after cloning
Like our content ? Please check KloudDB School https://school.klouddb.io/p/cloud-dba