Script for VACUUM FULL on all tables

Usual vacuum process cannot reclaim back free space from tables into OS. It just cleans deleted records and makes space in data pages but does not shrink data files. Which is OK if you keep using those tables for further updates and inserts. But if you have some...

Small useful Linux commands with watch

Sometimes it happens that you must do everything only in terminal from command line. So here are some useful commands: List of database sessions and queries from pg_stat_activity every 2 seconds with highlighting differences: watch -d -n 2 'psql -U upcload -d queries...

PostgreSQL on Ubuntu without swap

If you are running “normal server” you could be asking why the hell would someone want to use database without swap. But if you have virtual machine on cloud you probably know very good what I am talking about. Because cloud instances of Linux usually run...

Very simple bash script for monitoring waits on database

If your database has some problems with locks you can try to monitor them with this very simple bash script: #!/bin/bash while true; do l=$(ps -ef|grep wait|grep -v grep|wc -l) TIMESTAMP=$(date +"%Y%m%d_%H%M%S") if [ $l != 0 ]; then psql -U postgres -c...

Useful linux commands for PostgreSQL administrator

Also only some small hints (and also still in TODO): CommandExplanationDistro yum list postgresqlShow available packages (all)RedHat, CentOS yum search plproxysearch for available packagesRedHat, CentOS rpm -qa|grep postgresqlShows which postgresql packages are...

Linux commands for simple analysis of PostgreSQL logs

Just some simple hints:  (still in TODO…) CommandExplanation grep -i 'ERROR:' *.log|lessYou must be in directory with server logs. Command shows you lines with text "error:" (ignores big/small letters). Command "less" is used for pagination - you can list in...