all informations are inside great documentation
psql:
psql [option] -d [dbname] -h [hostname] -U [username]
psql -l #db list
\timing #set show sql execution time
\q #quit
\password #set password
\pset #change psql settings
inside psql:
\d [table] #table desc
\c [database] #connect to db
\da #agregation functions
\db+ #tablespaces
\dc #conversions
\df+ #functions
\dg+ \du+ #roles
\di+ #indexes
\ds+ #sequences
\dt+ #tables
\dv+ #views
\dSvtis+ #system views,tables,indexes,sequences
\dn+ #schemas
\do #operators
\dp #privileges
\encoding #encoding
\l+ #db description
\z #objects with privileges
\o [file] #spool file
help commands:
\? #info about commands with backslash
\h #help SQL
\h select #SQL help to SELECT command
parameters:
show all; #db parameters
show [parametr]; #ex. show search_path;
set search_path to [other_schema] #now you can see object from other schema
show search_path; #current schama
integrate with OS:
\!pwd #current path
\!cd [dir] #change path
administrating:
variable PGDATA point to cluster catalog
pg_ctl status #db state
pg_ctl -D [directory] [action]
pg_ctl start | stop |restart #db start,stop you can add -l [logfile]
pg_controldata [cluser_dir] #cluster info
DUMP:
export:
pg_dump [db_name] > [file] #dump in plaintext format
pg_dump -t ‘[table]‘ [db_name] > [file]
pg_dump -Fc #dump in pg_restore format
pg_dumpall > [plik] #dump all databases
import:
psql [baza] < [plik] #import from plaintext format
psql -f [plik] postgres #import from plaintext format
pg_restore -d [baza] [plik] #import from pg_restore format
pg_restore -l [plik] #content of dumpfile
CREATE:
initdb –pgdata | -D [cluster_dir] [-E encoding] #cluster initialization,create template1 and postgres database
createdb [dbname] [-D tablespace] [-E encoding] [-O owner] [-T template to create new database]
change host database IP:
change in $PG_DATA/postgresql.conf
change in $PG_DATA/pg_hba.conf (according manual – chapter 20: Client Authentication)
MANAGE ROLES:
\dg #check system privs
SELECT * FROM pg_roles;
create role [role_name]; #create role
change system privs:
ALTER ROLE [role_name] SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT [connlimit] | PASSWORD [password] | ENCRYPTED | UNENCRYPTED | VALID UNTIL [timestamp]
alter role [role] password ‘[pass]‘;
change object privs:
\z #check privs
=xxxx #privileges granted to PUBLIC
uname=xxxx #privileges granted to a user
group gname=xxxx #privileges granted to a group
r — SELECT (”read”)
w — UPDATE (”write”)
a — INSERT (”append”)
d — DELETE
R — RULE
x — REFERENCES
t — TRIGGER
X — EXECUTE
U — USAGE
C — CREATE
T — TEMPORARY
arwdRxt — ALL PRIVILEGES (for tables)
* — grant option for preceding privilege
GRANT [privs] ON [object] TO [role];
Ostatnie komentarze