Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Cygnus does not persist data into PostGreSQL #2146

Closed
YannickLecroart21 opened this issue Mar 10, 2022 · 6 comments
Closed

Cygnus does not persist data into PostGreSQL #2146

YannickLecroart21 opened this issue Mar 10, 2022 · 6 comments

Comments

@YannickLecroart21
Copy link

Hi,

I am using the following tutorial to persist data in a PostGresQL database via Cygnus.

https://documenter.getpostman.com/view/513743/RWEcR2DC

When using a MySQL database with Cygnus everything works fine as you can see below:

mysql> SHOW tables FROM openiot;
+-------------------------------------------------------+
| Tables_in_openiot                                     |
+-------------------------------------------------------+
| urn_ngsi-ld_TemperatureSensor_11401_TemperatureSensor |
| urn_ngsi-ld_TemperatureSensor_12435_TemperatureSensor |
| urn_ngsi-ld_TemperatureSensor_13477_TemperatureSensor |
| urn_ngsi-ld_TemperatureSensor_13828_TemperatureSensor |
| urn_ngsi-ld_TemperatureSensor_15205_TemperatureSensor |
| urn_ngsi-ld_TemperatureSensor_1546_TemperatureSensor  |
+-------------------------------------------------------+
109 rows in set (0.00 sec)

But when using PostGres as backend, the NGSIPostgreSQLSink does not create tables.

Here is my setup on docker:

  postgres:
    image: postgres:latest
    hostname: postgres
    expose:
      - "5432"
    ports:
      - "5432:5432"
    environment:
      - "POSTGRES_PASSWORD=password"
      - "POSTGRES_USER=postgres"
      - "POSTGRES_DB=postgres"
    volumes:
      - data-postgres:/var/lib/postgresql/data
    
  cygnus:
    image: fiware/cygnus-ngsi:latest
    hostname: cygnus
    depends_on:
        - postgres
    expose:
        - "5080"
    ports:
        - "5055:5055"
        - "5080:5080"
    environment:
        - "CYGNUS_POSTGRESQL_HOST=postgres"
        - "CYGNUS_POSTGRESQL_PORT=5432"
        - "CYGNUS_POSTGRESQL_USER=postgres"
        - "CYGNUS_POSTGRESQL_PASS=password"
        - "CYGNUS_POSTGRESQL_ENABLE_CACHE=true"
        - "CYGNUS_POSTGRESQL_SERVICE_PORT=5055"
        - "CYGNUS_LOG_LEVEL=DEBUG"
        - "CYGNUS_API_PORT=5080"
        - "CYGNUS_SERVICE_PORT=5055"

If I list the schemas with a PostGres client there is no openiot schema:

postgres=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

I also tried to mount the following agent.conf and cygnus_instance.conf files in docker:

docker

volumes:
   - ./conf/cygnus:/opt/apache-flume/conf/agent:ro

agent.conf

cygnus-ngsi.sources = http-source
cygnus-ngsi.sinks = postgresql-sink
cygnus-ngsi.channels = postgresql-channel


cygnus-ngsi.sources.http-source.channels = postgresql-channel
cygnus-ngsi.sources.http-source.type = org.apache.flume.source.http.HTTPSource
cygnus-ngsi.sources.http-source.port = 5050
cygnus-ngsi.sources.http-source.handler = com.telefonica.iot.cygnus.handlers.NGSIRestHandler
cygnus-ngsi.sources.http-source.handler.notification_target = /notify
cygnus-ngsi.sources.http-source.handler.default_service = default
cygnus-ngsi.sources.http-source.handler.default_service_path = /
cygnus-ngsi.sources.http-source.interceptors = ts gi
cygnus-ngsi.sources.http-source.interceptors.ts.type = timestamp
cygnus-ngsi.sources.http-source.interceptors.gi.type = com.telefonica.iot.cygnus.interceptors.NGSIGroupingInterceptor$Builder
cygnus-ngsi.sources.http-source.interceptors.gi.grouping_rules_conf_file = /usr/cygnus/conf/grouping_rules.conf
cygnus-ngsi.sources.http-source.interceptors.nmi.type = com.telefonica.iot.cygnus.interceptors.NGSINameMappingsInterceptor$Builder
cygnus-ngsi.sources.http-source.interceptors.nmi.name_mappings_conf_file = /usr/cygnus/conf/name_mappings.conf


cygnus-ngsi.sinks.postgresql-sink.channel = postgresql-channel
cygnus-ngsi.sinks.postgresql-sink.type = com.telefonica.iot.cygnus.sinks.NGSIPostgreSQLSink
cygnus-ngsi.sinks.postgresql-sink.enable_encoding = false
cygnus-ngsi.sinks.postgresql-sink.enable_name_mappings = false
cygnus-ngsi.sinks.postgresql-sink.enable_grouping = false
cygnus-ngsi.sinks.postgresql-sink.enable_lowercase = false
cygnus-ngsi.sinks.postgresql-sink.postgresql_host = postgres
cygnus-ngsi.sinks.postgresql-sink.postgresql_port = 5432
cygnus-ngsi.sinks.postgresql-sink.postgresql_database = postgres
cygnus-ngsi.sinks.postgresql-sink.postgresql_username = postgres
cygnus-ngsi.sinks.postgresql-sink.postgresql_password = password
cygnus-ngsi.sinks.postgresql-sink.attr_persistence = column
cygnus-ngsi.sinks.postgresql-sink.data_model = by-service-path
cygnus-ngsi.sinks.postgresql-sink.batch_size = 100
cygnus-ngsi.sinks.postgresql-sink.batch_timeout = 30
cygnus-ngsi.sinks.postgresql-sink.batch_ttl = 10
cygnus-ngsi.sinks.postgresql-sink.persist_errors = true


cygnus-ngsi.channels.postgresql-channel.type = memory
cygnus-ngsi.channels.postgresql-channel.capacity = 100000
cygnus-ngsi.channels.postgresql-channel.transactionCapacity = 10000

cygnus_instance.conf

CYGNUS_USER=cygnus
CONFIG_FOLDER=/usr/cygnus/conf
CONFIG_FILE=/usr/cygnus/conf/agent.conf
AGENT_NAME=cygnusagent
LOGFILE_NAME=cygnus.log
ADMIN_PORT=8081
POLLING_INTERVAL=30

But to no avail Cygnus is still not persisting data in PostGres.

Here are some logs from the Cygnus service:

time=2022-03-10T17:02:12.429Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=defineOperation | msg=org.eclipse.jetty.jmx.ObjectMBean[809] : defineOperation start false:ACTION:Starts the instance

time=2022-03-10T17:02:12.429Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=defineOperation | msg=org.eclipse.jetty.jmx.ObjectMBean[863] : Method Cache: start()

time=2022-03-10T17:02:12.429Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=getMBeanInfo | msg=org.eclipse.jetty.jmx.ObjectMBean[318] : Method Annotation found for: stop

time=2022-03-10T17:02:12.429Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=defineOperation | msg=org.eclipse.jetty.jmx.ObjectMBean[809] : defineOperation stop false:ACTION:Stops the instance

time=2022-03-10T17:02:12.430Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=defineOperation | msg=org.eclipse.jetty.jmx.ObjectMBean[863] : Method Cache: stop()

time=2022-03-10T17:02:12.431Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=getMBeanInfo | msg=org.eclipse.jetty.jmx.ObjectMBean[286] : Influenced by: org.eclipse.jetty.util.component.Container

time=2022-03-10T17:02:12.431Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=getMBeanInfo | msg=org.eclipse.jetty.jmx.ObjectMBean[291] : Annotations not found for: org.eclipse.jetty.util.component.Container

time=2022-03-10T17:02:12.431Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=getMBeanInfo | msg=org.eclipse.jetty.jmx.ObjectMBean[286] : Influenced by: org.eclipse.jetty.util.component.Destroyable

time=2022-03-10T17:02:12.432Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=getMBeanInfo | msg=org.eclipse.jetty.jmx.ObjectMBean[318] : Method Annotation found for: destroy

time=2022-03-10T17:02:12.432Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=defineOperation | msg=org.eclipse.jetty.jmx.ObjectMBean[809] : defineOperation destroy false:ACTION:Destroys this component

time=2022-03-10T17:02:12.432Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=defineOperation | msg=org.eclipse.jetty.jmx.ObjectMBean[863] : Method Cache: destroy()

time=2022-03-10T17:02:12.432Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=getMBeanInfo | msg=org.eclipse.jetty.jmx.ObjectMBean[286] : Influenced by: org.eclipse.jetty.util.component.Dumpable

time=2022-03-10T17:02:12.433Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=getMBeanInfo | msg=org.eclipse.jetty.jmx.ObjectMBean[318] : Method Annotation found for: dump

time=2022-03-10T17:02:12.433Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=defineOperation | msg=org.eclipse.jetty.jmx.ObjectMBean[809] : defineOperation dump false:INFO:Dump the nested Object state as a String

time=2022-03-10T17:02:12.433Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=defineOperation | msg=org.eclipse.jetty.jmx.ObjectMBean[863] : Method Cache: dump()

time=2022-03-10T17:02:12.433Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=getMBeanInfo | msg=org.eclipse.jetty.jmx.ObjectMBean[286] : Influenced by: org.eclipse.jetty.server.Handler

time=2022-03-10T17:02:12.433Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=getMBeanInfo | msg=org.eclipse.jetty.jmx.ObjectMBean[305] : Attribute Annotation found for: getServer

time=2022-03-10T17:02:12.433Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=defineAttribute | msg=org.eclipse.jetty.jmx.ObjectMBean[657] : defineAttribute server false:true:class org.eclipse.jetty.server.handler.ErrorHandler:the jetty server for this handler

time=2022-03-10T17:02:12.433Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=defineAttribute | msg=org.eclipse.jetty.jmx.ObjectMBean[729] : passed convert checks server for type class org.eclipse.jetty.server.Server

time=2022-03-10T17:02:12.434Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=getMBeanInfo | msg=org.eclipse.jetty.jmx.ObjectMBean[318] : Method Annotation found for: destroy

time=2022-03-10T17:02:12.434Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=defineOperation | msg=org.eclipse.jetty.jmx.ObjectMBean[809] : defineOperation destroy false:ACTION:destroy associated resources

time=2022-03-10T17:02:12.434Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=defineOperation | msg=org.eclipse.jetty.jmx.ObjectMBean[863] : Method Cache: destroy()

time=2022-03-10T17:02:12.434Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=beanAdded | msg=org.eclipse.jetty.jmx.MBeanContainer[208] : Registered org.eclipse.jetty.server.handler:type=errorhandler,id=0

time=2022-03-10T17:02:12.434Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=addBean | msg=org.eclipse.jetty.util.component.ContainerLifeCycle[322] : org.eclipse.jetty.server.Server@52e18a5d added {org.eclipse.jetty.server.handler.ErrorHandler@4a257334,AUTO}

time=2022-03-10T17:02:12.436Z | lvl=INFO | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=doStart | msg=org.eclipse.jetty.server.Server[372] : jetty-9.4.6.v20170531

time=2022-03-10T17:02:12.471Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=doStart | msg=org.eclipse.jetty.server.handler.AbstractHandler[110] : starting org.eclipse.jetty.server.Server@52e18a5d

time=2022-03-10T17:02:12.471Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=setStarting | msg=org.eclipse.jetty.util.component.AbstractLifeCycle[185] : starting qtp260686092{STOPPED,8<=0<=200,i=0,q=0}

time=2022-03-10T17:02:12.488Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=setStarted | msg=org.eclipse.jetty.util.component.AbstractLifeCycle[177] : STARTED @1893ms qtp260686092{STARTED,8<=8<=200,i=7,q=0}

time=2022-03-10T17:02:12.488Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=beanAdded | msg=org.eclipse.jetty.jmx.MBeanContainer[131] : beanAdded ServerConnector@cc1c486{HTTP/1.1,[http/1.1]}{0.0.0.0:5055}->org.eclipse.jetty.server.Server@52e18a5d

time=2022-03-10T17:02:12.489Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=beanAdded | msg=org.eclipse.jetty.jmx.MBeanContainer[131] : beanAdded ServerConnector@cc1c486{HTTP/1.1,[http/1.1]}{0.0.0.0:5055}->qtp260686092{STARTED,8<=8<=200,i=8,q=0}

time=2022-03-10T17:02:12.489Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=beanAdded | msg=org.eclipse.jetty.jmx.MBeanContainer[131] : beanAdded ServerConnector@cc1c486{HTTP/1.1,[http/1.1]}{0.0.0.0:5055}->org.eclipse.jetty.util.thread.ScheduledExecutorScheduler@36176a5b

ing | msg=org.eclipse.jetty.util.component.AbstractLifeCycle[185] : starting o.e.j.s.ServletContextHandler@30833221{/,null,UNAVAILABLE}

time=2022-03-10T17:02:12.551Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=<clinit> | msg=org.eclipse.jetty.http.PreEncodedHttpField[64] : HttpField encoders loaded: [org.eclipse.jetty.http.Http1FieldPreEncoder@5b3a334b]

Thanks in advance.
@YannickLecroart21 YannickLecroart21 changed the title Cygnus does not persist data into PostSQL Cygnus does not persist data into PostGreSQL Mar 10, 2022
@fgalan
Copy link
Member

fgalan commented Mar 11, 2022

We have a lot of deployments of Cygnus persisting information in PostgreSQL, so probably is a matter of configuration. Maybe the author of that tutorial (I guess that @jason-fox ) could provide support on this.

@AlvaroVega
Copy link
Member

I'm missing the event in your logs, it seems data was not arrived to cygnus.

@YannickLecroart21
Copy link
Author

Indeed @AlvaroVega Cygnus does not seem to receive any notification from Orion.

ngsi | op=run | msg=com.telefonica.iot.cygnus.interceptors.NGSINameMappingsInterceptor$PeriodicalNameMappingsReader[205] : [nmi] The configuration has not changed

time=2022-03-11T11:20:25.202Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=run | msg=org.apache.flume.node.PollingPropertiesFileConfigurationProvider$FileWatcherRunnable[131] : Checking file:/opt/apache-flume/conf/agent.conf for changes

time=2022-03-11T11:20:25.254Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=processNewBatches | msg=com.telefonica.iot.cygnus.sinks.NGSISink[527] : Batch accumulation time reached, the batch will be processed as it is

time=2022-03-11T11:20:25.328Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=run | msg=com.telefonica.iot.cygnus.interceptors.NGSINameMappingsInterceptor$PeriodicalNameMappingsReader[205] : [nmi] The configuration has not changed

time=2022-03-11T11:20:55.202Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=run | msg=org.apache.flume.node.PollingPropertiesFileConfigurationProvider$FileWatcherRunnable[131] : Checking file:/opt/apache-flume/conf/agent.conf for changes

time=2022-03-11T11:20:55.257Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=processNewBatches | msg=com.telefonica.iot.cygnus.sinks.NGSISink[527] : Batch accumulation time reached, the batch will be processed as it is

time=2022-03-11T11:20:55.329Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=run | msg=com.telefonica.iot.cygnus.interceptors.NGSINameMappingsInterceptor$PeriodicalNameMappingsReader[205] : [nmi] The configuration has not changed

time=2022-03-11T11:21:25.202Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=run | msg=org.apache.flume.node.PollingPropertiesFileConfigurationProvider$FileWatcherRunnable[131] : Checking file:/opt/apache-flume/conf/agent.conf for changes

time=2022-03-11T11:21:25.259Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=processNewBatches | msg=com.telefonica.iot.cygnus.sinks.NGSISink[527] : Batch accumulation time reached, the batch will be processed as it is

time=2022-03-11T11:21:25.329Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=run | msg=com.telefonica.iot.cygnus.interceptors.NGSINameMappingsInterceptor$PeriodicalNameMappingsReader[205] : [nmi] The configuration has not changed

time=2022-03-11T11:21:55.203Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=run | msg=org.apache.flume.node.PollingPropertiesFileConfigurationProvider$FileWatcherRunnable[131] : Checking file:/opt/apache-flume/conf/agent.conf for changes

time=2022-03-11T11:21:55.262Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=processNewBatches | msg=com.telefonica.iot.cygnus.sinks.NGSISink[527] : Batch accumulation time reached, the batch will be processed as it is

time=2022-03-11T11:21:55.329Z | lvl=DEBUG | corr=N/A | trans=N/A | srv=N/A | subsrv=N/A | comp=cygnus-ngsi | op=run | msg=com.telefonica.iot.cygnus.interceptors.NGSINameMappingsInterceptor$PeriodicalNameMappingsReader[205] : [nmi] The configuration has not changed

Here is what I use to create my Orion subscription:

headers = {'Content-Type': "application/json", 'fiware-service': "openiot", 'fiware-servicepath': "/"}

data = {
            "description": "Notify Cygnus of all context changes",
            "subject": {
                "entities": [
                {
                    "idPattern": ".*"
                }
                ]
            },
            "notification": {
                "http": {
                "url": "http://cygnus:5050/notify"
                },
                "attrsFormat": "legacy"
            },
            "throttling": 5
        }

The document says that attrsFormat=legacy is required since Cygnus currently only accepts notifications in the older NGSI v1 format. Am I missing an extra parameter or something to enable PostGresSink with Cygnus?

Regards,

@YannickLecroart21
Copy link
Author

It's all good now I found the issue.

My subscription for Orion uses port 5050 (which is the port exposed if the agent is MySQL) for PostGresSINK the port exposed by the agent is 5055 so I updated the subscription and it works fine now.

"url": "http://cygnus:5055/notify"

Always be careful with copy N paste I just learned it the hard way ^^

Thanks for your time.

@fgalan
Copy link
Member

fgalan commented Mar 11, 2022

Just a couple of comments:

  • I strongly encourage not using legacy format. NGSIv1 is deprecate. Cygnus is able to process NGSIv2 notifications perfectly.
  • I don't recommend using throttling. It may lead to loss of notifications

If you have got that subscription from the tutorial, the tutorial should be updated (CC: @jason-fox )

@YannickLecroart21
Copy link
Author

Yes removing makes sens in a "real time" scenario and I also removed the "attrsFormat": "legacy" parameter to use NGSIv2 directly.

I should mention that I had to change id format of my entities:

urn:ngsi-ld:TemperatureSensor:7921

The format above did not work because of the special character (see #1977)

So I use the following format to be able to create tables in PostGreSQL

urn:ngsild:TemperatureSensor:7921

Thanks again for your time @fgalan @AlvaroVega

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@AlvaroVega @fgalan @YannickLecroart21