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;

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

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

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…