Install Postgresql 9.6 on Ubuntu LTS 16.04, 14.04, 12.04 and 16.10


For any Ubuntu version you need to can do this by the official PostgreSQL Apt Repository.

Ubuntu Yakkety (16.10)

(It is not officialy supported but it works using the Xenial repository.)

sudo add-apt-repository "deb xenial-pgdg main"
wget --quiet -O - | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

Ubuntu Xenial (16.04)

sudo add-apt-repository "deb xenial-pgdg main"
wget --quiet -O - | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

Ubuntu Trusty (14.04)

sudo add-apt-repository "deb trusty-pgdg main"
wget --quiet -O - | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

Ubuntu Precise (12.04)

sudo add-apt-repository "deb precise-pgdg main"
wget --quiet -O - | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6
psql -c "ALTER USER postgres WITH PASSWORD 'sis98'" -d template1
sudo pg_dropcluster --stop 9.6 main
sudo pg_createcluster --locale en_US.UTF-8 9.6 main



Install pgAdmin 4 on Ubuntu 16.04

or pgAdmin 4 v1.2 on Ubuntu 16.04, according to the download page and desktop deployment:

Install dependencies, create a virtual environment, download, install & configure

sudo apt-get install virtualenv python-pip libpq-dev python-dev

virtualenv pgadmin4
cd pgadmin4
source bin/activate


pip install pgadmin4-1.2-py2-none-any.whl


Write the SERVER_MODE = False in lib/python2.7/site-packages/pgadmin4/ to configure to run in single-user mode:

echo "SERVER_MODE = False" >> lib/python2.7/site-packages/pgadmin4/


python lib/python2.7/site-packages/pgadmin4/

Access at http://localhost:5050


Other option is to use docker and a docker image provided by thaJeztah –

$ docker run --rm -p 5050:5050 thajeztah/pgadmin4




Send mail in Apache TomEE

Make javax.mail and impl as maven scope provided, as TomEE comes with geronimo mail implementation.

Also use resource injection of mail session:

(If mail account is Gmail, turn ON ‘Allow less secure apps’)

In Tomee.xml:

<?xml version="1.0" encoding="UTF-8"?>
    <!-- see -->
    <!-- activate next line to be able to deploy applications in apps -->
    <!-- <Deployments dir="apps" /> -->
    <Resource id="tomee/mail/GMailSMTP" type="javax.mail.Session">
    mail.smtp.user=<!-- your email address -->
    password=<!-- your password, and not 'mail.smtp.password' -->

Injection of resource:

@Path(value = "workline")
public class MailService {
    @Resource(mappedName = "java:comp/env/tomee/mail/GMailSMTP")
    private Session smtpSession;
    public boolean sendMail() throws NamingException {
        final Message message = new MimeMessage(this.smtpSession);
        try {
            message.setRecipients(Message.RecipientType.TO, new Address[]{
                new InternetAddress("")
            message.setSubject("Email from TomEE");
            message.setSentDate(new Date());
            message.setText("Email from TomEE");
        } catch (Exception e) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, e);
            return false;
        return true;

A raw implementation:

package com.origami.rpp.util;

import com.origami.config.SisVars;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;

 * @author Origami
public class Email {

    protected List<File> adjuntos = new ArrayList<>();
    protected String usuarioCorreo = SisVars.correo;
    protected String password = SisVars.pass;
    protected String rutaArchivo1;
    protected String nombreArchivo1;
    protected String rutaArchivo2;
    protected String nombreArchivo2;
    protected String destinatario;
    protected String copiaOcultaBCC;
    protected String copiaCC;
    protected String asunto;
    protected String mensaje;

    public Email(String destinatario, String copiaOcultaBCC, String copiaCC, String asunto, String mensaje, List<File> files) {
        this.destinatario = destinatario;
        this.copiaOcultaBCC = copiaOcultaBCC;
        this.copiaCC = copiaCC;
        this.asunto = asunto;
        this.mensaje = mensaje;
        if(files != null) this.adjuntos = files;

    public Email(String destinatario, String asunto, String mensaje, List<File> files) {
        this(destinatario, null, null, asunto, mensaje, files);

    public boolean sendMail() {
        try {
            Properties props = new Properties();
            props.setProperty("mail.transport.protocol", "smtp");
            props.setProperty("", SisVars.smtp_Host);
            props.setProperty("mail.smtp.starttls.enable", "true");
            props.setProperty("mail.smtp.port", SisVars.smtp_Port);
            props.setProperty("mail.smtp.user", usuarioCorreo);
            props.setProperty("mail.smtp.auth", "true");
            Session session = Session.getInstance(props, null);
            //CUERPO DEL MENSAJE
            MimeMessage mimeMessage = new MimeMessage(session);
            mimeMessage.setFrom(new InternetAddress(usuarioCorreo, "EMPRESA PUBLICA MUNICIPAL DEL REGISTRO DE LA PROPIEDAD DEL CANTON PORTOVIEJO EP"));
            mimeMessage.setSentDate(new Date());
            mimeMessage.addRecipients(Message.RecipientType.TO, InternetAddress.parse(destinatario));
            if (copiaOcultaBCC != null) {
                mimeMessage.addRecipients(Message.RecipientType.BCC, InternetAddress.parse(copiaOcultaBCC));
            if (copiaCC != null) {
                mimeMessage.addRecipients(Message.RecipientType.CC, InternetAddress.parse(copiaCC));
            //TEXTO DEL MENSAJE
            MimeBodyPart texto = new MimeBodyPart();
            Multipart multipart = new MimeMultipart();
            MimeBodyPart file;
            for (File f : adjuntos ) {
                file = new MimeBodyPart();
            // ENVIAR MENSAJE
            Transport transport = session.getTransport("smtp");
            transport.connect(usuarioCorreo, password);
            transport.sendMessage(mimeMessage, mimeMessage.getAllRecipients());
        } catch (MessagingException ex) {
            Logger.getLogger(Email.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception e) {
            Logger.getLogger(Email.class.getName()).log(Level.SEVERE, null, e);
        return true;


Damaged or corrupt SQLite database recovery

Download sqlite binaries from:

I will use sqlite-tools-win32 in Windows 10:

Following the instructions from spiceworks article  , I check the file integrity:

# (I use PowerShell as administrator)
.\sqlite3.exe .\jalcivar_db

# then check:
pragma integrity_check;

# exit

If I have error messages I can go to Next Step

# Extract an SQL dump:
echo .dump | .\sqlite3.exe .\jalcivar_db > jalcivar_db.sql

# With Notepad++, encode .sql to UTF-8 w/o BOM

# Create an new sqlite db file:
.\sqlite3.exe -init .\jalcivar_db.sql .\jalcivar_db.sqlite


Postgresql restore dump objects from list

pg_restore command brings an parameter for to define the objects list for restore from a dump.

Use -l for generate objects list from dump:

--list  [or]  -l

Create a file with that list, delete some objects, and use -L for use the new list for restore:

--use-list=list-file  [or]  -L list-file

Fix JMX RMI port configuration

Set the following ports in java command line:

Example on unique port 9229:

In Tomcat / TomEE set them in bin/ file:

export CATALINA_OPTS="$CATALINA_OPTS -server -Xmx6000m -Xms512m -XX:MaxPermSize=400m -Djava.rmi.server.hostname="

export JAVA_OPTS="-client -Xms8m -Xmx128m -XX:MaxPermSize=64m"

Due to a bug this doesnt works in java versions <= 1.7.0_51. Please upgrade the java/JDK version.


Postgis create extension error: cannot find

This error is produced by an unlinked version of libhdf5, actual is 8, postgis links 6.

The solution is to add version 6 to ldconfig (Centos 6):

# find the lib if exist in OS filesystem
# returns: /usr/lib64/openmpi/lib/
find / -name

cd /etc/
nano postgis-fix.conf

# add to file:

# Reload libs configs:

# check the lib:
ldconfig -p | grep libhdf5


Install Postgresql 9.6 on CentOS 6

# Get correct RPM from

cd /home/
mkdir installers
cd installers


rpm -i pgdg-centos96-9.6-3.noarch.rpm

# Install EPEL repos as:

rpm -ivh epel-release-6-8.noarch.rpm

yum install postgresql96 postgresql96-contrib postgresql96-server postgresql96-devel postgresql96-plpython postgis2_96 postgis2_96-devel postgis2_96-client postgis2_96-utils

service postgresql-9.6 initdb 'en_US.UTF8'
# or
# service postgresql-9.6 initdb 'es_ES.UTF-8'

chkconfig postgresql-9.6 on

# Darle password al usuario postgres linux/db:

passwd postgres
service postgresql-9.6 restart
su postgres
psql -c "ALTER USER postgres WITH PASSWORD 'xxxxxx'" -d template1

nano /var/lib/pgsql/9.6/data/postgresql.conf

# En la línea #listen_addresses quitamos el carácter # (habilitar) y reemplazamos la palabra localhost por el carácter *
# Configure la cantidad máxima de conexiones (max_connection) a 200.
# Guarde y cierre el archivo con Crtl+o, Enter, y Ctrl+x

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

# Definir en la seccion #IPv4 local connections :
host    all             all               password
host    all             all                  password

service postgresql-9.6 restart

# firewall rule for 5432 port
nano /etc/sysconfig/iptables
# Then add this entry before -A INPUT -j REJECT line
-A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT
# restart firewall
service iptables restart
# verify open ports:
netstat -tulpn
# Make sure iptables is allowing port 80 / 110 / 143 connections:
iptables -L -n

# Disable enforce
nano /etc/selinux/config
# change the SELINUX line to 

Set session variable in postgresql

Before version 9.2, you needed to add your custom class variable to custom_variable_classes parameter in postgresql.conf, like:

custom_variable_classes = 'myapp'

In 9.2, this requirement has been removed:

Remove the custom_variable_classes parameter (Tom Lane).
The checking provided by this setting was dubious. Now any setting can be prefixed by any class name.

So, since 9.2 you can just set your custom class variable as you are doing currently, no need to worry about changing postgresql.conf.

-- set_config(var_name, value, is_only_for_local_transaction)
-- if local_transaction is false, then var is setted for entire connection/session
-- Also:  set session "myapp.user" = 'admin';
SELECT set_config('myapp.user', 'admin', FALSE);
-- get var:
SELECT current_setting('myapp.user');

With SET LOCAL (Only in transaction):
Specifies that the command takes effect for only the current transaction. After COMMIT or ROLLBACK, the session-level setting takes effect again. Issuing this outside of a transaction block emits a warning and otherwise has no effect.

-- Also:  set LOCAL "myapp.user" = '22';
SELECT set_config('myapp.user', 'admin', TRUE);
-- get var:
SELECT current_setting('myapp.user');