Melkij

Отсутствие реакции на SIGTERM при продолжающемся потреблении CPU это примечательно. Но без -dbgsym и GDB ничего определённого выяснить фактически не удастся. Суть - как-то попали в какую-то ветвь кода, которая не считалась что может занимать продолжительное время и вызов обработчика сигналов CHECK_FOR_INTERRUPTS там не был предусмотрен. Из вариантов навскидку где возможно в 16.х наступить - dblink или fdw, create index using hash. Традиционно, ещё могут быть index scan по gin или gist, не в первый раз находятся у них такие грабли.
Как минимум, удостовериться, что используется свежая минорная версия. Завтра 16.9 выходит.

Ну, это из предположения что весьма подозрительный watchdog: BUG: soft lockup тут ни при чём. Что это такое я вряд ли подскажу. Выглядит нехорошо.

Такс, это всё про оставшийся процесс.
Про сам the database system is shutting down
При crash recovery делается immediate stop, все backend которые не завершаться за 5 секунд получат SIGKILL. Который, естественно, проигнорировать уже не могут, потому что процессу про SIGKILL никто даже и не скажет, его просто снимет ядро ОС.
Значит, перед всеми the database system is shutting down был received smart shutdown request или received fast shutdown request, который кто-то скомандовал явным образом. Оба режима штатного выключения не делают SIGKILL, а именно дождутся корректного завершения процесса. В случае с багом, когда процесс не отреагировал на SIGTERM, ну, вечно ждать и будет. Других вариантов кроме SIGKILL тут нет.

Если логи ещё сохранились на дату shutdown request - то смотреть что там делали с системой, что кто-то скомандовал stop или restart базе. Если не вмешательство администратора, то может быть какой-то аналог unattended-upgrades?

важен вопрос "зачем"

а так, универсально через промежуточное приведение к тексту ::text::bytea

Вникаете в предметную область той задачи, для которой эта таблица используется. Всё, ответ найден.

Нет ничего удивительного в таблицах, у которых нет поля id. В предметной области может быть достаточно хороший естественный ключ.

С точки зрения производительности - да, конечно, есть смысл не хранить ненужные данные. Меньше строка => меньше таблица => в тот же объём RAM влезает больше кеша и меньше IO. Немного, правда, чистыми 8гб на каждый миллиард строк получается всего для bigint. Но если поле больше ни для чего не нужно, то почему бы и нет?

Вы смотрите на совершенно бесполезный график. Смотрите на полезные.

Если у вас табличка с 10 индексами и к ней select * from foo where id = ? - то это минимум 11 AccessShareLock. Совершенно логично, что чем больше параллельно нагрузки - тем больше вы увидите AccessShareLock. И дальше куда с этим? Вот и получается совершенно бесполезный график. А задирание max_connections сделает только хуже.

Посмотрите график CPU, есть ли в принципе CPU свободный (помним что у вас наверняка есть HT, значит 100% утилизации недостижимы, реальный потолок окажется где-то в районе 70-90%)
Посмотрите графики латентности IO. Если проседает латентность чтения или записи под нагрузкой - то это будет причина замедления. СУБД очень чувствительны к латентности.
Дальше графики pg_stat_activity с разбивкой по state. Если растут idle in transaction - проверять как дела на приложении, а так же сеть.
Конечно, графики по pg_stat_statements. top5 запросов по времени выполнения, для начала.
И проверить наличие корреляции с графиком длительности самой старой транзакции.

Аутентификация через метод md5 выполнит парольную проверку как для пароля сохранённого с password_encryption = md5 (ныне уже deprecated, но встречается в большинстве баз, созданных не недавно), так и пароли сохранённые в scram-sha-256. Реализацию pg_hba md5 специально расширили при появлении scram-sha-256 таким образом, для облегчения миграции.

Указание scram-sha-256 в pg_hba будет требовать именно пароль согласно scram-sha-256 и не будет принимать пароли старых учётных записей, созданных с md5 encryption.

Вполне вероятно, что у вашей базы password_encryption сконфигурирован в scram-sha-256, все пользователи кто имеет пароли, так же в scram-sha-256 и нужды в md5 аутентификации в pg_hba нет.

Блокировки строк и блокировки таблиц (и прочих объектов) - два разных механизма. (а serializable изоляция и вовсе частично третий)

Блокировки строк отмечают информацию о себе специальными флагами в заголовке самой заблокированной строки. Запросы, которым нужна блокировка строки сначала проверяют, не отмечена ли эта строка блокировкой, затем проверяют что сейчас с той транзакцией, идентификатор transaction ID которой указан в информации о блокировке. Если транзакция ещё в работе - то начинаем ждать её завершение (или пропускаем строку для skip locked или кидает ошибку если nowait)
Блокировки строк, будь то select for share/for update или пишущие запросы, всегда работают с самой актуальной версией строки.

В то же самое время, любой запрос, трогающий таблицу (даже select) берёт блокировку на эту таблицу соответствующего своим потребностям уровня. Пока не возьмёт блокировку на таблицу - вообще не начнёт выполняться. Информация об этих блокировках размещается в сегменте shared memory памяти, потому доступны всем процессам базы. Если другие бекенды не держат блокировку таблицы конфликтующую с желаемым нашей транзакцией (или ещё только ждут блокировку более высокого уровня), то запрос блокировки удовлетворяется и работа продолжается.

должно быть

postgres=# create database mydb;

нет такого сокращение у парсера как create db, но, что важнее, SQL запрос должен завершаться символом ;

Приглашение командной строки потому и меняется с postgres=# на postgres-# как индикатор того, что psql ожидает дальнейший ввод многострочного SQL, пока не встретится ;
Метакоманды psql, в то же время, действительно продолжают обрабатываться во время многострочного ввода.

https://www.postgresql.org/docs/current/sql-syntax...
соответствовать синтаксису идентификаторов.

select distinct on (created_at::date) created_at, value from tablename order by created_at::date, value desc;

См. IEEE 754 потому что это именно он. И на всё наблюдаемое "странное" поведение типов с плавающей запятой ответ един - потому что IEEE 754.

Я выполняю 2 одинаковых запроса

"WHERE friends_info.user_id = 1" vs "WHERE friends_info.friend_id= 1"

о да, одинаковые, да как бы не так. Полностью разные запросы, естественно, могут иметь различные оптимальные планы выполнения.

Bitmap Index Scan on ix_friends_user_id (cost=0.00..61.56 rows=5465 width=0) (actual time=0.742..0.743 rows=88 loops=1)

Суть ошибки выбора плана. Какое распределение данных в таблице? Каков размер самой таблицы? Вероятно несколько пользователей занимают значительную часть таблицы и это сбивает оценку селективности.
Простое чуть приподнять SET STATISTICS по полю, собрать новый analyze и посмотреть на оценку числа строк.

PS: индекс ix_friends_user_id должен быть удалён как бесполезный при наличии friends_info_user_id_friend_id_key

А как между собой связаны postgres, пароль пользователя unix и способ авторизации ssh? Да никак.

ssh аутентификация по ключу не исключает наличия пароля пользователя, sudo может быть настроен без пароля, авторизация postgresql может быть настроена без sudo раз вы как-то собрали и запустили СУБД без использования запароленного sudo этого пользователя.

Проблема-то где?

Когда появляются требования ко времени восстановления из бекапа - то почти наверняка уже доросли до полноценного PiTR и лучше рассматривать именно полновесный бекап postgresql, чем сооружать снэпшоты.
Берёте pgbackrest, настраиваете архив WAL, желаемую периодичность снятия basebackup и сроки удержания. Ну либо barman или wal-g, тут по вкусу, смысл тот же.

Получаете полностью корректный с точки зрения самой базы снэпшот (ака basebackup), а так же возможность восстановиться на произвольную точку времени, а не только на момент снятия слепка. Без штрафа производительности записи от снэпшота LVM, без CoW файловых систем.

Реплика - не бекап, решают разные задачи. Реплика ответит на вопрос "как быстро мы можем восстановить базу при полном отказе основного сервера", но не ответит на вопрос "как быстро можем восстановить удалённую табличку". Если у вас важная база и всё ещё нет реплики - ну, это странно =)