In this article, the points when you make your own __bgworker module using the Background Worker Processes (hereinafter bgwoker) function that was introduced as a new function from PostgreSQL 9.3, and the __super-simple cluster management module that you actually made yourself. I will introduce "pg_promoter" __.
A feature that can be extended so that user-supplied code is executed in a separate process in PostgreSQL. As a feature,
--You can connect to the database internally --By linking with libpq, you can connect to the database as a client application. --You can select when to start bgworker (for example, when the server initialization is completed or when the standby is ready to accept reference queries).
And so on. For details, please refer to Official Document.
When I make my own bgworker, I will introduce three points that I felt when I actually made it.
There is a module called worker_spi in the contrib module of PostgreSQL, so it is easy to make it by referring to it. Since it has the minimum required functions, you can create your own bgworker just by modifying it.
_PG_init() About the _PG_init () function that is called when bgworker is loaded.
_PG_init()
void
_PG_init(void)
{
BackgroundWorker worker;
/*Define necessary parameters, etc.*/
/*From here, the settings for bgworker*/
worker.bgw_flags = BGWORKER_SHMEM_ACCESS |
BGWORKER_BACKEND_DATABASE_CONNECTION;
worker.bgw_start_time = BgWorkerStart_ConsistentState;
worker.bgw_restart_time = BGW_NEVER_RESTART;
worker.bgw_main = pg_promoter_main;
worker.bgw_notify_pid = 0;
/*bgworker registration*/
snprintf(worker.bgw_name, BGW_MAXLEN, "pg_promoter");
RegisterBackgroundWorker(&worker);
}
Here, the bgworker itself is set. Among them, worker.bgw_flags and worker.start_time are summarized below.
--worker.bgw_flags (sets the functionality required by the module)
| value | Contents |
|---|---|
| BGWORKER_SHMEM_ACCESS | Allow access to shared memory |
| BGWORKER_BACKEND_DATABASE_CONNECTION | Transactions and queries can be executed |
--worker.start_time (sets when bgworker starts)
| value | Contents |
|---|---|
| BgWorkerStart_PostmasterStart | Start after postgres initialization |
| BgWorkerStart_ConsistentState | Launch after allowing read-only queries to be executed |
| BgWorkerStart_RecoveryFinished | reference/Launched when run an update query |
Creating functions is not limited to bgworker, but I think it is easy to write code with __ that refers to other programs. The "pg_promoter" introduced next was created with reference to pg_ctl, pgbench, postgres_fdw, etc.
I made a super-simple cluster management module "pg_promoter" using the bgworker function. As the name suggests, "pg_promoter" is a simple module that simply promotes the standby server to the master (is it lacking in functionality?). The main features are as follows.
--Used only on the standby server side --Start when the standby server is ready to accept reference queries --Two setting items ("connection information to master server" and "monitoring interval") --Perform alive monitoring of the master server at regular intervals (do not monitor alive of the standby server itself) --Master server alive monitoring connects as a client using libpq --If there is an error in the master server, the standby is promoted to master. --Standby server promotion sends a signal internally
Modules registered as bgwoker are compiled and installed with make, make install in the same way as when installing the contrib module.
When registering bgworker, set it to "shared_preload_libraries".
postgresql.conf
shared_preload_libraries = 'pg_promoter'
#The following is pg_Setting items required for promoter
pg_promoter.primary_conninfo = 'host=localhost port=15432'
pg_promoter.keep_alive = 5
Make the above settings + replication settings.
pg_ctl start -D data
pg_ctl start -D 2data
Looking at the process,
14084 pts/2 S 0:00 /home/postgres/pgsql/9.3/bin/postgres -D data 14086 ? Ss 0:00 postgres: checkpointer process 14087 ? Ss 0:00 postgres: writer process 14088 ? Ss 0:00 postgres: wal writer process 14089 ? Ss 0:00 postgres: autovacuum launcher process 14090 ? Ss 0:00 postgres: stats collector process 14105 pts/2 S 0:00 /home/postgres/pgsql/9.3/bin/postgres -D 2data 14109 ? Ss 0:00 postgres: startup process waiting for 000000010000000000000003 14110 ? Ss 0:00 postgres: checkpointer process 14111 ? Ss 0:00 postgres: writer process 14112 ? Ss 0:00 postgres: stats collector process 14113 ? Ss 0:00 postgres: bgworker: pg_promoter 14114 ? Ss 0:00 postgres: wal receiver process streaming 0/3000090 14115 ? Ss 0:00 postgres: wal sender process postgres [local] streaming 0/3000090
You can see that 14113? Ss 0:00 postgres: bgworker: pg_promoter and bgworker are running.
In other words, "pg_promoter" is monitoring the life and death of the master (PID is 14084) in the background of PostgreSQL.
Try killing the standby server process in this state.
kill -sigkill 14084
Then after a few seconds. ..
LOG: received promote request LOG: redo done at 0/20000F0 LOG: selected new timeline ID: 2 LOG: worker process: pg_promoter (PID 14113) exited with exit code 1 LOG: unregistering background worker "pg_promoter" LOG: archive recovery complete LOG: database system is ready to accept connections LOG: autovacuum launcher started
I got a log and was promoted!
It's hard to understand if it's an article, so I thought "I'd like you to try it!", So I was planning to put it on github, but I haven't had enough time to give it. I will put it on github soon, so please use it!
(Added on 2013/12/24) Published on github! pg_promoter URL : https://github.com/Masahiko-Sawada/pg_promoter Please use it!
This time, I made a simple cluster management module using the function of bgworker. At first, I made it with interest, but I felt that if I made it, I could make a cluster management module that can be used reasonably well. It's been about 4 months since PostgreSQL 9.3 was released, but since it is a function that can do various things, I think that various modules will come out in the future. There is not much information about bgworker, so please refer to it when you make your own bgworker or use other bgworker! Have a nice weekend!
Recommended Posts