Filament Manager Plugin: How to setup postgresql database

Ok....There is a lot of good advice on here and around the web that can help you figure this out, but it can be a daunting task too! So, after MUCH trial and error, research and some luck, I got it figured out and wanted to share a concise tutorial on how to do it.

First...why would you want to do this? So that you can share the spool data across multiple instances of Octoprint and they all access and update the remaining amount of filament, no matter which instance or machine you're printing from.

So...here we go and it's really not that hard! :wink:

First...disable any THEMES or UI changes you may have! Why? because they may possible disable the TEST connection button from changing colors and you'll never get the feedback (green is good, red is bad) you need to know if you've successfully connected or not. Trust me on this one...this one kept me thinking it wasn't connecting for a LONG while, and it was my damn theme! I only figured this one out because I started all over on a fresh install of Octoprint and ONLY installed this mod and I noticed the RED (failed to connect) before due to my theme. This also means, that if I was previously successful, I never saw the GREEN (successful) one either!

Now, with that out of the way. Here we go...for real this time :smile:

  1. Download and install Putty terminal application to SSL into your Pi. You may use other tools, but I am most comfortable with this one, and it works great.
  2. sudo apt-get update Lets make sure we have all the latest updates!
  3. sudo apt-get upgrade Install them!
  4. sudo reboot now Reboot the Pi.
  5. ~/oprint/bin/pip install --upgrade pip Let's just upgrade pip while we're at it.
  6. sudo reboot now Not sure if it's necessary, but can't hurt!
  7. sudo apt-get install libpq-dev python-dev Install these dependencies first!
  8. ~/oprint/bin/pip install psycopg2 Now install psycopg2 (required as well)
  9. sudo reboot now yeah, yea....again...just to be safe.
  10. sudo apt-get install postgresql Install postgresql
  11. sudo -u postgres -i become postgres user so we can create the octoprint user and database!
  12. Now...your prompt will have changed, so next lets create the user via the interactive feature:
    createuser --interactive -P
    Enter the username: "octoprint", and answer "n" to all the next questions about this user.
  13. Now we create the database itself: createdb -O octoprint octoprint_filamentmanager
    14 Now exit the user and back to the main prompt by typing: exit
    15 Now we have to edit two files, the first one is here
    sudo nano /etc/postgresql/9.6/main/postgresql.conf
    In this file, find the line "listen_addresses = " and change it to

listen_address = '*'

save your changes, then onto the next using:
sudo nano /etc/postgresql/9.6/main/pg_hba.conf

The Wiki says it best here, so you do this:
Then append the following line to /var/lib/postgres/data/pg_hba.conf

host octoprint_filamentmanager octoprint 192.168.178.0/24 md5

Adapt the IP address to your network, e.g. if your server has the IP 192.168.0.25 use 192.168.0.0/24 instead. This allows all clients in your network to access the database.

  1. Last step...is to restart the service using: sudo systemctl restart postgresql.service

Now just enter the information into the database settings in Filament manager plugin, just like you set it up, and like the Wiki explains:

Picture shown at the bottom of this wiki page. Of course, enter in the IP address of YOUR Raspberry Pi that's running the database (it can be the same one you're running your octoprint on)

Once you click Test Connection button...if everything has gone correctly, the button will turn green, and you'll need to restart Octoprint to save the settings.

Good Luck!!!

Steps 5 and 8 haven't been done while under the virtual environment of OctoPrint, btw.

If you wanted the working pip and python environment to be affected, you'd first need to do this before a pip-related command (and again if there was another reboot):

source ~/oprint/bin/activate

But then again, does postgresql itself run from that virtual environment or not? If not, then your instructions would work for that. (But the virtual environment might also need to be updated for the sake of any plugins you write.)

i dont get the test green. i dont understand which ip i have to set in the pg_hba.conf
My raspberry pi has the ip 192.168.188.44. can somebody help me? all the commands here in the manual worked but the test button doesnt get green.

ok on the server it works but the client my 2nd pi cannot connect to the database. what i have to do or install on the 2nd pi that he can connect and use the database?

On the second pi, you should only need to do steps 1 through 9. No need to install postgresql on the second pi, but it will need the other stuff. Also, after a reboot I still sometimes have issues reconnecting. What I generally have to do is just restart the octopi instances a time or two but then they eventually both connect fine... It's weird.

I just installed a different database server (MongoDB) on a Pi yesterday. I was surprised to see that the default installation just bound it to the localhost port of 127.0.0.1 and not to all IP addresses on that machine. So in my case, I had to edit that to 0.0.0.0 which means (all addresses).

You might need to edit your pg_hba.conf so try researching this.

It works now but i have a new Problem. Now filament manager doesnt subtract the consumed Filament.

If it were me, I'd check the postgresql log to see if it indicates some sort of error. I'd also consider adjusting the log level for this plugin in the Settings area and then look at the octoprint.log file itself. In fact, it is probably logging any errors there already.

And please, keep this to a single thread.

Hi, I have a raspberry to control two printers.
I followed the tutorial until the end, but I can't connect to the database.
Could it be something related to the IP or the localhost? What configuration would be necessary?
Thanks

Sorry to reopen an older thread.
I'm currently trying to setup the database on a fresh Octopi setup. Step 1 thru 14 go smoothly, step 15 not. I get the error that the directory does not exist. What is going wrong?

code from step 5

pi@octopi:~ $ ~/oprint/bin/pip install --upgrade pip
DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won't be maintained after that date. A future version of pip will drop support for Python 2.7. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support
Looking in indexes: https://pypi.org/simple, https://www.piwheels.org/simple
Collecting pip
  Downloading https://www.piwheels.org/simple/pip/pip-20.1.1-py2.py3-none-any.whl (1.5MB)
     |β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ| 1.5MB 1.1MB/s 
Installing collected packages: pip
  Found existing installation: pip 19.3.1
    Uninstalling pip-19.3.1:
      Successfully uninstalled pip-19.3.1
Successfully installed pip-20.1.1
pi@octopi:~ $ sudo reboot now
[sudo] password for pi: 
Connection to octopi.local closed by remote host.
Connection to octopi.local closed.
MacBook-Pro-van-Fabian-2:~ Fabian$ ssh pi@octopi.local
pi@octopi.local's password: 
Linux octopi 4.19.118-v7+ #1311 SMP Mon Apr 27 14:21:24 BST 2020 armv7l

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Fri May 29 15:48:07 2020 from fe80::c9a:baed:7815:6bc6%wlan0

------------------------------------------------------------------------------
Access OctoPrint from a web browser on your network by navigating to any of:

    http://octopi.local
    http://192.168.0.134

https is also available, with a self-signed certificate.
------------------------------------------------------------------------------
This image comes without a desktop environment installed because it's not 
required for running OctoPrint. If you want a desktop environment you can 
install it via

    sudo /home/pi/scripts/install-desktop
------------------------------------------------------------------------------
OctoPrint version : 1.4.0
OctoPi version    : 0.17.0
------------------------------------------------------------------------------

pi@octopi:~ $ sudo apt-get install libpq-dev python-dev
[sudo] password for pi: 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
python-dev is already the newest version (2.7.16-1).
Suggested packages:
  postgresql-doc-11
The following NEW packages will be installed:
  libpq-dev libpq5
0 upgraded, 2 newly installed, 0 to remove and 7 not upgraded.
Need to get 302 kB of archives.
After this operation, 1,286 kB of additional disk space will be used.
Get:1 http://mirror.serverius.net/raspbian/raspbian buster/main armhf libpq5 armhf 11.7-0+deb10u1 [151 kB]
Get:2 http://mirror.serverius.net/raspbian/raspbian buster/main armhf libpq-dev armhf 11.7-0+deb10u1 [151 kB]
Fetched 302 kB in 1s (551 kB/s)    
Selecting previously unselected package libpq5:armhf.
(Reading database ... 47379 files and directories currently installed.)
Preparing to unpack .../libpq5_11.7-0+deb10u1_armhf.deb ...
Unpacking libpq5:armhf (11.7-0+deb10u1) ...
Selecting previously unselected package libpq-dev.
Preparing to unpack .../libpq-dev_11.7-0+deb10u1_armhf.deb ...
Unpacking libpq-dev (11.7-0+deb10u1) ...
Setting up libpq5:armhf (11.7-0+deb10u1) ...
Setting up libpq-dev (11.7-0+deb10u1) ...
Processing triggers for libc-bin (2.28-10+rpi1) ...
Processing triggers for man-db (2.8.5-2) ...
pi@octopi:~ $ ~/oprint/bin/pip install psycopg2
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support
Looking in indexes: https://pypi.org/simple, https://www.piwheels.org/simple
Collecting psycopg2
  Downloading psycopg2-2.8.5.tar.gz (380 kB)
     |β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ| 380 kB 2.1 MB/s 
Building wheels for collected packages: psycopg2
  Building wheel for psycopg2 (setup.py) ... done
  Created wheel for psycopg2: filename=psycopg2-2.8.5-cp27-cp27mu-linux_armv7l.whl size=422736 sha256=0853de2808990da3bb56eb826f68e083c744f0c5f0037808598689593d96ee30
  Stored in directory: /home/pi/.cache/pip/wheels/4a/a6/2c/67b73db8a215e7bc4805efe56b7f354795a490f17bc6547da4
Successfully built psycopg2
Installing collected packages: psycopg2
Successfully installed psycopg2-2.8.5
pi@octopi:~ $ sudo reboot now
Connection to octopi.local closed by remote host.
Connection to octopi.local closed.
MacBook-Pro-van-Fabian-2:~ Fabian$ ssh pi@octopi.local
pi@octopi.local's password: 
Linux octopi 4.19.118-v7+ #1311 SMP Mon Apr 27 14:21:24 BST 2020 armv7l

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Fri May 29 15:52:43 2020 from fe80::c9a:baed:7815:6bc6%wlan0

------------------------------------------------------------------------------
Access OctoPrint from a web browser on your network by navigating to any of:

    http://octopi.local
    http://192.168.0.134

https is also available, with a self-signed certificate.
------------------------------------------------------------------------------
This image comes without a desktop environment installed because it's not 
required for running OctoPrint. If you want a desktop environment you can 
install it via

    sudo /home/pi/scripts/install-desktop
------------------------------------------------------------------------------
OctoPrint version : 1.4.0
OctoPi version    : 0.17.0
------------------------------------------------------------------------------

pi@octopi:~ $ sudo apt-get install postgresql
[sudo] password for pi: 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  libllvm7 libxslt1.1 postgresql-11 postgresql-client-11
  postgresql-client-common postgresql-common sysstat
Suggested packages:
  postgresql-doc postgresql-doc-11 libjson-perl isag
The following NEW packages will be installed:
  libllvm7 libxslt1.1 postgresql postgresql-11 postgresql-client-11
  postgresql-client-common postgresql-common sysstat
0 upgraded, 8 newly installed, 0 to remove and 7 not upgraded.
Need to get 27.0 MB of archives.
After this operation, 105 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://mirror.serverius.net/raspbian/raspbian buster/main armhf libllvm7 armhf 1:7.0.1-8+rpi3 [11.4 MB]
Get:2 http://mirror.serverius.net/raspbian/raspbian buster/main armhf libxslt1.1 armhf 1.1.32-2.2~deb10u1 [215 kB]
Get:3 http://mirror.serverius.net/raspbian/raspbian buster/main armhf postgresql-client-common all 200+deb10u3 [84.9 kB]
Get:4 http://mirror.serverius.net/raspbian/raspbian buster/main armhf postgresql-client-11 armhf 11.7-0+deb10u1 [1,284 kB]
Get:5 http://mirror.serverius.net/raspbian/raspbian buster/main armhf postgresql-common all 200+deb10u3 [225 kB]
Get:6 http://mirror.serverius.net/raspbian/raspbian buster/main armhf postgresql-11 armhf 11.7-0+deb10u1 [13.3 MB]
Get:7 http://mirror.serverius.net/raspbian/raspbian buster/main armhf postgresql all 11+200+deb10u3 [60.9 kB]
Get:8 http://mirror.serverius.net/raspbian/raspbian buster/main armhf sysstat armhf 12.0.3-2 [513 kB]
Fetched 27.0 MB in 10s (2,596 kB/s)                                            
Preconfiguring packages ...
Selecting previously unselected package libllvm7:armhf.
(Reading database ... 47426 files and directories currently installed.)
Preparing to unpack .../0-libllvm7_1%3a7.0.1-8+rpi3_armhf.deb ...
Unpacking libllvm7:armhf (1:7.0.1-8+rpi3) ...
Selecting previously unselected package libxslt1.1:armhf.
Preparing to unpack .../1-libxslt1.1_1.1.32-2.2~deb10u1_armhf.deb ...
Unpacking libxslt1.1:armhf (1.1.32-2.2~deb10u1) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../2-postgresql-client-common_200+deb10u3_all.deb ...
Unpacking postgresql-client-common (200+deb10u3) ...
Selecting previously unselected package postgresql-client-11.
Preparing to unpack .../3-postgresql-client-11_11.7-0+deb10u1_armhf.deb ...
Unpacking postgresql-client-11 (11.7-0+deb10u1) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../4-postgresql-common_200+deb10u3_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (200+deb10u3) ...
Selecting previously unselected package postgresql-11.
Preparing to unpack .../5-postgresql-11_11.7-0+deb10u1_armhf.deb ...
Unpacking postgresql-11 (11.7-0+deb10u1) ...
Selecting previously unselected package postgresql.
Preparing to unpack .../6-postgresql_11+200+deb10u3_all.deb ...
Unpacking postgresql (11+200+deb10u3) ...
Selecting previously unselected package sysstat.
Preparing to unpack .../7-sysstat_12.0.3-2_armhf.deb ...
Unpacking sysstat (12.0.3-2) ...
Setting up postgresql-client-common (200+deb10u3) ...
Setting up postgresql-client-11 (11.7-0+deb10u1) ...
update-alternatives: using /usr/share/postgresql/11/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-common (200+deb10u3) ...
supported-versions: WARNING! Unknown distribution: raspbian
debian found in ID_LIKE, treating as Debian
Adding user postgres to group ssl-cert

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service β†’ /lib/systemd/system/postgresql.service.
Setting up libxslt1.1:armhf (1.1.32-2.2~deb10u1) ...
Setting up libllvm7:armhf (1:7.0.1-8+rpi3) ...
Setting up sysstat (12.0.3-2) ...

Creating config file /etc/default/sysstat with new version
update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode
Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service β†’ /lib/systemd/system/sysstat.service.
Setting up postgresql-11 (11.7-0+deb10u1) ...
Creating new PostgreSQL cluster 11/main ...
/usr/lib/postgresql/11/bin/initdb -D /var/lib/postgresql/11/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_GB.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/11/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Europe/London
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 11 main start

Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
update-alternatives: using /usr/share/postgresql/11/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql (11+200+deb10u3) ...
Processing triggers for systemd (241-7~deb10u4+rpi1) ...
Processing triggers for man-db (2.8.5-2) ...
Processing triggers for libc-bin (2.28-10+rpi1) ...
pi@octopi:~ $ sudo -u postgres -i
postgres@octopi:~$ createuser --interactive -P
Enter name of role to add: octoprint
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
NOTICE:  empty string is not a valid password, clearing password
postgres@octopi:~$ createdb -O octoprint octoprint_filamentmanager
postgres@octopi:~$ exit
logout
pi@octopi:~ $ sudo nano /etc/postgresql/9.6/main/postgresql.conf
pi@octopi:~ $ 

I'm not an expert on postgresql but I'm going to take a wild guess that between July 2019 and now, there was a new version of postgresql (old version 9.6, new version 11.7). Please do some examination of the directory tree starting with ls /etc/postgresql and see if you can't find the postgresql.conf file. Use the same cleverness to modify all the steps that mention 9.6 and you should make progress.

1 Like

Well, @b-morgan got the right end. Changing 9.6 to 11 in the file index led to succes. Thanks!

I have green test on the host OctoPrint server and on my 2nd server. How do I get my existing database to show up now? I've rebooted both and I only see the existing database prior to setting up the postgresql database on the host. I see nothing on the 2nd.

host: couldn't get address for 'octoprint': not found this is what i keep getting

I got this working last night and this morning it stopped. I'm giving up. This is more complicated than it needs to be and I can't find any simple updated instructions showing it working. It even appears this plugin has been abandoned. If anyone has other solutions to share a filament spools across 2 octoprint setups, please let me know.

Hi, I have followed all the steps several times and there is no way to connect. It seems that "sudo apt-get install postgresql" installs version 11, in the tutorials version 9.6 is used.

If I try to install version 9.6 with "sudo apt-get install postgresql-9.6" this information comes out:

pi@octopi:~ $ sudo apt-get install postgresql-9.6
Reading package lists ... Done
Building dependency tree
Reading state information ... Done
Note, selecting 'postgresql-9.6-repmgr' for regex 'postgresql-9.6'

How can I do the whole installation to use 9.6?

Thanks!

A much better question is how do I adjust the tutorial / installation instructions to use postgresql 11 instead of 9.6?

I think you will find the answer to that question in my response on May 29th.

With "sudo apt-get install postgresql" by default postgrestql 11 is installed.
Configuration files are now in:

locate postgresql.conf
sudo nano /etc/postgresql/11/main/postgresql.conf

locate pg_hba.conf
sudo nano /etc/postgresql/11/main/pg_hba.conf

But it didn't work for me, it doesn't connect, so I wanted to install version 9.6 to see if this is the problem. But it gives me the mistake I mentioned earlier.

Install postgresql 11 on one raspberry with Raspberry Pi OS 32bits, the other raspberry's with octoprint do not connect to the database.

Just an FYI, I got it working on postgresql v11. I am not sure if this will help, but for me it wasn't working until I disabled ssl in the "/etc/postgresql/11/main/postgresql.conf" file (ssl = off).
Also, I used a different database name, and reviewing the octoprint logs, the clients didn't update the database name until I restarted octoprint.
HTH

A few weeks ago I got it, it's something from the plugin, sometimes it doesn't start the connection with the database well, that's why it didn't connect. A reboot server is required.

I also managed to run postgresql inside a docker and connect with the plugin.