{"id":489,"date":"2014-09-08T16:18:45","date_gmt":"2014-09-08T16:18:45","guid":{"rendered":"http:\/\/shirishranjit.com\/blog1\/?page_id=489"},"modified":"2016-03-20T22:48:44","modified_gmt":"2016-03-21T02:48:44","slug":"postgres-database-cheatsheet","status":"publish","type":"page","link":"https:\/\/shirishranjit.com\/blog1\/technical-posts\/postgres-database-cheatsheet","title":{"rendered":"Postgres Database Setup\/Upgrade and Start\/Stop Cheatsheet"},"content":{"rendered":"

starting<\/h2>\n
\r\n\/usr\/pgsql-9.4\/bin\/pg_ctl -D \/var\/lib\/pgsql\/9.4\/data\/ -l logfile start\r\n<\/pre>\n
stopping<\/h2>\n
\r\n\/usr\/pgsql-9.4\/bin\/pg_ctl stop -m fast\r\n<\/pre>\n
Logging into postgres<\/h2>\n
sudo -i -u postgres psql\r\n<\/code><\/pre>\n
Resetting Password for postgres <\/h2>\n
    \n
  1. First Change the “pg_hba.conf” file to do all trust method<\/li>\n
  2. Stop the database server<\/li>\n
  3. Start the database server<\/li>\n
  4. Login as below:\n
    \r\npsql -U postgres -W  -h localhost\r\n<\/pre>\n<\/li>\n
  5. If you are asked for password, just type “Enter” – Now you are logged in.<\/li>\n
  6. Change Password as follows:\n
     ALTER ROLE postgres WITH PASSWORD \u2018?????\u2019; <\/pre>\n<\/li>\n
  7. Now change back your “pg_hba.conf” file to the original so that the password can be applied<\/li>\n
  8. Stop the database<\/li>\n
  9. Start the database<\/li>\n
    Now use your new password and it should work<\/p>\n<\/ol>\n
     Creating a new User in database <\/h2>\n
    Execute following command<\/p>\n
    createuser --interactive \"username\" <\/pre>\n
    This will give options for follow: <\/p>\n
    \r\nShall the new role be a superuser? (y\/n) n\r\nShall the new role be allowed to create databases? (y\/n) y\r\nShall the new role be allowed to create more new roles? (y\/n) y\r\n<\/pre>\n
    Selecting Activity in database<\/p>\n
    \n
    \n
    \n
    \n\n\n\n
    \n
    \n
    select<\/code> pid, usename, query_start, state, query <\/code>from<\/code> pg_stat_activity;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
    Upgrading the Postgresql to 9.5 version<\/h2>\n
    \r\n\r\nwget https:\/\/alioth.debian.org\/scm\/loggerhead\/pkg-postgresql\/postgresql-common\/trunk\/download\/head:\/apt.postgresql.org.s-20130224224205-px3qyst90b3xp8zj-1\/apt.postgresql.org.sh\r\nchmod a+x apt.postgresql.org.sh\r\nsed -i -e 's\/$CODENAME-pgdg main\/$CODENAME-pgdg main 9.5\/g' apt.postgresql.org.sh\r\nsudo .\/apt.postgresql.org.sh\r\n\r\nsudo apt-get install postgresql-9.5\r\nsudo apt-get install postgresql-client-9.5\r\nsudo apt-get install postgresql-contrib-9.5\r\n\r\n<\/code>\r\n<\/pre>\n
     <\/p>\n
    Canceling a query in db<\/p>\n
    \n
    \n
    \n
    \n\n\n\n
    \n
    \n
    select<\/code> pg_cancel_backend(pid)<\/code>select<\/code> pg_terminate_backend(pid)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
    Extract data as CVS:<\/h2>\n
    copying reports table.<\/p>\n
    \n
    \n
    \n
    \n\n\n\n
    \n
    \n
    \\copy reports <\/code>TO<\/code> 'reports_data_652014.csv'<\/code> CSV header<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
    \n
    \r\nSELECT rename, 100 * idx_scan \/ (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table\r\nFROM pg_stat_user_tables\r\nWHERE seq_scan + idx_scan > 0\r\nORDER BY n_live_tup DESC;\r\n<\/pre>\n<\/div>\n
    postgresql 9.5 psql peer authentication failed<\/h2>\n
    he problem is still your pg_hba.conf file (\/etc\/postgresql\/9.1\/main\/pg_hba.conf). This line:<\/p>\n
    local   all             postgres                                peer
    \nShould be<\/p>\n
    local   all             postgres                                trust<\/p>\n
    Helpful Links:<\/h2>\n
      \n
        \n
      1. Understanding How PostgreSQL Executes a Query<\/strong><\/li>\n<\/ol>\n<\/ol>\n
        http:\/\/etutorials.org\/SQL\/Postgresql\/Part+I+General+PostgreSQL+Use\/Chapter+4.+Performance\/Understanding+How+PostgreSQL+Executes+a+Query\/<\/a><\/p>\n
          \n
            \n
          1. PostgreSQL Hardware Performance Tuning<\/strong><\/li>\n<\/ol>\n<\/ol>\n
            #\u00a0http:\/\/momjian.us\/main\/writings\/pgsql\/hw_performance\/<\/a><\/p>\n
             <\/p>\n
            http:\/\/www.postgresql.org\/docs\/9.1\/static\/server-start.html<\/a><\/p>\n
            \n\t\t\t\t\tTweet<\/a>\n\t\t\t\t<\/div>
            \n\t\t\t\t\t\tFollow me<\/a>\n\t\t\t\t\t<\/div><\/div>","protected":false},"excerpt":{"rendered":"
            starting \/usr\/pgsql-9.4\/bin\/pg_ctl -D \/var\/lib\/pgsql\/9.4\/data\/ -l logfile start stopping \/usr\/pgsql-9.4\/bin\/pg_ctl stop -m fast Logging into postgres sudo -i -u postgres psql Resetting Password for postgres First Change the “pg_hba.conf” file to do all trust method Stop the database server Start the … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":198,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-489","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/pages\/489"}],"collection":[{"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/comments?post=489"}],"version-history":[{"count":23,"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/pages\/489\/revisions"}],"predecessor-version":[{"id":1236,"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/pages\/489\/revisions\/1236"}],"up":[{"embeddable":true,"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/pages\/198"}],"wp:attachment":[{"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/media?parent=489"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}