Useful PostgreSQL Command

List of database-

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-

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 as blocked_pid, a.usename as blocked_user, 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 = a.procpid
join pg_catalog.pg_locks kl
join pg_catalog.pg_stat_activity ka
on = ka.procpid
on bl.transactionid = kl.transactionid and !=
where not bl.granted;

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s