Postgres Database Setup/Upgrade and Start/Stop Cheatsheet

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

  1. First Change the “pg_hba.conf” file to do all trust method
  2. Stop the database server
  3. Start the database server
  4. Login as below: 
    psql -U postgres -W  -h localhost
  5. If you are asked for password, just type “Enter” – Now you are logged in.
  6. Change Password as follows: 
     ALTER ROLE postgres WITH PASSWORD ‘?????’;
  7. Now change back your “pg_hba.conf” file to the original so that the password can be applied
  8. Stop the database
  9. Start the database

    10. Now use your new password and it should work

Creating a new User in database

Execute following command

createuser --interactive "username"

This will give options for follow: 

Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y

Selecting Activity in database

select pid, usename, query_start, state, query from pg_stat_activity;

Upgrading the Postgresql to 9.5 version


wget https://alioth.debian.org/scm/loggerhead/pkg-postgresql/postgresql-common/trunk/download/head:/apt.postgresql.org.s-20130224224205-px3qyst90b3xp8zj-1/apt.postgresql.org.sh
chmod a+x apt.postgresql.org.sh
sed -i -e 's/$CODENAME-pgdg main/$CODENAME-pgdg main 9.5/g' apt.postgresql.org.sh
sudo ./apt.postgresql.org.sh

sudo apt-get install postgresql-9.5
sudo apt-get install postgresql-client-9.5
sudo apt-get install postgresql-contrib-9.5

 

Canceling a query in db

select pg_cancel_backend(pid)select pg_terminate_backend(pid)

Extract data as CVS:

copying reports table.

\copy reports TO 'reports_data_652014.csv' CSV header
 
SELECT rename, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 0
ORDER BY n_live_tup DESC;

postgresql 9.5 psql peer authentication failed

he problem is still your pg_hba.conf file (/etc/postgresql/9.1/main/pg_hba.conf). This line:

local all postgres peer
Should be

local all postgres trust

Helpful Links:

    1. Understanding How PostgreSQL Executes a Query

http://etutorials.org/SQL/Postgresql/Part+I+General+PostgreSQL+Use/Chapter+4.+Performance/Understanding+How+PostgreSQL+Executes+a+Query/

    1. PostgreSQL Hardware Performance Tuning

# http://momjian.us/main/writings/pgsql/hw_performance/

 

http://www.postgresql.org/docs/9.1/static/server-start.html