PostgreSQL Cheat Sheet

Cheat sheet for PostgreSQL CLI tool

Install PostgreSQL

sudo apt install postgresql postgresql-contrib

Switch to 'postgres' user

sudo -i -u postgres

Start PostgreSQL CLI

psql

Create new database

CREATE DATABASE your_database

Make sure to enable it so it starts on startup (even though this is default behaviour)

sudo systemctl enable postgresql

Set password

\password your_postgres_password

Connection URI

postgresql://postgres:your_postgres_password@your_server_ip:5432/your_database

List all databases

\l

Connect to the database

\c your_database

List all tables inside database

\dt

Describe a table

\d+ products

Backup database

pg_dump -U postgres -d your_database > backup.sql # Local
pg_dump --verbose --no-owner --no-acl "<connection_string>" -f output_file #Remote, koristiti verbose uvek!

Restore database from a backup

dropdb your_database
createdb your_database
psql -U postgres -d your_database < backup.sql # Local
psql --verbose "<connection_string>" -f input_file # Remote

Restore can be done with using following params to just load or append data without making changes to the schema

--data-only
--inserts

Exit PostgreSQL CLI

\q

Force go to superuser

su

Try logging out to go back to root

exit