German €9 ticket for trains: who's eligible, and what's the catch?

• Is this as good as it sound? No trick? No additional fee?

Yes, no tricks, no additional fee. However, it is only valid in regional trains, not in long-distance trains like IC, EC, ICE, night trains, and Flixtrain.

• Are tourists allowed to buy this too?

Yes.

• Are trains that accept them going to be totally packed or not available due to overcrowding?

Especially on weekends and during summer holiday time (which is July and August) you can expect especially trains in tourist regions to be packed (e.g. the trains to Sylt, probably also trains in the Alps region and around bigger cities)

• Can a family plan a whole trip around them? I'm aware they exclude certain higher speed trains, but they still seem like a steal for, say, visiting a region like Cologne, Bonn, Dusseldorf and travel between these cities and within the city.

A family can certainly plan a trip around them, with the limitations that you already mentioned. If you e.g. take a long-distance train with a separate ticket to the Cologne region, you can then use the 9-Euro-ticket to take buses, trams, subway, etc. around those cities and the region.

Personally, I wouldn't use the ticket to do longer trips, since by only using regional trains you would travel for several hours and have to change trains more often (e.g. from Frankfurt to Berlin it would take around 9 hours, instead of 4 hours with a direct ICE train).

A little bit more explanation about the fact that Mark already mentioned in his answer: some IC trains are also classified as regional trains. As Mark mentioned, in those trains, the 9-Euro-Ticket is usually not valid, however (to add even more confusion), DB has reached an agreement with some states for some of those connections, so that the 9-Euro-Ticket is valid.

One example of such a train, where the ticket is valid:

Unfortunately, the relevant description is not translated, it's this sentence:

Das 9-Euro-Ticket ist in allen RE-Zügen der DB Fernverkehr AG Stuttgart, Singen und Konstanz gültig

which means

The 9-Euro-Ticket is valid in all RE trains of DB Fernverkehr AG between Stuttgart, Singen and Konstanz

Another example, where the 9-Euro ticket is not valid on such a train:

Again, the relevant description is not translated, it's the sentence:

Das 9-Euro-Ticket ist in diesem RE der DB Fernverkehr AG nicht gültig

which means

The 9-Euro-Ticket is not valid in this RE of DB Fernverkehr

You can also check a specific connection if the 9-Euro-Ticket is offered. E.g. in my first example, if you click on the 9 Euro price offer, you see that it's that specific ticket which is offered for that connection, and then the 9-Euro-ticket is valid. If it's not offered for a specific connection, it's not valid (like in the second example, where the 9-Euro-ticket is not offered for the RE train).

The screenshots are taken from the German rail planner, which is one of the better train planners in Europe, available in several languages.

A few extra notes on this topic:

This offer, by the federal goverment (who does not supply the services), was made without previous consultations with the companies that are expected to meet a higher demand at a very short notice.

The city transports, for the most part, have a good idea what to expect.

But for regional trains, on a suddenly sunny weekend, problems should be expected.

Each ticket is valid for a specific calender month

• printed on the ticket as: Juni, Juli or August
• they can be bought at any time (I bought all 3 at once)

You must write your name on the ticket and be prepared to show an ID with that name. (i.e. the tickets are not transferable to other persons)

There are a few Regional trains where the ticket is not valid (organized by 'DB Fernverkehr AG' that normaly offers IC trains, but due to low demand are classifying them as regional trains). This will cause confusion.

The text from the original anouncement in March:

2022-03-22: Energiepreise: Ampel beschließt 300 Euro Einmalzahlung – Tanken wird günstiger | ZEIT ONLINE
...
As a further measure to relieve the burden on citizens, Ricarda Lang announced support for people who depend on local public transport: here, tickets for nine euros per month should be available nationwide – also for a limited period of three months, she said.
...

What's the catch?

This offer is temporary.

This offer is part of a package to make life cheaper for everyone, to (partly) offset high energy prices and cost of living. During the same time, driving is also cheaper for those driving petrol or diesel (and maybe gas, not sure) due to a tax reduction. I think drivers of electric cars have no price reduction. The ~10% reduction in petrol prices costs the federal government more than the €9-ticket.

Secondly, this offer is also seen as a promotional act to get people back on the train after traveller numbers did not bounce back to pre-pandemic levels when pandemic restrictions were mostly gone, although this is not the official motivation.