List of database-
\l

Backup database-
pg_dump -U geekstuff erp -f mydb.sql

Restore database-
psql -U erp -d erp_devel -f mydb.sql

Change owner of database-
ALTER DATABASE erp_v1 OWNER TO openerp;

Describing table-
\d table_name;

Delete content of file without deleting the file
echo -n > FILE_NAME

Export output of query to a file-
1. Login to postgres

psql -d dbname1 -U user1

2. Set the output to a file

\o /home/administrator/QueryOutput.txt

3. Fire the query

select * from company1 where salary > 50000;

Check if table is locked-
select bl.pid as blocked_pid, a.usename as blocked_user,
kl.pid as blocking_pid, ka.usename as blocking_user, a.current_query as blocked_statement
from pg_catalog.pg_locks bl
join pg_catalog.pg_stat_activity a
on bl.pid = a.procpid
join pg_catalog.pg_locks kl
join pg_catalog.pg_stat_activity ka
on kl.pid = ka.procpid
on bl.transactionid = kl.transactionid and bl.pid != kl.pid
where not bl.granted;

Finding 10 largest files in a given location
du -ah /path_to_folder | sort -n -r | head -n 10

Advertisements