Step 5: installing PostgreSQL on my Raspberry PI

Previously, we have been able to install Linux and configure it on our Raspberry PI. It is now the moment to turn our Raspberry PI into Raspberry PG, by installing PostgreSQL 9.3.

First, you need to instruct APT (Debian’s package manager) to use the PostgreSQL Community package repository. You can find detailed instructions for the installation process of the new PostgreSQL version (9.3) in the PostgreSQL Apt repository documentation. Here we just summarise the principal steps:

  • Create /etc/apt/sources.list.d/pgdg.list file with the following command ( Raspbian is a Debian wheezy system):
       deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main
  • Import the repository key, update the package lists, and start installing packages:
       wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
    sudo apt-get update
    sudo apt-get install postgresql-9.3

This is enough to install PostgreSQL 9.3. During the installation, the user “postgres” will be created (with home directory in /var/lib/postgresql/). Usually in a Raspbian version (such as in Debian/Ubuntu versions) PostgreSQL configuration files are stored in /etc/postgresql/9.3/main/, and data directory is stored in /usr/local/var/postgres/. Now is possible to Start/Stop/Restart the postmaster (the main process of a PostgreSQL server) using the command:

sudo service postgresql start|stop|restart

While tweaking the configuration file parameters, keep in mind that most of the changes should require a postmaster restart.

Updates

Since many things are changed during all this time, we decided to write several new blog article with the updates.

A first, excellent article has been written by my dear friend Rubens, explaining how to install PostgreSQL both on RaspberryPI and RaspberryPI2 with the last release of Raspbian. I invite all of you to read it.

Other blog articles will be written showing the differences between the old results and the ones achievable with the new RaspberryPI’s. Stay tuned!

32 thoughts on “Step 5: installing PostgreSQL on my Raspberry PI

  1. Pingback: Benchmark results of PostgreSQL on Raspberry PI | Raspberry PG

  2. Hi,

    Good instructions, but still cannot install pg on my Pi. I seem to have 7.1. The apt package configuration doesn’t find the package when running apt-get update.

    Have you experience of this?

    Even when running apt-get install postgresql, it says:
    (Reading database … 70195 files and directories currently installed.)
    Unpacking postgresql (from …/postgresql_9.1+134wheezy4_all.deb) …
    Setting up postgresql (9.1+134wheezy4)

    …but only a folder postgres-common is created to /etc/, not postgres-9.1 or similar.

    Any idea why it doesn’t work?

    Henri

  3. Hi Henry,
    For the first issue: did you obtain any error message when you launch the command deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main ? The file /etc/apt/sources.list.d/pgdg.list is correctly created?

    For the second issue: are you interested to 9.1 version of PostgreSQL? If so, I suggest to do:
    sudo apt-get install postgresql-9.1 postgresql-contrib-9.1
    If you are interested to the latest supported version instead, launch:
    sudo apt-get install postgresql postgresql-contrib

    Consider that Raspbian is a factual Debian 7.0 wheezy distribution, so what is valid for the second one is valid also for the first one.

    Let me know if you have any further problem.

    Giuseppe.

  4. Hi Giuseppe,
    Thanks for the write-up.’apt-get update’ is failing for me with this message:
    W: Failed to fetch http://apt.postgresql.org/pub/repos/apt/dists/wheezy-pgdg/Release Unable to find expected entry ‘main/binary-armhf/Packages’ in Release file (Wrong sources.list entry or malformed file)
    as is ‘apt-get install postgresql-9.3′. Looking in the repos, I only see amd and i386 ports, none for the arm in my PI. Am I missing something?
    thanks,
    Bill

      • Hi Tim,

        ‘deb …’ is not a real command. ‘deb …’ is a statement which has to be included in the file ‘/etc/apt/sources.list.d/pgdg.list’. Then, you have to follow all the other steps as listed in the blog.

        Hope it can be helpfull.

        Giuseppe.

  5. Hello folks,

    postgres repos have no armhf binaries currently(Apr. 2014).
    If you want newest PostgreSQL on your PI(Raspbian), then you must build it by your self (not sure but >~ 6 hours).
    If you dont want newest one, then use debians “postgresql” V. 9.1 .

    Make it @night as follows :D :

    ### add postgre’s sources to apt
    cd /tmp
    sudo echo “deb-src http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main” >> /etc/apt/sources.list.d/pgdg.list
    ### add apt key if not done before
    wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

    ### build dependencies(if not used before -> see “build-dep”. You’ll maybe need something collateral.)
    sudo apt-get update
    sudo apt-get upgrade
    sudo apt-get build-dep postgresql-9.3

    ### !!! ATTENTION -> do not run as root or buil will fail on test “can not run tests as root”.
    cd /tmp
    apt-get source –compile postgresql-9.3

    ### GO SLEEP |-)

    ### If you see many .deb files @morning :D in /tmp directory, then is all fine and you have had happiness ;)

    ### create own repository
    sudo mkdir /var/local/repository
    sudo echo “deb [ trusted=yes ] file:///var/local/repository ./” > /etc/apt/sources.list.d/my_own_repo.list
    sudo mv *.deb /var/local/repository/.
    cd /var/local/repository
    sudo mv /tmp/*.deb .
    sudo dpkg-scanpackages ./ > Packages && gzip -f Packages
    sudo apt-get update
    cd ~

    ### upgrade postgre’s libs
    sudo apt-get upgrade

    ### <:o) install postgre
    sudo apt-get install postgresql-9.3

    ### additionally availible packages
    postgresql-plperl-9.3
    postgresql-plpython3-9.3
    postgresql-plpython-9.3
    postgresql-pltcl-9.3

    have fun.

    • Hey folks,

      before “compile” you want two hyphens (this site merges them and makes on :@ ? )
      apt-get source –compile postgresql-9.3
      apt-get source –compile postgresql-9.3

    • Hi,

      It works well except, I have :

      root@cubie:/var/local/repository# aptitude install postgresql-9.3 postgresql-contrib-9.3
      The following NEW packages will be installed:
      libpq5{a} postgresql-9.3{b} postgresql-client-9.3{ab}
      postgresql-contrib-9.3
      0 packages upgraded, 4 newly installed, 0 to remove and 0 not upgraded.
      Need to get 0 B/4,503 kB of archives. After unpacking 20.8 MB will be used.
      The following packages have unmet dependencies:
      postgresql-client-9.3 : Depends: postgresql-client-common (>= 142~) but it is not going to be installed.
      postgresql-9.3 : Depends: postgresql-common (>= 142~) but it is not going to be installed.
      Depends: ssl-cert but it is not going to be installed.
      The following actions will resolve these dependencies:

      Keep the following packages at their current version:
      1) postgresql-9.3 [Not Installed]
      2) postgresql-client-9.3 [Not Installed]
      3) postgresql-contrib-9.3 [Not Installed]

      or :

      apt-get install postgresql-9.3 postgresql-contrib-9.3
      Reading package lists… Done
      Building dependency tree
      Reading state information… Done
      Some packages could not be installed. This may mean that you have
      requested an impossible situation or if you are using the unstable
      distribution that some required packages have not yet been created
      or been moved out of Incoming.
      The following information may help to resolve the situation:

      The following packages have unmet dependencies:
      postgresql-9.3 : Depends: postgresql-client-9.3 but it is not going to be installed
      Depends: postgresql-common (>= 142~) but it is not going to be installed
      E: Unable to correct problems, you have held broken packages.

      Any idea ?

      Thanks for the doc,
      Nicolas

  6. i succeeded at getting public key but updating throw this error:

    [Working]W: Failed to fetch http://apt.postgresql.org/pub/repos/apt/dists/wheezy-pgdg/Release: Unable to find expected entry ‘main/binary-armhf/Packages’ in Release file (Wrong sources.list entry or malformed file)
    E: Some index files failed to download. They have been ignored, or old ones used instead.
    E: Couldn’t rebuild package cache ”

    At link
    http://apt.postgresql.org/pub/repos/apt/dists/wheezy-pgdg/main/ there is no binary-armhf/Packages

  7. I know this website presents quality based content and extra data, is there any other website which provides these kinds of things in quality?

  8. when I compile from source (apt-get source postgresql-9.3 –compile), at the end I get an error at the end of the process:

    debian / rules: 175: recipe for target ‘override_dh_auto_test’ failed
    make [1]: *** [override_dh_auto_test] Error 1
    make [1]: Leaving directory ‘/tmp/postgresql-9.3-9.3.5′
    debian / rules: 92: recipe for target ‘build’ failed
    make: *** [build] Error 2
    dpkg-buildpackage: failure: debian / rules build returned an error exit status 2
    Failed build order “cd postgresql-9.3-9.3.5 && dpkg-buildpackage -b -uc”.
    E: Failed child process

    in capreta tmp I find any deb file, someone happens the same?

    • I also had this problem. After the compile failed, I cd to /tmp/postgresql-9.3-9.3.6 and followed the instructions in the INSTALL file and then it worked:

      ./configure
      make
      sudo make install
      adduser postgres
      sudo mkdir /usr/local/pgsql/data
      sudo chown postgres /usr/local/pgsql/data
      su – postgres
      /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
      ./configure
      gmake
      su
      gmake install
      adduser postgres
      sudo mkdir /usr/local/pgsql/data
      sudo chown postgres /usr/local/pgsql/data
      sudo mkdir /var/run/postgresql/
      sudo chown postgres /var/run/postgresql/
      su – postgres
      /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
      /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
      /usr/local/pgsql/bin/createdb test
      /usr/local/pgsql/bin/psql test

    • I’m getting the same error. Is there any other way of installing postgres 9.3 without building for source ?

      • I ended up compiling from source using the excellent commands from Rafael and Matiaan

        I then had to add a small startup script in my /etc/rc.local file that would start the postgres database on reboot of the pi

        I wish there was a way to do a regular install of 9.3 however, I feel like it will be difficult to uninstall/upgrade once apt-get is working; and then I will have multiple versions in the same path and it will all be very confusing.

  9. Pingback: Binary Postgis-2 package for RaspberryPI2? | DL-UAT

  10. Pingback: Step 5 (update): Installing PostgreSQL on my Raspberry Pi 1 and 2 | Raspberry PG

  11. Hi all, sorry for the late answer, but I had a lot of works in the last months. More important, many things are changed from my original posts: there have been other releases of Raspbian, and also a new generation of RaspberryPI – RaspberryPI2 – have been produced in the meantime. So we decided to write more blog articles, updating the odl ones.

    A first, excellent article has been written by my dear friend Rubens, explaining how to install PostgreSQL both on RaspberryPI and RaspberryPI2. I invite all of you to read it at the url

    http://raspberrypg.org/2015/06/step-5-update-installing-postgresql-on-my-raspberry-pi-1-and-2/

    Other blog articles will be written showing the differences between the old results and the ones achievable with the new RaspberryPI’s. Stay tuned!

  12. Hi
    Nice tutorial, thanks.
    Have you written an article on how to configure Postgres after you have installed it?
    So that there is a secure admin account and a working database?

Leave a Reply to danny Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>