#postgres #cheatsheet

postgres.md

• cheatsheet link

• handy command link

• sql query format link

Errors

• ERROR: must be member of role "test"

https://stackoverflow.com/questions/26684643/error-must-be-member-of-role-when-creating-schema-in-postgresql

• +2 minutes queries:

SELECT now() - query_start as "runtime", usename, datname, waiting, state, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '2 minutes'::interval
 ORDER BY runtime DESC;

• Check table sizes:

select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')
from information_schema.tables
order by 3;

• Create db and grant privilege to user:

CREATE DATABASE <db-name>;
CREATE USER <user-name> WITH PASSWORD <password>;
GRANT ALL PRIVILEGES ON DATABASE <db-name> to <user-name>;

Access psql:

psql -U <username> -d <dbname> -h <host>

you must use the same password for postgres master/slave DBs

• Backup & Restore. Reference:

# backup
pg_dump -U $username --format=c --file=$mydatabase.sqlc $dbname

# dump without owner & privileges
pg_dump -U $username --format=c --file=$mydatabase.sqlc -O -x $dbname

# -O, --no-owner               skip restoration of object ownership in plain-text format
# -x, --no-privileges          do not dump privileges (grant/revoke)
---

# restore only a specified index from a backup file:
pg_restore -U $username --dbname=$dbname --index=$indexname

# restore only a single function:
pg_restore -U $username --dbname=$dbname --function=$functionname(args)

# restore only a single table:
pg_restore -U $username --dbname=$dbname --table=$tablename

# simple restore:
pg_restore -U$username --dbname=$databasename $filename

• Query all postgres settings:

SELECT name,setting,unit FROM pg_settings;

• Create new schema and grant privileges:

CREATE USER abc WITH PASSWORD 'ks839#@S';
CREATE SCHEMA abc;
GRANT ALL ON ALL TABLES IN SCHEMA abc TO abc;
ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT ALL ON TABLES TO abc;
GRANT ALL PRIVILEGES ON SCHEMA abc TO abc;

• Read-only user:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO abc;
GRANT USAGE ON SCHEMA public TO abc;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO abc;