Re: Killed background writer process

[Raj kumar <rajkumar820999@xxxxxxxxx>]

Hi,

Thanks Laurenz and Vijay.. it really helps!

Thanks & Regards, 

Raj Kumar Narendiran 

On Fri, 4 Jun 2021, 15:22 Vijaykumar Jain, <vijaykumarjain.github@xxxxxxxxx> wrote:

not sure if this explains well,

window1 :

postgres=# begin transaction isolation level serializable;

BEGIN

postgres=*# delete from t;

DELETE 200

----- wait while you kill bg writer, should not be impacted.

postgres=*# commit;  

COMMIT

in a parallel window, strace the pid of background writer.

window2:

strace -p 13665

strace: Process 13665 attached

select(8, [5 6 7], NULL, NULL, {tv_sec=26, tv_usec=338078}) = ? ERESTARTNOHAND (To be restarted if no handler)

--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=13668, si_uid=1001, si_status=0, si_utime=0, si_stime=1} ---

wait4(-1, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], WNOHANG, NULL) = 13668

wait4(-1, 0x7fffa51c2474, WNOHANG, NULL) = 0

rt_sigreturn({mask=[]})                 = -1 EINTR (Interrupted system call)

rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0

clone(child_stack=NULL, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x7fabf885c610) = 13832

openat(AT_FDCWD, "postmaster.pid", O_RDWR) = 9

read(9, "13665\n/opt/postgresql-13/pgsql/d"..., 8191) = 88

close(9)                                = 0

getpid()                                = 13665

rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0

select(8, [5 6 7], NULL, NULL, {tv_sec=60, tv_usec=0}

window3:

postgres@go:~/postgresql-13.2/src$ ps -aef | grep postgres

root       13607   13594  0 15:04 pts/1    00:00:00 su - postgres

postgres   13608   13607  0 15:04 pts/1    00:00:00 -bash

root       13657   13537  0 15:17 pts/0    00:00:00 su - postgres

postgres   13658   13657  0 15:17 pts/0    00:00:00 -bash

postgres   13665       1  0 15:17 ?        00:00:00 /opt/postgresql-13/local/bin/postgres -D /opt/postgresql-13/pgsql/data

postgres   13667   13665  0 15:17 ?        00:00:00 postgres: checkpointer

postgres   13668   13665  0 15:17 ?        00:00:00 postgres: background writer

postgres   13669   13665  0 15:17 ?        00:00:00 postgres: walwriter

postgres   13670   13665  0 15:17 ?        00:00:00 postgres: stats collector

postgres   13671   13665  0 15:17 ?        00:00:00 postgres: logical replication launcher

postgres   13672   13658  0 15:17 pts/0    00:00:00 psql

postgres   13673   13665  0 15:17 ?        00:00:00 postgres: postgres postgres [local] idle in transaction

window4:

postgres@go:~$ kill 13668

On Fri, 4 Jun 2021 at 15:16, Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> wrote:

PostgreSQL: Documentation: 13: 19.4. Resource Consumption

There is a separate server process called the background writer, whose function is to issue writes of “dirty” (new or modified) shared buffers. When the number of clean shared buffers appears to be insufficient, the background writer writes some dirty buffers to the file system and marks them as clean. This reduces the likelihood that server processes handling user queries will be unable to find clean buffers and have to write dirty buffers themselves. However, the background writer does cause a net overall increase in I/O load, because while a repeatedly-dirtied page might otherwise be written only once per checkpoint interval, the background writer might write it several times as it is dirtied in the same interval. The parameters discussed in this subsection can be used to tune the behavior for local needs.

postgres/README at bc2a389efb3b52d259cefd53c16cfa00742116f2 · postgres/postgres (github.com)

I believe the postmaster checks if it is running, if not it starts again i think.

in source.

postgres/postmaster.c at bc2a389efb3b52d259cefd53c16cfa00742116f2 · postgres/postgres (github.com)

The same goes for wal writer as well.

On Fri, 4 Jun 2021 at 14:55, Raj kumar <rajkumar820999@xxxxxxxxx> wrote:

Hi Team,

I've killed background writer process for a testing purpose. I found that after sometime it automatically came up with a new PID without me doing anything from my end. 

How postgres automatically brings it up? Is there any impact on the Database and connections?

Thanks,

Raj Kumar Narendiran 

--

Thanks,

Vijay

Mumbai, India

--

Thanks,

Vijay

Mumbai, India