Postgres

Create a database

createdb mydb -E UTF8 -O myuser
  • or

CREATE DATABASE mydb WITH OWNER mypuser;
  • By default this will copy template1 db

Create a user

createuser --password myuser
  • or

CREATE USER myuser WITH password 'secret';

Change user password

ALTER USER myuser WITH PASSWORD 'moresecret';

Change user permissions

ALTER USER myuser CREATEDB;

Delete user

dropuser myuser
  • or

DROP USER myuser

List databases

\l

Connect to a database

\c <db>

List all tables

\dt

Describe table

\d+ table

List user and permissions

\du

Show active connections

SELECT * FROM pg_stat_activity;

Export select as CSV

copy(select * from table) to '/some/file' with csv header;

Import CSV

copy table from '/some'file' with csv header;

Backup / Restore

  • Backup in binary format

pg_dump -F c -b -U user database > backup.dump
  • Restore

pg_restore --disable-triggers -U user -d database backup.dump
  • If you want to disable all constraints for data import

echo "SET CONSTRAINTS ALL DEFERRED;" | psql
  • And to enable contraints

echo "SET CONSTRAINTS ALL IMMEDIATE;" | psql

Show all trigger of a table or view

select * from pg_trigger where tgrelid = 'schema.table'::regclass;
  • Schema name can be looked up in output of d

  • To get the source code of a trigger use

select p.prosrc from pg_trigger t join pg_proc p on p.oid=t.tgfoid where t.tgname = 'RI_ConstraintTrigger_a_130239';
  • Or all together

select t.*, p.prosrc from pg_trigger t join pg_proc p on p.oid=t.tgfoid where t.tgrelid = 'schema.table'::regclass;

Show all functions

\df+

Import SQL file

  • Beside psql < sqlfile there is the possibility to use

\i sqlfile

Change output format

\x on