PostgreSQL. Заметки
Где находится каталог с БД
sudo -u postgres psql -c "show data_directory;"[sudo] password for tech:
data_directory
-----------------------------
/var/lib/postgresql/14/main
(1 row)
Версия СУБД:
postgres --version
/bin/postgres -V
Табличные пространства
select * from pg_tablespace ;- pg_global — общие объекты кластера;
- pg_default — табличное пространство по умолчанию
sudo mkdir /var/lib/postgresql/ts_dir
sudo chown postgres /var/lib/postgresql/ts_dir
postgres=# create tablespace ts location '/var/lib/postgresql/ts_dir';
CREATE TABLESPACE
postgres=# select * from pg_tablespace ;
oid | spcname | spcowner | spcacl | spcoptions
--------+------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
243515 | ts | 10 | |
(3 rows)
В каталоге /var/lib/postgresql/ts_dir появился каталог PG_14_202107181 , пока пустой.
В каталоге $PGDATA/pg_tblspc появилась символьная ссылка на наш каталог.
:~/14/main/pg_tblspc$ ls - ltotal 0lrwxrwxrwx 1 postgres postgres 26 Sep 2 13:06 243515 -> /var/lib/postgresql/ts_dir
postgres=# \db List of tablespaces Name | Owner | Location------------+----------+---------------------------- pg_default | postgres | pg_global | postgres | ts | postgres | /var/lib/postgresql/ts_dir(3 rows)
При создании БД укажем тбсCREATE DATABASE appdb TABLESPACE ts;
В нашей папке создался каталог для БД ( 243516 )И в нём появились файлы БД, около трёхсот штук:
~/ts_dir/PG_14_202107181/243516$ ls603 2609_vm 2618 2661 2681 2704 2838 603_fsm 2610 2618_fsm 2662 2682 2753 2838_fsm 603_vm 2610_fsm 2618_vm 2663 2683 2753_fsm 2838_vm 604 2610_vm 2619 2664 2684 2753_vm 2839 605 2611 2619_fsm 2665 2685 2754 2840 605_fsm 2612 2619_vm 2666 2686 2755 2840_fsm 605_vm 2612_fsm 2620 2667 2687 2756 2840_vm 606 2612_vm 2650 2668 2688 2757 2841 606_fsm 2613 2651 2669 2689 2830 2995 606_vm 2615 2652 2670 2690 2831 2996 607 2615_fsm 2653 2673 2691 2832 3079
CREATE TABLE t1(
id integer GENERATED ALWAYS AS IDENTITY,
name text
); -- создастся в тбс, дефолтном для БД, в нашем случае - "ts"
CREATE TABLE t2(
n numeric
) TABLESPACE pg_default; -- явно указываем тбс
SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
tablename | tablespace
-----------+------------
t1 |
t2 | pg_default
(2 rows)
Пустое поле tablespace указывает на табличное пространство по умолчанию, а у второй таблицы поле заполнено.
Еще один способ задать табличное пространство без явного указания при создании объекта — предварительно установить табличное пространство в параметре default_tablespace.
appdb=# show default_tablespace; default_tablespace-------------------- (1 row)
Одно табличное пространство может использоваться для объектов нескольких баз данных.
Таблицы (и другие объекты, например, индексы), можно перемещать между табличными пространствами.
Физическое перемещение объекта:
=> ALTER TABLE t1 SET TABLESPACE pg_default;
Можно переместить и все объекты из одного табличного пространства в другое:
=> ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE ts;
На время ее выполнения доступ к перемещаемому объекту полностью блокируется.
Размер тбс - два сособа:
appdb=# select pg_size_pretty( pg_tablespace_size('ts') ); pg_size_pretty---------------- 8625 kB
appdb=# \db+ List of tablespaces
Name | Owner | Location | Access privileges | Size |------------+----------+----------------------------+-------------------+---------+ pg_default | postgres | | | 8688 MB | pg_global | postgres | | | 632 kB | ts | postgres | /var/lib/postgresql/ts_dir | | 8625 kB |
appdb=# DROP TABLESPACE ts;ERROR: tablespace "ts" is not empty
Сначала узнаем и запомним идентификатор табличного пространства:appdb=# SELECT oid FROM pg_tablespace WHERE spcname = 'ts'; oid-------- 243515
Затем получим список баз данных, в которых есть объекты из удаляемого пространства:
=> SELECT datname
FROM pg_database
WHERE oid IN (SELECT pg_tablespace_databases(243515));
datname---------- configdb appdb(2 rows)
appdb=# SELECT datnameappdb-# FROM pg_databaseappdb-# WHERE oid IN (SELECT pg_tablespace_databases(243515)); datname---------- configdb appdb(2 rows)
appdb=# \c configdbYou are now connected to database "configdb" as user "postgres".configdb=# SELECT relnamespace::regnamespace, relname, relkindconfigdb-# FROM pg_classconfigdb-# WHERE reltablespace = 243515; relnamespace | relname | relkind--------------+---------+--------- public | t | r(1 row)
configdb=# DROP TABLE t;DROP TABLEconfigdb=#
И вторая база данных. Поскольку ts является табличным пространством по умолчанию, у объектов в pg_class идентификатор табличного пространства равен нулю. Это, как нам уже известно, объекты системного каталога:
configdb=# \c appdbYou are now connected to database "appdb" as user "postgres".appdb=# SELECT count(*) FROM pg_class WHERE reltablespace = 0; count------- 360
Табличное пространство по умолчанию можно сменить; при этом все таблицы из старого пространства физически переносятся в новое. Предварительно надо отключиться от базы.
=> \c postgres
You are now connected to database "postgres" as user "student".
=> ALTER DATABASE appdb SET TABLESPACE pg_default;
Вот теперь табличное пространство может быть удалено.
=> DROP TABLESPACE ts;
DROP TABLESPACE
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 8697 MB |
pg_global | postgres | | | | 632 kB |
(2 rows)
/bin/postgres -V
- pg_global — общие объекты кластера;
- pg_default — табличное пространство по умолчанию
sudo mkdir /var/lib/postgresql/ts_dir
sudo chown postgres /var/lib/postgresql/ts_dir
postgres=# create tablespace ts location '/var/lib/postgresql/ts_dir'; CREATE TABLESPACE postgres=# select * from pg_tablespace ; oid | spcname | spcowner | spcacl | spcoptions --------+------------+----------+--------+------------ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | 243515 | ts | 10 | | (3 rows)
В каталоге /var/lib/postgresql/ts_dir появился каталог PG_14_202107181 , пока пустой.
В каталоге $PGDATA/pg_tblspc появилась символьная ссылка на наш каталог.
:~/14/main/pg_tblspc$ ls - l
В каталоге $PGDATA/pg_tblspc появилась символьная ссылка на наш каталог.
:~/14/main/pg_tblspc$ ls - l
total 0
lrwxrwxrwx 1 postgres postgres 26 Sep 2 13:06 243515 -> /var/lib/postgresql/ts_dir
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------------------------
pg_default | postgres |
pg_global | postgres |
ts | postgres | /var/lib/postgresql/ts_dir
(3 rows)
При создании БД укажем тбс
CREATE DATABASE appdb TABLESPACE ts;
В нашей папке создался каталог для БД ( 243516 )
И в нём появились файлы БД, около трёхсот штук:
~/ts_dir/PG_14_202107181/243516$ ls
603 2609_vm 2618 2661 2681 2704 2838
603_fsm 2610 2618_fsm 2662 2682 2753 2838_fsm
603_vm 2610_fsm 2618_vm 2663 2683 2753_fsm 2838_vm
604 2610_vm 2619 2664 2684 2753_vm 2839
605 2611 2619_fsm 2665 2685 2754 2840
605_fsm 2612 2619_vm 2666 2686 2755 2840_fsm
605_vm 2612_fsm 2620 2667 2687 2756 2840_vm
606 2612_vm 2650 2668 2688 2757 2841
606_fsm 2613 2651 2669 2689 2830 2995
606_vm 2615 2652 2670 2690 2831 2996
607 2615_fsm 2653 2673 2691 2832 3079
CREATE TABLE t1( id integer GENERATED ALWAYS AS IDENTITY, name text ); -- создастся в тбс, дефолтном для БД, в нашем случае - "ts"
CREATE TABLE t2( n numeric ) TABLESPACE pg_default; -- явно указываем тбс
SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public';
tablename | tablespace -----------+------------ t1 | t2 | pg_default (2 rows)
Пустое поле tablespace указывает на табличное пространство по умолчанию, а у второй таблицы поле заполнено.
Еще один способ задать табличное пространство без явного указания при создании объекта — предварительно установить табличное пространство в параметре default_tablespace.
appdb=# show default_tablespace;
default_tablespace
--------------------
(1 row)
Одно табличное пространство может использоваться для объектов нескольких баз данных.
Таблицы (и другие объекты, например, индексы), можно перемещать между табличными пространствами.
Физическое перемещение объекта:
=> ALTER TABLE t1 SET TABLESPACE pg_default;
Можно переместить и все объекты из одного табличного пространства в другое:
=> ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE ts;
На время ее выполнения доступ к перемещаемому объекту полностью блокируется.
Размер тбс - два сособа:
appdb=# select pg_size_pretty( pg_tablespace_size('ts') );
pg_size_pretty
----------------
8625 kB
appdb=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Size |
------------+----------+----------------------------+-------------------+---------+
pg_default | postgres | | | 8688 MB |
pg_global | postgres | | | 632 kB |
ts | postgres | /var/lib/postgresql/ts_dir | | 8625 kB |
appdb=# DROP TABLESPACE ts;
ERROR: tablespace "ts" is not empty
Сначала узнаем и запомним идентификатор табличного пространства:
appdb=# SELECT oid FROM pg_tablespace WHERE spcname = 'ts';
oid
--------
243515
Затем получим список баз данных, в которых есть объекты из удаляемого пространства:
=> SELECT datname FROM pg_database WHERE oid IN (SELECT pg_tablespace_databases(243515));
datname
----------
configdb
appdb
(2 rows)
appdb=# SELECT datname
appdb-# FROM pg_database
appdb-# WHERE oid IN (SELECT pg_tablespace_databases(243515));
datname
----------
configdb
appdb
(2 rows)
appdb=# \c configdb
You are now connected to database "configdb" as user "postgres".
configdb=# SELECT relnamespace::regnamespace, relname, relkind
configdb-# FROM pg_class
configdb-# WHERE reltablespace = 243515;
relnamespace | relname | relkind
--------------+---------+---------
public | t | r
(1 row)
configdb=# DROP TABLE t;
DROP TABLE
configdb=#
И вторая база данных. Поскольку ts является табличным пространством по умолчанию, у объектов в pg_class идентификатор табличного пространства равен нулю. Это, как нам уже известно, объекты системного каталога:
configdb=# \c appdb
You are now connected to database "appdb" as user "postgres".
appdb=# SELECT count(*) FROM pg_class WHERE reltablespace = 0;
count
-------
360
Табличное пространство по умолчанию можно сменить; при этом все таблицы из старого пространства физически переносятся в новое. Предварительно надо отключиться от базы.
=> \c postgres
You are now connected to database "postgres" as user "student".
=> ALTER DATABASE appdb SET TABLESPACE pg_default;
Вот теперь табличное пространство может быть удалено.
=> DROP TABLESPACE ts;DROP TABLESPACE
postgres=# \db+ List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Description ------------+----------+----------+-------------------+---------+---------+------------- pg_default | postgres | | | | 8697 MB | pg_global | postgres | | | | 632 kB | (2 rows)
TABLES
https://stackoverflow.com/questions/41991380/whats-the-difference-between-pg-table-size-pg-relation-size-pg-total-relatio
select
pg_relation_size(20306, 'main') as main,
pg_relation_size(20306, 'fsm') as fsm,
pg_relation_size(20306, 'vm') as vm,
pg_relation_size(20306, 'init') as init,
pg_table_size(20306),
pg_indexes_size(20306) as indexes,
pg_total_relation_size(20306) as total;
main | fsm | vm | init | pg_table_size | indexes | total
--------+-------+------+------+---------------+---------+--------
253952 | 24576 | 8192 | 0 | 286720 | 196608 | 483328
(1 row)Генерация серий:
select dt::date from generate_series('2021-01-31', '2021-05-31', interval '1 month') as dt;
select * from generate_series(1,10) a, generate_series(1,2) b;
Путь до основного файла относительно PGDATA можно получить функцией:
=> SELECT pg_relation_filepath('t');
-- id БД :
SELECT oid FROM pg_database WHERE datname = 'data_lowlevel'
SELECT relfilenode FROM pg_class WHERE relname = 't';
-- для индекса тоже работает:
SELECT pg_relation_filepath('t_pkey');
-- файлы последовательности, созданной для первичного ключа:
=> SELECT pg_relation_filepath(pg_get_serial_sequence('t','id'));
pg_relation_filepath ---------------------- base/16527/16528 (1 row)
Существует полезное расширение oid2name, входящее в стандартную поставку, с помощью которого можно легко связать объекты БД и файлы.
Можно посмотреть все базы данных:
postgres@dom:~$ /usr/lib/postgresql/14/bin/oid2name -p 39124 --- укажем порт, если он не дефолтный All databases: Oid Database Name Tablespace ----------------------------------- 243516 appdb pg_default 243528 configdb pg_default 243547 data_lowlevel pg_default 243514 eddb pg_default 18360 intercom pg_default 13726 postgres pg_default 13725 template0 pg_default 1 template1 pg_default
Список всех таблиц, не заходя в базу:
postgres@dom:~$ /usr/lib/postgresql/14/bin/oid2name -p 39124 -d mydb
From database "mydb":
Filenode Table Name
----------------------
243549 t
Можно по имени таблицы узнать имя файла:
student$ /usr/lib/postgresql/13/bin/oid2name -d data_lowlevel -t t
From database "data_lowlevel":
Filenode Table Name
----------------------
16530 t
Или наоборот, по номеру файла узнать таблицу:
student$ /usr/lib/postgresql/13/bin/oid2name -d data_lowlevel -f 16530
From database "data_lowlevel":
Filenode Table Name
----------------------
16530 tДля получения размера отдельного индекса можно воспользоваться функцией pg_table_size. Toast-части у индексов нет, поэтому функция покажет только размер всех слоев индекса (main, fsm).
Сейчас у таблицы только один индекс по первичному ключу, поэтому размер этого индекса совпадает со значением pg_indexes_size:
=> SELECT pg_table_size('t_pkey') AS t_pkey;t_pkey -------- 245760 (1 row)
Общий размер таблицы, включающий TOAST и все индексы:
=> SELECT pg_total_relation_size('t');
pg_total_relation_size
------------------------
827392
Комментарии
Отправить комментарий