How to know table fragmentation in postgresql database


psql -e -f /usr/share/postgresql/8.3/contrib/pgstattuple.sql $MYDB

psql $MYDB -c "CREATE TEMP TABLE fragmentation_stats(table_name text, tuple_count integer, tuple_len integer, tuple_percent float, dead_tuple_count integer, dead_tuple_len integer, dead_tuple_percent float, free_space integer, free_percent float);"

for i in `psql $MYDB -qt -c "select tablename from pg_tables where schemaname = '$MYSCHEMA' order by tablename;"`;do
echo $i;
psql $MYDB -c "insert into fragmentation_stats (select $i, pg_size_pretty(table_len), tuple_count, tuple_len, tuple_percent, dead_tuple_count, dead_tuple_len, dead_tuple_percent, pg_size_pretty(free_space), free_percent from pgstattuple('$i'));"
done

psql $MYDB -c "DROP TABLE fragmentation_stats;"


Bash list separator IFS

If you want to change it to \n:

IFS=$'\n'


Change PATH and LD_PATH for the system

for libs:
add a file in folder: /etc/ld.so.conf.d/

for path:
edit /etc/profile

How to know table / index size

SELECT pg_size_pretty(pg_relation_size('name'));

Find duplicate in a database


SELECT my_key, count(*) FROM my_table GROUP BY my_key HAVING count(*) > 1;


Insert timestamps with a loop

This sql command allow you to test data writting in your database. It can be useful to benchmark your disk performances or check the impact of a lock.


INSERT INTO table SELECT timeofday()::timestamp FROM generate_series(1,1000000) WHERE pg_sleep(1) IS NOT NULL;


Draw a rectangle on an image

convert -draw “rectangle x0 y0 x1 y1″ -fill none -stroke red -strokewidth 5

Create an iso from a directory

genisoimage -R -r -J -joliet-long --hide-rr-moved -iso-level 4 -V "Label" -o label_name.iso /tmp/directoryToSave

Install VMWare on Ubuntu

At first install those package:

apt-get install linux-headers-`uname -r` libx11-6 libx11-dev xspecs libxtst6 psmisc build-essential

Then download binaries on http://www.vmware.com/download/server/ (server & mui tgz source).

During the download, get a license number (http://register.vmware.com/content/registration.html) it’s totally free!

If you use kernel 2.6.27 please launch:
wget -c http://www.insecure.ws/warehouse/vmware-update-2.6.27-5.5.7-2.tar.gz

Then launch ./vmware-install.pl on each directory (update&mui or install&mui).

At the end of the mui installation you will get a starting failed message, to correct this bug please change in /etc/init.d/httpd.vmware file:

start)
vmware_exec "Starting httpd.vmware:" vmware_start_httpd
;;
stop)
vmware_exec "Shutting down http.vmware: " vmware_stop_httpd
;;

by

start)
if [ ! -d /var/run/vmware/httpd ]
then
echo "Directory: var/run/vmware/httpd Not found. Creating it."
mkdir /var/run/vmware/httpd
echo "Setting user and group ownership to: User: www-data, Group: nogroup"
chown www-data:nogroup /var/run/vmware/httpd
echo "Setting directory permissions to: RWX------ (700)"
chmod 700 /var/run/vmware/httpd
fi
echo "Starting httpd.vmware:"
vmware_start_httpd
;;
stop)
echo "Shutting down http.vmware: "
vmware_stop_httpd
;;


If you use Intrepid version and can’t launch vmware console please dl patch available at this page: http://www.monarialx.it/en/vmwareserver

Add a password access to a directory of your website

At first you need to create a file containing your password

htpasswd -c /var/www/itbdc/.htpasswd itbdc

Then create a file in your website: /var/www/itbdc/.htaccess

AuthType Basic
AuthUserFile /var/www/itbdc/.htpasswd
AuthGroupFile /dev/null
AuthName EnterPassword

require user itbdc
require valid-user

Then edit your httpd.conf and check those two lines:

AccessFileName .htaccess
<Directory>
	[...]
    AllowOverride All
	[...]
</Directory>

/etc/init.d/httpd restart