Postgresql: Obtener el tamaño de base de datos, esquemas y tablas

 Algunas consultas para obtener el tamño de lo que ocupan tablas, esquemas y bases de datos en postgres:

 
-- Tamaño de las bases de datos
SELECT pg_database.datname,
       pg_shadow.usename AS owner,
       pg_database_size(pg_database.datname) AS size,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS pretty_size
FROM pg_database                                        
JOIN pg_shadow ON pg_database.datdba = pg_shadow.usesysid;

-- Tamaño de los esquemas
SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100 as porcentaje
FROM (                                                                     
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY 1
;



-- Listado de tablas de todos los esquemas por tamaño
select 
    schemaname,
    relname as table_name,
    pg_size_pretty(pg_relation_size(schemaname ||'.'||relname)) as table_size ,
	pg_relation_size(schemaname ||'.'||relname)
from 
(
  select 
      schemaname, 
      relname 
  from pg_stat_user_tables order by relname 
) t
order by 4 desc;


SELECT pg_database.datname,
       pg_shadow.usename AS owner,
       pg_database_size(pg_database.datname) AS size,
   pg_size_pretty(pg_database_size(pg_database.datname)) AS pretty_size
FROM pg_database                                        
JOIN pg_shadow ON pg_database.datdba = pg_shadow.usesysid;

Postgres: Eliminar procesos con más de un día

Para eliminar consultas y procesos que llevan más de un día ejecutándose, y que están en estado idle, por ejemplo tras una desxconexión abrupta de red

Suave:

select  pg_cancel_backend(pid) from pg_stat_activity where state = 'idle' and backend_start < NOW() - INTERVAL '1 day' or  query_start < NOW() - INTERVAL '1 day' ;

 Dura:

select  pg_terminate_backend(pid) from pg_stat_activity where state = 'idle' and backend_start < NOW() - INTERVAL '1 day' or  query_start < NOW() - INTERVAL '1 day' ;

Instalación de Pgadmin4 en opensuse leap 42.2 mediante PIP

Tras intentar de varias formas, probando de varias maneras: varios repositorios de software http://software.opensuse.org/,  bajando el código código fuente ( no conseguía resolver la dependencia python3-htmlmin).... Esta es una forma en la que he conseguido que funcione bien. La instalación ha sido con opensuse leap 42.2 y PgAdmin 4 v1.4

1- Instalación de dependencias, quizá necesites alguna más dado que en las pruebas tuve que hacer varias instalaciones relacionadas con python:

zypper up
zypper in python-virtualenv python-pip libpq5 libpqxx-5_0 python-devel

2- Instalación mediante pip, recuerda cambia la ruta, en el ejemplo se instalará en ~/Programas/pgadmin4/:

cd ~/Programas/
virtualenv pgadmin4
cd pgadmin4
source bin/activate
wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v1.4/pip/pgadmin4-1.4-py2.py3-none-any.whl
sudo pip install --upgrade pip
sudo pip install pgadmin4-1.4-py2.py3-none-any.whl

3- Ejecución del servidor. En la primera ejecución nos preguntará por un usuario y contraseña, que será el que usemos en posteriores accesos.:

sudo python /usr/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py

4- Acceso a la web, en el navegador ponemos la direccion y como usuario y password, los que hemos puesto en el paso 3:

http://localhost:5050/

Postgresql: tamaño de una tabla

Para obtener el tamaño de una tabla en postgres

select pg_size_pretty(pg_total_relation_size('TABLA'));

Postgres : Ejecutar script en psql sin password

Cuando ejecutamos un script en psql no hay opciones para pasar el password como parámetro. PostgreSQL ofrece dos mecanismos para que no pregunte la constraseña. Esto es útil cuando vamos a ejecutar algún tipo de script.

1. Variable. La primera opción es definir la variable PGPASSWORD en nuestro script. Podemos poner export PGPASSWORD=miContraseña


2. Archivo.  Podemos crear el archivo ~/.pgpass (está el home), hay que darle los permisos 600 ( chmod 600 ~/.pgpass ) . Si tiene otros permisos postgreSQL lo ignora.El formato del archivo es el siguiente:
   hostname:port:database:username:password
   Podemos utilizar el * como comodín en lugar de alguna de las partes
   Podemos cambiar la ruta del archivo cambiando la variable PGPASSFILEpara que apunte a otra ruta

Usando cualquiera de estos métodos de postgres  no nos pedirá la contraseña al ejecutar los script. Se recomienda por motivos de seguridad almacenar la constraseña en el archivo, porque las variables podrían ser visibles por otros usuarios, mientras que el archivo no.

FUENTE:
http://www.postgresql.org/docs/9.1/interactive/libpq-envars.html

http://www.postgresql.org/docs/9.1/interactive/libpq-pgpass.html