Postgres Database Setup/Upgrade and Start/Stop Cheatsheet


/usr/pgsql-9.4/bin/pg_ctl -D /var/lib/pgsql/9.4/data/ -l logfile start


/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

chmod a+x
sed -i -e 's/$CODENAME-pgdg main/$CODENAME-pgdg main 9.5/g'
sudo ./

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

    1. PostgreSQL Hardware Performance Tuning