1. Is this for the Post Apocalyptic Petra?
No, content-wise Post Apocalyptic Petra is done since it was meant for the MSDOS game jam back in 2020, i've only fixing bugs (and made some ports). Also the 3D model in the video has more polygons than the entire game :-P.
I started making this ~6 months ago because i wanted to try and use
nxdk for making a simple 3D game on the original Xbox (nxdk is a free / open source SDK for it) as i bought some used xbox last year, though i've lost, regained and lost interest on this for a bit (especially since the controller i got with the xbox wasn't working properly and a few months ago stopped working completely - i ordered a new replacement one almost a year ago which is
finally about to arrive). At the moment i am slowly making the model mainly to have something to test things with - i might resume the game project at some point later though.
2. Is there any content to learn how to draw textures with a realistic artstyle(like yours in the video)? At best, i can do pixel art for texture
Well, this isn't really very realistic, it is more like digital painting. Modern 3D models are actually made using dedicated tools that let you paint using full materials (so, e.g., your brush doesn't affect the color but also normal, roughness and other aspects of a material), but a tool like Krita that is made for editing and painting 2D images wouldn't work here.
The approach i'm using is something that was used in older games, mainly around late 90s to early 2000s - before that textures had to fit in a palette (e.g. Quake) and after that games started using additional textures like normal maps, specular maps, etc to represent materials. Sadly this period was quite short and a ton of information about the approaches used for making textures seem to have disappeared (or at least modern stuff drown them completely). Even worse, sites like Polycount that go back to the 90s and have a community of artists had their forums reset at some point and i can't find stuff from the late 90s/early 2000s (from when i remember people posting a bunch of helpful tips), though chances are even if that stuff still existed it'd be posts full of images from long gone hosting sites (though some artists did post their own sites which had their own tutorials .
You can find a few of these things online, e.g.
this old book excerpt describes the basic idea behind mixing photosourced "detail" and manually drawn shapes (with manually drawn lighting, etc). There is also
CS Girl, a (very) old tutorial for making a 3D player model for Counter-Strike - it mainly focuses on the modelling side (and uses a now abandoned program, Milkshape) though it also has a section on making the texture and the ideas are pretty much the same regardless of the software used. The texture making part is all about painting the texture manually but you can also combine it with other methods.
For more modern stuff what you want to do is mainly check tutorials (99% of them will be videos on YouTube) about digital painting and there are *TONS* of them. The tutorials are really about painting 2D images but a lot of the same ideas apply to painting textures too. Also there are texture painting tutorials and while they focus on more cartoony stuff, again the ideas are often transferable to more realistic styles. Some tutorials on pixel art textures (like this one) can also help, you just replace the "
pixel art" with your brush painted art.
Sadly there isn't a single resource for this as this approach isn't used much (might become more common in 4-5 years when more indie games want to make use PS2-like graphics :-P), but you should be able to combine ideas from various places.