POSTGRESQL PG_PARTMAN İLE PARTITION
Bu yazımda Pg_partman kurulumu ve kullanımından bahsedeceğim;
Öncelikle postgresql'in devel paketini kuracağız.
yum install postgresql-devel
Sonrasında aşağıdaki link üzerinden pg_partman'i indirin.
https://github.com/pgpartman/pg_partman
Aşağıdaki komutları uygulayalım;
unzip pg_partman-master.zip
cd pg_partman-master
export PATH="$PATH:/usr/pgsql-12/bin"
Aşağıdaki komutlar ile devam edelim:
make (aşağıdaki gibi çıktı verecektir.)
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o src/pg_partman_bgw.o src/pg_partman_bgw.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC src/pg_partman_bgw.o -L/usr/pgsql-12/lib -Wl,--as-needed -L/usr/lib64/llvm5.0/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-12/lib',--enable-new-dtags -shared -o src/pg_partman_bgw.so
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o src/pg_partman_bgw.bc src/pg_partman_bgw.c
make install Aşağıdaki gibi çıktı verecektir.)
/bin/mkdir -p '/usr/pgsql-12/share/extension'
/bin/mkdir -p '/usr/pgsql-12/share/extension'
/bin/mkdir -p '/usr/pgsql-12/lib'
/bin/mkdir -p '/usr/pgsql-12/doc/extension'
/bin/mkdir -p '/usr/pgsql-12/bin'
/bin/install -c -m 644 .//pg_partman.control '/usr/pgsql-12/share/extension/'
/bin/install -c -m 644 .//updates/pg_partman--2.5.0--2.5.1.sql .//updates/pg_partman--2.3.2--2.3.3.sql .//updates/pg_partman--2.6.2--2.6.3.sql .//updates/pg_partman--4.1.0--4.2.0.sql .//updates/pg_partman--2.2.1--2.2.2.sql .//updates/pg_partman--1.8.7--2.0.0.sql .//updates/pg_partman--2.2.3--2.3.0.sql .//updates/pg_partman--1.8.0--1.8.1.sql .//updates/pg_partman--2.0.0--2.1.0.sql .//updates/pg_partman--2.2.0--2.2.1.sql .//updates/pg_partman--1.3.0--1.4.0.sql .//updates/pg_partman--2.3.3--2.3.4.sql .//updates/pg_partman--4.2.1--4.2.2.sql .//updates/pg_partman--1.8.2--1.8.3.sql .//updates/pg_partman--1.8.1--1.8.2.sql .//updates/pg_partman--1.7.0--1.7.1.sql .//updates/pg_partman--1.1.0--1.2.0.sql .//updates/pg_partman--2.5.1--2.6.0.sql .//updates/pg_partman--0.1.2--0.2.0.sql .//updates/pg_partman--2.3.0--2.3.1.sql .//updates/pg_partman--3.0.2--3.1.0.sql .//updates/pg_partman--0.3.1--0.3.2.sql .//updates/pg_partman--0.2.0--0.3.0.sql .//updates/pg_partman--3.2.0--3.2.1.sql .//updates/pg_partman--1.7.1--1.7.2.sql .//updates/pg_partman--2.3.4--2.4.0.sql .//updates/pg_partman--0.3.0--0.3.1.sql .//updates/pg_partman--4.2.0--4.2.1.sql .//updates/pg_partman--1.4.5--1.5.0.sql .//updates/pg_partman--3.0.1--3.0.2.sql .//updates/pg_partman--1.8.8--2.0.0.sql .//updates/pg_partman--1.5.0--1.5.1.sql .//updates/pg_partman--3.1.1--3.1.2.sql .//updates/pg_partman--1.2.0--1.3.0.sql .//updates/pg_partman--1.4.3--1.4.4.sql .//updates/pg_partman--3.0.0--3.0.1.sql .//updates/pg_partman--1.8.6--1.8.7.sql .//updates/pg_partman--3.1.0--3.1.1.sql .//updates/pg_partman--2.4.0--2.4.1.sql .//updates/pg_partman--1.7.2--1.8.0.sql .//updates/pg_partman--3.1.2--3.1.3.sql .//updates/pg_partman--0.4.1--0.4.2.sql .//updates/pg_partman--1.5.1--1.6.0.sql .//updates/pg_partman--2.6.4--3.0.0.sql .//updates/pg_partman--3.2.1--4.0.0.sql .//updates/pg_partman--2.3.1--2.3.2.sql .//updates/pg_partman--0.1.1--0.1.2.sql .//updates/pg_partman--0.4.0--0.4.1.sql .//updates/pg_partman--1.8.7--1.8.8.sql .//updates/pg_partman--2.6.3--2.6.4.sql .//updates/pg_partman--0.1.0--0.1.1.sql .//updates/pg_partman--1.0.0--1.1.0.sql .//updates/pg_partman--1.6.1--1.7.0.sql .//updates/pg_partman--1.4.4--1.4.5.sql .//updates/pg_partman--1.8.4--1.8.5.sql .//updates/pg_partman--2.4.1--2.5.0.sql .//updates/pg_partman--0.3.2--0.4.0.sql .//updates/pg_partman--2.1.0--2.2.0.sql .//updates/pg_partman--1.6.0--1.6.1.sql .//updates/pg_partman--1.4.1--1.4.2.sql .//updates/pg_partman--1.4.2--1.4.3.sql .//updates/pg_partman--4.0.0--4.1.0.sql .//updates/pg_partman--1.8.3--1.8.4.sql .//updates/pg_partman--3.1.3--3.2.0.sql .//updates/pg_partman--2.2.2--2.2.3.sql .//updates/pg_partman--1.4.0--1.4.1.sql .//updates/pg_partman--2.6.1--2.6.2.sql .//updates/pg_partman--0.4.2--1.0.0.sql .//updates/pg_partman--1.8.5--1.8.6.sql .//updates/pg_partman--2.6.0--2.6.1.sql .//sql/pg_partman--4.2.2.sql '/usr/pgsql-12/share/extension/'
/bin/install -c -m 755 src/pg_partman_bgw.so '/usr/pgsql-12/lib/'
/bin/mkdir -p '/usr/pgsql-12/lib/bitcode/src/pg_partman_bgw'
/bin/mkdir -p '/usr/pgsql-12/lib/bitcode'/src/pg_partman_bgw/src/
/bin/install -c -m 644 src/pg_partman_bgw.bc '/usr/pgsql-12/lib/bitcode'/src/pg_partman_bgw/src/
cd '/usr/pgsql-12/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o src/pg_partman_bgw.index.bc src/pg_partman_bgw/src/pg_partman_bgw.bc
/bin/install -c -m 644 .//doc/migration_to_partman.md .//doc/pg_partman.md .//doc/pg_partman_howto.md '/usr/pgsql-12/doc/extension/'
/bin/install -c -m 755 .//bin/common/*.py '/usr/pgsql-12/bin/'
Sonrasında aşağıdaki gibi pg_partman extension'ı oluşturulur.
-bash-4.2$ psql
psql (12.1)
Type "help" for help.
postgres=# CREATE EXTENSION pg_partman WITH SCHEMA partman;
CREATE EXTENSION
Pg_partman ile partition yapabilmek için, tablo create edilir.
postgres=# create table partman.eee (id integer, name text,ldate date not null);
CREATE TABLE
Tablonun detayına bakalır.
postgres=# d partman.eee
Table "partman.eee"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
ldate | date | | not null |
Partman ile partition oluşturulur.
postgres=# SELECT partman.create_parent('partman.eee', 'ldate', 'partman', 'yearly');
create_parent
---------------
t
(1 row)
Oluşturulan partitionların detayları görüntülenebilir. Yukardaki komut ile parttionları oluşşturmuş olduk, Burada benim dikkatimi çeken şey oluşturduğumuz tabloyu partitionlu yapıda oluşturmuyor bu yüzden yeni partition eklenemiyor bu yüzden partition sınırını aşan datalar tablonun kendisine insert ediliyor, bana çok mantıklı gelmedi bu extension ben kulanmam kendi projelerimde :)
postgres-# d+ partman.eee
Table "partman.eee"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | text | | | | extended | |
ldate | date | | not null | | plain | |
Triggers:
eee_part_trig BEFORE INSERT ON partman.eee FOR EACH ROW EXECUTE FUNCTION partman.eee_part_trig_func()
Child tables: partman.eee_p2015,
partman.eee_p2016,
partman.eee_p2017,
partman.eee_p2018,
partman.eee_p2019,
partman.eee_p2020,
partman.eee_p2021,
partman.eee_p2022,
partman.eee_p2023
Access method: heap
Yazar: Engin Yılmaz
T.C. Cumhurbaşkanlığı - Savunma Sanayii Başkanlığı himayesinde yürütülmektedir.
