JDBC DatabaseMetaData.getTables metodu

Giriş

docker-compose.yml şöyle. Yani veri tabanı ismi db

version: '3.9'
services:
  postgresql:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: db
    ports:
      - 5432:5432
    volumes:
      - ./postgresql_schema.sql:/docker-entrypoint-initdb.d/postgresql_schema.sql

PostgreSQL açısından bir veri tabanı şeklen şöyle

db
  Schemas
    information_schema <-- Schema ama listelenmiyor
    pg_catalog         <-- Schema ama listelenmiyor
    public
      Tables
        myworker  <-- Ben yarattım
      Views
      Materialized Views
      Indexes
      Functions
      Sequences
      Data types
      Aggregate functions
    myschema
      Tables
        myworker  <-- Ben yarattım
      Views
      Materialized Views
      Indexes
      Functions
      Sequences
      Data types
      Aggregate functions  

public schema altındaki tabloyu yaratmak için şöyle yaptım

CREATE TABLE IF NOT EXISTS myworker (...);

myschema schema altındaki tabloyu yaratmak için şöyle yaptım. Burada DBeaver ile bağlanırken URL olarak jdbc:postgresql://localhost:5432/db verdim.

CREATE SCHEMA myschema

CREATE TABLE IF NOT EXISTS myschema.myworker (...);

Burada önemli bir şey var. public schema için DDL şöyle

CREATE SCHEMA public AUTHORIZATION pg_database_owner;

myschema için DDL şöyle. Yani admin kullanıcısı bu schema'nın sahibi değil

CREATE SCHEMA myschema AUTHORIZATION myuser;

Örnek - catalog değeri geç

Elimizde şöyle bir kod olsun

try (Connection connection = ...;
     ResultSet tables = connection.getMetaData().getTables(
        connection.getCatalog(),
        null,
        null,
        new String[]{"TABLE", "VIEW"})) {
  ...
}

1. Eğer bağlantı şöyle ise yani veri tabanı belirtilmemişse 

jdbc:postgresql://localhost:5432/

connection.getCatalog() çağrısı "postgres" döner

connection.getSchema() çağrısı "public" döner

ResultSet te boş geliyor. Çünkü postgres diye bir veri tabanı var ama boş

2. Eğer bağlantı şöyle ise yani veri tabanı belirtilmişse

jdbc:postgresql://localhost:5432/db

connection.getCatalog() çağrısı db döner

connection.getSchema() çağrısı "public" döner

ResultSet dolu gelir ve içinde public + myschema schema altında yarattığım tabloları görürüm

Örnek - catalog değeri geçme

Elimizde şöyle bir kod olsun

try (Connection connection = ...;
     ResultSet tables = connection.getMetaData().getTables(
        null,
        null,
        null,
        new String[]{"TABLE", "VIEW"})) {
  ...
}

Yukarıdaki durumun aynı çıktısını alırım. Yani PostgreSQL açısından catalog belirtilse de belirtilmese de  aynı

Örnek - Veri tabanındaki Tüm Tablolar Yani Database Discovery

Şöyle yaparız. Burada catalog ve schema ve tablaneNamePattern parametreleri belirtilmediği için tüm PostgreSQL schemalarındaki tüm tabloları buluyor. Tabloları buluyor çünkü Types olarak TABLE belirtilmiş. 

İlginç olan şey rs.getString("TABLE_CAT") yapsak bile sonucun null gelmesi. Yani Postgres bir şekilde catalog ismini döndürmüyor

try(ResultSet rs = conn.getMetaData().getTables(null,null,null,new String[] {"TABLE"})) {
  while (rs.next()) {
    System.out.println(rs.getString("TABLE_SCHEM") + "." + rs.getString("TABLE_NAME"));
  }
}

// Output
app96.t1
app96.t2
public.administration$account
public.appmodule$uploadedfile
public.audittrail$audittrailsuperclass

OpenTelemetry

Giriş

Açıklaması şöyle

To monitor PostgreSQL, you can use OpenTelemetry PostgreSQL receiver that comes with OpenTelemetry Collector.

OpenTelemetry Collector is designed to collect, process, and export telemetry data from multiple sources. It acts as a centralized and flexible data pipeline that simplifies the management of telemetry data in distributed systems.

Uptrace is a OpenTelemetry backend that supports distributed tracing, metrics, and logs. You can use it to monitor applications and troubleshoot issues.

pgBackRest - Yedekleme (Backup) İçindir

Giriş

Açıklaması şöyle

pgBackRest is a modern PostgreSQL Backup & Restore solution that has all the features you may ever need:

- Parallel backup and restore.

- Full, differential, and incremental backups.

- Delta restore.

- ZSTD compression.

- Encryption.

- And many more.

Full backup

Örnek

Şöyle yaparız

sudo -u postgres pgbackrest --type=full --stanza=demo backup

Differential backup

Açıklaması şöyle

Differential backup only copies files that have changed since the last full backup. It is smaller than a full backup, but to restore it you will need the base full backup.

Örnek

Şöyle yaparız

sudo -u postgres pgbackrest --type=diff --stanza=demo backup

Incremental backup

Açıklaması şöyle

Incremental backup only copies files that have changed since the last backup (full, differential, or incremental). It is smaller than a full or differential backup, but to restore it you will need all dependant backups.

Örnek

Şöyle yaparız

sudo -u postgres pgbackrest --type=incr --stanza=demo backup

Backup restore

Örnek

Şöyle yaparız

# To restore the cluster from the last backup:
sudo -u postgres pgbackrest --stanza=demo --delta restore

# To view all available backups:
sudo -u postgres pgbackrest --stanza=demo info

AVG + OVER (PARTITION BY ...)

Örnek

Şöyle yaparız. Her division'a göre gruplanıyor ve ortalama maaş bulunuyor

SELECT empno, salary, division,
avg(salary) OVER (PARTITION BY division) FROM EMPLOYEES;

Örnek

Şöyle yaparız. 

rank : fiyata göre gruplanıyor ve azalarak her farklı fiyat grubuna numara veriliyor

average_price : products tablosu date alanına göre gruplanıyor ve o günün fiyat ortalaması bulunuyor

SELECT id, name, price, date, 
RANK() OVER (ORDER BY price DESC) AS rank,
AVG(price) OVER (PARTITION BY date) AS average_price,
MAX(price) OVER () AS max_price
FROM products

Debezium Connector PostgreSQL to Sink

Örnek - Redis'e Stream

Şöyle yaparız. Burada Debezium Kafka yerine Redis'e yazıyor

debezium.sink.type=redis
debezium.sink.redis.address=redis:6379
debezium.source.connector.class=io.debezium.connector.postgresql.PostgresConnector
debezium.source.offset.storage.file.filename=data/offsets.dat
debezium.source.offset.flush.interval.ms=0
debezium.source.database.hostname=postgres
debezium.source.database.port=5432
debezium.source.database.user=postgres
debezium.source.database.password=postgres
debezium.source.database.dbname=postgres
debezium.source.database.server.name=tutorial
debezium.source.schema.whitelist=test_schema
debezium.source.plugin.name=pgoutput

Örnek - Rabbitmq'ya

Şöyle yaparız. debezium.event_store.mt_events isimli exchange'e yazar

debezium.source.connector.class=io.debezium.connector.postgresql.PostgresConnector
debezium.source.offset.storage.file.filename=data/offsets.dat
debezium.source.database.hostname=localhost
debezium.source.database.port=5432
debezium.source.database.user=postgres
debezium.source.database.password=1234
debezium.source.database.dbname=MartenDB
debezium.source.table.include.list=event_store.mt_events
debezium.source.column.include.list=event_store.mt_events.data
debezium.source.topic.prefix=debezium
debezium.source.plugin.name=pgoutput

debezium.sink.type=rabbitmq
debezium.sink.rabbitmq.connection.host=localhost
debezium.sink.rabbitmq.connection.port=5672
debezium.sink.rabbitmq.connection.username=guest
debezium.sink.rabbitmq.connection.password=guest

Stored Procedures

PostgreSQL Version 11 ile eklendi

CREATE TEMP TABLE

Giriş

Açıklaması şöyle

A temporary table in Postgres is a table whose definition and data are visible only within the current session or transaction. These tables are created and managed in a similar way to regular tables, but they are automatically dropped at the end of the session or transaction, depending on their scope.

jsonb - JPA İle Kullanım

Örnek - Insert

Şöyle yaparız

INSERT INTO room_categories (name, code, metadata)
    VALUES (?, ?, ? ::jsonb ); 

Örnek - Update

Elimizde şöyle bir kod olsun

@Entity
@Table(name = “room_categories”)
@TypeDef(name = “jsonb”, typeClass = JsonBinaryType.class)
public class RoomCategory extends AbstractEntity implements Serializable {

  private String name;

  private String code;

  @Type(type = "jsonb")
  @Column(columnDefinition = "json")
  private Metadata metadata;
}

public class Metadata implements Serializable {
  private String field1;
  private String field2;
}

Şu kod çalışmaz

int updateCount = entityManager.createNativeQuery("""
    UPDATE
        room_categories
    SET
        metadata = :metadata
    WHERE
        code = :code AND
        metadata ->> 'field1' is null            
    """)
.setParameter("code ", "123-ABC")
.setParameter(
    "metadata",
    new Metadata()
        .setField1("ABC")
        .setField2("123")
)
.executeUpdate();

Hata şöyle. Metadata sınıfı Serializable olduğu için Hibernate sınıfı bytea tipine çevirmeye çalışıyor.

ERROR: column “metadata” is of type jsonb but expression is of type bytea Hint: You will need to rewrite or cast the expression.

Düzeltmek için şöyle yaparız

int updateCount = entityManager.createNativeQuery("""
    UPDATE
        room_categories
    SET
        metadata = :metadata
    WHERE
        code = :code AND
        metadata ->> 'field1' is null            
    """)
.setParameter("code ", "123-ABC")
.unwrap(org.hibernate.query.Query.class) // Burada Hibernate tipine çevir
.setParameter(
    "metadata",
    new Metadata()
        .setField1("ABC")
        .setField2("123"),
    JsonBinaryType.INSTANCE // setParameter metoduna JsonBinary tip olduğunu belirt
)
.executeUpdate();

Açıklaması şöyle

First, we had to unwrap the JPA Query to a Hibernate org.hibernate.query.Query and call the setParameter method that takes a Hibernate Type instance.

Now, Hibernate will know that the metadata parameter needs to be handled by the JsonBinaryType, and not by the SerializableType.

Örnek - Select

Şöyle yaparız. Burada tabloyu dolduruyoruz. 

CREATE TABLE item (
  id INT8 NOT NULL,
  jsonb_content JSONB,
  PRIMARY KEY (id)
)

INSERT INTO item (id, jsonb_content) VALUES (1, 
  '{"top_element_with_set_of_values":["TAG1","TAG2","TAG11","TAG12","TAG21","TAG22"]}');


-- item without any properties, just an empty json
INSERT INTO item (id, jsonb_content) VALUES (6, '{}');

-- int values
INSERT INTO item (id, jsonb_content) VALUES (7, '{"integer_value": 132}');

-- double values
INSERT INTO item (id, jsonb_content) VALUES (10, '{"double_value": 353.01}');

-- enum values
INSERT INTO item (id, jsonb_content) VALUES (13, '{"enum_value": "SUPER"}');

-- string values
INSERT INTO item (id, jsonb_content) VALUES (16,
  '{"string_value": "this is full sentence"}');

-- inner elements
INSERT INTO item (id, jsonb_content) VALUES (21, '{"child": {"pets" : ["dog", "cat"]}}');

Şöyle yaparız. Entity nesnemize jsonb sütun için String tipinden bir alan ekliyoruz. Sütun tipinin jsonb olduğunu columnDefinition ile gelirtiyoruz.

@Entity
@Table(name = "item")
public class Item {

  @Column(name = "jsonb_content", columnDefinition = "jsonb")
  private String jsonbContent;
  ...  
}

Şöyle yaparız. Burada entityManager.createNativeQuery() ile sorgu yapıyoruz

private EntityManager entityManager;

public List<Item> findAll(String expression) {
  return entityManager
    .createNativeQuery("SELECT * FROM item i WHERE "
    + "i.jsonb_content#>>'{string_value}' LIKE '"
    + expression + "'", Item.class)
    .getResultList();
}