postgresql one liners

Home

Change to postgres user and open psql prompt

sudo -u postgres psql postgres

List databases

postgres=# \l

List roles

postgres=# \du

Create role

postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD 'password1' CREATEDB;

Create role with multiple privileges

postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD

postgres=# 'password1' CREATEDB CREATEROLE REPLICATION SUPERUSER;

Alter role

postgres=# ALTER ROLE demorole1 CREATEROLE CREATEDB REPLICATION SUPERUSER;

Drop role

postgres=# DROP ROLE demorole1;

Create database

postgres=# CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING 'UTF8';

Grant privileges to new user

postgres=# GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole1;

Drop database

postgres=# DROP DATABASE demodb1;

connect to database

postgres=# \c <databasename>

List tables in connected database

postgres=# \dt

List columns on table

postgres=# \d <tablename>

Backup database

# pg_dump <databasename> > <outfile>

Useful Views

pg_stat_activity (currently executing queries):

select * from pg_stat_activity where current_query not like '<%';

pg_user (all database users):

select * from pg_user;

pg_database (all databases and their sizes):

select datname,

pg_size_pretty(pg_database_size(datname))

from pg_database

order by pg_database_size(datname) desc;

pg_tables (all tables and their size, with/without indexes):

select tablename, pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as size, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size from pg_tables  where schemaname = 'MY_SCHEMA_NAME';

pg_namespace (all schemas):

select * from pg_namespace order by 1;

Reference:- 

http://blog.jasonmeridth.com/posts/postgresql-command-line-cheat-sheet/

Back To Top
Home