CONEXION SSL

psql "sslmode=require host=pg4-test dbname=template1" --username=pgrigio0 -W

VER CONFIGURACION EN VIVO

$ psql gis -c "show shared_buffers;"

VER USUARIOS ACTIVOS

mydb=# SELECT usesysid, usename FROM pg_stat_activity;
$ ps -ef | grep "postgres: "

EJECUTAR SCRIPT EN UNA BASE

script “creabase.sql” en base “dbtest” sobre servidor “localhost”

$ psql -f createbase.sql dbtest
$ psql dbtest < createbase.sql
dbtest=# \i createbase.sql

CONECTARSE A BASE DE DATOS

usuario “prueba” en servidor “localhost” con clave “pepe” en base “dbprueba”

$ psql -U prueba -h localhost -W -d dbprueba
$ psql -U prueba -h localhost -W "dbname=dbprueba sslmode=require"

LISTAR ESQUEMAS DE UNA BASE

postgres=# \dp
postgres=# select schema_name from information_schema.schemata;

VER ESQUEMA ACTUAL

postgres=# SHOW search_path;

CONECTARSE A UN ESQUEMA

postgres=# SET search_path TO myschema,public;

AGREGAR BASE

“dbprueba”

$ createdb dbprueba

VER BASES

$ psql -l
postgres=# \l
postgres=# SELECT datname FROM pg_database;

USAR BASE “dbprueba”

postgres=# \c dbprueba

VER USUARIOS DE LA BASE

usuarios y atributos de la base actual

postgres=# \du
mydb=# SELECT * FROM "pg_user";

RENOMBRAR BASE

renombrar base “nombreviejo” a “nombrenuevo”

mydb=# ALTER DATABASE nombreviejo rename to nombrenuevo;

CAMBIAR DUEÑO DE BASE

cambiar nombre a “unuevo” a la base “test”

test=# ALTER DATABASE test OWNER TO unuevo;

VER TAMAÑO DE LAS BASES

mydb=# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;

VER TABLAS DE LA BASE

postgres=# \dt
mydb=# SELECT * FROM "pg_tables";

VER TABLAS LISTAS Y SECUENCIAS DE LA BASE

postgres=# \d

CAMBIAR DUEÑO DE UNA TABLA

tabla “tabla” al usuario “user”

mydb=#  ALTER TABLE tabla owner to user;

LISTAR USUARIOS

mydb=# SELECT * FROM "pg_user";
postgres=# \z

LISTAR PERMISOS

Permisos de todos los usuarios y bases

postgres=# select datname as "Relation", datacl as "Access permissions" from pg_database;

QUIEN TIENE PERMISOS EN TABLA

Quien tiene permisos en la tabla mitabla de la base dbprueba

dbprueba=# \z mitabla

Permisos de los usuarios en la base 'prueba'

postgres=# select datname as "Relation", datacl as "Access permissions" from pg_database where datname = 'prueba';
prueba=# \dp

CREAR USUARIO

usuario con permisos de superuser

$ createuser -drs usuario

BORRAR USUARIO

usuario “admin” desde el servidor “localhost”

$ dropuser admin

ASIGNAR ROL A USUARIO

asignar rol “lectura” a usuario “user”

mydb=# GRANT user to lectura;

LISTAR ROLES

listar roles y usuarios asignados a cada uno

prueba=# \du

QUITAR LOGIN

mydb=# ALTER ROLE ooliquidacion_w WITH NOLOGIN;

LIMITAR CANTIDAD DE CONEXIONES

mydb=# ALTER ROLE usuario WITH CONNECTION LIMIT 1;

ASIGNAR PERMISOS

todos los permisos a “user” en base “dbase”

dbase=# GRANT ALL ON DATABASE dbase TO user;

REVOCAR PERMISOS

todos los permisos a “user” en todas las tablas de “base”

dbase=# REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user;

select a “user” en base “dbase”

dbase=# GRANT select ON ALL TABLES IN SCHEMA public to user;

TIEMPO MAXIMO DE QUERY POR USUARIO

no permite consultas mayores a 10 segundos para el usuario

mydb=# ALTER ROLE usuario SET statement_timeout=10000;

CAMBIAR CLAVE

mydb=# UPDATE pg_shadow SET passwd = 'new_password' where usename = 'username';

LISTADO DE PERMISOS

SELECT (read)           r
INSERT (append)         a
UPDATE, DELETE (write)  w
RULE                    R
CONNECT                 c
CREATE                  C
TEMPORARY (tables)      T

VER PROCESOS CORRIENDO

mydb=# select * from pg_stat_activity;

MATAR PROCESO

matar una query que esta corriendo

mydb=# SELECT pg_cancel_backend(procpid);

en caso que no responda se puede matar desde el propio linux (sin usar -9)

$ kill procpid

EXPORTAR TODAS LAS BASES

$ pg_dumpall >bases.sql

EXPORTAR BASE

base “temp”

$ pg_dump test >text.sql

dump de base “prueba” con formato custom para después importar

$ pg_dump -Fc prueba > prueba.dump

IMPORTAR BASE

importar a base “prueba” desde archivo dump “prueba.dump” con formato custom

$ pg_restore --clean --role=postgres --dbname=prueba --no-owner prueba.dump

PROCESOS CORRIENDO

en el master

$ ps -ef | grep sender

en el esclavo

$ ps -ef | grep receiver

ESTADO DE LA REPLICA

$ pg_lsclusters

VER ESCLAVOS CONECTADOS AL MASTER

en el master

$ psql -c "select client_addr, state, sent_location, write_location, flush_location, replay_location from pg_stat_replication;"

VER RETRASO EN EL ESCLAVO

en el esclavo

postgres=# select now() - pg_last_xact_replay_timestamp() AS replication_delay;