PostgreSQL两三事

不要用Graphic Installer

至少在Ubuntu下,觉得原生的apt-get管理方式更合适,PG的文件资源会被分配到应该的地方,Linux的系统文件结构也是种非常稳健的架构。例如在/etc/postgresql下可以找到conf文件是一件清晰到爽的事情。正常通过apt安装的方式参见这篇博文

之前有一台VM就是没有忍住一时之快,用了Graphic Installer,所有的东西被塞到/opt/PostgreSQL/x.x中,当然,conf、bin等文件都在其中。在没有手动配置的情况下,start/stop/restart/reload之类的事情,不得不交给pg_ctl做,而与postgres账户之间的来回切换,也会浪费宝贵的时间。例如重启PG需要

而出现冲突的时候还会需要忍不住 -m fast 一下。而如果使用系统服务,至少可以这样:

修改postgresql.conf

数据库系统参数是很重要的功能,根据应用特点进行性能调优时,往往需要用到。目前用到的几个参数里,shared_buffers一般会尽量设置大一些,有人建议设为RAM的10%,其实我觉得更大一些也没什么问题。tcp_keepalives_idle作为秒数表示空闲时间间隔,当一个tcp连接持续该时间闲置,db会发送tcp_keeplive包给客户端,若连续tcp_keepalives_count个包都在tcp_keepalives_interval秒内没有回应,则会认为这个tcp已死。

修改postgresql.conf后可以通过 select pg_reload_conf();重新加载配置。但是配置里有些是支持动态的,而有些必须要重启db,例如shared_buffers就如此,悲催。重加载或重启后,可通过show <配置项>命令查看当前已生效的配置项值,例如:

查看当前的服务器状态

可调用PG自带的一个视图:

这个视图可以查出目前的连接,以及各自连接的状态、时间点、SQL内容等,视图内容:

当然,只要不是很老的PG版本,可以用pg_terminate_backend来终止相应的会话,这篇文章例举了更多的使用场景。例如:

终止某个用户的会话:

如果觉得terminate太暴力,还可以使用pg_cancel_backend。

76 thoughts on “PostgreSQL两三事

  1. my site说道:

    I just want to mention I am new to weblog and absolutely liked you’re web page. Likely I’m want to bookmark your blog . You absolutely have very good articles and reviews. With thanks for revealing your website page.

  2. pron best说道:

    qbHYVk Wow, superb weblog structure! How long have you been running a blog for? you make blogging look easy. The overall look of your site is great, let alone the content material!

  3. this site说道:

    LzrFrU This awesome blog is definitely interesting and also factual. I have found many handy stuff out of this source. I ad love to come back over and over again. Cheers!

  4. pron best说道:

    kkcVfg Your style is really unique in comparison to other folks I have read stuff from. Thank you for posting when you ave got the opportunity, Guess I all just book mark this page.

  5. Very good blog article.Really thank you! Want more.

  6. Wow, awesome blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your site is wonderful, let alone the content!. Thanks For Your article about sex.

  7. I will certainly digg it and personally recommend to my friends.

  8. hungrymate说道:

    This very blog is definitely entertaining and besides diverting. I have picked up a bunch of handy things out of this source. I ad love to visit it over and over again. Thanks!

  9. that has been a long time coming. It will strengthen the viability

  10. If you are ready to watch comic videos on the internet then I suggest you to go to see this web site, it consists of really therefore comical not only videos but also additional material.

  11. I really liked your article.Thanks Again. Much obliged.

  12. Say, you got a nice article.Much thanks again. Want more.

  13. free app for pc说道:

    Wonderful work! This is the type of info that should be shared around the web. Shame on Google for not positioning this post higher! Come on over and visit my web site. Thanks =)

  14. you offer guest writers to write content for you?

  15. If some one needs expert view about running a blog afterward i recommend him/her to go to see this weblog, Keep up the pleasant work.

  16. I truly appreciate this blog post. Keep writing.

  17. worthy info说道:

    There is definately a great deal to find out about this topic. I love all the points you made.

  18. chaturbate说道:

    Thanks again for the blog article.Thanks Again. Keep writing.

  19. Thanks a lot for the article post.Really looking forward to read more. Will read on…

  20. شهرزاد说道:

    A round of applause for your blog.Really looking forward to read more. Really Cool.

  21. Really informative blog article. Will read on…

  22. You have brought up a very great points, thanks for the post.

  23. I think this is a real great blog post.Much thanks again. Really Cool.

  24. Really enjoyed this article. Will read on…

  25. پنجره说道:

    Thanks for the post.Really looking forward to read more. Will read on

  26. Thanks-a-mundo for the blog.Thanks Again.

  27. incredibly nice post, i unquestionably genuinely like this website, retain on it

  28. Thanks so much for the article.Really looking forward to read more. Keep writing.

  29. wow, awesome blog article. Much obliged.

  30. Great, thanks for sharing this blog post.Thanks Again. Really Cool.

  31. Well, one of the worst toxic offenders have already been

  32. Visit this I was suggested this web site by my cousin. I am not sure whether this post is written by him as no one else know such detailed about my problem. You are wonderful! Thanks!

  33. whoah this blog is excellent i love reading your articles. Keep up the good work! You know, a lot of people are looking around for this information, you can aid them greatly.

  34. Gab Alternative说道:

    Thanks again for the article post.Really looking forward to read more. Really Cool.

  35. Cam4说道:

    Looking forward to reading more. Great article post.Thanks Again. Keep writing.

  36. Cam dolls说道:

    Some really good content on this site, appreciate it for contribution.

  37. sexcamly说道:

    Usually I don at learn post on blogs, but I wish to say that this write-up very forced me to check out and do so! Your writing style has been amazed me. Thanks, very nice post.

  38. This is my first time pay a visit at here and i am really pleassant to read all at one place.

  39. bitcoin说道:

    tout est dans la formation video ! < Liked it!

  40. wow, awesome blog.Much thanks again. Will read on…

  41. There is apparently a bunch to realize about this. I believe you made certain nice points in features also.

  42. Really appreciate you sharing this blog article.Thanks Again.

  43. prediksi bola说道:

    wow, awesome blog.Thanks Again.

  44. Thanks again for the blog article.Thanks Again. Great.

  45. Very Fascinating Weblog! Thank You For This Blog!

  46. Really enjoyed this blog article.Really thank you! Really Cool.

  47. Saved as a favorite, I really like your blog!

  48. You might have an extremely good layout for the blog i want it to work with on my internet site too

  49. You made some decent factors there. I regarded on the internet for the difficulty and located most individuals will associate with together with your website.

  50. I truly appreciate this blog.Much thanks again. Really Great.

  51. دانلود说道:

    You can certainly see your skills in the work you write. The sector hopes for more passionate writers such as you who are not afraid to mention how they believe. At all times follow your heart.

  52. Only a smiling visitant here to share the love (:, btw outstanding pattern. Make the most of your regrets. To regret deeply is to live afresh. by Henry David Thoreau.

  53. Secret Flask说道:

    This particular blog is without a doubt awesome additionally informative. I have picked up a lot of helpful tips out of this source. I ad love to come back again soon. Thanks a lot!

  54. I went over this website and I believe you have a lot of wonderful info , saved to my bookmarks (:.

  55. 迷你仓比较说道:

    Thank you for your article post.Thanks Again. Great.

  56. It as hard to find well-informed people about this topic, but you sound like you know what you are talking about! Thanks

  57. Treinkaartjes说道:

    Really informative blog article.Really looking forward to read more. Want more.

  58. Trein说道:

    You made some first rate points there. I looked on the internet for the issue and found most people will go together with along with your website.

  59. of course we of course we need to know our family history so that we can share it to our kids a

  60. watch out for brussels. I all appreciate if you continue this in future.

  61. This page truly has all the info I needed concerning this subject and didn at know who to ask.

  62. Thank you a bunch for sharing this with all of us you really understand

  63. Thank you for sharing this very good article. Very interesting ideas! (as always, btw)

  64. Muchos Gracias for your article.Really looking forward to read more. Will read on

  65. This blog is without a doubt educating and besides amusing. I have found a bunch of handy stuff out of this source. I ad love to come back again soon. Thanks a lot!

  66. http://boilx.us/说道:

    Some truly prize blog posts on this internet site , saved to favorites.

  67. This site really has all of the information I needed about this subject and didn at know who to ask.

  68. own blog? Any help would be really appreciated!

  69. sextus.mobi说道:

    wonderful post, very informative. I wonder why the other specialists of this sector don at realize this. You must proceed your writing. I am sure, you ave a great readers a base already!

  70. adult说道:

    I truly appreciate this blog article.Really thank you! Cool.

  71. I truly appreciate this blog post.Much thanks again. Will read on

  72. link m88说道:

    Wow, marvelous blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your web site is great, as well as the content!

  73. It is pretty worth enough for me. In my view, if all

  74. chung cu tecco说道:

    need, and just what the gaming trade can supply. Today, these kinds of types

发表评论

电子邮件地址不会被公开。