PostgreSQL Cheat Sheet – DEV Community

We gathered a listing of helpful PostgreSQL instructions and queries when working with PostgreSQL databases. Please ping us @ForestAdmin for those who’d like so as to add one thing to the cheat sheet!



Getting began with PostgreSQL

Login and enter PostgreSQL command line utility psql

$ sudo su - postgres psql
Enter fullscreen mode

Exit fullscreen mode

Record postgres databases

l
Enter fullscreen mode

Exit fullscreen mode

Hook up with postgres database

c databaseName
Enter fullscreen mode

Exit fullscreen mode

Exit postgreSQL command line utility psql

q
Enter fullscreen mode

Exit fullscreen mode



Managing PostgreSQL

Test PostgreSQL model

SELECT model();
Enter fullscreen mode

Exit fullscreen mode

Test if PostgreSQL is put in

$ which psql
Enter fullscreen mode

Exit fullscreen mode

Test if PostgreSQL is working

$ pgrep -fa -- -D | grep postgres
Enter fullscreen mode

Exit fullscreen mode

Restart PostgreSQL on Linux

# systemctl restart postgresql
// or
# service postgresql restart
// or
# /and so forth/init.d/postgresql restart
// or
# /sbin/service postgresql restart
Enter fullscreen mode

Exit fullscreen mode

Restart PostgreSQL on OSX

# brew companies restart postgres
// or
# pg_ctl -D /usr/native/var/postgres -l /usr/native/var/postgres/server.log restart
Enter fullscreen mode

Exit fullscreen mode

Restart PostgreSQL on Home windows

Winkey + R
Sort "companies.msc"
Click on "restart"
Enter fullscreen mode

Exit fullscreen mode

Change PostgreSQL person password

$ sudo -u postgres psql
password postgres
Enter fullscreen mode

Exit fullscreen mode

Exit from psql

q
Enter fullscreen mode

Exit fullscreen mode



Interacting with databases in PostgreSQL

Record postgres databases

l
Enter fullscreen mode

Exit fullscreen mode

Join or swap to postgres database

c databaseName
Enter fullscreen mode

Exit fullscreen mode

Create new postgres database

CREATE DATABASE databaseName
Enter fullscreen mode

Exit fullscreen mode

Delete postgres database

DROP DATABASE databaseName
Enter fullscreen mode

Exit fullscreen mode

Rename postgres database

ALTER DATABASE old_databaseName TO new_databaseName
Enter fullscreen mode

Exit fullscreen mode

Question postgres JSON information

SELECT *
FROM mytable
WHERE myfield @> '{"key1":"valueA"}'
Enter fullscreen mode

Exit fullscreen mode



Interacting with tables in PostgreSQL

Record postgres tables

dt
Enter fullscreen mode

Exit fullscreen mode

Describe postgres desk

d tableName
Enter fullscreen mode

Exit fullscreen mode

Create postgres desk

CREATE TABLE tableName( 
    columnName columnType,
    columnName columnType
);
Enter fullscreen mode

Exit fullscreen mode

Delete postgres desk

DROP TABLE tableName CASCADE
Enter fullscreen mode

Exit fullscreen mode



Backup and restore PostgreSQL database

Backup postgres database

$ pg_dump -d databaseName -U userName > backupFile
Enter fullscreen mode

Exit fullscreen mode

Restore postgres database

psql databaseName < backupFile
Enter fullscreen mode

Exit fullscreen mode



Managing roles and permissions in PostgreSQL

Record postgres roles

SELECT rolname FROM pg_roles;
Enter fullscreen mode

Exit fullscreen mode

Create postgres person

CREATE USER userName WITH PASSWORD 'userPassword';
Enter fullscreen mode

Exit fullscreen mode

Delete postgres person

DROP USER userName;
Enter fullscreen mode

Exit fullscreen mode

Change postgres person password

ALTER ROLE userName WITH PASSWORD 'userPassword';
Enter fullscreen mode

Exit fullscreen mode

Record 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;
Enter fullscreen mode

Exit fullscreen mode

Grant all permissions on postgres database

GRANT ALL PRIVILEGES ON DATABASE databaseName TO userName;
Enter fullscreen mode

Exit fullscreen mode

Grant connection permissions on postgres database

GRANT CONNECT ON DATABASE databaseName TO userName;
Enter fullscreen mode

Exit fullscreen mode

Record permissions for particular function postgres

SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = userName ORDER BY table_name;
Enter fullscreen mode

Exit fullscreen mode



ProstgreSQL psql instructions recap

? Record all out there psql instructions

h COMMAND Get assistance on particular command

l Record databases

c databaseName Hook up with database

dt Record tables

d tableName Describe desk

d+ tableName Describe desk with particulars

dn Record schemas

df Record features

dv Record views

du Record customers

dy Record occasions

di Record indexes

q Exit


Must construct admin panels or a GUI instrument for PostgreSQL? Try Forest Admin for PostgreSQL



Add a Comment

Your email address will not be published. Required fields are marked *