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 http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

Ubuntu Xenial (16.04)

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

Ubuntu Trusty (14.04)

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

Ubuntu Precise (12.04)

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | 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

Source:

http://askubuntu.com/questions/831292/how-to-install-postgresql-9-6-on-any-ubuntu-version

http://askubuntu.com/questions/557124/how-to-specify-a-different-locale-for-postgresql-installation

 

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

cd
virtualenv pgadmin4
cd pgadmin4
source bin/activate

wget https://ftp.postgresql.org/pub/pgadmin3/pgadmin4/v1.2/pip/pgadmin4-1.2-py2-none-any.whl

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

Configure

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

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

Run

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

Access at http://localhost:5050

 

Other option is to use docker and a docker image provided by thaJeztah – https://github.com/thaJeztah/pgadmin4-docker

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

 

Source:
http://askubuntu.com/questions/831262/how-to-install-pgadmin-4-in-desktop-mode-on-ubuntu-16-04

 

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:

https://dwuysan.wordpress.com/2016/02/17/sending-email-on-apache-tomee/

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

In Tomee.xml:

<?xml version="1.0" encoding="UTF-8"?>
<tomee>
    <!-- see http://tomee.apache.org/containers-and-resources.html -->
 
    <!-- 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.host=smtp.gmail.com
    mail.smtp.starttls.enable=true
    mail.smtp.port=587
    mail.transport.protocol=smtp
    mail.smtp.auth=true
    mail.smtp.user=<!-- your email address -->
    password=<!-- your password, and not 'mail.smtp.password' -->
    </Resource>
</tomee>

Injection of resource:

@Stateless
@LocalBean
@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("someone@gmail.com")
            });
            message.setSubject("Email from TomEE");
            message.setSentDate(new Date());
            message.setText("Email from TomEE");
            Transport.send(message);
        } 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.io.File;
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 {
            //INGRESO DE LAS POROPIEDADES DE LA CONEXION
            Properties props = new Properties();
            props.setProperty("mail.transport.protocol", "smtp");
            props.setProperty("mail.smtp.host", 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");
            //INSTANCIA DE LA SESSION
            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.setSubject(asunto);
            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();
            texto.setText(mensaje);
            //CONTENEDOR DE LAS PARTES
            Multipart multipart = new MimeMultipart();
            multipart.addBodyPart(texto);
            //ADJUNTAR LOS ARCHIVO EN PARTES
            MimeBodyPart file;
            for (File f : adjuntos ) {
                file = new MimeBodyPart();
                file.attachFile(f.getAbsolutePath());
                multipart.addBodyPart(file);
            }
            //AGREGAR MULTIPART EN CUERPO DEL MENSAJE
            mimeMessage.setContent(multipart);
            // ENVIAR MENSAJE
            Transport transport = session.getTransport("smtp");
            transport.connect(usuarioCorreo, password);
            transport.sendMessage(mimeMessage, mimeMessage.getAllRecipients());
            transport.close();
            
            
            
        } 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:
http://www.sqlite.org/download.html

I will use sqlite-tools-win32 in Windows 10:
http://www.sqlite.org/2017/sqlite-tools-win32-x86-3160200.zip

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
.quit

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

.quit

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

https://www.postgresql.org/docs/current/static/app-pgrestore.html
http://stackoverflow.com/a/31043192/3662679

Fix JMX RMI port configuration

Set the following ports in java command line:

com.sun.management.jmxremote.port
com.sun.management.jmxremote.rmi.port

Example on unique port 9229:

-Djava.net.preferIPv4Stack=true 
-Dcom.sun.management.jmxremote
-Dcom.sun.management.jmxremote.port=9229 
-Dcom.sun.management.jmxremote.rmi.port=9229
-Djava.rmi.server.hostname=190.57.138.218 
-Dcom.sun.management.jmxremote.authenticate=false 
-Dcom.sun.management.jmxremote.ssl=false

In Tomcat / TomEE set them in bin/setenv.sh file:

export CATALINA_OPTS="$CATALINA_OPTS -server -Xmx6000m -Xms512m -XX:MaxPermSize=400m -Djava.net.preferIPv4Stack=true -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=9229 -Dcom.sun.management.jmxremote.rmi.port=9229 -Djava.rmi.server.hostname=190.57.138.218 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false"

export JAVA_OPTS="-client -Xms8m -Xmx128m -XX:MaxPermSize=64m -Djava.net.preferIPv4Stack=true"

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

Resources:

http://stackoverflow.com/questions/20884353/why-java-opens-3-ports-when-jmx-is-configured/21552812#21552812

Postgis create extension error: cannot find libhdf5.so.6

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/libhdf5.so.6
find / -name libhdf5.so.6

cd /etc/ld.so.conf.d/
nano postgis-fix.conf

# add to file:
/usr/lib64/openmpi/lib/

# Reload libs configs:
ldconfig

# check the lib:
ldconfig -p | grep libhdf5

References:
http://gis.stackexchange.com/questions/31177/error-creating-a-spatial-database-error-could-not-load-library-usr-pgsql-9

Install Postgresql 9.6 on CentOS 6

# Get correct RPM from http://yum.postgresql.org/repopackages.php

cd /home/
mkdir installers
cd installers

wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-centos96-9.6-3.noarch.rpm

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

# Install EPEL repos as:
# http://www.tecmint.com/how-to-enable-epel-repository-for-rhel-centos-6-5/

wget http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
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
exit

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             127.0.0.1/32            password
host    all             all             0.0.0.0/0               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 
SELINUX=disabled

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');

Source:
https://www.postgresql.org/docs/9.5/static/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE
http://dba.stackexchange.com/questions/97095/set-session-custom-variable-to-store-user-id
https://www.postgresql.org/docs/current/static/sql-set.html