Управление базами данных

База данных создаётся SQL-командой CREATE DATABASE. Текущий пользователь автоматически назначается владельцем
Для выполнения команды CREATE DATABASE необходимо подключение к серверу базы данных (при установлении подключения обязательно задается база к которой происходит подключение, поэтому к postgres'у без баз подключиться не выйдет). Первая база данных всегда создаётся командой initdb при инициализации пространства хранения данных. Эта база данных называется postgres

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

Для удобства, есть утилита командной строки для создания баз данных - createdb. Она просто подключается к базе данных postgres и выполняет CREATE DATABASE. Без параметров она создаст базу данных с именем текущего пользователя

Иногда необходимо создать базу данных для другого пользователя и назначить его владельцем, чтобы он мог конфигурировать и управлять ею:

CREATE DATABASE имя_базы OWNER имя_роли;

Лишь суперпользователь может создавать базы данных для других (для ролей, членом которых он не является)

Также существует вторая системная база template0. При инициализации она содержит те же самые объекты, что и template1 (В template0 не следует вносить никакие изменения после инициализации кластера)
Если в команде CREATE DATABASE указать в качестве шаблона template0 вместо template1, вы сможете получить «чистую» пользовательскую базу данных, не содержащую ничего, что могло быть добавлено на месте в template1
Для создания базы данных на основе template0, используйте:

CREATE DATABASE dbname TEMPLATE template0;

Можно создавать дополнительные шаблоны баз данных, и, более того, можно копировать любую базу данных кластера, если указать её имя в качестве шаблона в команде CREATE DATABASE

В таблице pg_database есть два полезных флага для каждой базы данных - столбцы datistemplate и datallowconn:

• datistemplate указывает на факт того, что база данных может выступать в качестве шаблона в команде CREATE DATABASE. Если флаг установлен, то для пользователей с правом CREATEDB клонирование доступно; если флаг не установлен, то лишь суперпользователь и владелец базы данных могут её клонировать
• datallowconn указывает на возможность подключаться к этой базе данных (однако текущие сессии не закрываются при сбросе этого флага). База template0 обычно помечена как datallowconn = false для избежания любых её модификаций. И template0, и template1 всегда должны быть помечены флагом datistemplate = true

testdb=# select datname,datistemplate,datallowconn from pg_database ;
  datname  | datistemplate | datallowconn 
-----------+---------------+--------------
 benchmark | f             | t
 template1 | t             | t
 template0 | t             | f
 postgres  | f             | t
 testdb    | f             | t
 testdb2   | f             | t
(6 rows)

База данных postgres также создаётся при инициализации кластера. Она используется пользователями и приложениями для подключения по умолчанию. Представляет собой всего лишь копию template1, и может быть удалена и повторно создана при необходимости

В базе postgres действительно пусто)))
Для меня это было открытием, я думал что таблицы pg_* находятся внутри нее

You are now connected to database "postgres" as user "postgres".
postgres=# \dt                                                  ### Внутри пусто
Did not find any relations.
postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# select * from pg_database;                             ### А таблицы pg_* доступны и внутри других баз
  oid  |  datname  | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace |                           datacl                           
-------+-----------+--------+----------+------------+----------+---------------+--------------+--------------+---------------+--------------+------------+---------------+------------------------------------------------------------
 16384 | benchmark |     10 |        0 | C          | C        | f             | t            |           -1 |         13754 |          726 |          1 |          1663 | 
     1 | template1 |     10 |        0 | C          | C        | t             | t            |           -1 |         13754 |          726 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
 13754 | template0 |     10 |        0 | C          | C        | t             | f            |           -1 |         13754 |          726 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
 13755 | postgres  |     10 |        0 | C          | C        | f             | t            |           -1 |         13754 |          726 |          1 |          1663 | {=Tc/postgres,postgres=CTc/postgres,testuser=CTc/postgres}
 16415 | testdb    |     10 |        0 | C          | C        | f             | t            |           -1 |         13754 |          726 |          1 |          1663 | 
 16419 | testdb2   |     10 |        0 | C          | C        | f             | t            |           -1 |         13754 |          726 |          1 |          1663 | 
(6 rows)

testdb=# 

PostgreSQL имеет множество параметров конфигурации времени исполнения. Можно выставить специфичные для базы данных значения по умолчанию
Клиенты могут делать это, выполняя команду SET geqo TO off (geqo выбран как пример). Для того чтобы это действовало по умолчанию в конкретной базе данных, необходимо выполнить команду:

ALTER DATABASE mydb SET geqo TO off;

Установка сохраняется, но не применяется тотчас. В последующих подключениях к этой базе данных, эффект будет таким, будто перед началом сессии была выполнена команда SET geqo TO off;. Пользователь по-прежнему может изменять этот параметр во время сессии; ведь это просто значение по умолчанию. Чтобы сбросить такое установленное значение, используйте ALTER DATABASE dbname RESET varname

Базы данных удаляются командой DROP DATABASE. Лишь владелец базы данных или суперпользователь могут удалить базу. Удаление базы данных это необратимая операция
Невозможно выполнить команду DROP DATABASE пока существует хоть одно подключение к заданной базе. Однако можно подключиться к любой другой, в том числе и template1
template1 может быть единственной возможностью при удалении последней пользовательской базы данных кластера
Также есть cli-утилита dropdb

Табличные пространства в PostgreSQL позволяют администраторам организовать логику размещения файлов объектов базы данных в файловой системе
Табличные пространства позволяют администратору управлять дисковым пространством для инсталляции PostgreSQL. Это полезно минимум по двум причинам:

• Во-первых, это нехватка места в разделе, на котором был инициализирован кластер и невозможность его расширения. Табличное пространство можно создать в другом разделе и использовать его до тех пор, пока не появится возможность переконфигурирования системы
• Во-вторых, табличные пространства позволяют администраторам оптимизировать производительность согласно бизнес-процессам, связанным с объектами базы данных. Например, часто используемый индекс можно разместить на очень быстром и надёжном, но дорогом SSD-диске. В то же время таблица с архивными данными, которые редко используются и скорость к доступа к ним не важна, может быть размещена в более дешёвом и медленном хранилище

Несмотря на внешнее размещение относительно основного каталога хранения данных PostgreSQL, табличные пространства являются неотъемлемой частью кластера и не могут трактоваться, как самостоятельная коллекция файлов данных. Они зависят от метаданных, расположенных в главном каталоге, и потому не могут быть подключены к другому кластеру, или копироваться по отдельности. Также, в случае потери табличного пространства (при удалении файлов, сбое диска и т. п.), кластер может оказаться недоступным или не сможет запуститься. Таким образом, при размещении табличного пространства во временной файловой системе, например, в RAM-диске, возникает угроза надёжности всего кластера

CREATE TABLESPACE для создания

CREATE TABLESPACE fastspace LOCATION '/mnt/tipasuperssd';
CREATE TABLE foo(i int) TABLESPACE fastspace;

Каталог должен существовать, быть пустым и принадлежать пользователю ОС, под которым запущен PostgreSQL. Все созданные впоследствии объекты, принадлежащие целевому табличному пространству, будут храниться в файлах расположенных в этом каталоге. Каталог не должен размещаться на съёмных или устройствах временного хранения, так как кластер может перестать функционировать из-за потери этого пространства
Создавать табличное пространство должен суперпользователь базы данных, но после этого можно разрешить обычным пользователям его использовать

Когда default_tablespace имеет значение отличное от пустой строки, он будет использоваться неявно в качестве значения параметра TABLESPACE в командах CREATE TABLE и CREATE INDEX, если в самой команде не задано иное

Табличное пространство, связанное с базой данных, также используется для хранения её системных каталогов. Более того, это табличное пространство используется по умолчанию для таблиц, индексов и временных файлов, создаваемых в базе данных, если не указано иное в выражении TABLESPACE, или переменной default_tablespace, или temp_tablespaces (соответственно). Если база данных создана без указания конкретного табличного пространства, то используется пространство, к которому принадлежит копируемый шаблон

При инициализации кластера автоматически создаются два табличных пространства:

• pg_global используется для общих системных каталогов
• pg_default используется по умолчанию для баз данных template1 и template0 (и соответственно наследуется на создаваемые из этих шаблонов новые базы (если эта настройка не переопределена))

Для удаления пустого табличного пространства используйте команду DROP TABLESPACE (сперва его надо опустошить и только потом удалять)

В pg_tablespaces хранится инфа о табличных пространствах, а также метакоманда \db пожет инфу о них

postgres=# select * from pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions 
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        | 
  1664 | pg_global  |       10 |        | 
 16421 | fastspace  |       10 |        | 
(3 rows)

postgres=# \db
            List of tablespaces
    Name    |  Owner   |     Location      
------------+----------+-------------------
 fastspace  | postgres | /mnt/tipasuperssd
 pg_default | postgres | 
 pg_global  | postgres | 
(3 rows)

PostgreSQL использует символические ссылки для упрощения реализации табличных пространств

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

Каталог $PGDATA/pg_tblspc содержит символические ссылки, которые указывают на внешние табличные пространства кластера

root@pgsql-edu:/var/lib/postgresql/14/main/pg_tblspc# ls -lha
total 8.0K
drwx------  2 postgres postgres 4.0K Jan  8 04:37 .
drwx------ 19 postgres postgres 4.0K Jan  4 00:12 ..
lrwxrwxrwx  1 postgres postgres   17 Jan  8 04:37 16421 -> /mnt/tipasuperssd
root@pgsql-edu:/var/lib/postgresql/14/main/pg_tblspc#