What parts would one typically use for automated PCB testing?
The pins used for making the kind of test fixtures, which the O.P. is describing, are called: contact probes, or spring loaded pins, or pogo pins.  They come in various sizes and with various shapes of tips.  The choice of a tip depends on the type of test pad: via, flat pad, pin from a throughole component on the solder side.

Here's a datasheet of the pogo pin family as an example. This particular family is available on Mouser.