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

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;

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;

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

VER USUARIOS CONECTADOS A UNA BASE labase

mydb=# SELECT usename,client_addr FROM pg_stat_activity WHERE datname = 'labase';

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

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 ',';

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;