This message was deleted.

This is odd. I will try to test this myself and let you know what I find. In the meantime, as a workaround, if you configure a quickstart configured to point to postgres DB and you just start the stand-alone Druid, it will create the metadata tables.

@Sergio Ferragut, I was able to create the tables using metadata-init tool. Then I imported data for audit, rules, segment & task tables from derby to pg instance using dbeaver table data import utility. All the 4 metadata tables in PG now has same data as derby tables. After moving the data I changed my common.runtime.properties to connect to PG instance in all 4 nodes. Restarted all the services in order of master > data > query. UI is working, in services tab all services are up. However my segments and task tabs shows empty in UI. Its like fresh new druid. How can I retain all my data as is from Derby DB to PG db without impacting the UI ?

Did you copy the data in the druid_segments table? That's the one that contains the Deep Storage references.

Yes. I copied the druid_segments table data also as is. And only change was the MD repository. When i switch back to earlier derby DB, my segments and tasks show up as it was left, only change is the segments start getting downloaded from deep storage to history node automatically.

can you log in to postgres cli and just do a

SELECT count(*) FROM druid.druid_segments

Yes. It is working that way

Do you see the count that you expect?

I cross checked the data across Derby and PG. It all matches

Could you also check it with

SELECT used, count(*) FROM druid.druid_segments GROUP BY 1

It has gone onto druid DB, public schema though. bcz the metadata-init tool created the tables on public schema

I'm not sure. But was there a druid schema created automatically when you restarted the master node? does it contain these tables. That may be another missing step in the metadata init, I'm not certain.

I checked both the ways. using init tool as well as passing the PG URI in runtime config and restarting the services. But in both the cases it created the tables in

public

schema, instead of creating a druid schema automatically or failing the script in case there is no druid schema.

Sounds good. Also take a look at

druid.metadata.storage.tables.base

it isn't clear to me but perhaps this is the "schema" name. https://druid.apache.org/docs/latest/configuration/index.html#metadata-storage

I did provide druid as that value. But I think it represents database

It is still interesting to see the count grouped by "used" column, just to check if the coordinator marked the segments as unused at some point. That could have happened if the default retention rule on the new metadata was "dropForever" for some reason. Did you copy the

druid_rules

table as well? What are the current retention rules?

This is the result of the grouped by sql - used, count false, 8 true, 573

good. so it does have published segments and the retention rule theory is out. the used=true segments are what drive the coordinator/broker timeline map. So this must just be about pointing at the right set of tables. Let me know how the "druid" schema test goes, but I'm doubting that is the issue.

Ya. Sorry. "loadForever"

In the coordinator log you should see messages like

Polled and found 573 segments in the database

Do you see that?

While checking the logs, it looks like it is not able to read the data from the payload column of segments & tasks table. Let me try to re insert the data from the bkp files and check back.. Thanks for all the help buddy !

Perhaps an encoding issue between your source and target? payload should be encoded the same way, utf-8 is the default. Perhaps your postgres setup needs that explicitly when creating the database:

createdb --encoding=UTF8 ....

BTW. I found where the schema is configured. it is

druid.metadata.postgres.dbTableSchema

and defaults to

public

.

Re copied the data from CSV to PG. And this time it worked. Thanks for all the help.. 🙂 Is there a way to also export tasks table from Derby inorder to import into postgress ?

druid_tasks holds task status information which you can also copy if you want to keep the history of task execution, but I don't think it is necessary.