# PostgreSQL


## Cliente

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


## Debug

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: "
```

VER SERVIDOR AL QUE ESTOY CONECTADO
```
\conninfo
```
```
SELECT CURRENT_USER usr, :'HOST' host, inet_server_port() port;
```
```
SELECT inet_server_addr() ip, inet_server_port() port;
```



## Bases

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"
```

usuario "prueba" en servidor "localhost" con clave "pepe" en base "dbprueba" con esquema por defecto "pruebaschema"
```
$ psql -U prueba -h localhost -W "dbname=dbprueba sslmode=require options=--search_path=pruebaschema"
```


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;
```


## Tablas

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;
```


## Usuarios

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
```


## Procesos y consultas

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
```


VER USUARIOS CONECTADOS A UNA BASE labase
```
mydb=# SELECT usename,client_addr FROM pg_stat_activity WHERE datname = 'labase';
```


## Importar y exportar

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
```


## Actualizar datos con unl desde pc sin acceso al filesystem

Procedimiento para actualizar datos tomándolos desde un archivo csv|unl ubicado en una pc cliente del servidor. La limitación es que el comando `copy` sólo levanta archivos del filesystem local del servidor.
```
cat  /home/cliente/actas_sin_objeto_para_actualizar.csv| psql "sslmode=require host=postgre.server.com dbname=labase" --username=cliente -W -c "create temp table tmp_datos_domicilio (
    objeto_id          bigint,
    tipo_objeto        character varying(20),
    objeto             character varying(500),
    acta_id            bigint,
    t_acta             integer,
    nro_acta           bigint,
    serie              character varying(2),
    fecha_acta         timestamp without time zone,
    lugar_acta_id      bigint,
    ref_geografica     character varying(255),
    cant_encontradas   integer,
    cod_calle          integer,
    nom_calle          character varying(50),
    cod_intersec       integer,
    nom_intersec       character varying(50),
    altura             integer,
    letra_a            character varying(5),
    bis_a              boolean,
    sec                integer,
    mnz                integer,
    gra                integer,
    div                integer,
    sdiv               integer,
    carpeta            integer,
    catastral          character varying(25));
copy from stdin;"

copy tmp_datos_domicilio from stdin delimiter ',';
```

## Replicación

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;
```