李二狗 — @Meniny
PostgreSQL cheatsheet
Install:
sudo apt update
sudo apt install postgresql postgresql-contrib
PSQL
Magic words:
sudo -u postgres -i
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
Creating a User and granting all priviliges to a db
CREATE DATABASE test_db;
CREATE USER test_username WITH PASSWORD 'password';
ALTER ROLE test_username SET client_encoding TO 'utf8';
ALTER ROLE test_username SET default_transaction_isolation TO 'read committed';
GRANT ALL PRIVILEGES ON DATABASE test_db TO test_username;
DB Backing Up
pg_dump name_of_database > name_of_backup_file-- to create a backup filepsql empty_database < backup_file-- to fill empty db with backedup data
