Soporte linux | Nautilux

Herramientas de usuario

Herramientas del sitio

Traza:
db:postgresql

Diferencias

Muestra las diferencias entre dos versiones de la página.

Enlace a la vista de comparación

Próxima revisión		Revisión previa
db:postgresql [2020/08/04 13:45] – creado grillodb:postgresql [2020/12/09 16:06] (actual) – [Debug] grillo
Línea 5: Línea 5:
  
 CONEXION SSL CONEXION SSL
-``` psql "sslmode=require host=pg4-test dbname=template1" --username=pgrigio0 -W ```+``` 
 +psql "sslmode=require host=pg4-test dbname=template1" --username=pgrigio0 -W 
 +```
  
  
Línea 11: Línea 13:
  
 VER CONFIGURACION EN VIVO VER CONFIGURACION EN VIVO
-``` $ psql gis -c "show shared_buffers;" ```+``` 
 +$ psql gis -c "show shared_buffers;" 
 +```
  
 VER USUARIOS ACTIVOS VER USUARIOS ACTIVOS
-``` mydb=# SELECT usesysid, usename FROM pg_stat_activity; ``` +``` 
-``` $ ps -ef | grep "postgres: " ```+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; 
 +``` 
  
  
Línea 21: Línea 39:
  
 EJECUTAR SCRIPT EN UNA BASE EJECUTAR SCRIPT EN UNA BASE
 +
 script "creabase.sql" en base "dbtest" sobre servidor "localhost" script "creabase.sql" en base "dbtest" sobre servidor "localhost"
-``` $ psql -f createbase.sql dbtest ``` +``` 
-``` $ psql dbtest < createbase.sql ``` +$ psql -f createbase.sql dbtest 
-``` dbtest=# \i createbase.sql ```+$ psql dbtest < createbase.sql 
 +dbtest=# \i createbase.sql 
 +```
  
 CONECTARSE A BASE DE DATOS CONECTARSE A BASE DE DATOS
 +
 usuario "prueba" en servidor "localhost" con clave "pepe" en base "dbprueba" 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" ```+$ 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 AGREGAR BASE
 +
 "dbprueba" "dbprueba"
-``` $ createdb dbprueba ```+``` 
 +$ createdb dbprueba 
 +```
  
 VER BASES VER BASES
-``` $ psql -l ``` +``` 
-``` postgres=# \l ``` +$ psql -l 
-``` postgres=# SELECT datname FROM pg_database; ```+postgres=# \l 
 +postgres=# SELECT datname FROM pg_database; 
 +```
  
 USAR BASE USAR BASE
 "dbprueba" "dbprueba"
-``` postgres=# \c dbprueba ```+ 
 +``` 
 +postgres=# \c dbprueba 
 +```
  
 VER USUARIOS DE LA BASE VER USUARIOS DE LA BASE
 +
 usuarios y atributos de la base actual usuarios y atributos de la base actual
-``` postgres=# \du ``` +``` 
-``` mydb=# SELECT * FROM "pg_user"; ```+postgres=# \du 
 +mydb=# SELECT * FROM "pg_user"; 
 +```
  
 RENOMBRAR BASE RENOMBRAR BASE
 +
 renombrar base "nombreviejo" a "nombrenuevo" renombrar base "nombreviejo" a "nombrenuevo"
-``` mydb=# ALTER DATABASE nombreviejo rename to nombrenuevo; ```+``` 
 +mydb=# ALTER DATABASE nombreviejo rename to nombrenuevo; 
 +```
  
 CAMBIAR DUEÑO DE BASE CAMBIAR DUEÑO DE BASE
 +
 cambiar nombre a "unuevo" a la base "test" cambiar nombre a "unuevo" a la base "test"
-``` test=# ALTER DATABASE test OWNER TO unuevo; ```+``` 
 +test=# ALTER DATABASE test OWNER TO unuevo; 
 +```
  
 VER TAMAÑO DE LAS BASES 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; ```+``` 
 +mydb=# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database; 
 +```
  
  
Línea 64: Línea 129:
  
 VER TABLAS DE LA BASE VER TABLAS DE LA BASE
-``` postgres=# \dt ``` +``` 
-``` mydb=# SELECT * FROM "pg_tables"; ```+postgres=# \dt 
 +mydb=# SELECT * FROM "pg_tables"; 
 +```
  
 VER TABLAS LISTAS Y SECUENCIAS DE LA BASE VER TABLAS LISTAS Y SECUENCIAS DE LA BASE
-``` postgres=# \d ```+``` 
 +postgres=# \d 
 +```
  
 CAMBIAR DUEÑO DE UNA TABLA CAMBIAR DUEÑO DE UNA TABLA
 +
 tabla "tabla" al usuario "user" tabla "tabla" al usuario "user"
-``` mydb=#  ALTER TABLE tabla owner to user; ```+``` 
 +mydb=#  ALTER TABLE tabla owner to user; 
 +```
  
  
Línea 78: Línea 150:
  
 LISTAR USUARIOS LISTAR USUARIOS
-``` mydb=# SELECT * FROM "pg_user"; ``` +``` 
-``` postgres=# \z ```+mydb=# SELECT * FROM "pg_user"; 
 +postgres=# \z 
 +```
  
 LISTAR PERMISOS LISTAR PERMISOS
 +
 Permisos de todos los usuarios y bases Permisos de todos los usuarios y bases
-``` postgres=# select datname as "Relation", datacl as "Access permissions" from pg_database; ```+``` 
 +postgres=# select datname as "Relation", datacl as "Access permissions" from pg_database; 
 +```
  
 QUIEN TIENE PERMISOS EN TABLA QUIEN TIENE PERMISOS EN TABLA
 +
 Quien tiene permisos en la tabla mitabla de la base dbprueba Quien tiene permisos en la tabla mitabla de la base dbprueba
-``` dbprueba=# \z mitabla ```+``` 
 +dbprueba=# \z mitabla 
 +```
  
 Permisos de los usuarios en la base 'prueba' 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 ```+postgres=# select datname as "Relation", datacl as "Access permissions" from pg_database where datname = 'prueba'; 
 +prueba=# \dp 
 +```
  
 CREAR USUARIO CREAR USUARIO
 +
 usuario con permisos de superuser usuario con permisos de superuser
-``` $ createuser -drs usuario ```+``` 
 +$ createuser -drs usuario 
 +```
  
 BORRAR USUARIO BORRAR USUARIO
 +
 usuario "admin" desde el servidor "localhost" usuario "admin" desde el servidor "localhost"
-``` $ dropuser admin ```+``` 
 +$ dropuser admin 
 +```
  
 ASIGNAR ROL A USUARIO ASIGNAR ROL A USUARIO
 +
 asignar rol "lectura" a usuario "user" asignar rol "lectura" a usuario "user"
-``` mydb=# GRANT user to lectura; ```+``` 
 +mydb=# GRANT user to lectura; 
 +```
  
 LISTAR ROLES LISTAR ROLES
 +
 listar roles y usuarios asignados a cada uno listar roles y usuarios asignados a cada uno
-``` prueba=# \du ```+``` 
 +prueba=# \du 
 +```
  
 QUITAR LOGIN QUITAR LOGIN
-``` mydb=# ALTER ROLE ooliquidacion_w WITH NOLOGIN; ```+``` 
 +mydb=# ALTER ROLE ooliquidacion_w WITH NOLOGIN; 
 +```
  
 LIMITAR CANTIDAD DE CONEXIONES LIMITAR CANTIDAD DE CONEXIONES
-``` mydb=# ALTER ROLE usuario WITH CONNECTION LIMIT 1; ```+``` 
 +mydb=# ALTER ROLE usuario WITH CONNECTION LIMIT 1; 
 +```
  
 ASIGNAR PERMISOS ASIGNAR PERMISOS
 +
 todos los permisos a "user" en base "dbase" todos los permisos a "user" en base "dbase"
-``` dbase=# GRANT ALL ON DATABASE dbase TO user; ```+``` 
 +dbase=# GRANT ALL ON DATABASE dbase TO user; 
 +```
  
 REVOCAR PERMISOS REVOCAR PERMISOS
 +
 todos los permisos a "user" en todas las tablas de "base" todos los permisos a "user" en todas las tablas de "base"
-``` dbase=# REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user; ```+``` 
 +dbase=# REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user; 
 +```
  
 select a "user" en base "dbase" select a "user" en base "dbase"
-``` dbase=# GRANT select ON ALL TABLES IN SCHEMA public to user; ```+``` 
 +dbase=# GRANT select ON ALL TABLES IN SCHEMA public to user; 
 +```
  
 TIEMPO MAXIMO DE QUERY POR USUARIO TIEMPO MAXIMO DE QUERY POR USUARIO
 +
 no permite consultas mayores a 10 segundos para el usuario no permite consultas mayores a 10 segundos para el usuario
-``` mydb=# ALTER ROLE usuario SET statement_timeout=10000; ```+``` 
 +mydb=# ALTER ROLE usuario SET statement_timeout=10000; 
 +```
  
 CAMBIAR CLAVE CAMBIAR CLAVE
-``` mydb=# UPDATE pg_shadow SET passwd = 'new_password' where usename = 'username'; ```+``` 
 +mydb=# UPDATE pg_shadow SET passwd = 'new_password' where usename = 'username'; 
 +```
  
 LISTADO DE PERMISOS LISTADO DE PERMISOS
Línea 148: Línea 259:
  
 VER PROCESOS CORRIENDO VER PROCESOS CORRIENDO
-``` mydb=# select * from pg_stat_activity; ```+``` 
 +mydb=# select * from pg_stat_activity; 
 +```
  
 MATAR PROCESO MATAR PROCESO
 +
 matar una query que esta corriendo matar una query que esta corriendo
-``` mydb=# SELECT pg_cancel_backend(procpid); ```+``` 
 +mydb=# SELECT pg_cancel_backend(procpid); 
 +```
 en caso que no responda se puede matar desde el propio linux (sin usar -9) en caso que no responda se puede matar desde el propio linux (sin usar -9)
-``` $ kill procpid ```+``` 
 +$ kill procpid 
 +``` 
 + 
 + 
 +VER USUARIOS CONECTADOS A UNA BASE labase 
 +``` 
 +mydb=# SELECT usename,client_addr FROM pg_stat_activity WHERE datname = 'labase'; 
 +```
  
  
Línea 160: Línea 284:
  
 EXPORTAR TODAS LAS BASES EXPORTAR TODAS LAS BASES
-``` $ pg_dumpall >bases.sql ```+``` 
 +$ pg_dumpall >bases.sql 
 +```
  
 EXPORTAR BASE EXPORTAR BASE
 +
 base "temp" base "temp"
-``` $ pg_dump test >text.sql ```+``` 
 +$ pg_dump test >text.sql 
 +``` 
 dump de base "prueba" con formato custom para después importar dump de base "prueba" con formato custom para después importar
-``` $ pg_dump -Fc prueba > prueba.dump ```+``` 
 +$ pg_dump -Fc prueba > prueba.dump 
 +```
  
 IMPORTAR BASE IMPORTAR BASE
 +
 importar a base "prueba" desde archivo dump "prueba.dump" con formato custom importar a base "prueba" desde archivo dump "prueba.dump" con formato custom
-``` $ pg_restore --clean --role=postgres --dbname=prueba --no-owner prueba.dump ```+``` 
 +$ 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 ## Replicación
  
 PROCESOS CORRIENDO PROCESOS CORRIENDO
 +
 en el master en el master
-``` $ ps -ef | grep sender ```+``` 
 +$ ps -ef | grep sender 
 +``` 
 en el esclavo en el esclavo
-``` $ ps -ef | grep receiver ```+``` 
 +$ ps -ef | grep receiver 
 +```
  
 ESTADO DE LA REPLICA ESTADO DE LA REPLICA
-``` $ pg_lsclusters ```+``` 
 +$ pg_lsclusters 
 +```
  
 VER ESCLAVOS CONECTADOS AL MASTER VER ESCLAVOS CONECTADOS AL MASTER
 +
 en el master en el master
-``` $ psql -c "select client_addr, state, sent_location, write_location, flush_location, replay_location from pg_stat_replication;" ```+``` 
 +$ psql -c "select client_addr, state, sent_location, write_location, flush_location, replay_location from pg_stat_replication;" 
 +```
  
 VER RETRASO EN EL ESCLAVO VER RETRASO EN EL ESCLAVO
 +
 en el esclavo en el esclavo
-``` postgres=# select now() - pg_last_xact_replay_timestamp() AS replication_delay; ```+``` 
 +postgres=# select now() - pg_last_xact_replay_timestamp() AS replication_delay; 
 +```
                                                                                                                                                                                          
  
db/postgresql.1596548745.txt.gz · Última modificación: 2020/08/04 13:45 por grillo