CREATE ROLE

Giriş

Kullanıcı bilgilerini değiştirmek için ALTER ROLE kullanılır.

CREATE ROLE ve CREATE USER Farkı Nedir?

Açıklaması şöyle.

In PostgreSQL 9.4 documentation it says: "CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE."

Açıklaması şöyle. Yani NOLOGIN kullanıcıları grup policy gibi düşünmek gerekir.

According to the description the LOGIN/NOLOGIN attribute determines whether or not a role can be used to connect from a client. A client can be anything from your pgAdmin III to lets say a web application. To test this you might want to create a role with LOGIN attribute and use it instead of your postgres role to connect to your server via pdAdmin III.

A role with NOLOGIN attribute can't do this. This type of role can be regarded as an object you can add privileges to. LOGIN roles might then inherit those privileges by adding them as a member. One can think of the whole matter in terms of groups and users being members of groups.

So after all I think this is just another way of expressing what you already said.

Örnek

felix isminde yeni kullanıcı yaratmak için şöyle yaparız.

CREATE ROLE felix;

Örnek

emp4 isminde yeni kullanıcı yaratmak için şöyle yaparız.

CREATE ROLE emp4 WITH LOGIN PASSWORD 'password';

Örnek

felix isminde ve şifresi süreli yeni kullanıcı yaratmak için şöyle yaparız.

CREATE ROLE felix WITH LOGIN PASSWORD 'password' VALID UNTIL '2020-09-30';

Data Type Formatting Functions - TO_DATE

Giriş

Açıklaması şöyle. Yani bir tipi String'e çevirir, veya String'den parse eder.

The PostgreSQL formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types.

Formatlama için kullanılan metodlar şöyle

to_char(...),to_date(...),to_number(...),to_timestamp(...)

Örnek

Şöyle yaparız.

to_char(to_date(proforma_invoice_date, 'DD/MM/YYYY')), 'Month')

CREATE INDEX

CREATE INDEX CONCURRENTLY

Açıklaması şöyle

Creating an index is always a resource-intensive operation, so always use the “CONCURRENTLY” parameter so that the creation does not affect the performance of the index source table.

Örnek

Şöyle yaparız

CREATE INDEX CONCURRENTLY index_name ON table_name USING btree (column);

1. Index Tipleri

Index tipleri şöyle

- Balanced Tree (B-Tree)

- Hash

- Generalized Inverted Index (GIN) : GIN INDEX yazısına taşıdım

- Generalized Inverted Search Tree (GiST)

- Space partitioned GiST (SP-GiST)

- Block Range Indexes (BRIN)

Açıklaması şöyle

You should know your indexes, although 99% of the time, you’re going to use B-Tree indexes, there’s that 1% that can make a huge difference if used right.

Balanced Tree (B-Tree)

Balanced Tree (B-Tree) yazısına taşıdım

Bu index aynı zamanda pattern_ops ile de kullanılabilir. 

- text_pattern_ops : text için

- varchar_pattern_ops : varchar için

- bpchar_pattern_ops : char için

değerlerini alabilir.

text_pattern_ops 

Ne zaman fuzzy search kullanmak gerekir?

Eğer bu iki maddeden birine takılıyorsak pg_trgm Module - Pattern Matching Full Text Search yazısına bakınız. Açıklaması şöyle

The BTree index can only search smaller strings with either direct match or prefix/suffix match with slightly better performance. But in the real world, users often misspell words, and it gets pretty hard to search for them. This is where the fuzzy search capabilities of PostgreSQL come in. They can search strings with similar words and do not have the size limitations of BTree indexes.

1. Eğer Full Text Search Yapıyorsak Takılırız

Şöyle yaparız. Burada prefix/suffix arama yapılmıyor ve index kullanılsa da yavaş olduğu görülecektir.

SELECT
  DISTINCT(event_type)
FROM
  storm_events
WHERE
  event_type ILIKE '%winter%'

2. Eğer Index Yaratamıyorsak Takılırız

B-Tree index 'in büyüklüğü belli bir değeri aşamaz. Şöyle yaparız.

CREATE INDEX anchored_search ON storm_events (event_type text_pattern_ops)

Çıktı olarak şunu alırız

index row size 4144 exceeds btree version 4 maximum 2704 for index "..."

Kullanım 

Örnek - Text Alan + LIKE

Açıklaması şöyle.

The B-Tree index is one of the simplest yet commonly used indexes in the PostgreSQL world. But when it comes to text, it cannot handle searches.

Görmek için şöyle yaparız. Sequential Scan yaptığı görülebilir.

EXPLAIN ANALYZE SELECT
  event_type
FROM
  storm_events
WHERE
  event_type LIKE '%Storm'

Index'i yaratmak için pattern_ops kullanılmalı. Şöyle yaparız. 

CREATE INDEX anchored_search ON storm_events (event_type text_pattern_ops)

Örnek - Text Alan + REGEX

Şöyle yaparız. S veya H ile başlayan event_type'ları seçer.

SELECT
  DISTINCT(event_type)
FROM
  storm_events
WHERE
  event_type ~ '^(S|H)'

2. Partial Index

Partial Index yazısına taşıdım

Array Functions and Operators

İndeks İle Erişme
Şöyle yaparız.

select (array['Cat', 'Dog', 'Horse'])[3];

pgAdmin GUI Tool

Giriş

Açıklaması şöyle

An Open Source cross-platform PostgreSQL GUI tool. 

Strengths:

- Compatible with Linux, Windows, macOS.

- Allows for working with multiple servers at a time.

- CSV file export.

- Query planning functionality.

- An ability to monitor your sessions, database locks via the dashboard.

- Shortcuts in the SQL editor for more convenient work.

- A procedural language debugger inside aimed to help in code debugging. 

- Thorough documentation and vibrant community.

Weaknesses:

- Slow and not intuitive UI compared to the paid competitors.

- Too heavy.

- Not easy to onboard.

- You need advanced skills to work with several databases at a time.

Login

Şöyle yaparız

version: '3.8'
services:
  db:
    container_name: pg_container
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: root
      POSTGRES_DB: test_db
    ports:
      - "5432:5432"
  pgadmin:
    container_name: pgadmin4_container
    image: dpage/pgadmin4
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@admin.com
      PGADMIN_DEFAULT_PASSWORD: root
    ports:
      - "5050:80"

Açıklaması şöyle

First, access the pgadmin4 via your favorite web browser by visiting the URL http://localhost:5050/. Use the admin@admin.com as the email address and root as the password to log in.

Servers > Create > Server 

Şeklen şöyle. Yeni bir PostgreSQL sunucusu yaratır.

Database Simgesi
Restore Menüsü

Tüm bağlantıların kapalı olması gerekir. Ayrıcate veritabanı drop edilir. Açıklaması şöyle.

Before restoring a database, you need to terminate all connections to that database and prepare the backup file.

Table Simgesi
Import Menüsü
Tablo'ya dosyadan veriyi aktarmayı sağlar.

Restore Menüsü
Önce tablonun truncate edilmesi gerekir.

ALTER SYSTEM

max_connections

Örnek

Şöyle yaparız

ALTER SYSTEM SET max_connections = 250;

max_prepared_transactions

Örnek

Şöyle yaparız

ALTER SYSTEM SET max_connections = 250;

shared_buffer

Örnek
Veri tabanının kullanacağı cache bellek miktarını belirtmek için şöyle yaparız.

ALTER SYSTEM SET shared_buffer TO = <value>

psql - PostgreSQL interactive terminal

Giriş
psql için bazı notlar

postgresql Kullanıcısı

Açıklaması şöyle. postgresql kullanıcısı psql komutunu çalıştır

There is a sub-class of non-root users that are often called "system users". Despite what the name suggests, they are ordinary users. They just happen to be created for special purposes like running a particular daemon and owning that daemon's files and directories. e.g. user lp for a printer daemon, ftp for ftpd, postgres for the postgresql database, and many more. They usually have a disabled password and their shell set to /bin/false or /usr/sbin/nologin or similar (user postgres is a notable exception because it's fairly common to su to user postgres to run psql for maintenance tasks).

Dump dosyasını geri yüklemek için şöyle yaparız

psql dbname < infile

Auto Complete

Açıklaması şöyle

Q : Is it possible to make autocomplete working in psql on Windows? As I know from Linux users, they can make use pressing TAB to autocomplete, which is quite handy, I guess.

A : psql's autocompletion comes by virtue of the readline or libedit library, whichever was configured when PostgreSQL was built from source. The Windows binaries were built without that support, most likely because there are no Windows ports of these libraries. If you port either of these libraries to Windows, you can build PostgreSQL from source and enjoy command line completion.

Çıktı

Giriş yaptıktan sonra gösterilen bilgi şöyledir

# psql -U myuser -d mydb -h 127.0.0.1 -p 5432 -W
Password:
psql (13.2 (Debian 13.2-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256,
 compression: off)
Type "help" for help.

Seçenekler

-c seçeneği
SQL cümlesini verir
Örnek
Şöyle yaparız.

psql mydb myuser -c "update endpoint set endpoint_group_id = 15 where mac_address='a';"

-d/--dbname seçeneği
Bağlanılacak veri tabanı ismini verir.

Örnek

Şöyle yaparız

psql -h localhost -U postgres -d mydb

-f/--file seçeneği

Çalıştırılacak SQL dosyasını belirtir.

Örnek

Şöyle yaparız

psql -U postgres -f /opt/scripts/test_script.sql

-h/--host seçeneği

Bağlanılacak host ismini belirtir. host ismi belirtilmezse localhost kullanılır.

ON_ERROR_STOP seçeneği
Örnek

Şöyle yaparız

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB"

Örnek

Şöyle yaparız

#!/bin/bash
set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
  create schema test_schema;
  create table test_schema.employee(
          id  SERIAL PRIMARY KEY,
          firstname   TEXT    NOT NULL,
          lastname    TEXT    NOT NULL,
          email       TEXT    not null,
          age         INT     NOT NULL,
          salary         real,
          unique(email)
      );
EOSQL

--set seçeneği

Örnek

SSL ile bağlanmak için şöyle yaparız

psql -h <HOST_NAME> -p 5432 -U <USER_NAME> -W -d <DB_NAME> --set=sslmode=require

-U/--username seçeneği
Bağlantı için kullanılacak kullanıcı ismini belirtir.

-V seçeneği
Şöyle bir çıktı alırız

psql (PostgreSQL) 11.12

-w/--no-password seçeneği

Açıklaması şöyle

Never issue a password prompt. If the server requires password authentication and a password is not available from other sources such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

-W/--password seçeneği

Açıklaması şöyle

Force psql to prompt for a password before connecting to a database, even if the password will not be used.

Şöyle yaparız

psql -h localhost -p 5432 -U postgres -W -d postgres

Meta Commands
Meta Commands yazısına taşıdım

PG_STAT_STATEMENTS Sistem Tablosu - İstatistikleri İçerir

Giriş

Açıklaması şöyle

The pg_stat_statements view returns statistics for all SQL queries that have run on the database since the last reset of statistics.

Note that the pg_stat_statements view is not enabled by default in Postgres. You may need to have a chat with your DevOps/DB admin team to enable it.

Kurulum

Eğer kurulu değilse kurmak için şöyle yaparız

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

calls Sütunu

Kaç defa çağrıldığını gösterir

Örnek

Şöyle yaparız. total_time milisaniyeden dakikaya çevrilir. Ortalama ise milisaniye olarak gösterilir

SELECT
  (total_time / 1000 / 60) as total,
  (total_time/calls) as avg,
  query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 100;

Örnek

Şöyle yaparız

SELECT
  pss.userid,
  pss.dbid,
  pd.datname as db_name,
  round((pss.total_exec_time + pss.total_plan_time)::numeric, 2) as total_time,
  pss.calls,
  round((pss.mean_exec_time+pss.mean_plan_time)::numeric, 2) as mean,
  round((100 * (pss.total_exec_time + pss.total_plan_time) / sum((pss.total_exec_time + pss.total_plan_time)::numeric) OVER ())::numeric, 2) as cpu_portion_pctg,
  pss.query
FROM pg_stat_statements pss, pg_database pd
WHERE pd.oid=pss.dbid
ORDER BY (pss.total_exec_time + pss.total_plan_time)
DESC LIMIT 100;

mean_time Sütunu

Örnek

En yavaş 20 sorguyu görmek için şöyle yaparız

SELECT query,
    round(total_time::numeric, 2) AS total_time,
    calls,
    round(mean_time::numeric, 2) AS mean,
    round(stddev_time::numeric, 2) AS std_dev,
    round ((100* total_time / sum (total_time::numeric)
       OVER ())::numeric, 2) AS percentage_overall, 
    userid, dbid, queryid
FROM pg_stat_statements
WHERE mean_time > 10
ORDER BY total_time DESC
LIMIT 20;

total_time Sütunu

Örnek - Query Profiling

Yavaş sorguları bulmak için kullanılabilir. Açıklaması şöyle.

This can be accomplished through their modules auto_explain and pg_stat_statements, checking pg_stat_activity for table and index activity on your server, analyzing the slow query log, or reviewing in your code.

Açıklaması şöyle

Query profiling is a technique for analyzing query performance and identifying potential bottlenecks. PostgreSQL provides support for query profiling through the use of the pg_stat_statements module, which can be used to collect and analyze query execution statistics.

Örnek

En yavaş 10 sorguyu görmek için şöyle yaparız

SELECT query, total_time, calls, rows FROM pg_stat_statements 
  ORDER BY total_time DESC LIMIT 10;

Örnek

En yavaş 10 sorguyu görmek için şöyle yaparız

-- Identify slow-running queries
SELECT query, calls, total_time, rows
FROM pg_stat_statements
WHERE total_time > '1 second'
ORDER BY total_time DESC;

2. Query Caching

Açıklaması şöyle

Query caching is a technique for storing the results of frequently executed queries in memory for faster access. PostgreSQL provides support for query caching through the use of the shared cache, which can be configured to cache query plans and results.

Örnek

Şöyle yaparız

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

VACUUMLO Komutu

Giriş
psql.exe gibi harici bir komuttur. Açıklaması şöyle.

PostgreSQL VACUUM is a technique to remove tuples that have been deleted or are now obsolete from their table to reclaim storage occupied by those dead tuples, also known as Bloat. VACUUM is an important process to maintain, especially for frequently-updated tables before it starts affecting your PostgreSQL performance.

VACUUM için 2 seçenek var.

1. Auto Vacuum
Açıklaması şöyle.

The most popular process for PostgreSQL VACUUM is the built-in autovacuum,... . The autovacuum daemon is optional, but highly recommended in the PostgreSQL community, at it automates both VACUUM and ANALYZE commands, continuously checking tables for deal tuples. 

Auto Vacuum'un bazı dezavantajları var. Açıklaması şöyle.

 - autovacuum may run even when turned off to deal with transaction ID wraparound.
- autovacuum is constantly running, which makes it start over every time it runs out of space, and start a new worker for each database in your cluster.
- autovacuum can cause out of memory errors.
- autovacuum may have trouble keeping up on a busy server.
- autovacuum can easily consume too much I/O capacity.

2. Manual Vacuum
Örnek
Şöyle yaparız.

vacuumlo -h <hostname> -U <username> -W -v <database_name>