Databases
PostgreSQL
psql client — connect, meta-commands, and administration.
Connect
psqlConnect as current OS userpsql -U username dbnameSpecify user and databasepsql -h host -p 5432 dbRemote connectionsudo -u postgres psqlConnect as postgres roleMeta-commands
\lList databases\c dbnameConnect to database\dtList tables in current schema\d tablenameDescribe table\d+ tablenameDetailed table description\duList roles/users\dnList schemas\dfList functions\i file.sqlExecute SQL from file\qQuit psqlAdministration
createdb dbnameCreate a new databasedropdb dbnameDrop a databasepg_dump dbname > dump.sqlExport databasepsql dbname < dump.sqlImport SQL dumpRoles & privileges
-- Create user with password
CREATE USER ada WITH PASSWORD 'secret';
-- Grant all on database
GRANT ALL PRIVILEGES ON DATABASE mydb TO ada;
-- Superuser role
CREATE USER admin SUPERUSER;
-- Revoke
REVOKE ALL ON DATABASE mydb FROM ada;