DB-Server

Getting replication lag in PostgreSQL

Here a 2 simple queries to get the replication lag on Postgres servers: select now() – pg_last_xact_replay_timestamp() as lag; select pg_last_xlog_receive_location() receive, pg_last_xlog_replay_location() replay, ( extract(epoch FROM now()) – extract(epoch FROM pg_last_xact_replay_timestamp()) )::int lag;

DB-Server

Installing Postgres 9.4.5 on Ubuntu Trusty

By default on Ubuntu Trusty there is only Postgres 9.3 available This shows how to get the latest version installed. Add the Postgres Repository: echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" > /etc/apt/sources.list.d/postgres.list Import the repository key, and run an update: wget https://www.postgresql.org/media/keys/ACCC4CF8.asc apt-key add ACCC4CF8.asc apt-get update Install Postgres: apt-get install postgresql-9.4

DB-Server

InnoDB Error Table mysql.innodb_table_stats not found

Follow these steps to recover from this innodb error "InnoDB: Error: Table mysql.innodb_table_stats not found.". First try to drop these tables from a mysql console: USE mysql; DROP TABLE innodb_index_stats; DROP TABLE innodb_table_stats; DROP TABLE slave_master_info; DROP TABLE slave_relay_log_info; DROP TABLE slave_worker_info; This may throw some errors. If your mysql data folder Read more…

DB-Server

Installing mysql 5.6 on Debian Wheezy

Add the Repo: wget http://repo.mysql.com/mysql-apt-config_0.2.1-1debian7_all.deb dpkg -i mysql-apt-config_0.2.1-1debian7_all.deb The above package will create a file /etc/apt/sources.list.d/mysql.list which will contain lines like these or similar: deb http://repo.mysql.com/apt/debian/ wheezy mysql-5.6 deb-src http://repo.mysql.com/apt/debian/ wheezy mysql-5.6 Install mysql 5.6: apt-get update apt-get install mysql-server-5.6

DB-Server

MySQL Query to calculate DB and Table sizes

Calculate sizes of all your databases in MB: SELECT table_schema "DB Name", SUM( data_length + index_length) / 1024 / 1024 "DB Size" FROM information_schema.TABLES GROUP BY table_schema; Calculate table sizes for a specific database: SELECT TABLE_NAME, table_rows, data_length, index_length,  round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM Read more…