Recently we had interesting problem. There are some maintenance tools, that do stuff that touch database. One can be calling repack, another can be specific dump, and yet another can be applying migrations from application.
The problem is that they can step over each other toes, and cause issues.
So we needed to add some way to prevent them from running at the same time…
Every now and then someone complains (me included) that PostgreSQL doesn't have job scheduler.
This is true, to some extent. I'll try to show you couple of approaches to solving this particular problem.
Continue reading How to run some tasks without user intervention, at specific times?
In my work I'm often in situation where I want to restart remote server, and then immediately connect to it over ssh.
This can be done by repeating ssh HOST in shell, but it gets tedious.
I am doing quite a lot of work inside tmux. I especially love that I can start multiple windows/panes and use them to run the same thing across multiple servers, while still having normal shell access between steps.
This thing was greatly improved when I wrote tmux_send_to_many, but one thing was missing – waiting for the thing, running in tmux window to end.
Generally, I had to look at all the windows, and decide when to launch next step on my own.
Not anymore.
I wrote previously about tmux_send_to_many and group_by shell tools that I authored.
Since then I got some more ideas for changes, and for new tool, so figured I'll make an honest git repo for it. Repo is on GitLab, and all the tools inside can be freely used.
While I was doing it, I fixed some things in tmux_send_to_many.
And then – I remembered that for long time I was missing “visual-sleep" type of tool. One that will show some progress information while it's working.
Enter vsleep.
Continue reading visual sleep in shell, and shell_utils repo information
I assume that everyone reading my blog understands GROUP BY clause in SQL.
Lately I've been doing some maintenance work, and found myself in a position that I could really use similar thing in shell.
Tmux is terminal multiplexer. Kinda like old screen, but with much more functionality.
When I work on my servers, it's pretty common that I have to do the same things to multiple servers. To make my life easier I start tmux, and in there start many “windows", each related to work on single server.
I name the windows in a way that let's me quickly find them, without false positives.
For example, if I'd have to upgrade servers db1..db5 then I'd create windows “up-db1" .. “up-db5", and each window would work on single server.
This is already scriptable – let's assume I'd want to show uptime in all of the windows, I can:
tmux lsw -F '#W' | grep -E '^up-db[0-9]+$' | xargs -r -d$'\n' -I% tmux send-keys -t % uptime Enter
But this gets tedious fast.
Continue reading Automation for doing stuff in multiple windows in tmux
I usually write about PostgreSQL, but lately someone asked for help, and one of the problems was similar to sudo command from title.
This was not the first time I saw it, so figured, I'll write a blogpost about it, just so I can refer people to it in the future.
On 27th of February, Heikki Linnakangas committed patch:
Add support for piping COPY to/from an external program. This includes backend "COPY TO/FROM PROGRAM '...'" syntax, and corresponding psql \copy syntax. Like with reading/writing files, the backend version is superuser-only, and in the psql version, the program is run in the client. In the passing, the psql \copy STDIN/STDOUT syntax is subtly changed: if you the stdin/stdout is quoted, it's now interpreted as a filename. For example, "\copy foo from 'stdin'" now reads from a file called 'stdin', not from standard input. Before this, there was no way to specify a filename called stdin, stdout, pstdin or pstdout. This creates a new function in pgport, wait_result_to_str(), which can be used to convert the exit status of a process, as returned by wait(3), to a human-readable string. Etsuro Fujita, reviewed by Amit Kapila.
Continue reading Waiting for 9.3 – Add support for piping COPY to/from an external program.