Frequently used unix commands by Greenplum DBA's

posted Apr 29, 2017, 1:16 AM by Sachchida Ojha
Script argument: $1 is the first argument, $2 is the second argument, and so on. The variable $0 is the script's name. The total number of arguments is stored in $#. The variables $@ and $* return all the arguments
1. route - show / manipulate the IP routing table
see also:  ip route
2. nslookup
3. ifconfig -a
4. hostname
5. ping
6. ethtool eth0
7. netstat -rn
8. top
9. vmstat
10. w

--Find out total space used by primary segment databases (excluding log files and local backup files)
[gpadmin@sachi ~]$gpssh -f $GPHOME/hosts.seg "du –h --exclude=*pg_log* --exclude=*db_dump* -s /data[12]/primary/gpseg*"

--Change owner of all tables in Public schema
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" sachi` ; do psql -c "alter table $tbl owner to gpadmin" sachi ; done

--Move all tables from Public Schema to a specified schema.

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname='public';" sachi`; do `psql -c "ALTER TABLE $tbl SET SCHEMA sachi;" sachi`; done

DATABASES=`psql -q -c "\l" | sed -n 4,/\eof/p | grep -v rows | grep -v template0 | awk {'print $1}' | sed 's/^://g' | sed -e '/^$/d' | grep -v '|'`

datediff() {
 d1=$(date -d "$1" +%s)
 d2=$(date -d "$2" +%s)
 echo $(( (d1 - d2) / 86400 )) days
 }

timespent() {
 d1=$(date -d "$1" +%s)
 d2=$(date -d "$2" +%s)
 echo $(( (d1 - d2) )) seconds
 }

11.uptime
12. ps
13. free
14. iostat
15.sar
16. mpstat
17.pmap
18. /proc file system - various kernet stats
# cat /proc/cpuinfo
# cat /proc/meminfo
# cat /proc/zoneinfo
# cat /proc/mounts
# cat /proc/version
19. lsof

Find out total space used by log files of primary segment databases

[gpadmin@sachi ~]$gpssh -f $GPHOME/hosts.seg "du –h -s /data[12]/primary/gpseg*/pg_log*"

$(date +%s)

start_time=$(date +%s)
end_time=$(date +%s)

duration=`expr $end_time - $start_time`

echo `expr $difference / 3600`:`expr "(" $difference / 60 ")" % 60`:`expr $difference % 60`
List files in current directory and its size

for i in `ls -lh|awk {'print $9,$5}'`; do echo $i; done

20. last
21. df
22. du
23. kill
24. traceroute
25. rsync
26. rpm
27. tar
28. pwd
29. lsb_rlease -a
30. uname -a  [-prints the name, version and other details about the current machine and the operating system running on it.]

Find out total space used by backup files of primary segment databases

[gpadmin@sachi ~]$gpssh -f $GPHOME/hosts.seg "du –h -s /data[12]/primary/gpseg*/db_dumps*"

Change all uppercase to lowercase with vi?
:%s/.*/\L&/

Conversely, :%s/.*/\U&/ will change all the characters to uppercase.


List all directory and subdirectory

ls -lDR | grep ':$' | head |sed -e 's/:$//'

Using grep and awk to filter our idle connections
posted Jul 8, 2015, 10:05 AM by Sachchida Ojha
[sachi@localhost ~]$ ps -ef | awk '/sachi/ && /idle/'
gpadmin  13356  1436  0 09:13 ?        00:00:00 postgres: port  5432, sachi sachi [local] con120988 [local] idle
sachi    17449 13370  0 09:38 pts/1    00:00:00 awk /sachi/ && /idle/

[sachi@localhost ~]$ ps -ef | awk '/sachi/ && /idle/'|grep -v awk
gpadmin  13356  1436  0 09:13 ?        00:00:00 postgres: port  5432, sachi sachi [local] con120988 [local] idle

[sachi@localhost ~]$ ps -ef | awk '/sachi/ && /idle/'|grep -v awk
gpadmin  13356  1436  0 09:13 ?        00:00:00 postgres: port  5432, sachi sachi [local] con120988 [local] idle

[sachi@localhost ~]$ ps -ef |grep sachi
sachi     2380  2353  0 Jun26 ?        00:00:06 gnome-session --session gnome-classic
sachi     2388     1  0 Jun26 ?        00:00:00 dbus-launch --sh-syntax --exit-with-session
sachi     2396     1  0 Jun26 ?        00:00:00 /bin/dbus-daemon --fork --print-pid 4 --print-address 6 --session
sachi     2484     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfsd
sachi     2551     1  0 Jun26 ?        00:00:00 /usr/libexec//gvfsd-fuse /run/user/1000/gvfs -f -o big_writes
sachi     2554  2380  0 Jun26 ?        00:00:02 /usr/bin/ssh-agent /bin/sh -c exec -l /bin/bash -c "env GNOME_SHELL_SESSION_MODE=classic gnome-session --session gnome-classic"
sachi     2595     1  0 Jun26 ?        00:00:00 /usr/libexec/at-spi-bus-launcher
sachi     2599  2595  0 Jun26 ?        00:00:00 /bin/dbus-daemon --config-file=/etc/at-spi2/accessibility.conf --nofork --print-address 3
sachi     2603     1  0 Jun26 ?        00:00:00 /usr/libexec/at-spi2-registryd --use-gnome-session
sachi     2611  2380  0 Jun26 ?        00:00:22 /usr/libexec/gnome-settings-daemon
sachi     2627     1  0 Jun26 ?        00:00:02 /usr/bin/pulseaudio --start
sachi     2632     1  0 Jun26 ?        00:00:00 /usr/bin/gnome-keyring-daemon --start --components=ssh
sachi     2726     1  0 Jun26 ?        00:00:04 /usr/libexec/gvfs-udisks2-volume-monitor
sachi     2741     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfs-afc-volume-monitor
sachi     2746     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfs-mtp-volume-monitor
sachi     2750     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfs-gphoto2-volume-monitor
sachi     2754     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfs-goa-volume-monitor
sachi     2757     1  0 Jun26 ?        00:00:00 /usr/libexec/goa-daemon
sachi     2764     1  0 Jun26 ?        00:00:43 /usr/libexec/goa-identity-service
sachi     2766  2380  0 Jun26 ?        00:01:31 /usr/bin/gnome-shell
sachi     2773     1  0 Jun26 ?        00:00:00 /usr/libexec/dconf-service
sachi     2781     1  0 Jun26 ?        00:00:00 /usr/libexec/gsd-printer
sachi     2798     1  0 Jun26 ?        00:00:00 /usr/bin/ibus-daemon --replace --xim --panel disable
sachi     2806  2798  0 Jun26 ?        00:00:00 /usr/libexec/ibus-dconf
sachi     2808     1  0 Jun26 ?        00:00:00 /usr/libexec/ibus-x11 --kill-daemon
sachi     2816     1  0 Jun26 ?        00:00:00 /usr/libexec/gnome-shell-calendar-server
sachi     2820     1  0 Jun26 ?        00:00:06 /usr/libexec/mission-control-5
sachi     2832     1  0 Jun26 ?        00:00:00 /usr/libexec/evolution-source-registry
sachi     2840  2798  0 Jun26 ?        00:00:00 /usr/libexec/ibus-engine-simple
sachi     2942     1  0 Jun26 ?        00:00:00 /usr/libexec/evolution-addressbook-factory
sachi     2947     1  0 Jun26 ?        00:00:01 /usr/libexec/gconfd-2
sachi     2954     1  0 Jun26 ?        00:00:00 /usr/libexec/tracker-store
sachi     2970     1  0 Jun26 ?        00:00:00 /usr/libexec/evolution-calendar-factory
sachi     3010     1  0 Jun26 ?        00:00:00 /usr/libexec/gnome-session-failed --allow-logout
sachi     3014  2380  0 Jun26 ?        00:00:00 /usr/bin/seapplet
sachi     3029  2380  0 Jun26 ?        00:00:00 abrt-applet
root     13024   948  0 09:11 ?        00:00:00 sshd: sachi [priv]
sachi    13033 13024  0 09:11 ?        00:00:00 sshd: sachi@pts/0
sachi    13039 13033  0 09:11 pts/0    00:00:00 -bash
sachi    13355 13039  0 09:13 pts/0    00:00:00 psql
gpadmin  13356  1436  0 09:13 ?        00:00:00 postgres: port  5432, sachi sachi [local] con120988 [local] idle
root     13358   948  0 09:13 ?        00:00:00 sshd: sachi [priv]
sachi    13363 13358  0 09:13 ?        00:00:00 sshd: sachi@pts/1
sachi    13370 13363  0 09:13 pts/1    00:00:00 -bash
sachi    17897 13370  0 09:40 pts/1    00:00:00 ps -ef
sachi    17898 13370  0 09:40 pts/1    00:00:00 grep --color=auto sachi

[sachi@localhost ~]$  ps -ef | awk '/sachi/ && /idle/'|grep -v awk
gpadmin  13356  1436  0 09:13 ?        00:00:00 postgres: port  5432, sachi sachi [local] con120988 [local] idle

[sachi@localhost ~]$  ps -ax | awk '/sachi/ && /idle/'|grep -v awk
13356 ?        Ssl    0:00 postgres: port  5432, sachi sachi [local] con120988 [local] idle

[sachi@localhost ~]$  ps -ax | awk '/sachi/ && /idlee/'|grep -v awk

[sachi@localhost ~]$  ps -ax | awk '/sachi/ && /idle/'|grep -v awk
13356 ?        Ssl    0:00 postgres: port  5432, sachi sachi [local] con120988 [local] idle

[sachi@localhost ~]$ ps -ef |grep sachi| grep -v idle
sachi     2380  2353  0 Jun26 ?        00:00:06 gnome-session --session gnome-classic
sachi     2388     1  0 Jun26 ?        00:00:00 dbus-launch --sh-syntax --exit-with-session
sachi     2396     1  0 Jun26 ?        00:00:00 /bin/dbus-daemon --fork --print-pid 4 --print-address 6 --session
sachi     2484     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfsd
sachi     2551     1  0 Jun26 ?        00:00:00 /usr/libexec//gvfsd-fuse /run/user/1000/gvfs -f -o big_writes
sachi     2554  2380  0 Jun26 ?        00:00:02 /usr/bin/ssh-agent /bin/sh -c exec -l /bin/bash -c "env GNOME_SHELL_SESSION_MODE=classic gnome-session --session gnome-classic"
sachi     2595     1  0 Jun26 ?        00:00:00 /usr/libexec/at-spi-bus-launcher
sachi     2599  2595  0 Jun26 ?        00:00:00 /bin/dbus-daemon --config-file=/etc/at-spi2/accessibility.conf --nofork --print-address 3
sachi     2603     1  0 Jun26 ?        00:00:00 /usr/libexec/at-spi2-registryd --use-gnome-session
sachi     2611  2380  0 Jun26 ?        00:00:22 /usr/libexec/gnome-settings-daemon
sachi     2627     1  0 Jun26 ?        00:00:02 /usr/bin/pulseaudio --start
sachi     2632     1  0 Jun26 ?        00:00:00 /usr/bin/gnome-keyring-daemon --start --components=ssh
sachi     2726     1  0 Jun26 ?        00:00:04 /usr/libexec/gvfs-udisks2-volume-monitor
sachi     2741     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfs-afc-volume-monitor
sachi     2746     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfs-mtp-volume-monitor
sachi     2750     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfs-gphoto2-volume-monitor
sachi     2754     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfs-goa-volume-monitor
sachi     2757     1  0 Jun26 ?        00:00:00 /usr/libexec/goa-daemon
sachi     2764     1  0 Jun26 ?        00:00:43 /usr/libexec/goa-identity-service
sachi     2766  2380  0 Jun26 ?        00:01:31 /usr/bin/gnome-shell
sachi     2773     1  0 Jun26 ?        00:00:00 /usr/libexec/dconf-service
sachi     2781     1  0 Jun26 ?        00:00:00 /usr/libexec/gsd-printer
sachi     2798     1  0 Jun26 ?        00:00:00 /usr/bin/ibus-daemon --replace --xim --panel disable
sachi     2806  2798  0 Jun26 ?        00:00:00 /usr/libexec/ibus-dconf
sachi     2808     1  0 Jun26 ?        00:00:00 /usr/libexec/ibus-x11 --kill-daemon
sachi     2816     1  0 Jun26 ?        00:00:00 /usr/libexec/gnome-shell-calendar-server
sachi     2820     1  0 Jun26 ?        00:00:06 /usr/libexec/mission-control-5
sachi     2832     1  0 Jun26 ?        00:00:00 /usr/libexec/evolution-source-registry
sachi     2840  2798  0 Jun26 ?        00:00:00 /usr/libexec/ibus-engine-simple
sachi     2942     1  0 Jun26 ?        00:00:00 /usr/libexec/evolution-addressbook-factory
sachi     2947     1  0 Jun26 ?        00:00:01 /usr/libexec/gconfd-2
sachi     2954     1  0 Jun26 ?        00:00:00 /usr/libexec/tracker-store
sachi     2970     1  0 Jun26 ?        00:00:00 /usr/libexec/evolution-calendar-factory
sachi     3010     1  0 Jun26 ?        00:00:00 /usr/libexec/gnome-session-failed --allow-logout
sachi     3014  2380  0 Jun26 ?        00:00:00 /usr/bin/seapplet
sachi     3029  2380  0 Jun26 ?        00:00:00 abrt-applet
root     13024   948  0 09:11 ?        00:00:00 sshd: sachi [priv]
sachi    13033 13024  0 09:11 ?        00:00:00 sshd: sachi@pts/0
sachi    13039 13033  0 09:11 pts/0    00:00:00 -bash
sachi    13355 13039  0 09:13 pts/0    00:00:00 psql
root     13358   948  0 09:13 ?        00:00:00 sshd: sachi [priv]
sachi    13363 13358  0 09:13 ?        00:00:00 sshd: sachi@pts/1
sachi    13370 13363  0 09:13 pts/1    00:00:00 -bash
sachi    21534 13370  0 10:02 pts/1    00:00:00 ps -ef
sachi    21535 13370  0 10:02 pts/1    00:00:00 grep --color=auto sachi
[sachi@localhost ~]$ ps -ef |grep sachi| grep -v idle|grep -v grep
sachi     2380  2353  0 Jun26 ?        00:00:06 gnome-session --session gnome-classic
sachi     2388     1  0 Jun26 ?        00:00:00 dbus-launch --sh-syntax --exit-with-session
sachi     2396     1  0 Jun26 ?        00:00:00 /bin/dbus-daemon --fork --print-pid 4 --print-address 6 --session
sachi     2484     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfsd
sachi     2551     1  0 Jun26 ?        00:00:00 /usr/libexec//gvfsd-fuse /run/user/1000/gvfs -f -o big_writes
sachi     2554  2380  0 Jun26 ?        00:00:02 /usr/bin/ssh-agent /bin/sh -c exec -l /bin/bash -c "env GNOME_SHELL_SESSION_MODE=classic gnome-session --session gnome-classic"
sachi     2595     1  0 Jun26 ?        00:00:00 /usr/libexec/at-spi-bus-launcher
sachi     2599  2595  0 Jun26 ?        00:00:00 /bin/dbus-daemon --config-file=/etc/at-spi2/accessibility.conf --nofork --print-address 3
sachi     2603     1  0 Jun26 ?        00:00:00 /usr/libexec/at-spi2-registryd --use-gnome-session
sachi     2611  2380  0 Jun26 ?        00:00:22 /usr/libexec/gnome-settings-daemon
sachi     2627     1  0 Jun26 ?        00:00:02 /usr/bin/pulseaudio --start
sachi     2632     1  0 Jun26 ?        00:00:00 /usr/bin/gnome-keyring-daemon --start --components=ssh
sachi     2726     1  0 Jun26 ?        00:00:04 /usr/libexec/gvfs-udisks2-volume-monitor
sachi     2741     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfs-afc-volume-monitor
sachi     2746     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfs-mtp-volume-monitor
sachi     2750     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfs-gphoto2-volume-monitor
sachi     2754     1  0 Jun26 ?        00:00:00 /usr/libexec/gvfs-goa-volume-monitor
sachi     2757     1  0 Jun26 ?        00:00:00 /usr/libexec/goa-daemon
sachi     2764     1  0 Jun26 ?        00:00:43 /usr/libexec/goa-identity-service
sachi     2766  2380  0 Jun26 ?        00:01:31 /usr/bin/gnome-shell
sachi     2773     1  0 Jun26 ?        00:00:00 /usr/libexec/dconf-service
sachi     2781     1  0 Jun26 ?        00:00:00 /usr/libexec/gsd-printer
sachi     2798     1  0 Jun26 ?        00:00:00 /usr/bin/ibus-daemon --replace --xim --panel disable
sachi     2806  2798  0 Jun26 ?        00:00:00 /usr/libexec/ibus-dconf
sachi     2808     1  0 Jun26 ?        00:00:00 /usr/libexec/ibus-x11 --kill-daemon
sachi     2816     1  0 Jun26 ?        00:00:00 /usr/libexec/gnome-shell-calendar-server
sachi     2820     1  0 Jun26 ?        00:00:06 /usr/libexec/mission-control-5
sachi     2832     1  0 Jun26 ?        00:00:00 /usr/libexec/evolution-source-registry
sachi     2840  2798  0 Jun26 ?        00:00:00 /usr/libexec/ibus-engine-simple
sachi     2942     1  0 Jun26 ?        00:00:00 /usr/libexec/evolution-addressbook-factory
sachi     2947     1  0 Jun26 ?        00:00:01 /usr/libexec/gconfd-2
sachi     2954     1  0 Jun26 ?        00:00:00 /usr/libexec/tracker-store
sachi     2970     1  0 Jun26 ?        00:00:00 /usr/libexec/evolution-calendar-factory
sachi     3010     1  0 Jun26 ?        00:00:00 /usr/libexec/gnome-session-failed --allow-logout
sachi     3014  2380  0 Jun26 ?        00:00:00 /usr/bin/seapplet
sachi     3029  2380  0 Jun26 ?        00:00:00 abrt-applet
root     13024   948  0 09:11 ?        00:00:00 sshd: sachi [priv]
sachi    13033 13024  0 09:11 ?        00:00:00 sshd: sachi@pts/0
sachi    13039 13033  0 09:11 pts/0    00:00:00 -bash
sachi    13355 13039  0 09:13 pts/0    00:00:00 psql
root     13358   948  0 09:13 ?        00:00:00 sshd: sachi [priv]
sachi    13363 13358  0 09:13 ?        00:00:00 sshd: sachi@pts/1
sachi    13370 13363  0 09:13 pts/1    00:00:00 -bash
sachi    21571 13370  0 10:02 pts/1    00:00:00 ps -ef
[sachi@localhost ~]$ 

Command to add an entry in the pg_hba.conf file of all the segments including master

1. Log into the master.
2. gpssh -f ~/gpconfigs/hostfile
3.$ps -ef | grep postgres | grep silent | grep -v grep | awk '{print $10}' | while read line ; do cp $line"/pg_hba.conf" $line"/pg_hba.conf.bk."$(date +"%m-%d-%Y-%H:%M:%S") ; done
4. ps -ef | grep postgres | grep silent | grep -v grep | awk '{print $10}' | while read line ; do echo "host all gpadmin 172.28.12.250/32 trust" >> $line"/pg_hba.conf" ; done
5.ps -ef | grep postgres | grep silent | grep -v grep | awk '{print $10}' | while read line ; do tail -1 $line/pg_hba.conf ; done
Comments