Replication

Giriş

Primary veri tabanından diğerlerine replication işini hiç yapmadım. 

Şartlar

Açıklaması şöyle

The basic requirement replication requirement,

1. Replica DB required same PG version

2. Replica DB is in always sync with Primary DB.

Replication Lag

Örnek

Gecikmenin sebebi için bir örnek burada. Burada replica üzerinde koşan sorgu, WAL sender'i engellediği için gecikme oluyor. Özet olarak silme işlemi eskice kayıtlar üzerinde yapılsa daha iyi

You see when you have postgres replication you have a dilemma.

In one hand someone is querying standby with long running query, pinning a snapshot at t7.

On the other hand the primary has deleted some rows and vacuum is about to purge deleted tuples on t7 as no query on the primary needs them. Vacuum purges those, new WAL records of the purge.

WAL sender kicks in and send the vacuumed WAL entries to purge t7 on the standby but guess what? standby still need them. This blocks replication as the WAL receiver cannot apply the purged row if a query need them.

This creates lag in replication and as a result entire replication halt until queries are done.

Replication Lag Ölçümü

Eğer Patroni kullanmıyorsak elle ölçüm yapılabilir

Örnek

Şöyle yaparız

# Check the current LSN on primary DB.
psql -h primary_host -c "SELECT pg_current_wal_lsn();"

# check replica lag
psql -h replica_host 
  -c "SELECT pg_is_in_recovery(),pg_is_wal_replay_paused(),pg_last_wal_receive_lsn(),
    pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();"

# Check the difference between primary DB LSN and replica replay LSN.
lsn_diff_size = psql -h replica_host 
  -c "SELECT pg_wal_lsn_diff('54/5A282990','54/5A282990');"

# Take the different and Check the size in MB or GB.
// LAG difference in MB
psql -h replica_host -c "SELECT round(lsn_diff_size/pow(1024,2.0),2 missing_lsn_mIB;" 

// LAG difference in GB
psql -h replica_host -c "SELECT round(lsn_diff_size/pow(1024,3.0),2 missing_lsn_GIB;"

Ayarlar

Not : Bir örnek burada

1. Master Ayarları

Master üzerinde bir kullanıcı yarat

CREATE USER replication 
REPLICATION LOGIN CONNECTION LIMIT 1 
ENCRYPTED PASSWORD 'replicationpa55word';

# change the maximum number of connections allowed to the replication user
ALTER ROLE replication CONNECTION LIMIT -1;

/etc/postgresql/12/main/postgresql.conf dosyasına şunu ekle. 172.XXX adresini master bilgisayarın gerçek IP adresi ile değiştir. 

listen_addresses = 'localhost,172.16.10.220'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

Slave bilgisayarın bağlanabilmesi için doğrulanması gerekir.  etc/postgresql/12/main/pg_hba.conf dosyasına şunu ekle. 

host    replication     replication     172.16.10.119/0   md5

Master bilgisayarı tekrar başlat

sudo /etc/init.d/postgresql restart
veya 
sudo service postgresql restart

Örnek

Şöyle yaparız

# On primary — in postgresql.conf
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB

# On replica - recovery.conf or postgresql.conf
primary_conninfo = 'host=primary-host port=5432 user=replicator'
hot_standby = on

2. Slave Ayarları

/etc/postgresql/12/main/postgresql.conf dosyasına şunu ekle. 172.XXX adresini slave bilgisayarın gerçek IP adresi ile değiştir. 

listen_addresses = 'localhost,172.16.10.119'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

Master bilgisayarın bağlanabilmesi için doğrulanması gerekir.  etc/postgresql/12/main/pg_hba.conf dosyasına şunu ekle. 

host    replication     replication     172.16.10.220/0   md5

Slave bilgisayarın tüm verisini sil

sudo rm -rfv * cd /var/lib/postgresql/12/main/

Master bilgisayardaki tüm veriyi slave bilgisayara kopyala. Burada şifre isteyecektir. Şifre replicationpa55word. Bitince slave bilgisayar tekrar başlatılır

sudo su postgres \
  pg_basebackup \
  -h 172.16.10.220 \
  -U replication \
  -p 5432 \
  -D /var/lib/postgresql/12/main/  \
  -Fp \
  -Xs \
  -P \
  -R

Multi-Version Concurrency Control

Giriş

Açıklaması şöyle

Postgres was the first DBMS to rollout multi-version concurrency control (MVCC), which means reading never blocks writing and vice versa. This feature is one of the main reasons why businesses prefer Postgres to MySQL. As Postgres explains, "Unlike most other database systems which use locks for concurrency control, Postgres maintains data consistency by using a multiversion model. This means that while querying a database each transaction sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This protects the transaction from viewing inconsistent data that could be caused by (other) concurrent transaction updates on the same data rows, providing transaction isolation for each database session."

Utilizing what Oracle calls 'snapshot isolation', MVCC lets multiple readers and writers concurrently interact with the Postgres database, eliminating the need for a read-write lock every time someone interacts with the data. A side benefit is this process provides a big efficiency boost.

Açıklaması şöyle

When we execute a SQL statement, it uses a snapshot of data instead of every row. This prevents users from viewing inconsistent data generated by concurrent transactions. It also minimizes lock contentions for different sessions trying to read or write data.

Bir başka açıklama şöyle

Postgres works with MVCC (Multiversion Concurrency Control) that ultimately creates a new row for update/insert. This helps in concurrent transactions with older transactions having access to an older version of data(as they need to be completed) but at the same time, it’s adding a lot of stale storage in the system. So at a time, only one version of a row will be active and all others will present as dead(stale) rows.

Kısaca tüm bu açıklamalar şunu söylüyor, PostgreSQL satırların kopyasını alıyor ve bir müddet sonra bu satırlar gereksiz/geçersiz hale geliyorlar. Bu yüzden bu satırları ara ara VACUUM, REPACK Extension gibi bir şeyle temizlemek gerekiyor.

Transaction ID

Her transaction için bir tane ID üretilir. Bunu görmek için şöyle yaparız

BEGIN;
SELECT * FROM table1;
SELECT txid_current();
txid_current 
--------------
          754
(1 row)

COMMIT;

MVCC model

MVCC modelde her satır için gizli xmin ve xmax sütunları vardır. Bunları görmek için şöyle yaparız

SELECT xmin, xmax, * FROM <table_name>:
 xmin | xmax | <column_name>
------+------+---------------
  800 |    0 | <column_value>

Açıklaması şöyle

xmin holds the txid of the transaction that inserted this tuple.

xmax holds the txid of the transaction that deleted or updated this tuple. If this tuple has not been deleted or updated, t_xmax is set to 0, which means INVALID.

Örnek

Şeklen şöyle

| xmin | xmax | c1  | c_tid |
|------|------|-----|-------|
| 9    | 12   | 100 | (0,1) |
| 12   | -    | 200 | (0,2) |

Açıklaması şöyle

If transaction 9 creates a row, a new tuple is created with a header xmin value of 9. If a later transaction 12 updated the row, a new tuple is created with xmin 12 and the old tuple xmax is marked as 12 indicating that the old tuple was “alive” from 9–12. Thus the row has two tuples (row versions) one which lived between 9–12 and one that is alive starting from 12 and onwards. A repeatable read transaction 10 for instance must read the row’s old tuple 9–12 and not the new one updated by transaction 11. 

Daha sonra VACUUM çalıştırılır ve şöyle olur

| xmix | xmax | c1  | c_tid |
|------|------|-----|-------|
| 12   | -    | 200 | (0,2) |

Açıklaması şöyle

A VACUUM operation clean tuples that have been deleted AND no longer required by any running transactions.

DELETE İşlemi

Açıklaması şöyle. PostgreSQL silinmesi istenen satırları hemen silmiyor. Bu yüzden bu satırları ara ara VACUUM ile silmek gerekiyor.

When your Java application executes a DELETE or UPDATE statement against a PostgreSQL database, a deleted record is not removed immediately nor is an existing record updated in its place. Instead, the deleted record is marked as a dead tuple and will remain in storage. The updated record is, in fact, a brand new record that PostgreSQL inserts by copying the previous version of the record and updating requested columns. The previous version of that updated record is considered deleted and, as with the DELETE operation, marked as a dead tuple.

There is a good reason why the database engine keeps old versions of the deleted and updated records in its storage. For starters, your application can run a bunch of transactions against PostgreSQL in parallel. Some of those transactions do start earlier than others. But if a transaction deletes a record that still might be of interest to a few transactions started earlier, then the record needs to be kept in the database (at least until the point in time when all earlier started transactions finish). This is how PostgreSQL implements MVCC (multi-version concurrency protocol).

It’s clear that PostgreSQL can’t and doesn’t want to keep the dead tuples forever. This is why the database has its own garbage collection process called vacuuming. There are two types of VACUUM — the plain one and the full one. The plain VACUUM works in parallel with your application workloads and doesn’t block your queries. This type of vacuuming marks the space occupied by dead tuples as free, making it available for new data that your app will add to the same table later. The plain VACUUM doesn’t return the space to the operating system so that it can be reused by other tables or 3rd party applications (except in some corner cases when a page includes only dead tuples and the page is in the end of a table).

Sütun Tipleri - json

Giriş

Json verisi saklamak için kullanılır. Açıklaması şöyle.

The only difference between json & jsonb is their storage:

- json is stored in its plain text format, while
- jsonb is stored in some binary representation

There are 3 major consequences of this:

- jsonb usually takes more disk space to store than json (sometimes not)
- jsonb takes more time to build from its input representation than json
- json operations take significantly more time than jsonb (& parsing also needs to be done each time you do some operation at a json typed value)

When jsonb will be available with a stable release, there will be two major use cases, when you can easily select between them:

- If you only work with the JSON representation in your application, PostgreSQL is only used to store & retrieve this representation, you should use json.
- If you do a lot of operations on the JSON value in PostgreSQL, or use indexing on some JSON field, you should use jsonb.

Açıklaması şöyle. json tipi 2012 yılında PostgreSQL 9.2 ile eklendi. Yani jsonb sütün tipinden daha önce eklendi. 

In 2012, PostgreSQL 9.2 introduced the first JSON data type in Postgres. It had syntax validation but it stored the incoming document directly as text with white spaces included. It wasn't very useful for real-world querying, index-based searching, and other functionalities you would normally do with a JSON document.

Açıklaması şöyle.

...a JSON datatype (2012), and a potpourri of new features in PostgreSQL 10 (better native support for partitioning and replication, full text search support for JSON, etc.)

Örnek

Şöyle yaparız

CREATE TABLE IF NOT EXISTS posts (
   ...
   metadata json default '{}'
 );

ALTER TABLE

Giriş

Söz dizimi şöyle

ALTER TABLE table_name
ADD column_name data_type [constraint],
MODIFY column_name data_type [constraint],
DROP column_name,
ADD CONSTRAINT constraint_name constraint_definition,
DROP CONSTRAINT constraint_name;

DROP COLUMN

Örnek

Normalde şöyle yaparız

ALTER TABLE foo DROP COLUMN bar;

Ancak bazen "vsnprintf failed: Invalid argument" şeklinde bir hata geliyor. Bu durumda şöyle yaparız

SET lc_messsages = 'C';
ALTER TABLE foo DROP COLUMN bar;

REPLICA IDENTITY

Örnek

Şöyle yaparız

ALTER TABLE ingredients REPLICA IDENTITY FULL;

Açıklaması şöyle

The ALTER TABLE command with the REPLICA IDENTITY clause is used to set the replication identity for a table. When using Debezium's PostgreSQL connector, the connector requires a unique primary key or a unique identifier to keep track of changes. If a table does not have a primary key or a unique identifier, you will get an error, saying that the table does not have a replica identity, which is used for tracking changes. By running ALTER TABLE ... REPLICA IDENTITY FULL, you're setting the table's replication identity to "full", which means that the entire row is used as the identifier for change tracking purposes.

Docker Compose ve PostgreSQL

Giriş

Docker compose ile kullanmak için bazı notlar

Image İsmi

Şunlar olabilir

- postgres:11.1

- postgres:13.3

- postgres:15.1

- postgres:15rc2

- debezium/postgres

- debezium/postgres:13

En Basit

Şöyle yaparız

version: '3'
services:

  authorization-db:
    image: postgres:11.1
    container_name: auth-db
    ports:
      - "5432:5432"

command Alanı

Şunlar olabilir

- max_connections

- max_prepared_transactions

Örnek - max_ connections

Şöyle yaparız

services:
  database:
    image: postgres:latest
    command: postgres -c 'max_connections=250'

environment Alanı

Ortam Değişkenleri Şunlar olabilir

- POSTGRES_PASSWORD

- POSTGRES_USER

- POSTGRES_DB

Örnek

Şöyle yaparız. Burada iki tane veri tabanı çalıştırılıyor.

version: '3'
services:
  course-catalog-operational-db:
    image: postgres:13.3
    container_name: course-catalog-operational-db
    command:
      - "postgres"
      - "-c"
      - "wal_level=logical"
    environment:
      POSTGRES_PASSWORD: 123456
      POSTGRES_DB: course-catalog-db
    ports:
      - "5433:5432"
  instructors-legacy-db:
    image: postgres:13.3
    container_name: instructors-legacy-db
    command:
      - "postgres"
      - "-c"
      - "wal_level=logical"
    environment:
      POSTGRES_PASSWORD: 123456
      POSTGRES_DB: instructors-db
    ports:
      - "5434:5432"
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

Örnek - Hasura GraphQL + PostgreSQL 15

Şöyle yaparız

version: '3.6'
services:
  postgres:
    image: postgres:15rc2
    restart: always
    volumes:
    - db_data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: postgrespassword
    ports:
    - "5432:5432"
  graphql-engine:
    image: hasura/graphql-engine:v2.13.0
    ports:
    - "8080:8080"
    depends_on:
    - "postgres"
    restart: always
    environment:
      ## postgres database to store Hasura metadata
      HASURA_GRAPHQL_METADATA_DATABASE_URL: postgres://postgres:postgrespassword@postgres:5432/postgres
      ## this env var can be used to add the above postgres database to Hasura as a data source. this can be removed/updated based on your needs
      PG_DATABASE_URL: postgres://postgres:postgrespassword@postgres:5432/postgres
      ## enable the console served by server
      HASURA_GRAPHQL_ENABLE_CONSOLE: "true" # set to "false" to disable console
      ## enable debugging mode. It is recommended to disable this in production
      HASURA_GRAPHQL_DEV_MODE: "true"
      HASURA_GRAPHQL_ENABLED_LOG_TYPES: startup, http-log, webhook-log, websocket-log, query-log
      ## uncomment next line to set an admin secret
      HASURA_GRAPHQL_ADMIN_SECRET: myadminsecretkey
volumes:
  db_data:

docker-entrypoint-initdb

Veri tabanı başlarken çalıştırılacak SQL dosyalarını belirtiriz

Örnek

Şöyle yaparız

services:
  postgres:
    image: postgres
    ports:
      - "5432:5432"
    restart: always
    environment:
      POSTGRES_PASSWORD: password
      POSTGRES_DB: blogdb
      POSTGRES_USER: user
    volumes:
      - ./data/postgresql:/var/lib/postgresql
      - ./pg-initdb.d:/docker-entrypoint-initdb.d

healthcheck

Örnek

Şöyle yaparız. 

-q ile quite belirtiliyor

-d ile ile veri tabanı ismi belirtiliyor

-U ile kullanıcı ismi belirtiliyor

version: '3'services:
  postgres:
    image: postgres:13.1
    healthcheck:
      test: [ "CMD", "pg_isready", "-q", "-d", "postgres", "-U", "root" ]
      timeout: 45s
      interval: 10s
      retries: 10
    restart: always
    environment:
      - POSTGRES_USER=root
      - POSTGRES_PASSWORD=password
      - APP_DB_USER=docker
      - APP_DB_PASS=docker
      - APP_DB_NAME=docker
    volumes:
      - ./db:/docker-entrypoint-initdb.d/
    ports:
      - 5432:5432

Örnek

Şöyle yaparız. Kullanıcı ismi -U ile belirtiliyor

postgres:
  container_name: scheduling-airflow-postgres
  image: postgres:13
  environment:
    POSTGRES_USER: airflow
    POSTGRES_PASSWORD: airflow
    POSTGRES_DB: airflow
  deploy:
    resources:
      limits:
        cpus: "0.40"
        memory: 1200M
  volumes:
    - postgres-db-volume:/var/lib/postgresql/data
  healthcheck:
    test: ["CMD", "pg_isready", "-U", "airflow"]
    interval: 5s
    retries: 5
  restart: always
  profiles:
    - scheduling
  networks:
    - datastack  

restart Alanı

Genellikle always değeri verilir. Açıklaması şöyle

restart always : is used to restart the container if there is an error when creating the container.

volumes Alanı

Pod'un kullandığı /var/lib/postgresql/data dizini bir volume'a bağlanır

Örnek

Şöyle yaparız

version: '3.8'

services:
  ...
  db:
    image: postgres:15.2
    restart: always
    environment:
      POSTGRES_USER: book-user
      POSTGRES_PASSWORD: k9ZqLC
      POSTGRES_DB: bookdb
    volumes:
      - db-data:/var/lib/postgresql/data
    ports:
      - 6432:5432
volumes:
  db-data:
    driver: local

pgbench komutu

Giriş

pgbench komutu veri tabanında tablolar oluşturur ve bunları 1 milyon satır ile doldurur.  Daha sonra test yaparız

Örnek

Şöyle yaparız

$ pgbench -c 10 -j 2 -t 1000 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 75.438 ms
initial connection time = 160.700 ms
tps = 132.559344 (without initial connection time)
$

Sonra shared_buffer seçeneğini değiştirelim. Önce şöyle olsun

$ show shared_buffers;
 shared_buffers
----------------
 128MB
(1 row)

Şöyle yapalım

sudo vi /etc/postgresql/15/main/postgresql.conf

...
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 1GB                    # min 128kB
                                        # (change requires restart)

Veri tabanını tekrar başlatalım

sudo systemctl restart postgresql

Değeri kontrol edelim

$ show shared_buffers;
 shared_buffers
----------------
 1GB
(1 row)

Testi tekrar koşalım

$ pgbench -c 10 -j 2 -t 1000 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 47.632 ms
initial connection time = 148.379 ms
tps = 209.944478 (without initial connection time)

latency değeri azaldı. Ayrıca transaction per second değeri de arttı

-C seçeneği - Connection Overhead

Açıklaması şöyle

The -C option in the pgbench indicates that for every single transaction, pgbench will close the open connection and create a new one. This is useful for measuring the connection overhead.

Örnek

Şöyle yaparız

$ pgbench -c 20 -t 100 -S my_benchmark_test_db -h 127.0.0.1 -p 6432 -U my_db_user -C -f mysql.sql
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 50
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
number of failed transactions: 0 (0.000%)
latency average = 178.276 ms
average connection time = 8.867 ms
tps = 112.185757 (including reconnection times)
SQL script 1: 
 - weight: 1 (targets 50.0% of total)
 - 1022 transactions (51.1% of total, tps = 57.326922)
 - number of failed transactions: 0 (0.000%)
 - latency average = 85.993 ms
 - latency stddev = 50.377 ms
SQL script 2: mysql.sql
 - weight: 1 (targets 50.0% of total)
 - 978 transactions (48.9% of total, tps = 54.858835)
 - number of failed transactions: 0 (0.000%)
 - latency average = 84.039 ms
 - latency stddev = 51.036 ms

-c seçeneği - the_number_of_clients_to_connect_with

Kaç tane connection açılacağını belirtir. 

Örnek

Şöyle yaparız

$  pgbench -c 1000 -T 60 my_benchmark_test_db -h 127.0.0.1 -p 5432 -U my_db_user
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
pgbench: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  sorry, too many clients already
connection to server at "127.0.0.1", port 5432 failed: FATAL:  sorry, too many clients already
pgbench: error: could not create connection for client 44

Örnek

Şöyle yaparız

pgbench -c 1000 -T 60 my_benchmark_test_db -h 127.0.0.1 -p 6432 -U my_db_user
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: 
scaling factor: 50
query mode: simple
number of clients: 1000
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 47370
number of failed transactions: 0 (0.000%)
latency average = 1106.280 ms
initial connection time = 8788.955 ms
tps = 903.930420 (without initial connection time)
dmi@dmi-VirtualBox:~$

-i seçeneği - initialize

Örnek

Şöyle yaparız

$ /usr/pgsql-10/bin/pgbench -i -s 5 testdb_1
...

$ psql testdb_1

testdb_1=# \dt+
List of relations
Schema |       Name       | Type    |  Owner   |    Size   | 
--------+------------------+-------+----------+---------+----
public |   pgbench_accounts | table | postgres | 64 MB     |
public |   pgbench_branches | table | postgres | 40 kB     |
public |   pgbench_history  | table | postgres | 0   bytes |
public |   pgbench_tellers  | table | postgres |   40 kB   |
(4 rows)

-s seçeneği - scale

Normal veri setinden ne kadar daha fazla kullanılacağını belirtir

Örnek

Şöyle yaparız

$ pgbench -i -s 50 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres
Password:
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
5000000 of 5000000 tuples (100%) done (elapsed 10.19 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 30.29 s (drop tables 0.05 s, create tables 0.04 s, client-side generate 10.64 s, vacuum 4.75 s, primary keys 14.81 s).
$

-t seçeneği - the_number_of_transactions_to_execute

Söz dizimi şöyle

pgbench -c <the_number_of_clients_to_connect_with> -j <the_number_of_workers_processes> 
  -t <the_number_of_transactions_to_execute> <sample_db_name>

-T seçeneği - duration of the test

Örnek

Şöyle yaparız

pgbench -c 10 -j 2 -t 1000 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres

Örnek

Şöyle yaparız

pgbench -c 50 -j 2 -T 180 benchmark_delay

Açıklaması şöyle

In this example, -c sets the number of client connections, -T defines the duration of the test in seconds, and -U specifies the user.

PostGIS ST_DISTANCE

Giriş

İmzası şöyle

ST_Distance(geometry g1, geometry g2);

Örnek
İki nokta arasındaki mesafeyi şöyle buluruz.

SELECT ST_Distance(ST_GeomFromText('POINT(27.185425 88.124582)',4326),
 ST_GeomFromText('POINT(27.1854258 88.124500)', 4326));

Örnek

Tabloya yeni bir sütun ekleyelim ve index koyalım

ALTER TABLE clients_details_locations ADD COLUMN geom geometry(Point, 4326);

UPDATE clients_details_locations 
   SET geom = ST_SetSRID(ST_MakePoint(longitude , latitude), 4326);

CREATE INDEX clients_details_locations_geom_idx  ON clients_details_locations 
  USING GIST (geom);

Bir noktaya en yakın noktaları bulmak için şöyle yaparız

SELECT ... order by st_distance(geom,client_point)