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

Установка

Установка postgres на ubuntu 14.04

sudo apt-get install postgresql postgresql-contrib

Если 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.

Команды

Выполняются после подключения к command-line tool psql.

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

ids=#
ids=# \d <table>
– аналог MySQL USE
\l – аналог MySQL SHOW DATABASES
\d – аналог MySQL SHOW TABLES, так же альтернатива:
SELECT * FROM pg_catalog.pg_tables
\d <table> – аналог MySQL SHOW FIELDS
\conninfo – инфа о коннекте
\du – о пользователях
\q – выход
SHOW data_directory\hba_file\ident_file – смотрим местонахождение важных для postgre файлов
Логи
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 'copy  (select * from table) 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 (второй совет с удалением файлов)

Leave a Reply