Напоминатель для работы с PostgreSQL

R5AM, Александр Ящук, Москва, 2018г.

Установка и настройка PostgreSQL в Ubuntu:

    
  sudo apt install postgresql postgresql-contrib
    

В конфиге, например /var/lib/pgsql/9.5/data/pg_hba.conf или /etc/postgresql/9.6/main/pg_hba.conf, заменить параметр в строке для IPv4 "peer", "ident" или "trust" на "md5":

    
  # IPv4 local connections:
  host    all             all             127.0.0.1/32            md5
    

Если не заменить, то будет ошибка: psql: FATAL: Peer authentication failed for user «postgres»

Перезапустить PostgreSQL:

    
  sudo /etc/init.d/postgresql restart    
    

Проверить работоспособность - залогиниться пользователем 'postgres', просмотреть базы, выйти:

    
  user_name@user_comp:~$ sudo -i -u postgres
  [sudo] пароль для user_name: 
  postgres@user_comp:~$ psql
  psql (9.6.9)
  Type "help" for help.
  
  postgres=# \list
  
  postgres=# \quit
  exit
    

Создать пользователя:

    
  sudo -u postgres createuser new_user_name
    

Создать БД:

    
  sudo -u postgres createdb db_name
    

Указать пароль пользователя:

    
  $ sudo -u postgres psql
  psql=# alter user new_user_name with encrypted password 'password';
    

Наградить пользователя привилегиями:

    
  psql=# grant all privileges on database db_name to new_user_name;
    

Изменить пароль администратора 'postgres' в PostgreSQL:

    
  $ sudo -u postgres psql
  psql=# \password postgres
    

В конфиге, например /var/lib/pgsql/9.5/data/pg_hba.conf или /etc/postgresql/9.6/main/pg_hba.conf, заменить для администратора БД параметр в строке для IPv4 "peer", "ident" или "trust" на "md5":

    
  # Database administrative login by Unix domain socket
  local   all             postgres                                md5
    

Перезапустить PostgreSQL:

    
  sudo /etc/init.d/postgresql restart    
    

Пример создания базы данных в консоли:

  CREATE DATABASE monitoring
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;
    

Дамп SQL:

    
  sudo -u postgres pg_dump имя_БД > файл_дампа.sql
    

Восстановления дампа:

    
  sudo -u postgres psql имя_БД < файл_дампа.sql
    

Использовать программу сжатия, например gzip:

    
  pg_dump имя_БД | gzip > имя_файла.gz
  gunzip -c имя_файла.gz | psql имя_БД
    или
  cat имя_файла.gz | gunzip | psql имя_БД
    

Дампы мелкими кусками, а не одним большим файлом, пример с 1 Мб:

    
  Нарезать:
  pg_dump имя_БД | split -b 1m - имя_файла
  
  Собрать:
  cat имя_файла* | psql имя_БД