PostgreSQL под Linux

Создайте файл /etc/apt/sources.list.d/pgdg.list и добавьте в него информацию о репозитории

deb http://apt.postgresql.org/pub/repos/apt/ YOUR_DEBIAN_VERSION_HERE-pgdg main

например так

sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" > /etc/apt/sources.list.d/pgdg.list

Импортируйте ключ репозитория

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

И обновите список пакетов

sudo apt-get update

после чего можно приступать к установке

apt-get install postgresql-9.5

Проверяем что всё встало как надо

ps -ef | grep postgre  
netstat -na|grep 5432

Создаём папку для данных

mkdir /var/postgresql/
chown postgres:postgres /var/postgresql/
chmod 755 /var/postgresql/
cd /tmp
sudo -H -u postgres /usr/lib/postgresql/9.5/bin/pg_upgrade \
   -b /usr/lib/postgresql/9.4/bin \
   -B /usr/lib/postgresql/9.5/bin \
   -d /var/lib/postgresql/9.4/main \
   -D /var/lib/postgresql/9.5/main \
   -o ' -c config_file=/etc/postgresql/9.4/main/postgresql.conf' \
   -O ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf'  

http://narbeh.org/blog/upgrade-postgresql-9-4-to-9-5-in-debian/

/etc/postgresql/9.5/main/
environment  
pg_ctl.conf  
pg_hba.conf  
pg_ident.conf  
  
start.conf

Смотрим где и что

ps auxw | grep postgres | grep -- -D
pg_lsclusters

pg_hba.conf

настройки для авторизации для всех баз и пользователей

sudo nano /etc/postgresql/9.5/main/pg_hba.conf

Разрешаем из локалки ходить с паролем пользователя

host    all    all    192.168.0.1/16    md5

postgresql.conf

sudo nano /etc/postgresql/9.5/main/postgresql.conf

Папка баз данных

data_directory = '/var/postgresql/9.5/main'

Порт (может по умолчанию оказаться другой, если 5432 занят, например, если вы мигрировали с предыдущей версии или создали 2й кластер)

port = 5432

На каких интерфейсах висит слушатель

listen_addresses = 'localhost,192.168.0.1'

Если поменять путь к фалам баз данных, то их нужно тоже скопировать или создать

sudo su postgres
/usr/lib/postgresql/9.5/bin$ ./initdb -D /var/postgresql/

Создание нового кластера

Кластер БД - это набор баз данных, который управляется одним экземпляром запущенного сервера БД.

Если на системе уже есть PostgeSQL то под наше ПО создаёс новый кластер.

pg_createcluster --locale de_DE.UTF-8 --start 8.3 main

Удаление

pg_dropcluster --stop <version> main

поработать с СУБД через оболочку:

sudo -u postgres psql

Работаем с СУБД через оболочку:

sudo -u postgres psql

Создадим тестовую базу данных и тестового пользователя:

postgres=# CREATE DATABASE test_database;
CREATE DATABASE

postgres=# CREATE USER test_user WITH password 'qwerty';
CREATE ROLE
postgres=# GRANT ALL privileges ON DATABASE test_database TO test_user;
GRANT

Не забудьте создать схему данных или создавайте объенты в схеме паблик

CREATE SCHEMA wog;

Список баз и таблиц

\l - базы
\dt - таблицы

или

SELECT datname FROM pg_database
WHERE datistemplate = false;
SELECT table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name;

To switch databases:

\connect database_name
\c database_name  

Список пользователей

SELECT u.usename AS "User name", u.usesysid AS "User ID",
  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text)
       WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
       WHEN u.usecreatedb THEN CAST('create database' AS pg_catalog.text)
       ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;

Сносим забытый пароль

ALTER USER "user_name" WITH PASSWORD 'new_password';

Для выхода из оболочки введите команду \q.

Теперь попробуем поработать с созданной базой данных от имени test_user:

psql -h localhost test_database test_user

Создадим новую таблицу:

test_database=> CREATE SEQUENCE user_ids;
CREATE SEQUENCE
test_database=> CREATE TABLE users (id INTEGER PRIMARY KEY DEFAULT NEXTVAL('user_ids'), login CHAR(64), password CHAR(64));
NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "users_pkey" FOR TABLE "users"
CREATE TABLE

Смотрим список

SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'

или

\dt

TableSpace

create tablespace alagava_data location '/var/lib/postgresql/9.4/alagava_data';
create tablespace alagava_indx location '/var/lib/postgresql/9.4/alagava_indx';

Не забудьте создать каталог и дать на него права postgres

SET default_tablespace = alagava_data ;

Чтоб все пользовательские TS жили в правильно месте.

https://postgrespro.ru/docs/postgresql/9.6/creating-cluster

Расширения

Расширения, доступные для установки в данный момент, можно найти в системном представлении

pg_available_extensions или pg_available_extension_versions.

Установить

CREATE EXTENSION hstore;
ALTER EXTENSION pg_trgm SET SCHEMA schema_name;

Список установленных

pg_available_extensions

Перезапуск

sudo service postgresql restart

или

/etc/init.d/postgresql {start|stop|restart|reload|force-reload|status} [version ..]

Резервное копирование в PostgreSQL выглядит примерно так:

pg_dump -c -h 192.168.0.1 -U test_user test_database > ./dump.sql

Если у вас большая база данных, обратите также внимание на поддержку утилитой pg_dump флага -Fc.

Восстановление из резервной копии:

cat dump.sql | psql -h 192.168.0.1 test_database test_user

или

psql -h 192.168.0.1 test_database test_user < dump.sql