Postgres разные хинты

sudo apt-get install postgresql postgresql-contrib

Установка на MacOS достаточно простая, в пакете поставки сразу включен pgadmin, psql и другие полезные утилиты (типо pgdump). Недостаток при установке – в моем случае установился непонятный пароль (хотя явно вводил классику ввиду posgres), если столкнетесь с таким же, то нужно сбрасывать пароль для postgres – решается указанием пароля в psql, но перед этим может потребоваться изменение в pg_hba (md5 -> trust) + рестарт сервера, но я не думаю, что это обязательно.

/Library/PostgreSQL/15/data/pg_hba.conf
/Library/PostgreSQL/15/bin/pg_ctl restart -D /Library/PostgreSQL/15/data/

/Library/PostgreSQL/15/bin/psql -U postgres
ALTER USER postgres WITH PASSWORD 'new_password';

Если postgresql используется в связке с php, то обязательно нужно установить модуль php с поддержкой postgresql, после этого перезагрузить apache. В противном случае ничего работать не будет.

Wiki выдает:
(Can't contact the database server: Postgres functions missing, have you compiled PHP with the --with-pgsql option? (Note: if you recently installed PHP, you may need to restart your webserver and database) )

В целом сайт может выдавать:
Страница 192.168.1.100 не работает
Сайт 192.168.1.100 пока не может обработать этот запрос.
HTTP ERROR 500

Ставим модуль:

sudo apt-get install php5-pgsql
sudo service apache2 restart

После этого в выводе phpinfo(); должен появиться блок pgsql.

-bash-4.2# psql <db_name>
psql (9.4.22)
Type "help" for help.

ids=#
ids=# \d <table>

SELECT * FROM pg_catalog.pg_tables

tail -f /var/lib/postgresql/<version>/data/postmaster.log

/etc/postgresql/<version>/main/...

ЗАПУСК

Запуск делается через пользователя postgres. При запуске может потребоваться указание бинарного файла и data_directory.

sudo -i -u postgres
/usr/lib64/postgresql-9.5/bin/postgres -D /etc/postgresql-9.5 --data-directory=/var/lib/postgresql/9.5/data

Подключение

Подключение происходит из под юзера postgres, так же как и запуск:

~$ psql
psql: FATAL: role "<user>" does not exist
~$ sudo -i -u postgres
~$ psql
psql (9.3.15)
Type "help" for help.
postgres=#

Запросы из командной строки

psql <db>  -c 'truncate table <table>'
psql <db> -c 'copy (select version()) to stdout'
psql <db> -c 'copy (select * from <table>) to stdout'
psql <db> -c 'copy (SELECT <field>, COUNT(<field>) FROM <table> GROUP BY <field>) to stdout'

Бекап и восстановление

Бекап тоже делается из под юзера postgres:

sudo pg_dumpall -U postgres > pg_dumpall.sql

Восстановить из бекапа можно так:

sudo -i -u postgres
psql -f <file> postgres

В терминал пойдут исполняемые команды:
SET
SET
SET
CREATE ROLE
...

При восстановлении нужно учитывать, что:

1. Если база не пустая новый дамп в виде перечня исполняемых команд, сделанный  pg_dumpall, может “не встать”. Самый простой способ – полное удаление и установка production версии postgresql (см. ниже про удаление).

2. Если версии postgresql отличаются между production серверов и бекапным, могут быть разного рода проблемы. Самый простой способ – полное удаление и установка production версии postgresql.

3. Настройки postgres не переносятся, поэтому в файле /etc/postgresql/<version>/main/pg_hba.conf может понадобиться изменить MD5 (по умолчанию) на trust. Симптомы: в таком случае может писаться “Peer authentication failed for user” или “password authentication failed for user”. После изменения конфига перезагружаем postgresql.

~$ psql -d xxx -U xxx
psql: FATAL: Peer authentication failed for user "xxx"
~$ psql -d xxx -U xxx -h 127.0.0.1
Password for user xxx: 
psql: FATAL: password authentication failed for user "xxx"
FATAL: password authentication failed for user "xxx"

SETTINGS
DEFAULT
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

TRUSTED
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust

sudo service postgresql restart

4. Так же может не перенестись атрибут LOGIN для учетных записей, отличных от postgres. Смотрим права для учетки через \du, возможно напротив нее стоит NO LOGIN. Если это так – выдаем права. Симптомы: в таком случае при подключении из под проблемной учетки выдает “not permitted to log in”.

~$ psql -d xxx -U xxx -h 127.0.0.1
psql: FATAL: role "xxx" is not permitted to log in
~$ psql
postgres=# ALTER ROLE xxx WITH LOGIN;
ALTER ROLE

УДАЛЕНИЕ

Полное удаление postgresql (второй совет с удалением файлов)