Install PostgreSQL 9.5 on Ubuntu 14.04

PostgreSQL 9.5 is freshly released (text written 2015-01-13) and here are some hints how to install it on Ubuntu 14.04:

• create file: /etc/apt/sources.list.d/pgdg.list:

  sudo vi /etc/apt/sources.list.d/pgdg.list

• add into it line with repository (trusty-pgdg is for 14.04, wily-pgdg for 15.10, to check your repository run

  lsb_release -cs

  :

  deb http://apt.postgresql.org/pub/repos/apt/ <...repository...>-pgdg main

• import keys:

  wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - 

• update apt-get repos info:

  sudo apt-get update

• install PostgreSQL:

  sudo apt-get install postgresql-9.5

• now should be server up and running
  • you can check if postgres server is listening:

    netstat -l|grep post

    • you will see result like this:

      tcp        0      0 localhost:postgresql    *:*                     LISTEN
      unix  2      [ ACC ]     STREAM     LISTENING     21970    /var/run/postgresql/.s.PGSQL.5432
      

• if you need install also development tools:

  sudo apt-get install postgresql-server-dev-9.5

That is all. Your shining new PostgreSQL 9.5 is running.

Of course if you want to connect to it you need:

• allow listening to all connections in /etc/postgresql/9.5/main/postgresql.conf

  listen_addresses = '*'

• add your IP into /etc/postgresql/9.5/main/pg_hba.conf – into IPv4:

  host  all all IP_ADDRESS/BITS trust

• set password for postgres database user:

  sudo -u postgres psql postgres

  • in psql type:

    \password postgres

    • type 2x new password
    • exit psql:

      \q

• restert postgresql server (reload of configuration is not enough):

  sudo service postgresql restart

• check iptables:

  sudo iptables -L

• allow remote connections in Ubuntu firewall using:

  sudo iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

• save iptables configuration using:

  sudo invoke-rc.d iptables-persistent save

  • if you get error: invoke-rc.d: unknown initscript, /etc/init.d/iptables-persistent not found.
    • then install iptables-persistent:

      sudo apt-get install iptables-persistent

      • if you are working in Ubuntu desktop you will get desktop window asking you if current iptables config should be saved – separate for IP4 and IP6 – say “yes” for both
      • this save of config is only during instalation – if you add some more rules you need to save iptables manually
• and also you need to use newest pgAdmin 1.22 – older versions do not work with pg 9.5