Contar cantidad de registros en todas las tablas Postgresql

Varias veces he tenido que contar los registros de todas las tablas de una base en particular.
Dejo la consulta para poder hacerlo en postgresql


SELECT
nspname AS schemaname,relname,reltuples::integer
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
nspname NOT IN ('pg_catalog', 'information_schema') AND
relkind='r'
ORDER BY reltuples DESC;

Enmascarar datos en postgresql

Me paso que en una base postgresql teníamos que cambiar los nombres de las personas y sus documentos para que no se conocieran datos particulares de ellas.

Con la siguiente consulta lo realizamos

El campo del documento es un char de 20

UPDATE tabla_documento SET campo_documento = substring(md5(campo_documento) from 1 for 20);

El campo del nombre es un char muy grande

Update tabla_persona set campo_nombre = md5(campo_nombre);

Permisos en postgres para correr aplicaciones Genexus

Una aplicación genexus Evo3 que en desarrollo accedía a la base con el superusuario de postgres, al tener que pasarla a producción tuve que restringir algunos permisos sobre la base de datos por temas de seguridad, acá le copio los pasos que tuve que realizar




• Crear rol aplicación: grants_app_miapp
• Crear usuario aplicación: app_miapp
• Otorgar privilegios de: select, update, insert y delete, sobre todas las tablas de la base miapp
• Otorgar privilegios de: select y usage, sobre todas las secuencias de la base miapp

Comandos ejecutados:

-- Executing query:
CREATE ROLE grants_app_miapp
  SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;

-- Executing query:
ALTER ROLE grants_app_miapp IN DATABASE miapp
  SET search_path = "public";

-- Executing query:
COMMENT ON ROLE grants_app_miapp  IS 'Rol de aplicación de la base de datos miapp.';

-- Executing query:
CREATE ROLE app_miapp LOGIN
  PASSWORD ''
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

-- Executing query:
COMMENT ON ROLE app_miapp IS 'Usuario de aplicación de la base de datos miapp. Obtiene los privilegios a través del rol grants_app_miapp';

-- Executing query:
GRANT grants_app_miapp TO app_miapp;
Query returned successfully with no result in 42 ms.

-- Executing query:
grant select,update,insert,delete on all tables in schema public to grants_app_miapp;

-- Executing query:
grant select on all SEQUENCEs in schema public to grants_app_miapp;

-- Executing query:
grant usage on all SEQUENCEs in schema public to grants_app_miapp;

Matar consultas colgadas postgres

Muchas veces tenemos consultas que estan colgadas por consumir recursos o cualquier otro tema, para matarlas primero tenemos que quedarnos con el pid con la siguiente consulta:

psql -h servidor -U postgres -d postgres (nos conectamos al server)

postgres=# SELECT  * FROM pg_stat_activity WHERE datname='mi base de datos';

Luego podemos matar solo una consulta por el pid

postres=# SELECT pg_terminate_backend(numero de pid);

o matar todas las consultas de una base de datos

postgres=# SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname='mi base de datos';

Ver estructura de tabala en postgreSQL

Para ver la definicion de una tabla en postgres se ejecuta la siguiente consulta

SELECT * FROM information_schema.columns WHERE table_name = 'nombre tabla con comilla simple';

Consulta que esta corriendo en postgreSQL

Para saber que consulta esta corriendo en postgreSQL se debe de ejecutar una consulta, lo podes hacer por consola o por cualquier ide de consultas (sugiero utilizar SQL Workbench pero es indistinto)


SELECT * FROM pg_stat_activity ORDER BY 1;

Optimizacion PostgresSql

Cuando trabajamos con BD y movemos de una grandes volúmenes de datos, por ejemplo hacemos un delete de una tabla con miles o millones de registros el sistema la base de datos le cuesta volver a lo optimo que esperábamos que estuviera. En cada motor existe la forma de actualizar las estadísticas, que a muy grosero modo es saber cuantos registros tiene una tabla y cual es su mejor forma de abordarla.

En postgres yo he utilizados estas 2 formas, una para windows y otra para linux, y me ha funcionado bastante bien. Quizás haya otras formas pero con mis pocos conocimientos en postgres con esto pude volver a utilizar una tabla que tenia millones de registros y los borre de una, y cuando intentaba consultar algún registro demoraba muchísimo.


- Para windows
"C:\Program Files\PostgreSQL\9.3\bin\vacuumdb.exe" -a -U postgres -h 127.0.0.1
"C:\Program Files\PostgreSQL\9.3\bin\reindexdb.exe" -a -U postgres -h 127.0.0.1


- Para linux
#!/bin/bash

# reindexado
psql -U usuario postgres -c "reindex database rucaf"

# vacuum sobre todas las bases de datos
su postgres -c "vacuumdb -a"

# actualizacin de estadisticas
su postgres -c "vacuumdb -z rucaf"

Backup y Restore de PostgreSql por consola

Tengo un servidor Linux que es el equipo de testing con postgresql, y el equipo Windows también con postgresql, necesito hacer un backup en el Windows y tirarlo en el Linux.

En la carpeta de instalación de PostrgreSQL hay un ejecutable Pd_dump.exe, lo que hay que ejecutar desde la consola es lo siguiente:

Cd c:/…/bin/

Pg_dump.exe –C  –U usuario base > archivo.respaldo.sql

-C es para incluir el Create

-U es para decirle cual es el usuario

base se debe de cambiar por la base a respaldar

archivo.respaldo.sql es el nombre del sql a crear, cambiarlo

luego desde Linux para levantar la base debemos de hacer lo siguiente:

su postgres

psql < archivo.respaldo.sql

Con esto tenemos la base que estaba en el equipo de desarrollo Windows en el equipo de Testing Linux

Instalación de Postgresql, Java, Jboss, en Linux Centos, para alojar aplicación genexus

En el siguiente post voy a comentar todos los pasos que tuve que dar para poder tener una máquina virtual (en mi caso pero podría ser un equipo físico) con Jboss y que funcione una aplicación generada con genexus.

Detalle de software que utilice.

Linux Centos 6.4 minimal

Genexus Evo3 U1

Java 7

PostgreSql 9

Jboss 7.1.1

Voy a ir contando paso a paso lo que hice y por qué.

Primero tengo que instalar Centos, eso no lo voy a detallar pero comento que descargue la versión 6.4 que la tuve que buscar bastante porque estaba discontinuada por no tener mantenimiento (necesitaba que fuera esa versión porque estaba replicando un ambiente de producción). Luego de tener la instalación completa, la actualizamos

yum update

yum upgrade

Luego de tener el equipo instalado y funcionado con las últimas actualizaciones vamos a empezar a instalar el software base.

Con el siguiente comando vemos la versión de java que podríamos instalar

yum search java | grep 'java-'

Buscamos la versión 7 y corremos lo siguiente

yum install java-1.7.0-openjdk-src.x86_64

Ahora vamos a instalar PostgreSQL, con la misma idea buscaos la versión que tenemos en los repositorios e instalamos la que queramos.

yum install postgresql-server.x86_64

Luego vamos a configurar postgresql, debemos de seguir la siguiente secuencia

service postgresql initdb

service postgresql start

su postgres

psql

ALTER USER postgres WITH PASSWORD ‘pass_que_prefiera;

\q

Exit

Si vamos a acceder desde afuera deberíamos de tocar los siguientes archivos

/var/lib/pgsql/data/pg_hba.conf

En mi caso como si quiera accede desde afura tuve que dejarlo asi:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only

local   all         all                               trust

# IPv4 local connections:

host    all         all         127.0.0.1/32          trust

# IPv6 local connections:

host    all         all         ::1/128               trust

#######ident

host    all         all         192.168.73.153/32           trust

Y el archivo postgresql.conf debo de buscar las líneas que están comentadas y des comentarlas y modificarlas para que quede así:

listen_addresses = '*'

port = 5432 

Ahora debemos de continuar instalando jboss, para eso instalamos algunas herramientas previas

yum install wget

yum install unzip

Ahora descargamos e instalamos jboss

wget http://download.jboss.org/jbossas/7.1/jboss-as-7.1.1.Final/jboss-as-7.1.1.Final.zip

unzip jboss-as-7.1.1.Final.zip -d /usr/share

adduser jboss

chown -fR jboss.jboss /usr/share/jboss-as-7.1.1.Final/

su jboss

cd /usr/share/jboss-as-7.1.1.Final/bin/

./add-user.sh (ManagementRealm, usuario foo y pass)

Ahora ejecutamos jboss y probamos que funciones accediendo por el navegador, por las dudas bajamos el firewall porque por defecto tiene reglas y pueden hacernos ruido en este paso.

Limpiar las reglas de iptables (firewall)

iptables -F

iptables -X

iptables -t nat -F

iptables -t nat -X

iptables -t mangle -F

iptables -t mangle -X

iptables -P INPUT ACCEPT

iptables -P FORWARD ACCEPT

iptables -P OUTPUT ACCEPT

Levantar jboss (ver que no de ningún error)

/usr/share/jboss-as-7.1.1.Final/bin/standalone.sh -Djboss.bind.address=0.0.0.0 -Djboss.bind.address.management=0.0.0.0&

Acceder de cualquier navegador a la ip del equipo y debería de mostrarnos la página de jboss inicial.

Con esto tendremos casi todo, pero lo mejor es dejar el firewall con las reglas que tiene y agregar nuestras reglas.

Les dejo un ejemplo del archivo /etc/sysconfig/iptables en el que expongo el puerto 8080 para jboss, el 9990 para la consola de administración de jboss, y el 5432 para conectarme a postgres desde fuera (esto si es producción no se debería de agregar)

############### archivo /etc/sysconfig/iptables ########################

# Generated by iptables-save v1.4.7 on Wed Apr 15 07:59:02 2015

*filter

:INPUT ACCEPT [0:0]

:FORWARD ACCEPT [0:0]

:OUTPUT ACCEPT [13:1644]

-A INPUT -p tcp -m tcp --dport 8080 -j ACCEPT

-A INPUT -p tcp -m tcp --dport 5432 -j ACCEPT

-A INPUT -p tcp -m tcp --dport 9990 -j ACCEPT

-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT

-A INPUT -p icmp -j ACCEPT

-A INPUT -i lo -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT

-A INPUT -j REJECT --reject-with icmp-host-prohibited

# jboss

-A FORWARD -p tcp -m tcp --dport 8080 -j ACCEPT

# postgresql

-A FORWARD -p tcp -m tcp --dport 5432 -j ACCEPT

# jboss admin

-A FORWARD -p tcp -m tcp --dport 9990 -j ACCEPT

-A FORWARD -j REJECT --reject-with icmp-host-prohibited

-A OUTPUT -p tcp -m tcp --dport 8080 -j ACCEPT

-A OUTPUT -p tcp -m tcp --dport 5432 -j ACCEPT

-A OUTPUT -p tcp -m tcp --dport 9990 -j ACCEPT

COMMIT

# Completed on Wed Apr 15 07:59:02 2015

############### FIN ######################################

Ademas deberiamos de configurar para que jboss se levantara cada vez que se reinicie el servidor, yo cree un arrancador muy basico que se puede configurar a nuestras necesidades. Este archivo se podria llamar /etc/init.d/jobss711

######################### archivo /etc/init.d/jobss711 #################3

#!/bin/bash 

### BEGIN INIT INFO 

# Provides:          jbossas7 

# Required-Start:    $local_fs $remote_fs $network $syslog 

# Required-Stop:     $local_fs $remote_fs $network $syslog 

# Default-Start:     2 3 4 5 

# Default-Stop:      0 1 6 

# Short-Description: Start/Stop JBoss AS 7 

### END INIT INFO 

# chkconfig: 35 92 1 

 

## Include some script files in order to set and export environmental variables 

## as well as add the appropriate executables to $PATH. 

[ -r /etc/profile.d/java.sh ] && . /etc/profile.d/java.sh 

[ -r /etc/profile.d/jboss.sh ] && . /etc/profile.d/jboss.sh 

 

JBOSS_HOME=/usr/share/jboss-as-7.1.1.Final

 

AS7_OPTS="$AS7_OPTS -Dorg.apache.tomcat.util.http.ServerCookie.ALLOW_HTTP_SEPARATORS_IN_V0=true"   ## See AS7-1625 

AS7_OPTS="$AS7_OPTS -Djboss.bind.address.management=0.0.0.0" 

AS7_OPTS="$AS7_OPTS -Djboss.bind.address=0.0.0.0" 

 

case "$1" in 

    start) 

        echo "Starting JBoss AS 7..." 

        #sudo -u jboss sh ${JBOSS_HOME}/bin/standalone.sh $AS7_OPTS           ##  If running as user "jboss" 

        #start-stop-daemon --start --quiet --background --chuid jboss --exec ${JBOSS_HOME}/bin/standalone.sh -- $AS7_OPTS   ## Ubuntu 

        ${JBOSS_HOME}/bin/standalone.sh $AS7_OPTS & 

    ;; 

    stop) 

        echo "Stopping JBoss AS 7..." 

        #sudo -u jboss sh ${JBOSS_HOME}/bin/jboss-admin.sh --connect command=:shutdown            ##  If running as user "jboss" 

        #start-stop-daemon --start --quiet --background --chuid jboss --exec ${JBOSS_HOME}/bin/jboss-admin.sh -- --connect command=:shutdown     ## Ubuntu 

        ${JBOSS_HOME}/bin/jboss-cli.sh --connect command=:shutdown 

    ;; 

    *) 

        echo "Usage: /etc/init.d/jbossas7 {start|stop}"; exit 1; 

    ;; 

esac 

 

exit 0

######################### FIN archivo #################

Con chkconfig podremos agregarlo para que inicie y finalice cuando arranquemos o apaguemos el equipo, ver como se adapta a cada instalación de Linux, por ejemplo si quieren que se arranque cuando iniciamos entorno grafico o no. Por ejemplo algo asi

chkconfig –level 345 jboss711 on

Probemos en reiniciar el equipo y probar que siga funcionando jboss y postgresql

Esta todo funcionando, ahora lo único que falta es crear el war desde gx, con el Depoyment wizard sin hacer ningún cambio, lo único que hay que hacer es agregar todas las clases al war generado (se puede editar con winzip) ya que genexus solo agrega algunas y si se quiere agregar cuando se está generando desde la aplicación DW de gx se cae (en mi caso, pueden probarlo ustedes).

Y por último hay que tirar el war en /usr/share/jboss-as-7.1.1.Final/standalone/deployments, mirar el log y ver que no tire ningún error, y por último en un navegador accede a la aplicación.

Recordar que el link sería algo así:

http://ip:8080/aplicacion/servlet/com.paquete.login

Backup / Restore base de datos PostgreSQL

¿Cómo exportar e importar una base de datos postgreSQL?

La forma fácil, pero que no siempre funciona es hacerlo por el PgAdmin, ahí es gráfico y se puede hacer un bakup y un restore.

Lo que voy a explicar son un par de formas de hacerlo desde consola, para los que utilizamos Linux, que también se puede utilizar de Windows.

Backup:  $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

Restore: $ psql -U {user-name} -d {desintation_db} -f {dumpfilename.sql}

Backup:  $ pg_dump -U user bd_name > archive_name.sql

Restore: $ psql -U user db_name < /directory/archive.sql

Instalación y configuración básica de PostgreSQL

Voy a explicar muy brevemente los pasos que tuve que hacer para instalar en un Linux Ubutu 13.04 un postgreSQL y configurarlo para acceder desde otro equipo con PgAdmin.

Primero se necesita obviamente un Linux instalado y actualizado.

El primer paso es instalar, yo utilizo apg-get install postgresql.

Luego debemos de ir a la carpeta /etc/postgresql/9.1/mail, esto obviamente cambia depende de la versión instalada.

Editamos el archivo postgresql.conf

Buscamos la siguiente linea y la dejamos asi:

#listen_addresses = 'localhost'

listen_addresses = '*'

Luego tenemos que habilitar desde que red vamos a acceder, esto lo hacemos en el archivo pg_hba.conf que qudaria algo asi:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only

local   all             all                                     md5

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

host    all             all             192.0.0.1/32                      md5

# IPv6 local connections:

#host    all             all             ::1/128                 ident

Con esto estamos habilitando toda la red 192, es decir cualquier equipo que tenga una ip 192.*.*.* puede acceder a la base de datos.

Debemos de setear el pass del superusuario postgre

# su postgres -c psql postgres

postgres=# alter user postgres with password 'mi_contraseña';

postgres=# \q

Con reiniciar el servicio de postgre (o directamente el equipo) quedaría todo pronto para acceder a nuestra base de datos postgreSQL desde otro equipo que no sea el server.

Show full processlist para postgresSQL

Me enfrente a un problema con una aplicación en postgreSQL y lo primero que quise hacer es el conocido (por mi) show full processlist, para detectar que consultas sql se están corriendo en el motor. En postgreSQL esto no funciona entonces buscando un poco en internet encontré la forma de hacerlo.

La consulta que hay que correr es lo siguiente

select * from pg_stat_activity;

esto se puede hacer de 2 formas, por ejemplo en pgAdmin correrla con cualquier sql. O también podemos hacerlo con consola, entonces se debe de seguir los siguientes pasos:

-          # su postgres

-          # [postgres@srv]$ psql

-          postgres=# select * from pg_stat_activity;

Con esto se pueden ver las consultas igual que en MySql.