Filament Manager, can't connect to PostgreSql Database

Plugins
1

Hey Everyone, I'm hoping I can get some direction, or some ideas as to where I've gone wrong. I am not a Linux pro by any means, but am slowly learning and can follow instructions. If my wall of text is too long, I'll put a TL:DR at the bottom

I performed the Octopi install on my Raspberry Pi 3 B+, and Octoprint is currently version 1.3.8 and Octopi shows 0.15.0. Filament Manager is version 0.5.3 and I had great success using it. I really liked it. I decided to add an Ender 3 printer, and in anticipation, I tried to configure things for the external database option. I am having problems getting Filament Manager to connect to the database.

I promise, I have searched for how to get this setup, I have pasted a few links below as proof!! (if there is a problem with external links, I will gladly remove them...just let me know)
https://bpwalters.com/blog/setting-up-postgresql-on-raspberry-pi/


http://raspberrypg.org/index.html@p=248.html#more-248

and I definitely followed closely to this post (in this plugins portion of the forum),

I tried installing PostgreSQL to the Raspberry pi itself, as well as on my desktop (windows 10), and can not get it to connect in either situation. I CAN connect my desktop to the database when it is running on the Raspberry Pi...but only as postgres user (not sure if that is a clue or not). When I tried connecting as user octoprint (as the instructions say in the plugin supplemental page), I can't actually open the tables in the database.

Here is what I have setup in the postgresql.conf file...(cutdown to the relevent data, found in this path /etc/postgresql/9.6/main)

Screenshot%20(2)
Screenshot (2).png754×570 15.8 KB

Here is the pg_hba.conf contents (same folder as above)

Screenshot%20(1)
Screenshot (1).png915×944 46.3 KB

Here is what I get when I found another post mentioning a command to see what the Pi will listen for...

Screenshot%20(3)
Screenshot (3).png843×163 11.8 KB

When I enter/login to Postgresql on the Raspberry pi, I see this,

postgres@CR-10-Octopi:/etc/postgresql/9.6/main$ psql
psql (9.6.7)
Type "help" for help.

Upon configuring things on the Raspberry Pi, I was sure to restart the service, using this command...
sudo systemctl restart postgresql.service

**And I even restarted the Pi itself, as well as Octoprint in order to try to get the connection going. I have run both ** sudo apt-get update & sudo apt-get upgrade .

**TL:DR - Filament Manager plugin will not connect to PostgreSQL database running on the Pi itself, nor the Windows 10 desktop (separate instance) running it. Desktop can connect to PostgreSQL on Raspberry Pi, but only using postgres user. **

If there is any additional information needed, please let me know. I feel like I've either installed the postgresql in the wrong location on the RPi (not sure if that's possible) so the plugin can't see it, or I'm missing some permission that needs to be set (more likely, since I can't connect to the desktop database either).

*Thanks for the tip about the ''' !

2

When I enter/login to Postgresql on the Raspberry pi, I see this,
postgres@CR-10-Octopi:/etc/postgresql/9.6/main$ psql
psql (9.6.7)
Type "help" for help.

Well, it looks like psql is connecting, yeah? Otherwise I'm having trouble reading it because of the formatting. Put three backticks on a line, then the terminal stuff, then three backticks after it so the text looks like this.

```
# IPv6 local connections:
host all all ::/0 md5
```
3

I think I would remote into the Raspi, run the PostgreSQL CLI using octoprint as the user and localhost as the destination.

4

I tried your suggestion tedder42 for the 3 ticks, it seems in some places it helped, and in others it didn't. I altered things to include the screenshots.Hopefully that makes more sense.

OutsourcedGuru, is this what you mean?

Screenshot%20(4)
Screenshot (4).png903×66 3.62 KB

I guess I didn't set up the octoprint as a user correctly, though I do see it in pgadmin when I connect to the RPi from my win10 pc. However, why would my connection not work when I even use the postgres user to login?

5

[not ticks, the backticks]*https://en.wikipedia.org/wiki/Grave_accent#Use_in_programming)- typically on the same key as the tilde.

But again, psql is running, what's the problem? Did outsourcedguru guess it, that it works from the server the DB is on but you can't talk to it from the pi?

6

Actually no, the server is running on the same Pi as Octoprint, and I can't connect to it in the plugin. So octopi can't connect to the server, all of which is on the same RPi.

I tried using the octoprint user, which I guess I messed up, but postgres doesn't connect either.

7

Ahhh. That makes sense. You can connect on the commandline because it does some UNIX authentication magic. You can't connect from Octoprint because it tries to use internet magic. Your problems are going to be in the pg_hba. Let me look for a minute.

8

Tedder42, this is going to be some localhost related thing isn't it? I was banging my head A LOT, because I can connect to the server using my Windows10 machine. Its how I created the database and loaded the exported files from the plugin so I didn't have to re-create it all.

Probably either gonna be an 'AHAAAAA' moment or a 'DOH' moment!

9

Hmmm. I assume you've restarted postgres (or the whole server, same thing). Your pg_hba looks decent. Here's mine, stolen from a work environment, then changed to use your local network of 192.168.1.0. You shouldn't have any lines that are active (not commented out).

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# DB admin login
local   all             postgres                                peer
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# local network:
host all all 192.168.1.0/24 md5
# IPv6 local connections:
# host    all             all             ::1/128                 md5

I was banging my head A LOT, because I can connect to the server using my Windows10 machine

Oh, interesting. So you can connect local in unix, or remote from another machine, but not local from Octoprint. There's a change in the above from yours- no more 'peer' entries. I've locked down two types of access that you had open:

  • removed IPv6 access, because you aren't using it
  • removed access from the whole world (0.0.0.0/0), because everything is local(host) or on your local network (192.168.1.x).

Anyhow, remove your entries, add the ones from me, restart postgres, cross your fingers.

(edit- I overlooked one peer entry from my work files, added it above, the first entry)

10

Just wanted to say, thanks for taking the time to help!

I was indeed always sure to restart the service whenever I made a change, I guess I could be using the wrong command though, I used this....
sudo systemctl restart postgresql.service Hopefully that is correct.

When I tried to connect after making the changes you suggested (and restarted the service using the command above), it didn't work. So I restarted the RPi to be sure.

Here are my edits to the pg_hba.conf

Screenshot%20(5)
Screenshot (5).png908×927 39 KB

And just to be sure I'm not missing something elsewhere, hear is the plugin connection info I've used,

Screenshot%20(6)
Screenshot (6).png1019×950 43.5 KB

And here is the connection setting to the RPi database from my win10 PC,

Screenshot%20(7)
Screenshot (7).png1497×1023 80.9 KB

I guess you could second guess if the RPi is IP 192.168.1.150 but I promise, its what I type in to my browser all the time to access it for printing. I set it up in my DHCP Reservations so I always could bring it up.

11

Hmmm. Try these from the Pi shell. They'll prompt for a password, but it'll force it to talk over the network even though it is local.

psql --username=postgres --password  --host=localhost --port=5432 octoprint_filamentmanager
psql --username=postgres --password  --host=192.168.1.150 --port=5432 octoprint_filamentmanager

I'll need to see the output from those. Also not sure if you ever posted what the errors from the plugin looks like- both in the UI and at the bottom of ~/.octoprint/logs/octoprint.log too.

This is fussy but we'll figure it out eventually.

12

Actually, there never was an error from the plugin, it just says the button 'Test Connection' would turn green if it connected. And I assumed it wasn't working since the database never loaded.

Here are the commands, I did them in order as you typed them. Can you give a brief explanation as to what they each mean or are trying to do?

pi@CR-10-Octopi:/etc/postgresql/9.6/main $ psql --username=postgres --password  --host=localhost --port=5432 octoprint_filamentmanager
Password for user postgres:
psql: FATAL:  no pg_hba.conf entry for host "::1", user "postgres", database "octoprint_filamentmanager", SSL on
FATAL:  no pg_hba.conf entry for host "::1", user "postgres", database "octoprint_filamentmanager", SSL off
pi@CR-10-Octopi:/etc/postgresql/9.6/main $ psql --username=postgres --password  --host=192.168.1.150 --port=5432 octoprint_filamentmanager
Password for user postgres:
psql (9.6.7)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

octoprint_filamentmanager=#
13

I thought I saw earlier that it used the octoprint user/role for the database it created.

14

I tried setting up the user octoprint as the guide suggested, however even when using Win10 to connect, I could not access the tables. I posted a screenshot in response to your suggestion, and it seemed I must have done something incorrectly as when I tried to connect as the user octoprint on the Rpi, I received a 'No passwd entry for user 'octoprint'

I can connect to the database when using postgres as is visible in the screenshot of the pgadmin connection.

15

Basically, they are doing connections to PG and forcing 'network ports' (--port), which means it will use the same auth methods as Octoprint.

psql: FATAL: no pg_hba.conf entry for host "::1", user "postgres", database "octoprint_filamentmanager", SSL on

That's interesting, because ::1 is an IPv6 style entry. That sort of means that you are right, it's a localhost oddity. I bet this'll work- the second one worked, but you shouldn't configure the plugin to use the 192.168 address (bad form, it may change, etc).

psql --username=postgres --password  --host=127.0.0.1 --port=5432

What we're doing here is forcing it to use 'internet ports' and also forcing it to use ipv4 instead of ipv6.

Assuming that works, change the plugin to use the 127 IP in the URI, and the plugin should be working. Actually, it should be working now even with the other IP. To test and see if it will complain on failure, change the username to something bad (nope instead of postgres, for example) and see if "test connection" gives an error.

16

ok, so the new line you gave me worked, (I think from what I see returned it means it worked).

Screenshot%20(8)
Screenshot (8).png892×210 24.7 KB

However, when trying the plugin with URL as postgresql://127.0.0.1 and user postgres, nothing happens.

I tried the octoprint.log...and I hope what I grabbed is after the restart...if you need details prior, just let me know.

2018-06-28 00:26:24,298 - octoprint.environment - INFO - Detected environment is Python 2.7.13 under Linux (linux2). Details:
|  hardware:
|    cores: 4
|    freq: 1400.0
|    ram: 918192128
|  os:
|    id: linux
|    platform: linux2
|  plugins:
|    octopi_support:
|      model: unknown
|      revision: a020d3
|      version: 0.15.0
|  python:
|    pip: 10.0.1
|    version: 2.7.13
|    virtualenv: /home/pi/oprint
2018-06-28 00:26:24,308 - octoprint.server - INFO - Reset webasset folder /home/pi/.octoprint/generated/webassets...
2018-06-28 00:26:24,323 - octoprint.server - INFO - Reset webasset folder /home/pi/.octoprint/generated/.webassets-cache...
2018-06-28 00:26:24,544 - octoprint.server - INFO - Shutting down intermediary server...
2018-06-28 00:26:24,947 - octoprint.server - INFO - Intermediary server shut down
2018-06-28 00:26:24,951 - octoprint.events - INFO - Processing startup event, this is our first event
2018-06-28 00:26:24,953 - octoprint.events - INFO - Adding 1 events to queue that were held back before startup event
2018-06-28 00:26:24,965 - octoprint.filemanager - INFO - Adding backlog items from all storage types to analysis queue...
2018-06-28 00:26:24,996 - octoprint.plugins.pluginmanager - INFO - Loaded plugin repository data from disk, was still valid
2018-06-28 00:26:25,132 - octoprint.filemanager - INFO - Added 0 items from storage type "local" to analysis queue
2018-06-28 00:26:25,450 - octoprint.plugins.pluginmanager - INFO - Loaded notice data from disk, was still valid
2018-06-28 00:26:25,712 - octoprint.plugins.discovery - INFO - Registered OctoPrint instance on CR-10-Octopi for _http._tcp
2018-06-28 00:26:25,724 - octoprint.plugins.discovery - INFO - Registered OctoPrint instance on CR-10-Octopi for _octoprint._tcp
2018-06-28 00:26:25,728 - octoprint.server - INFO - Listening on http://127.0.0.1:5000
2018-06-28 00:26:25,729 - octoprint.plugins.HeaterTimeout - INFO - Starting up...
2018-06-28 00:26:25,741 - octoprint.plugins.discovery - INFO - Registered OctoPrint instance on CR-10-Octopi for SSDP
2018-06-28 00:26:25,770 - octoprint.server.util.sockjs - INFO - New connection from client: ::ffff:192.168.1.110
2018-06-28 00:26:25,887 - octoprint.plugins.anywhere - WARNING - Not connected to server ws or connection lost
2018-06-28 00:26:25,967 - octoprint.server.util.sockjs - INFO - New connection from client: 127.0.0.1
2018-06-28 00:26:26,815 - octoprint.plugins.astroprint - INFO - Connected to astroprint service
2018-06-28 00:26:27,110 - octoprint.plugins.filamentmanager - ERROR - Failed to fetch selected spools, pause feature will not be available: 'NoneType' object has no attribute 'get_all_selections'
2018-06-28 00:26:27,111 - octoprint.plugins.filamentmanager - ERROR - Failed to set temperature offsets: 'NoneType' object has no attribute 'get_all_selections'
2018-06-28 00:26:27,112 - octoprint.plugins.filamentreload - INFO - Filament Sensor Reloaded started
2018-06-28 00:26:27,113 - octoprint.plugins.filamentreload - INFO - Setting up sensor.
2018-06-28 00:26:27,114 - octoprint.plugins.filamentreload - INFO - Using Board Mode
2018-06-28 00:26:27,115 - octoprint.plugins.filamentreload - INFO - Filament Sensor active on GPIO Pin [2]
2018-06-28 00:26:27,116 - octoprint.plugin - ERROR - Error while calling plugin filamentreload
Traceback (most recent call last):
  File "/home/pi/oprint/local/lib/python2.7/site-packages/octoprint/plugin/__init__.py", line 225, in call_plugin
    result = getattr(plugin, method)(*args, **kwargs)
  File "/home/pi/oprint/local/lib/python2.7/site-packages/octoprint_filamentreload/__init__.py", line 61, in on_after_startup
    self._setup_sensor()
  File "/home/pi/oprint/local/lib/python2.7/site-packages/octoprint_filamentreload/__init__.py", line 55, in _setup_sensor
    GPIO.setup(self.pin, GPIO.IN, pull_up_down=GPIO.PUD_UP)
ValueError: The channel sent is invalid on a Raspberry Pi
2018-06-28 00:26:27,118 - octoprint.plugins.multicam - INFO - MultiCam Loaded! (more: [{'URL': '/webcam/?action=stream', 'isButtonEnabled': False, 'name': 'Default'}])
2018-06-28 00:26:27,121 - octoprint.plugins.tplinksmartplug - INFO - TPLinkSmartplug loaded!
2018-06-28 00:26:27,144 - octoprint.plugins.pluginmanager - INFO - Loaded plugin repository data from disk, was still valid
2018-06-28 00:26:27,380 - octoprint.server.preemptive_cache - INFO - Preemptively caching / (ui _default) for {'query_string': 'l10n=en', 'path': '/', 'base_url': 'http://192.168.1.150/'}
2018-06-28 00:26:27,460 - octoprint.plugins.pluginmanager - INFO - Loaded notice data from disk, was still valid
2018-06-28 00:26:40,421 - octoprint.server.preemptive_cache - INFO - ... done in 13.04s
2018-06-28 00:27:26,212 - octoprint.server.util.sockjs - INFO - Client connection closed: ::ffff:192.168.1.110
2018-06-28 00:27:33,872 - octoprint.server.util.sockjs - INFO - New connection from client: ::ffff:192.168.1.110
2018-06-28 00:27:36,774 - octoprint.plugins.filamentmanager - ERROR - Failed to fetch profiles lastmodified timestamp: 'NoneType' object has no attribute 'get_profiles_lastmodified'
2018-06-28 00:27:36,774 - octoprint.plugins.filamentmanager - ERROR - Failed to fetch profiles: 'NoneType' object has no attribute 'get_all_profiles'
2018-06-28 00:27:36,779 - tornado.access - ERROR - 500 GET /plugin/filamentmanager/profiles (127.0.0.1) 9.57ms
2018-06-28 00:27:45,396 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 25.22ms
2018-06-28 00:27:46,961 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 23.05ms
2018-06-28 00:28:05,049 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 22.61ms
2018-06-28 00:28:07,666 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 26.50ms
2018-06-28 00:28:12,550 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 22.52ms
2018-06-28 00:28:14,689 - py.warnings - WARNING - /home/pi/oprint/local/lib/python2.7/site-packages/octoprint/util/__init__.py:621: UnicodeWarning: Unicode unequal comparison failed to convert both arguments to Unicode - interpreting them as being unequal
  if value_source != value_target:

2018-06-28 00:28:32,296 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 25.50ms
2018-06-28 00:28:33,695 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 24.71ms
2018-06-28 00:28:34,539 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 22.73ms
2018-06-28 00:28:42,630 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 22.83ms
2018-06-28 00:28:43,591 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 23.34ms
2018-06-28 00:28:44,390 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 22.94ms
2018-06-28 00:28:45,141 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 22.99ms
2018-06-28 00:28:45,868 - tornado.access - WARNING - 400 POST /plugin/filamentmanager/database/test (127.0.0.1) 24.28ms
17

Thank you for getting the backticks correct :slight_smile:

There's an (unrelated) error from octoprint_filamentreload in there. Guessing you don't have it configured. That's the first exception visible:

...
  File "/home/pi/oprint/local/lib/python2.7/site-packages/octoprint_filamentreload/__init__.py", line 55, in _setup_sensor
    GPIO.setup(self.pin, GPIO.IN, pull_up_down=GPIO.PUD_UP)
ValueError: The channel sent is invalid on a Raspberry Pi

These look like startup noise- the database isn't fully initted for the filament manager. Might be okay:

2018-06-28 00:27:36,774 - octoprint.plugins.filamentmanager - ERROR - Failed to fetch profiles lastmodified timestamp: 'NoneType' object has no attribute 'get_profiles_lastmodified'
2018-06-28 00:27:36,774 - octoprint.plugins.filamentmanager - ERROR - Failed to fetch profiles: 'NoneType' object ha

I don't know, things seem legit. I mean, it's connecting (that's how it knows the timestamp is wrong), so by some measure you don't have a DB connection issue. Now it's probably a matter of using/configuring the plugin properly.

18

I attempted to reconfigure things, thinking perhaps I had done something wrong. Originally I thought maybe because I was importing the data from the plugin that I had already created, perhaps the plugin didn't want that. So I tried creating another database, thinking perhaps it needed a blank/fresh one in order to set everything up correctly.

It still wont' connect, however the octoprint.log has a different message in there.

2018-06-28 02:30:13,467 - octoprint.plugins.filamentmanager - ERROR - Failed to fetch selected spools, pause feature will not be available: 'NoneType' object has no attribute 'get_all_selections'
2018-06-28 02:30:13,468 - octoprint.plugins.filamentmanager - ERROR - Failed to set temperature offsets: 'NoneType' object has no attribute 'get_all_selections'

I was reading up on this 'NoneType' object, and it seems in every instance, it refers back to (swiped from a different webpage) 'NoneType means that instead of an instance of whatever Class or Object you think you're working with, you've actually got None. That usually means that an assignment or function call up above failed or returned an unexpected result.'

So somehow, despite being a brand new and empty database, the plugin is trying to pull data that does not exist, is that correct? Or could I be missing some sort of dependency that might help it run?

From the wiki, https://github.com/malnvenshorn/OctoPrint-FilamentManager/wiki/Setup-PostgreSQL-on-Arch-Linux

It says it needs the psycopg2, which I did install. Though besides a restart, nothing else configuration wise is mentioned.

19

I think it's all working according to that wiki entry. Contact the author, it's like it isn't creating the tables.

20

You'd think that the postgres user would have the rights to do that. I note that the -O octoprint from the install script means "owner".