Useful PostgreSQL Queries by mr-anderson

View this thread on: hive.blog | peakd.com | ecency.com

technology · @mr-anderson · Jul 8 '17

Useful PostgreSQL Queries

<html>
<p><img src="http://68.media.tumblr.com/e71003674212b3d7c28b112a30b8b76b/tumblr_inline_nmydsoMUBy1qhx62z_250.jpg" width="174" height="180"/></p>
<p><br></p>
<p>-- idle queries</p>
<p>SELECT now()-xact_start, usename&nbsp;</p>
<p>FROM pg_stat_activity&nbsp;</p>
<p>WHERE current_query != '&lt;IDLE&gt;'&nbsp;</p>
<p>ODER BY xact_start asc&nbsp;</p>
<p>LIMIT 5;</p>
<p><br></p>
<p>-- show running queries (pre 9.2)</p>
<p>SELECT procpid, age(query_start, clock_timestamp()), usename, current_query&nbsp;</p>
<p>FROM pg_stat_activity&nbsp;</p>
<p>WHERE current_query != '&lt;IDLE&gt;' AND current_query NOT ILIKE '%pg_stat_activity%'&nbsp;</p>
<p>ORDER BY query_start desc;</p>
<p><br></p>
<p>-- show running queries (9.2)</p>
<p>SELECT pid, age(query_start, clock_timestamp()), usename, query&nbsp;</p>
<p>FROM pg_stat_activity&nbsp;</p>
<p>WHERE query != '&lt;IDLE&gt;' AND query NOT ILIKE '%pg_stat_activity%'&nbsp;</p>
<p>ORDER BY query_start desc;</p>
<p><br></p>
<p>-- kill running query</p>
<p>SELECT pg_cancel_backend(procpid);</p>
<p><br></p>
<p>-- kill idle query</p>
<p>SELECT pg_terminate_backend(procpid);</p>
<p><br></p>
<p>-- vacuum command</p>
<p>VACUUM (VERBOSE, ANALYZE);</p>
<p><br></p>
<p>-- all database users</p>
<p>select * from pg_stat_activity where current_query not like '&lt;%';</p>
<p><br></p>
<p>-- all databases and their sizes</p>
<p>select * from pg_user;</p>
<p><br></p>
<p>-- all tables and their size, with/without indexes</p>
<p>select datname, pg_size_pretty(pg_database_size(datname))</p>
<p>from pg_database</p>
<p>order by pg_database_size(datname) desc;</p>
<p><br></p>
<p>-- cache hit rates (should not be less than 0.99)</p>
<p>SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) &nbsp;as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio</p>
<p>FROM pg_statio_user_tables;</p>
<p><br></p>
<p>-- table index usage rates (should not be less than 0.99)</p>
<p>SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table</p>
<p>FROM pg_stat_user_tables&nbsp;</p>
<p>ORDER BY n_live_tup DESC;</p>
<p><br></p>
<p>-- how many indexes are in cache</p>
<p>SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) &nbsp;as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio</p>
<p>FROM pg_statio_user_indexes;</p>
<p><br></p>
<p>-- Dump database on remote host to file</p>
<p>$ pg_dump -U username -h hostname databasename &gt; dump.sql</p>
<p><br></p>
<p>-- Import dump into existing database</p>
<p>$ psql -d newdb -f dump.sql</p>
<p><br></p>
</html>

👍 mr-anderson, muhammadriky

`author`	mr-anderson
`permlink`	useful-postgresql-queries
`category`	technology
`json_metadata`	{"tags":["technology","programming","database"],"image":["http://68.media.tumblr.com/e71003674212b3d7c28b112a30b8b76b/tumblr_inline_nmydsoMUBy1qhx62z_250.jpg"],"app":"steemit/0.1","format":"html"}
`created`	2017-07-08 11:13:39
`last_update`	2017-07-08 11:13:39
`depth`	0
`children`	5
`last_payout`	2017-07-15 11:13:39
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	2,609
`author_reputation`	10,487,662,369
`root_title`	"Useful PostgreSQL Queries"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	7,741,722
`net_rshares`	212,038,052
`author_curate_reward`	""

properties (23)vote details (2)

voter	weight	wgt%	rshares	pct	time
mr-anderson	0 B		212,038,052	100%
muhammadriky	0 B		0	100%

@lightviz · Jul 8 '17

Hi, guys, Follow me and Upvote my posts in my blog and i will do the same thing!!!
![DQmTiCwPX5Eh6J9TrHZ9Aki1E8u7jM6gc2LqvycQ91JFrjQ.gif](https://steemitimages.com/DQmTiCwPX5Eh6J9TrHZ9Aki1E8u7jM6gc2LqvycQ91JFrjQ/DQmTiCwPX5Eh6J9TrHZ9Aki1E8u7jM6gc2LqvycQ91JFrjQ.gif)

👍 mr-anderson

`author`	lightviz
`permlink`	re-mr-anderson-useful-postgresql-queries-20170708t113021693z
`category`	technology
`json_metadata`	{"tags":["technology"],"image":["https://steemitimages.com/DQmTiCwPX5Eh6J9TrHZ9Aki1E8u7jM6gc2LqvycQ91JFrjQ/DQmTiCwPX5Eh6J9TrHZ9Aki1E8u7jM6gc2LqvycQ91JFrjQ.gif"],"app":"steemit/0.1"}
`created`	2017-07-08 11:32:54
`last_update`	2017-07-08 11:32:54
`depth`	1
`children`	1
`last_payout`	2017-07-15 11:32:54
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	264
`author_reputation`	5,029,332,800
`root_title`	"Useful PostgreSQL Queries"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	7,743,256
`net_rshares`	208,062,339
`author_curate_reward`	""

properties (23)vote details (1)

voter	weight	wgt%	rshares	pct	time
mr-anderson	0 B		208,062,339	100%

@mr-anderson · Jul 8 '17

Done )

👍 lightviz

`author`	mr-anderson
`permlink`	re-lightviz-re-mr-anderson-useful-postgresql-queries-20170708t115255223z
`category`	technology
`json_metadata`	{"tags":["technology"],"app":"steemit/0.1"}
`created`	2017-07-08 11:52:18
`last_update`	2017-07-08 11:52:18
`depth`	2
`children`	0
`last_payout`	2017-07-15 11:52:18
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	6
`author_reputation`	10,487,662,369
`root_title`	"Useful PostgreSQL Queries"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	7,744,780
`net_rshares`	533,912,901
`author_curate_reward`	""

properties (23)vote details (1)

voter	weight	wgt%	rshares	pct	time
lightviz	0 B		533,912,901	100%

@steemitboard · Aug 6 '17

Congratulations @mr-anderson! You have completed some achievement on Steemit and have been rewarded with new badge(s) :

[![](https://steemitimages.com/70x80/http://steemitboard.com/notifications/voted.png)](http://steemitboard.com/@mr-anderson) Award for the number of upvotes received

Click on any badge to view your own Board of Honor on SteemitBoard.
For more information about SteemitBoard, click [here](https://steemit.com/@steemitboard)

If you no longer want to receive notifications, reply to this comment with the word `STOP`

> By upvoting this notification, you can help all Steemit users. Learn how [here](https://steemit.com/steemitboard/@steemitboard/http-i-cubeupload-com-7ciqeo-png)!

properties (22)

`author`	steemitboard
`permlink`	steemitboard-notify-mr-anderson-20170806t080147000z
`category`	technology
`json_metadata`	{"image":["https://steemitboard.com/img/notifications.png"]}
`created`	2017-08-06 08:01:45
`last_update`	2017-08-06 08:01:45
`depth`	1
`children`	0
`last_payout`	2017-08-13 08:01:45
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	701
`author_reputation`	38,975,615,169,260
`root_title`	"Useful PostgreSQL Queries"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	10,920,942
`net_rshares`	0

@steemitboard · Aug 10 '17

Congratulations @mr-anderson! You have completed some achievement on Steemit and have been rewarded with new badge(s) :

[![](https://steemitimages.com/70x80/http://steemitboard.com/notifications/firstpayout.png)](http://steemitboard.com/@mr-anderson) You got your First payout

Click on any badge to view your own Board of Honor on SteemitBoard.
For more information about SteemitBoard, click [here](https://steemit.com/@steemitboard)

If you no longer want to receive notifications, reply to this comment with the word `STOP`

> By upvoting this notification, you can help all Steemit users. Learn how [here](https://steemit.com/steemitboard/@steemitboard/http-i-cubeupload-com-7ciqeo-png)!

properties (22)

`author`	steemitboard
`permlink`	steemitboard-notify-mr-anderson-20170810t154732000z
`category`	technology
`json_metadata`	{"image":["https://steemitboard.com/img/notifications.png"]}
`created`	2017-08-10 15:47:30
`last_update`	2017-08-10 15:47:30
`depth`	1
`children`	0
`last_payout`	2017-08-17 15:47:30
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	692
`author_reputation`	38,975,615,169,260
`root_title`	"Useful PostgreSQL Queries"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	11,409,795
`net_rshares`	0

@steemitboard · Aug 31 '17

Congratulations @mr-anderson! You have completed some achievement on Steemit and have been rewarded with new badge(s) :

[![](https://steemitimages.com/70x80/http://steemitboard.com/notifications/votes.png)](http://steemitboard.com/@mr-anderson) Award for the number of upvotes

Click on any badge to view your own Board of Honor on SteemitBoard.
For more information about SteemitBoard, click [here](https://steemit.com/@steemitboard)

If you no longer want to receive notifications, reply to this comment with the word `STOP`

> By upvoting this notification, you can help all Steemit users. Learn how [here](https://steemit.com/steemitboard/@steemitboard/http-i-cubeupload-com-7ciqeo-png)!

properties (22)