postgres – btree level 1 not found in index

We have a jira instance which uses the postgres database to store its information. The server had roughly 50 Gb of disk, so it ran out of space before we noticed. We extended the partition to 100 Gb but apparently it was too late.
Jira started complaining about it with an 500 internal server error (Estado HTTP 500 – Could not determine database type. (Conexión rechazada. Verifique que el nombre del Host y el puerto sean correctos y que postmaster este aceptando conexiones TCP/IP.)). Viewing the logs we found Caused by: java.net.ConnectException: Connection refused

We checked our database and database settings for external connections and couldn’t connect.

Then we tried to start the postgres service

sudo service postgresql start

2018-12-04 09:01:59.268 CST [21281] LOG:  database system was interrupted while in recovery at 2018-12-04 08:59:20 CST
2018-12-04 09:01:59.268 CST [21281] HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
2018-12-04 09:01:59.631 CST [21281] LOG:  database system was not properly shut down; automatic recovery in progress
2018-12-04 09:01:59.636 CST [21281] LOG:  redo starts at 7/CC020C68
2018-12-04 09:01:59.639 CST [21281] LOG:  record with zero length at 7/CC07E998
2018-12-04 09:01:59.639 CST [21281] LOG:  redo done at 7/CC07E968
2018-12-04 09:01:59.639 CST [21281] LOG:  last completed transaction was at log time 2018-12-03 10:29:49.99078-06
2018-12-04 09:01:59.641 CST [21281] FATAL:  btree level 1 not found in index "19881"

VITALLY IMPORTANT FIRST RESPONSE

I did a backup of the postgres data files

ssh admin@jira.server
sudo tar cvf postgres.bkp.tar /var/lib/postgresql/

Copied it to my local machine

rsync -av admin@jira.server:postgres.bkp.tar .

then installed the same version (series) that is on the server into my local machine.
Since I have ubuntu 18.04 I had to add the postgres repository to install postgres version 9.3.

echo “deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main” | sudo tee /etc/apt/sources.list.d/pgdg.list
wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
sudo apt update
sudo apt install postgresql-9.3 postgresql-contrib-9.3

Then I tried to run postgres locally with the backup data

cd /tmp
tar xvf postgres.bkp.tar
sudo su postgres
/usr/lib/postgresql/9.3/bin/postgres -D /tmp/var/lib/postgresql/9.3/main/

it complained about not finding the configuration file so I just copied it from the default dirs

cp /etc/postgresql/10/main/postgresql.conf /tmp/var/lib/postgresql/9.3/main/
cp -R /etc/postgresql/10/main/conf.d/ /tmp/postgres/var/lib/postgresql/9.3/main/

edit the postgresql.conf file and change the data dir appropiately

data_directory = ‘/tmp/var/lib/postgresql/9.3/main’

then I tried again (but this time in single mode) and it reproduced exactly the same error as on server. Great!

postgres@elite-tsj:/tmp$ /usr/lib/postgresql/9.3/bin/postgres --single -P -d 1 -D /tmp/var/lib/postgresql/9.3/main/
2018-12-04 09:01:59.268 CST [21281] LOG:  database system was interrupted while in recovery at 2018-12-04 08:59:20 CST
2018-12-04 09:01:59.268 CST [21281] HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
2018-12-04 09:01:59.631 CST [21281] DEBUG:  checkpoint record is at 7/CC025C50
2018-12-04 09:01:59.631 CST [21281] DEBUG:  redo record is at 7/CC020C68; shutdown FALSE
2018-12-04 09:01:59.631 CST [21281] DEBUG:  next transaction ID: 0/9547804; next OID: 227497
2018-12-04 09:01:59.631 CST [21281] DEBUG:  next MultiXactId: 3; next MultiXactOffset: 5
2018-12-04 09:01:59.631 CST [21281] DEBUG:  oldest unfrozen transaction ID: 676, in database 1
2018-12-04 09:01:59.631 CST [21281] DEBUG:  oldest MultiXactId: 1, in database 1
2018-12-04 09:01:59.631 CST [21281] DEBUG:  transaction ID wrap limit is 2147484323, limited by database with OID 1
2018-12-04 09:01:59.631 CST [21281] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 1
2018-12-04 09:01:59.631 CST [21281] LOG:  database system was not properly shut down; automatic recovery in progress
2018-12-04 09:01:59.634 CST [21281] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2018-12-04 09:01:59.636 CST [21281] LOG:  redo starts at 7/CC020C68
2018-12-04 09:01:59.639 CST [21281] LOG:  record with zero length at 7/CC07E998
2018-12-04 09:01:59.639 CST [21281] LOG:  redo done at 7/CC07E968
2018-12-04 09:01:59.639 CST [21281] LOG:  last completed transaction was at log time 2018-12-03 10:29:49.99078-06
2018-12-04 09:01:59.639 CST [21281] DEBUG:  resetting unlogged relations: cleanup 0 init 1
2018-12-04 09:01:59.641 CST [21281] FATAL:  btree level 1 not found in index "19881"

since this data is a copy i just executed pg_resetxlog as shown here.
Be aware that use of the pg_resetxlog utility should be done as an absolute last resort, and there are still some things you should try first..

/usr/lib/postgresql/9.3/bin/pg_resetxlog /tmp/var/lib/postgresql/9.3/main/

it complained I had to force it, there you go

/usr/lib/postgresql/9.3/bin/pg_resetxlog -f /tmp/var/lib/postgresql/9.3/main/

done! Now let’s see if it’s working again

sudo su postgres
/usr/lib/postgresql/9.3/bin/postgres -D /tmp/var/lib/postgresql/9.3/main/

boom! it’s working.

Now it’s time to do a backup with sql dump (pg_dump) or better yet a full sql dump (pg_dumpall).

In another terminal (or tab)

sudo su postgres
pg_dumpall -p 5433 > /tmp/postgres-full.dmp

in my case I had to specify the port (-p 5433).

Now that I have a full backup I can try the same on production.

/usr/lib/postgresql/9.3/bin/pg_resetxlog -f /var/lib/postgresql/9.3/main/
sudo service postgresql start

thank the gods! (to recall the option pointed here)

Anuncios

SQL – Limitar el número de resultados en Oracle

Al realizar una consulta que debía regresarme un sólo resultado, me regresaba 2 e incluso 4. Esto se debía a que existen registros ‘duplicados’ -la tabla no tiene una llave primaria, huh? no pregunten-

Bueno, entonces me interesaba obtener sólo el registro más actual, por suerte, la tabla si tiene un campo con la fecha que se creó el registro.

Para limitar el número de resultados varía de acuerdo a la base de deatos que estes utilizando, por ejemplo, en MySQL es con la palabra LIMIT, en SQL Server con TOP y en Oracle con ROWNUM

Así que, en Oracle, la consulta más simple que se me ocurrió fue la siguiente

SELECT *
FROM USERS
WHERE ID = :ID
AND ROWNUM <= 1
ORDER BY FECHA_ALTA DESC

Pero no me arrojó los resultados esperados, de hecho, siempre me regresaba el mismo registro aunque creará un registro más actual. Esto se debe al orden en que Oracle ejecuta los comandos de la sentencia -primero ejecuta la claúsula WHERE que regresa un sólo registro y luego lo ordena de acuerdo a ORDER BY (si, ordena un sólo registro)-

La solución es realizar una subconsulta de la siguiente manera

SELECT * FROM (
SELECT *
FROM USERS
WHERE ID = :ID
ORDER BY FECHA_ALTA DESC
) WHERE ROWNUM <= 1

Fuentes:
http://www.w3schools.com/sql/sql_top.asp
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Ubuntu – Crear una base de datos postgresql para un proyecto

Para comenzar un proyecto, de las primeras cosas que necesitamos es una base de datos. En ubuntu podemos utilizar postgresql.

Para instalar postgres basta con ejecutar el siguiente comando

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Una vez instalado podemos conectarnos como el usuario postgres (super admin)

sudo -i -u postgres
psql

Una vez en el prompt de postgresql podemos ejecutar los siguientes comandos

\conninfo    # nos muestra información de nuestra conexión
\conn database_name    # cambiar la base de datos activa
\d    # lista los objetos de la base de datos
\dt    # lista las tablas de la base de datos
\q    # salir del prompt de postgresql

Ya fuera del prompt de postgresql podemos crear un usuario y su base de datos de la siguiente manera

createuser -D -A -P username
createdb -O username dbname

El primer comando crea un usuario sin privilegio de crear bases de datos (-D), ni agregar usuarios (-A) y nos preguntará la contraseña para el usuario (-P). El segundo comando crea la base de datos y asigna como propietario al usuario que especifiquemos.

Para conectarnos como un usuario diferente necesitamos hacerlo de la siguiente manera

psql -U username -h 127.0.0.1 database

Fuentes:
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04
http://stackoverflow.com/questions/8420761/postgres-authentication-fails-when-try-to-login-with-different-unix-user
https://help.ubuntu.com/community/PostgreSQL#Managing_users_and_rights

Spring – Utilizar un pool de conexiones y una clase auxiliar para la ejecución de SQL

La mayoría de aplicaciones requieren acceso a una base de datos. En java existe JDBC que nos permite realizar esta tarea.
En una aplicación distribuida y escalable nos vemos en la necesidad de utilizar un pool de conexiones para no causar un problema de rendimiento. Por ejemplo, he creado servicios web (REST) que deseo sean consumidos desde dispositivos móviles, para ello necesito que los servicios sean escalables.

Ya que utilicé Spring Roo para crear la plantilla inicial del proyecto, este me agregó la siguiente configuración inicial.

applicationContext.xml

...
    <bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource">
        <property name="driverClassName" value="${database.driverClassName}"/>
        <property name="url" value="${database.url}"/>
        <!--<property name="username" value="${database.username}"/>-->
        <!--<property name="password" value="${database.password}"/>-->
        <property name="testOnBorrow" value="true"/>
        <property name="testOnReturn" value="true"/>
        <property name="testWhileIdle" value="true"/>
        <property name="timeBetweenEvictionRunsMillis" value="1800000"/>
        <property name="numTestsPerEvictionRun" value="3"/>
        <property name="minEvictableIdleTimeMillis" value="1800000"/>
        <property name="validationQuery" value="SELECT 1 FROM DUAL"/>
    </bean>
...

como podemos ver, esta configuración crea un DataSource de tipo org.apache.commons.dbcp.BasicDataSource. Este tipo de DataSource crea un pool de conexiones automáticamente. Genial!

Ahora, ¿cómo lo utilizamos en nuestras clases para poder ejecutar una consulta SQL?

Podríamos utilizarlo de la forma tradicional con JDBC

...
    @Autowired
    public DistritosServiceImpl(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public List<Distrito> getDistritos() {
        List<Distrito> distritos = new ArrayList<Distrito>();
        String sql = 
                "select id, nombre " +
                        "from distritos";
        Connection con = null;
        try{
            con = this.dataSource.getConnection();
            PreparedStatement stmt = con.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                distritos.add(new Distrito(rs.getInt("id"), rs.getString("nombre")));
            }
        }catch (Exception e){
            e.printStackTrace();
            if(con != null){
                try {
                    con.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
        return distritos;
    }
...

pero existe una mejor manera, utilizando JdbcTemplate

...
    @Autowired
    public DistritosServiceImpl(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public List<Distrito> getDistritos() {
        String sql =
                "select id, nombre " +
                        "from distritos";
        return jdbcTemplate.query(sql, new RowMapper<Distrito>() {
            @Override
            public Distrito mapRow(ResultSet rs, int i) throws SQLException {
                return new Distrito(rs.getInt("id"), rs.getString("nombre"));
            }
        });
    }
...

Fuentes:

SQL – Obtener el año actual

En Oracle, si necesitas utilizar el año actual (de la base de datos) para el valor de alguna columna de tipo Number, lo podríamos hacer de la siguiente manera

    INSERT INTO TEXP
            (FOLIO,
             NUMERO,
             ANYO
            )
         VALUES (SEQ_TEXP.NEXTVAL,
             (SELECT MAX (NUMERO) + 1
                FROM TEXP
               WHERE ANYO = TO_NUMBER(TO_CHAR (SYSDATE, 'YYYY'))
             ),
             TO_NUMBER(TO_CHAR (SYSDATE, 'YYYY'))
            );

En este caso la tabla TEXP tiene 3 columnas de tipo Number (FOLIO, NUMERO Y ANYO).
Para obtener el FOLIO utilizamos una secuencia, para obtener el NUMERO hacemos una subconsulta para saber cúal es el valor máximo para el año actual y sumamos 1, para el ANYO utilizamos el año actual.
Pues resulta que no es necesario utilizar la función TO_NUMBER, la base de datos hace la conversión automática de un valor de tipo CHAR (o VARCHAR) a NUMBER y visceversa. De hecho la conversión que hace la base de datos es más eficiente que utilizar la función TO_NUMBER (el doble, 20ms contra 10ms para esta consulta sencilla).

    INSERT INTO TEXP
            (FOLIO,
             NUMERO,
             ANYO
            )
         VALUES (SEQ_TEXP.NEXTVAL,
             (SELECT MAX (NUMERO) + 1
                FROM TEXP
               WHERE ANYO = TO_CHAR (SYSDATE, 'YYYY')
             ),
             TO_CHAR (SYSDATE, 'YYYY')
            );

Fuentes:
http://stackoverflow.com/questions/1119710/how-do-i-get-the-current-year-using-sql-on-oracle

Eclipse – Consulta dinámica en BIRT

Para modificar la consulta de un DataSet puedes utilizar la sección “Property Binding”

en mi caso necesitaba reemplazar en la conulta el valor de un parámetro.

Para reemplazar una cadena con otra lo puedes hacer en el “Expression Builder” de la siguiente manera



var q=new Packages.java.lang.String("SELECT COUNT(*) AS TOTAL \
FROM @OFICINA.EMPLEADOS A, @OFICINA.PUESTOS R, \
@OFICINA.SALARIOS D, @OFICINA.DEPTOS O \
WHERE A.NID_ACO_EXP= R.NID_ACO \
R.NID_DMD=D.NID_DMD AND R.NORDEN=O.NID_ORDEN \
AND TRUNC(R.FECHA) BETWEEN :INICIO AND :FIN");
q.replaceAll("@OFICINA",params["OFICINA"].value);

el código es javascript, pero podemos utilizar clases Java también. En este caso utilizamos la clase String de Java en vez de Javascript, porque el método de javascript no reemplaza todas las coincidencias.

Fuentes:
birt exchange

Oracle – update con join

Si necesitas actualizar los registros de una tabla de acuerdo a los valores de otra, neceistas hacer un update con un join. En oracle, este tipo de actualización se realiza con una consulta anidada de la siguiente manera:

update (
    select t1.col1, t2.col2
    from tabla1 t1
       join tabla2 t2 on t1.id_tabla2=t2.id
          and t1.col1=condicion1
 )
 set col1=col2

Fuentes:
http://stackoverflow.com/questions/9410870/plsql-update-with-inner-join