Consultas útiles en PostgreSQL

Useful PostgreSQL quieries

Odoo CMS - a big picture

Podemos ayudarlo

Le gustaría conocer más acerca de esta potente base de datos de libre distribución?

PostgreSQL ha sido comparado con motores de bases de datos tan conocidos como Oracle y SQL Server

Contáctenos para que uno de nuestros asesores le de las mejores opciones para el uso de esta y otras herramientas libres para su empresa.

Contacte con nosotros »

Crear Usuarios y Modificar Permisos

 

Crear usuarios

Desde la línea de comandos con el usuario postgres:

createuser nombreusuario 

Si Postgres ver 8.4 o anterior preguntará si el usuario se le permite crear bases de datos, nuevos roles.

Modificar permisos

Para modificar los permisos del usuario PostgreSQL (v9.0 y posteriores ya no pregunta nada al ejecutar createuser desde la línea de comandos), dentro de psql:

ALTER USER nombreusuario NOSUPERUSER CREATEDB;

Modificar su password dentro de psql:

alter role nombre_usuario with password 'password';

Cambiar el dueño a los objetos de la base de datos y a la propia base de datos


1.-Cambiar el OWNER de la base de datos:


ALTER DATABASE $BASEDEDATOS OWNER TO $USER;
2.- Cambiar el owner a los objetos de la base de datos (conectarse antes a la base de datos con psql -d BASE_DE_DATOS):

 
UPDATE pg_class SET relowner = (SELECT oid FROM pg_roles WHERE rolname = '$USER') 
WHERE relname IN (SELECT relname FROM pg_class, pg_namespace
WHERE pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = 'public');

 
Donde se cambia $USER por el nombre del usuario a quien se le asignará como dueño de esta base de datos.

Terminar procesos en una base de datos.

PostgreSQL 9.2:


    SELECT pg_terminate_backend(pg_stat_activity.pid)
    FROM pg_stat_activity
    WHERE pg_stat_activity.datname = 'target_db'
      AND pid <> pg_backend_pid();

PostgreSQL 9.1 y anteriores:

    SELECT pg_terminate_backend(pg_stat_activity.procpid)
    FROM pg_stat_activity
    WHERE pg_stat_activity.datname = 'target_db'
      AND procpid <> pg_backend_pid();


Mostrar las consultas en ejecución (antes de ver. 9.2)

SELECT procpid, age(query_start, clock_timestamp()), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;


Mostrar las consultas en ejecución (posterior ver. 9.2)

SELECT pid, age(query_start, clock_timestamp()), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;


Matar consulta en ejecución


SELECT pg_cancel_backend(procpid);


Matar consulta inactiva

SELECT pg_terminate_backend(procpid);


Comando VACUUM

VACUUM (VERBOSE, ANALYZE);


Todos los usuarios de bases de datos

select * from pg_stat_activity where current_query not like '


Todas las bases de datos y sus tamaño


select * from pg_user;


Todas las tablas y su tamaño, con / sin índices

select datname, pg_size_pretty(pg_database_size(datname))
from pg_database order by pg_database_size(datname) desc;


Las tasas de éxito de caché (no debe ser inferior a 0,99)

SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio FROM pg_statio_user_tables;


Tabla de tasas de utilización del índice  (no debe ser inferior a 0,99)

SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;


Cuántos índices hay en caché

SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio FROM pg_statio_user_indexes;


Volcar Base de datos en el host remoto a un archivo

$ pg_dump -U username -h hostname databasename > dump.sql


Importar el archivo de volcado en una base de datos existente

$ psql -d newdb -f dump.sql