Month: October 2022

No comments

MySQL and PostgreSQL Equivalent Commands

  By , ,

In this blog post, I’ve associated several PostgreSQL commands with what I know in MySQL. Below is a table of some of the frequently used commands.

MySQLPostgreSQLDescription
mysqldumppg_dumpallextract a PostgreSQL database cluster into a script file
mysqldumppg_dumpextract a PostgreSQL database into a script file or other archive file
N/Apg_restorerestore a PostgreSQL database from an archive file created by pg_dump
N/Apg_toppg_top is a PostgreSQL interactive monitoring utility, similar to the Unix top utility, providing a dynamically updated view of activity within a PostgreSQL database
mysqlpsqlPostgreSQL interactive terminal
Exit Mysql
{quit|exit|
[ctrl-c]}		Exit Postgres\qquit psql,
show databases;\l or \listView all of the defined databases on the server
use [dbname];\c [dbname]Connect to a database
show tables;\dt or \dt+List all the tables in the current schema
describe [tablename];\d [tablename]Shows the columns, types, modifiers, indexes, and tables referenced by keys.
show create table [tablename];No direct equivalent, use below command from shell:
pg_dump -st tablename dbname		This will provide the sql used to create a table.
select * from mysql.user;select * from pg_user;
\du		Lists all database roles
show full processlist;select * from pg_stat_activity;Show what queries are currently running in the DB
show variables;show all;Displays the current setting of run-time parameters
show engine innodb status\GSELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_database;
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_user_indexes;
SELECT * FROM pg_locks;		There is no central place in PostgreSQL to get all of the information obtained by running show engine InnoDB status in MySQL. However, there are a number of queries you can run to get roughly equivalent data.
show slave status\Gselect * from pg_stat_replication;
select now() – pg_last_xact_replay_timestamp() AS replication_delay;		 Monitor replication 
\G\xSets or toggles expanded table formatting mode

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

PostgreSQL – Get The Top 10 Time-Consuming Queries in AWS RDS PostgreSQL and TencentDB for PostgreSQL

  By ,

Here is a script to get the Top 10 Time-Consuming Queries (This will work for PostgreSQL version 13 and above)

We will make use of the pg_stat_statements view. This view will tell us which types of queries are slow and how often these queries are called.

In AWS RDS, this module is already installed by default. So, all you need to do is to create the extension before running the script. 

create extension pg_stat_statements;

In Tencent DB for PostgreSQL, both module and the extension are enabled by default. So, there is no need to do anything before running the query.

Disable expanded display

\x

Script to get Top 10 Time Consuming Queries

SELECT 
  t2.rolname, 
  t3.datname, 
  queryid, 
  calls, 
  (
    total_plan_time + total_exec_time
  ) / 1000 as total_time_seconds, 
  (min_plan_time + min_exec_time) / 1000 as min_time_seconds, 
  (max_plan_time + max_exec_time) / 1000 as max_time_seconds, 
  (mean_plan_time + mean_exec_time) / 1000 as mean_time_seconds, 
  (
    stddev_plan_time + stddev_exec_time
  ) / 1000 as stddev_time_seconds, 
  rows, 
  shared_blks_hit, 
  shared_blks_read, 
  shared_blks_dirtied, 
  shared_blks_written, 
  local_blks_hit, 
  local_blks_read, 
  local_blks_dirtied, 
  local_blks_written, 
  temp_blks_read, 
  temp_blks_written, 
  blk_read_time / 1000 as blk_read_time_seconds, 
  blk_write_time / 1000 as blk_write_time_seconds 
FROM 
  pg_stat_statements t1 
  JOIN pg_roles t2 ON (t1.userid = t2.oid) 
  JOIN pg_database t3 ON (t1.dbid = t3.oid) 
WHERE 
  t2.rolname != 'rdsadmin' 
  AND queryid IS NOT NULL;

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter