psql.md

PostgreSQL commands

sudo -u postgres psql

help

\?

list database

\l

connect to database

\c <db name>

display table (in current database)

\dt [table name]

DESCRIBE TABLE

\d+ <table name>

display user

\du

remove user

DROP USER IF EXISTS midpoint_miranda_test;

dump database

pg_dumpall > outfile

pg_dumpall backs up each database in a given cluster, and also preserves cluster-wide data such as role and tablespace definitions.

https://www.postgresql.org/docs/9.1/static/backup-dump.html

remove db cluster

rm -rf /var/lib/pgsql/data/

reinstall db cluster

sudo -U pgsql initdb
sudo -u postgres psql < dump.sql

https://serverfault.com/q/574474/115596

Also need to add /var/lib/pgsql/data/pg_hba.conf back.

current time

select now();

change editor

export EDITOR=nano
psql database_name
\e