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