Docs https://django-tenants.readthedocs.io/en/latest/use.html#all-tenants-command python manage.py makemigrations python manage.py migrate python manage.py create_tenant python manage.py createsuperuser python manage.py create_tenant_superuser #Commands for initialijing departments For All Tenents python manage.py all_tenants_command init_departments institute/data/department_list.csv For Single Tenent python manage.py tenant_command init_departments institute/data/department_list.csv --schema=gfg #Postgress sudo -i -u postgres psql # postgres data dump command pg_dump -U root -W -F p gfg_erp > /var/www/30_05_24_data.sql # get the file from server scp -r root@172.105.62.40:/var/www/30_05_24_data.sql . # dump the data in your local psql -U postgres -d target_database_name -f /path/to/your/sqlfile.sql username/password : DATABASES = { 'default': { 'ENGINE': 'django_tenants.postgresql_backend', 'NAME': 'gfg_erp', 'USER': 'root', 'PASSWORD': 'password', 'HOST': 'localhost', 'PORT': '5432' } } \l - Display database \c - Connect to database \dn - List schemas \dt - List tables inside public schemas \dt schema1.* - List tables inside a particular schema. For example: 'schema1'. Magic words: psql -U postgres Some interesting flags (to see all, use -h or --help depending on your psql version): -E: will describe the underlaying queries of the \ commands (cool for learning!) -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS) Most \d commands support additional param of __schema__.name__ and accept wildcards like *.* \?: Show help (list of available commands with an explanation) \q: Quit/Exit \c __database__: Connect to a database \d __table__: Show table definition (columns, etc.) including triggers \d+ __table__: More detailed table definition including description and physical disk size \l: List databases \dy: List events \df: List functions \di: List indexes \dn: List schemas \dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones) \dT+: List all data types \dv: List views \dx: List all extensions installed \df+ __function__ : Show function SQL code. \x: Pretty-format query results instead of the not-so-useful ASCII tables \copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV \des+: List all foreign servers \dE[S+]: List all foreign tables \! __bash_command__: execute __bash_command__ (e.g. \! ls) User Related: \du: List users \du __username__: List a username if present. create role __test1__: Create a role with an existing username. create role __test2__ noinherit login password __passsword__;: Create a role with username and password. set role __test__;: Change role for current session to __test__. grant __test2__ to __test1__;: Allow __test1__ to set its role as __test2__. \deu+: List all user mapping on server Configuration Service management commands: sudo service postgresql stop sudo service postgresql start sudo service postgresql restart Changing verbosity & querying Postgres log: 1) First edit the config file, set a decent verbosity, save and restart postgres: sudo vim /etc/postgresql/9.3/main/postgresql.conf # Uncomment/Change inside: log_min_messages = debug5 log_min_error_statement = debug5 log_min_duration_statement = -1 sudo service postgresql restart Now you will get tons of details of every statement, error, and even background tasks like VACUUMs tail -f /var/log/postgresql/postgresql-9.3-main.log How to add user who executed a PG statement to log (editing postgresql.conf): log_line_prefix = '%t %u %d %a ' Check Extensions enabled in postgres: SELECT * FROM pg_extension; Show available extensions: SELECT * FROM pg_available_extension_versions; Create command There are many CREATE choices, like CREATE DATABASE __database_name__, CREATE TABLE __table_name__ ... Parameters differ but can be checked at the official documentation. Handy queries SELECT * FROM pg_proc WHERE proname='__procedurename__': List procedure/function SELECT * FROM pg_views WHERE viewname='__viewname__';: List view (including the definition) SELECT pg_size_pretty(pg_total_relation_size('__table_name__'));: Show DB table space in use SELECT pg_size_pretty(pg_database_size('__database_name__'));: Show DB space in use show statement_timeout;: Show current user's statement timeout SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';: Show table indexes Get all indexes from all tables of a schema: SELECT t.relname AS table_name, i.relname AS index_name, a.attname AS column_name FROM pg_class t, pg_class i, pg_index ix, pg_attribute a, pg_namespace n WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) AND t.relnamespace = n.oid AND n.nspname = 'kartones' ORDER BY t.relname, i.relname Execution data: Queries being executed at a certain DB: SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query FROM pg_stat_activity WHERE datname='__database_name__'; Get all queries from all dbs waiting for data (might be hung): SELECT * FROM pg_stat_activity WHERE waiting='t' Currently running queries with process pid: SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s; Get Connections by Database: SELECT datname, numbackends FROM pg_stat_database; Casting: CAST (column AS type) or column::type '__table_name__'::regclass::oid: Get oid having a table name Query analysis: EXPLAIN __query__: see the query plan for the given query EXPLAIN ANALYZE __query__: see and execute the query plan for the given query ANALYZE [__table__]: collect statistics Generating random data (source): INSERT INTO some_table (a_float_value) SELECT random() * 100000 FROM generate_series(1, 1000000) i; Get sizes of tables, indexes and full DBs: select current_database() as database, pg_size_pretty(total_database_size) as total_database_size, schema_name, table_name, pg_size_pretty(total_table_size) as total_table_size, pg_size_pretty(table_size) as table_size, pg_size_pretty(index_size) as index_size from ( select table_name, table_schema as schema_name, pg_database_size(current_database()) as total_database_size, pg_total_relation_size(table_name) as total_table_size, pg_relation_size(table_name) as table_size, pg_indexes_size(table_name) as index_size from information_schema.tables where table_schema=current_schema() and table_name like 'table_%' order by total_table_size ) as sizes; COPY command: Import/export from CSV to tables: COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] ( option [, ...] ) ] COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | STDOUT } [ [ WITH ] ( option [, ...] ) ] List all grants for a specific user SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'user_to_check' ORDER BY table_name; List all assigned user roles SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolconnlimit, r.rolvaliduntil, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof, r.rolreplication FROM pg_catalog.pg_roles r ORDER BY 1; Check permissions in a table: SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='name-of-the-table'; Kill all Connections: SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid(); Keyboard shortcuts CTRL + R: reverse-i-search Tools ptop and pg_top: top for PG. Available on the APT repository from apt.postgresql.org. pg_activity: Command line tool for PostgreSQL server activity monitoring. Unix-like reverse search in psql: $ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc $ source $HOME/.editrc Show IP of the DB Instance: SELECT inet_server_addr(); File to save PostgreSQL credentials and permissions (format: hostname:port:database:username:password): chmod 600 ~/.pgpass Collect statistics of a database (useful to improve speed after a Database Upgrade as previous query plans are deleted): ANALYZE VERBOSE; To obtain the CREATE TABLE query of a table, any visual GUI like pgAdmin allows to easily, but else you can use pg_dump, e.g.: pg_dump -t '.' --schema-only (source) Resources & Documentation Operations Cheat Sheet: Official PG wiki cheat sheet with an amazing amount of explanations of many topics, features, and many many internal implementation details Postgres Weekly newsletter: The best way IMHO to keep up to date with PG news 100 psql Tips: Name says all, lots of useful tips! PostgreSQL Exercises: An awesome resource to learn to learn SQL, teaching you with simple examples in a great visual way. Highly recommended. A Performance Cheat Sheet for PostgreSQL: Great explanations of EXPLAIN, EXPLAIN ANALYZE, VACUUM, configuration parameters and more. Quite interesting if you need to tune-up a postgres setup. annotated.conf: Annotations of all 269 postgresql.conf settings for PostgreSQL 10. psql -c "\l+" -H -q postgres > out.html: Generate a html report of your databases (source: Daniel Westermann)