====== Notas Postgres ====== ===== Comandos shell ===== createdb NOMBREBD dropdb NOMBREBD createuser USER -P PASSWORD -E # Encripted Enter name of role to add: username Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n dropuser USER pg_dump pg_dumpall pg_restore vacuumdb clusterdb psql NOMBREBD ==> Arrancar psql ===== Una vez dentro: ===== alter user USER with encrypted password 'PASSWORD'; grant all privileges on database DATABASE to cashier; \i f ==> Ejecutar comandos desde archivo \o f ==> Enviar resultados a archivo \echo XX ==> echo \l ==> Lista de bases de datos \d ==> Lista las tablas \d tabla ==> Lista de campos de la tabla \h ==> Ayuda \e ==> Editar ultimo comando con vim (Ejecutar) # su - postgres $ psql psql (9.6.0) Type "help" for help. postgres=# help You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit ===== Create a schema called test in the default database called postgres ===== postgres=# CREATE SCHEMA test; ===== Create a role (user) with password ===== postgres=# CREATE USER xxx PASSWORD 'yyy'; ===== Grant privileges (like the ability to create tables) on new schema to new role ===== postgres=# GRANT ALL ON SCHEMA test TO xxx; ===== Grant privileges (like the ability to insert) to tables in the new schema to the new role ===== postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx; ===== Disconnect ===== postgres=# \q ===== Became a standard user. ===== The default authentication mode is set to ‘ident’ which means a given Linux user xxx can only connect as the postgres user xxx. # su - xxx ===== Login from xxx user in shell to default postgres db ===== $ psql -d postgres psql (9.2.4) Type "help" for help. ===== Create a table test in schema test ===== postgres=>CREATE TABLE test.test (coltest varchar(20)); CREATE TABLE ===== Insert a single record into new table ===== postgres=>insert into test.test (coltest) values ('It works!'); INSERT 0 1 ===== First SELECT from a table ===== postgres=>SELECT * from test.test; coltest It works! (1 row) ===== Drop test table ===== postgresql=>DROP TABLE test.test; DROP TABLE create role myuser login password 'xxxx'; create database mydb encoding 'utf8' owner myuser; set search_path to xxxx; alter user user_name set search_path to xxxx; ===== General ===== \copyright show PostgreSQL usage and distribution terms \errverbose show most recent error message at maximum verbosity \g [FILE] or ; execute query (and send results to file or |pipe) \gexec execute query, then execute each value in its result \gset [PREFIX] execute query and store results in psql variables \q quit psql \crosstabview [COLUMNS] execute query and display results in crosstab \watch [SEC] execute query every SEC seconds ===== Help ===== \? [commands] show help on backslash commands \? options show help on psql command-line options \? variables show help on special variables \h [NAME] help on syntax of SQL commands, * for all commands ===== Query Buffer ===== \e [FILE] [LINE] edit the query buffer (or file) with external editor \ef [FUNCNAME [LINE]] edit function definition with external editor \ev [VIEWNAME [LINE]] edit view definition with external editor \p show the contents of the query buffer \r reset (clear) the query buffer \s [FILE] display history or save it to file \w FILE write query buffer to file ===== Input/Output ===== \copy ... perform SQL COPY with data stream to the client host \echo [STRING] write string to standard output \i FILE execute commands from file \ir FILE as \i, but relative to location of current script \o [FILE] send all query results to file or |pipe \qecho [STRING] write string to query output stream (see \o) ===== Informational ===== (options: S = show system objects, + = additional detail) \d[S+] list tables, views, and sequences \d[S+] NAME describe table, view, sequence, or index \da[S] [PATTERN] list aggregates \dA[+] [PATTERN] list access methods \db[+] [PATTERN] list tablespaces \dc[S+] [PATTERN] list conversions \dC[+] [PATTERN] list casts \dd[S] [PATTERN] show object descriptions not displayed elsewhere \ddp [PATTERN] list default privileges \dD[S+] [PATTERN] list domains \det[+] [PATTERN] list foreign tables \des[+] [PATTERN] list foreign servers \deu[+] [PATTERN] list user mappings \dew[+] [PATTERN] list foreign-data wrappers \df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions \dF[+] [PATTERN] list text search configurations \dFd[+] [PATTERN] list text search dictionaries \dFp[+] [PATTERN] list text search parsers \dFt[+] [PATTERN] list text search templates \dg[S+] [PATTERN] list roles \di[S+] [PATTERN] list indexes \dl list large objects, same as \lo_list \dL[S+] [PATTERN] list procedural languages \dm[S+] [PATTERN] list materialized views \dn[S+] [PATTERN] list schemas \do[S] [PATTERN] list operators \dO[S+] [PATTERN] list collations \dp [PATTERN] list table, view, and sequence access privileges \drds [PATRN1 [PATRN2]] list per-database role settings \ds[S+] [PATTERN] list sequences \dt[S+] [PATTERN] list tables \dT[S+] [PATTERN] list data types \du[S+] [PATTERN] list roles \dv[S+] [PATTERN] list views \dE[S+] [PATTERN] list foreign tables \dx[+] [PATTERN] list extensions \dy [PATTERN] list event triggers \l[+] [PATTERN] list databases \sf[+] FUNCNAME show a function's definition \sv[+] VIEWNAME show a view's definition \z [PATTERN] same as \dp ===== Formatting ===== \a toggle between unaligned and aligned output mode \C [STRING] set table title, or unset if none \f [STRING] show or set field separator for unaligned query output \H toggle HTML output mode (currently off) \pset [NAME [VALUE]] set table output option (NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null| numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager| unicode_border_linestyle|unicode_column_linestyle|unicode_header_linestyle}) \t [on|off] show only rows (currently off) \T [STRING] set HTML tag attributes, or unset if none \x [on|off|auto] toggle expanded output (currently off) ===== Connection ===== \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo} connect to new database (currently "ctm900") \encoding [ENCODING] show or set client encoding \password [USERNAME] securely change the password for a user \conninfo display information about current connection ===== Operating System ===== \cd [DIR] change the current working directory \setenv NAME [VALUE] set or unset environment variable \timing [on|off] toggle timing of commands (currently off) \! [COMMAND] execute command in shell or start interactive shell ===== Variables ===== \prompt [TEXT] NAME prompt user to set internal variable \set [NAME [VALUE]] set internal variable, or list all if no parameters \unset NAME unset (delete) internal variable ===== Large Objects ===== \lo_export LOBOID FILE \lo_import FILE [COMMENT] \lo_list \lo_unlink LOBOID large object operations