Postgres basics

Get postgres prompt

su postgres
psql
postgres=#

su postgres
psql <dbname>

[edit] List databases

postgres=# \l

[edit] List users

postgres=# \du

[edit] Dump a database

su postgres
pg_dump <dbname> > db.out

[edit] Import a database

su postgres
psql -d <dbname> -f db.out

[edit] Create role

createuser -S -D -R <role_name>

PG RESTORE COMMAND

---

pg_restore -h localhost -U origami_dbadmin -d kvvs -W kvvs_20170316_22_00_01.tar

PG DUMP COMMAND

---

pg_dump -Ft -U origami_dbadmin ddugky_misdbnew -x -O -f "/var/www/html/ddugky_mis_2/ddugky_misdb.tar"

PG DUMP WITHOUT VERSION PROBLEMS (plain format dump file)

---

pg_dump -f ddugky_misdbnew.db -F p -U origami_dbadmin ddugky_misdbnew
pg_dump -U username dbname > dump.sql

PG RESTORE WITHOUT VERSION PROBLEMS (plain format dump file)

---

psql -f ddugky_misdbnew.db dd_misdb origami_dbadmin

Restoration Command Summary

sudo -i
su postgres
psql
CREATE DATABASE mykalamasserydb
CREATE USER mykalauser WITH ENCRYPTED PASSWORD 'pass';
GRANT ALL PRIVILEGES ON DATABASE mykalamasserydb TO mykalauser;
\q
gzip -d mykalamassery_06_13_2021.sql.gz
psql -U mykalauser -d mykalamasserydb -f mykalamassery_06_13_2021.sql