$wpdb->prepare affecting the query?

You can use the array directly because the $wpdb->prepare() method can be called like vsprintf(), which works like sprintf() but accepts arrays of arguments.

$post_types = get_post_types(); 
unset( $post_types['attachment'], $post_types['nav_menu_item'] );

global $wpdb;
$replace_query = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_type IN ( %s )", $post_types ), ARRAY_A );

The problem with your code is that you get a string like this one:

"'page', 'attachment', 'revision', 'custom_css', 'customize_changeset'"

So, the IN statement you get is like:

IN ( "'page', 'attachment', 'revision', 'custom_css', 'customize_changeset'" )

Instead of the correct one that would be an array of strings:

IN ( 'page', 'attachment', 'revision', 'custom_css', 'customize_changeset' )