pg_background is an extension for Postgres 9.5. Initially this extension was shared by Robert Haas in PostgreSQL community for demo purpose. Some modification has been done, by me, to keep it in sync with latest version of Postgres version >=9.5. Extra error handling and command results has been added.
This module allows user to arbitrary command in a background worker and gives capability to users to launch
This module comes with following SQL APIs:
make
make install
After installing module, please use following command install this extension on source and target database as given below:
psql -h server.hostname.org -p 5444 -c "CREATE EXTENSION pg_background;" dbname
To execute a command in background user can use following SQL API
SELECT pg_background_launch('SQL COMMAND');
To fetch the result of command executed background worker, user can use following command:
SELECT pg_background_result(pid)
pid is process id returned by pg_background_launch function
SELECT pg_background_launch('vacuum verbose public.sales');
pg_background_launch
----------------------
11088
(1 row)
SELECT * FROM pg_background_result(11088) as (result text);
INFO: vacuuming "public.sales"
INFO: index "sales_pkey" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "sales": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_1866942"
INFO: index "pg_toast_1866942_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_1866942": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
result
--------
VACUUM
(1 row)
If user wants to execute the command wait for result, then they can use following example:
SELECT * FROM pg_background_result(pg_background_launch('vacuum verbose public.sales')) as (result TEXT);
INFO: vacuuming "public.sales"
INFO: index "sales_pkey" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "sales": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_1866942"
INFO: index "pg_toast_1866942_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_1866942": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
result
--------
VACUUM
(1 row)