">

Postgresql Pg_partman ile Partition

Postgresql Pg_partman ile Partition

Merhaba,

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

Umarım Faydalı olmuştur...

 

 

Yorumlar

Henüz yorum yapılmadı.

Yorum Yapın