We gathered a list of handy PostgreSQL commands and queries when working with PostgreSQL databases. Please ping us @ForestAdmin if you’d like to add something to the cheat sheet!
Getting started with PostgreSQL
Login and enter PostgreSQL command line utility psql
$ sudo su - postgres psql
List postgres databases
\l
Connect to postgres database
\c databaseName
Exit postgreSQL command line utility psql
\q
Managing PostgreSQL
Check PostgreSQL version
SELECT version();
Check if PostgreSQL is installed
$ which psql
Check if PostgreSQL is running
$ pgrep -fa -- -D | grep postgres
Restart PostgreSQL on Linux
# systemctl restart postgresql
// or
# service postgresql restart
// or
# /etc/init.d/postgresql restart
// or
# /sbin/service postgresql restart
Restart PostgreSQL on OSX
# brew services restart postgres
// or
# pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log restart
Restart PostgreSQL on Windows
Winkey + R
Type "services.msc"
Click "restart"
Change PostgreSQL user password
$ sudo -u postgres psql
\password postgres
Exit from psql
\q
Interacting with databases in PostgreSQL
List postgres databases
\l
Connect or switch to postgres database
\c databaseName
Create new postgres database
CREATE DATABASE databaseName
Delete postgres database
DROP DATABASE databaseName
Rename postgres database
ALTER DATABASE old_databaseName TO new_databaseName
Query postgres JSON data
SELECT *
FROM mytable
WHERE myfield @> '{"key1":"valueA"}'
Interacting with tables in PostgreSQL
List postgres tables
\dt
Describe postgres table
\d tableName
Create postgres table
CREATE TABLE tableName(
columnName columnType,
columnName columnType
);
Delete postgres table
DROP TABLE tableName CASCADE
Backup and restore PostgreSQL database
Backup postgres database
$ pg_dump -d databaseName -U userName > backupFile
Restore postgres database
psql databaseName < backupFile
Managing roles and permissions in PostgreSQL
List postgres roles
SELECT rolname FROM pg_roles;
Create postgres user
CREATE USER userName WITH PASSWORD 'userPassword';
Delete postgres user
DROP USER userName;
Change postgres user password
ALTER ROLE userName WITH PASSWORD 'userPassword';
List all assigned roles postgres
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;
Grant all permissions on postgres database
GRANT ALL PRIVILEGES ON DATABASE databaseName TO userName;
Grant connection permissions on postgres database
GRANT CONNECT ON DATABASE databaseName TO userName;
List permissions for specific role postgres
SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = userName ORDER BY table_name;
ProstgreSQL psql commands recap
\? List all available psql commands
\h COMMAND Get help on specific command
\l List databases
\c databaseName Connect to database
\dt List tables
\d tableName Describe table
\d+ tableName Describe table with details
\dn List schemas
\df List functions
\dv List views
\du List users
\dy List events
\di List indexes
\q Exit
Need to build admin panels or a GUI tool for PostgreSQL? Check out Forest Admin for PostgreSQL
Top comments (1)
Who doesn't like a good cheat sheet? This one is for you all bootcamp students! (cc Le Wagon, Ironhack) 😍