Postgresql locks

Locks in postgres

Find locks

select pid, state, usename, query, query_start 
from pg_stat_activity 
where pid in (
  select pid from pg_locks l 
  join pg_class t on l.relation = t.oid 
  and t.relkind = 'r' 
  where t.relname = 'search_hit'
);

Killing locks

SELECT pg_cancel_backend(PID);

How to calculate the MySQL database size

Connect to mysql and run the command bellow

# total db size
SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

# total per db size
SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "schema_name";

MySQL backup in time

Run mysqldump on master database (needs to have innodb)

# replication point in time
mysqldump --single-transaction --flush-logs \
--master-data=2 --all-databases > backup.sql

# Note the position and the log file from the backup.sql and insert it into the slave</>

shell# mysql -u USER -p PASS
mysql> stop slave;
shell# mysql < backup.sql
shell# mysql -u USER -p PASS 
mysql> CHANGE MASTER TO MASTER_LOG_FILE='the_log_file_written_into_dump',
mysql> MASTER_LOG_POS = xxx ;
mysql> start slave;