bookmarks/cheat/postgresql.md

Disable paging in postgresql

\pset pager off

Connect to a database

\c database_name

Show tables and sequences in current schema

\d

Describe a table

\d table_name

Show only tables with their size (+) in current schema

\dt+

Show databases with their privileges and size (+)

\l+

Show available schemas

\dn

Gets the size of a schema

SELECT pg_size_pretty(SUM(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::BIGINT) FROM pg_tables WHERE schemaname = 'schema_name'

Return back to shell

\q

List users and their access privileges on databases

\du

List default access privileges

\ddp

List tables, views and sequences with their associated access privileges

\dp

List of roles

SELECT rolname FROM pg_roles;

Detail of a role

SELECT * FROM pg_roles WHERE rolname = '...'

Which version of PostgreSQL am I running? src

If you're using CLI and you're a postgres user, then you can do this: psql -c "SELECT version();"

Creating a copy of a database in PostgreSQL.

# backup
pg_dumpall > db.out 
# restore 
# (might requires some DROP DATABASE xxx if you do want to replace existing data with same db and table names.
psql -f db.out postgres