POSTGRESQL YEDEKLEME VE KURTARMA -1 (BACKUP AND RECOVERY) LOGICAL BACKUP

Veri tabanı sistemlerinin en temel ve en önemli özelliklerinden biride hata durumlarında sizi koruyacak bir yedekleme politikasına sahip olmasıdır. Veritabanı herhangi bir nedenden dolayı crash olduğunda veya veritabanı geçmiş bir andaki haline döndürülmek istendiğinde veritabanı backup/restore işlemleri kullanılır. PostgreSQL veri tabanında temelde üç tip backup/restore operasyonu mevcuttur.

1. Logical Backup
   • Sql Dump (pg_dump ve pg_dumpall)
   • Copy Komutu (CSV Load , Unload)
2. File System level Backup (Cold Backup, Offline Backup)
3. Online Backup (Continuous Archiving)
   • pg_basebackup
   • pgBackRest (extension)

1.LOGICAL BACUP (SQL DUMP)

Bu yedek yönteminde veri tabanı objelerinin SQL scriptlerinin text olarak yedeklenmesi mantığı üzerinden yapılır, geri dönüş (restore) senaryolarında ise bu sql ler tekrar çalıştırılarak backup tan geri dönülmüş olur, bu yaklaşım küçük veritabanları açısından sorun teşkil etmesede veritabanı boyutu büyüdünde, bu yöntem çok yavaş kalacak ve zorunda kalmadıkça kullanılabilir bir yöntem olmaktan çıkacaktır, anca pg_dump komutları bir çok açıdan DBA ler tarafından sıkça kullanılan bir çok faydalı özellikler barındıran bir komut setidir.

Pg_dump komut satırı aracı veritabanı veya veritabanı nesneleri için SQL dump backup ve restore işlemlerini yapmamızı sağlar. Kullanım şekli en basit haliyle aşağıdaki gibidir.

Syntax:pg_dump [options] [dbname]

Bu komut çıktı olarak bir text dosyası üretir. Herhangi bir uzak sunucudan pg_dump komutu ile veritabanına bağlanıp backup/restore işlemleri gerçekleştirilebilir. Veritabanına bağlanacak kullanıcıların yedeklerin alınacak objeler üzerinde okuma hakkı olmalıdır, çünkü pg_dump dump alınacak veritabanındaki tüm objeleri satır satır okuyup sql cümlesi halinde bize bir text dosyasına yazacaktır. Veritabanı backup pg_dump ile alınacak ise superuser yetkisine sahip bir kullanıcı ile çalıştırılmalıdır.

pg_dump bazı özelliklerini kısaca bahsedecek olursak
Yedek alınacak veritabanında yedek alınması istenen bütün nesnelerin kayıtlarını sql ifadeleri olarak text dosyalarına döker

• Cluster düzeyinde global nesnelerin tanımlarını (DLL) almaz , bunun için pg_dumpall kullanılır
• pg_dump read ve write işlemlerini engellemez
• pg_dump tarafından oluşturulan dump dosyaları tutarlıdır (consistent), yani pg_dump başlatıldığı andaki Transaction ID yi kaydeder ve o ana göre Transaction snapshot oluşturur ve bu txid den sonraki değişimler almaz.

pg_dump tüm parametreleri ve açıklamalarını pg_dump –-help  komutu ile öğrenebiliriz;

$ pg_dump --help pg_dump dumps a database as a text file or to other formats. Usage:   pg_dump [OPTION]... [DBNAME] General options:   -f, --file=FILENAME          output file or directory name   -F, --format=c|d|t|p         output file format (custom, directory, tar,                                plain text (default))   -j, --jobs=NUM               use this many parallel jobs to dump   -v, --verbose                verbose mode   -V, --version                output version information, then exit   -Z, --compress=0-9           compression level for compressed formats   --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock   --no-sync                    do not wait for changes to be written safely to disk   -?, --help                   show this help, then exit Options controlling the output content:   -a, --data-only              dump only the data, not the schema   -b, --blobs                  include large objects in dump   -B, --no-blobs               exclude large objects in dump   -c, --clean                  clean (drop) database objects before recreating   -C, --create                 include commands to create database in dump   -e, --extension=PATTERN      dump the specified extension(s) only   -E, --encoding=ENCODING      dump the data in encoding ENCODING   -n, --schema=PATTERN         dump the specified schema(s) only   -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)   -O, --no-owner               skip restoration of object ownership in                                plain-text format   -s, --schema-only            dump only the schema, no data   -S, --superuser=NAME         superuser user name to use in plain-text format   -t, --table=PATTERN          dump the specified table(s) only   -T, --exclude-table=PATTERN  do NOT dump the specified table(s)   -x, --no-privileges          do not dump privileges (grant/revoke) Connection options:   -d, --dbname=DBNAME      database to dump   -h, --host=HOSTNAME      database server host or socket directory   -p, --port=PORT          database server port number   -U, --username=NAME      connect as specified database user   -w, --no-password        never prompt for password   -W, --password           force password prompt (should happen automatically)   --role=ROLENAME          do SET ROLE before dump

Buradaki bütün parametreleri tek tek inceleyemeyeceğiz ancak DBA ler tarafından ihtiyaç duyulacak çok kullanılacak komutları örnekler üzerinden görüyor olacağız.

pg_dump komutunda ki bizim için önemli optionları nelerdir 

pg_dump Options

• -a Data only. Do not dump the data definitions (schema)
• -s :Data definitions (schema) only (DDL). Do not dump the data
• -n <schema> – Dump from the specified schema only
• -t <table> – Dump specified table only
• -f <path/file name.backup> – Send dump to specified file
• -Fp – Dump in plain-text SQL script (default)
• -Ft – Dump in tar format
• -Fc – Dump in compressed, custom format
• -v – Verbose option
• -o use oids

pgAdmin4 ile Dump Backup Almak

Kitabın başında dvdrental veritabanını appdb olarak restore etmiştik şimdi bu veritabanının sql dump dosyasını Pgadmin4 ile client makinemiz üzerine alalım;

pgAdmin4 açılırİlgili Veritabanına bağlantı yapılırappdb üzerinde sağ click yapıyoruzbackup seçeneğini seçerek ilerliyoruz

• Açılan ekranda filename nereye kaydedeceğiz dump dosyamızı
• formatı ne olacak
• Şifrelimi vs gibi seçenekleri seçebiliyoruz bazı dump optionlarını buradan da seçebiliyoruz
• Backup dediğimizde

Başarıyla tamamladı.

Eğer veritabanı ve dump alınacak obje boyutu çok büyükse bu işlem dump alması ve bu dumptan geri dönülmesi oldukça zaman alan ve diskte yer tutan bir işlem olacaktır bu yüzden pg_dump ile dump alırken sıkıştırma option nu kullanılabilir oldukça faydalıdır, network üzerinden trafiği azaltma, disk üzerinde daha az yer kaplama gibi avantajları olacaktır. Aşağıda örnek olarak Linux ta popüler olan gzip ile sıkıştırma örneği verilmiştir.

pg_dump dbname | gzip > filename.gz

• Ayrıca split komutuyla Dump çıktısı belli boyutlarda birden cok dosyaya bölünmek istenir ise komut aşağıdaki gibidir.
  • pg_dump dbname | split -b 1m - filename
• Sadece bir tablonun dump dosyasını almak istiyorsak
  • pg_dump -t tablename -d dbname > filename
• Dump işleminin parallel job lar ile yapılmasını istiyorsak
  • pg_dump -j <prosess_sayısı> -F d -f out.dir dbname

pg_dumpall

Alınan DUMP Dosyalarının Restore Edilmesi

pg_dumpall fonksiyonunun pg_dump fonksiyonundan temel farkı, sadece bir veritabanı yerine komple cluster içeriklerinin uygun şekilde yedeklenmesini sağlayabilmesidir. pg_dumpall, verilen bir kümedeki her veritabanının yedeğini ve cluster çapındaki rol ve tablo alanlarının tanımları (DDL), ilişkileri, izinleri vs. gibi verilerin yedeğini de alır. Temel kullanımı şu şekildedir. Cluster düzeyinde pg_dumpall ile bir dump alabilmek için superuser ile kullanılmalıdır.

pg_dumpall > dumpfile

pg_dumpall komutu için

$ pg_dumpall --help pg_dumpall extracts a PostgreSQL database cluster into an SQL script file. Usage:   pg_dumpall [OPTION]... General options:   -f, --file=FILENAME          output file name   -v, --verbose                verbose mode   -V, --version                output version information, then exit   --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock   -?, --help                   show this help, then exit Options controlling the output content:   -a, --data-only              dump only the data, not the schema   -c, --clean                  clean (drop) databases before recreating   -E, --encoding=ENCODING      dump the data in encoding ENCODING   -g, --globals-only           dump only global objects, no databases   -O, --no-owner               skip restoration of object ownership   -r, --roles-only             dump only roles, no databases or tablespaces   -s, --schema-only            dump only the schema, no data (DDL)   -S, --superuser=NAME         superuser user name to use in the dump   -t, --tablespaces-only       dump only tablespaces, no databases or roles   -x, --no-privileges          do not dump privileges (grant/revoke)   --binary-upgrade             for use by upgrade utilities only   --column-inserts             dump data as INSERT commands with column names   --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting   --disable-triggers           disable triggers during data-only restore   --exclude-database=PATTERN   exclude databases whose name matches PATTERN   --extra-float-digits=NUM     override default setting for extra_float_digits   --if-exists                  use IF EXISTS when dropping objects   --inserts                    dump data as INSERT commands, rather than COPY   --load-via-partition-root    load partitions via the root table   --no-comments                do not dump comments   --no-publications            do not dump publications   --no-role-passwords          do not dump passwords for roles   --no-security-labels         do not dump security label assignments   --no-subscriptions           do not dump subscriptions   --no-sync                    do not wait for changes to be written safely to disk   --no-tablespaces             do not dump tablespace assignments   --no-toast-compression       do not dump TOAST compression methods   --no-unlogged-table-data     do not dump unlogged table data   --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands   --quote-all-identifiers      quote all identifiers, even if not key words   --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts   --use-set-session-authorization                                use SET SESSION AUTHORIZATION commands instead of                                ALTER OWNER commands to set ownership Connection options:   -d, --dbname=CONNSTR     connect using connection string   -h, --host=HOSTNAME      database server host or socket directory   -l, --database=DBNAME    alternative default database   -p, --port=PORT          database server port number   -U, --username=NAME      connect as specified database user   -w, --no-password        never prompt for password   -W, --password           force password prompt (should happen automatically)   --role=ROLENAME          do SET ROLE before dump

PgAdmin4 ile Global dump almak için;

SQL Dump Restore Adımları;

pg_dump tarafından oluşturulan dump dosyaları psql tarafından okunup içerisindeki sql ler çalıştırılarak restore yapılabilir. 

psql dbname < dumpfile

yukarıdaki komutta dump file içerisinde database oluşturma kısmı yoktur (DDL) o yüzden dbname i öncesinde oluşturmuş olmanız gerekmektedir, bu kısımları bir sonraki aşamada örneklerde yapıyor olacağız

pg_dumpall ile tüm cluster (Cluster içerisindeki tüm databaseler) yedek alındıysa restore için aşağıdaki gibi bir komut kullanılır;

psql -f <dumpfile> postgres

PgAdmin4 ile Dump Restore yapmak

pg_restore kullanarak Restore yapma

pg_dump ile alınmış bir yedeği psql ile restore edebileceğimiz gibi pg_restore komutu ile de restore edebiliriz. psql ile pg_dump tarafından oluşturulmuş düz metin dosyalarını restore ederken pg_restore ile düz metin olmayan sıkıştırılmış, tar formatında veya özel format ile formatlanmış bir dump dosyasını restore edebiliriz.

pg_restore -d <dbname> <dumpfile>

$ pg_restore --help pg_restore restores a PostgreSQL database from an archive created by pg_dump. Usage:   pg_restore [OPTION]... [FILE] General options:   -d, --dbname=NAME        connect to database name   -f, --file=FILENAME      output file name (- for stdout)   -F, --format=c|d|t       backup file format (should be automatic)   -l, --list               print summarized TOC of the archive   -v, --verbose            verbose mode   -V, --version            output version information, then exit   -?, --help               show this help, then exit Options controlling the restore:   -a, --data-only              restore only the data, no schema   -c, --clean                  clean (drop) database objects before recreating   -C, --create                 create the target database   -e, --exit-on-error          exit on error, default is to continue   -I, --index=NAME             restore named index   -j, --jobs=NUM               use this many parallel jobs to restore   -L, --use-list=FILENAME      use table of contents from this file for                                selecting/ordering output   -n, --schema=NAME            restore only objects in this schema   -N, --exclude-schema=NAME    do not restore objects in this schema   -O, --no-owner               skip restoration of object ownership   -P, --function=NAME(args)    restore named function   -s, --schema-only            restore only the schema, no data (DDL)   -S, --superuser=NAME         superuser user name to use for disabling triggers   -t, --table=NAME             restore named relation (table, view, etc.)   -T, --trigger=NAME           restore named trigger   -x, --no-privileges          skip restoration of access privileges (grant/revoke)   -1, --single-transaction     restore as a single transaction   --disable-triggers           disable triggers during data-only restore   --enable-row-security        enable row security   --if-exists                  use IF EXISTS when dropping objects   --no-comments                do not restore comments   --no-data-for-failed-tables  do not restore data of tables that could not be                                created   --no-publications            do not restore publications   --no-security-labels         do not restore security labels   --no-subscriptions           do not restore subscriptions   --no-tablespaces             do not restore tablespace assignments   --section=SECTION            restore named section (pre-data, data, or post-data)   --strict-names               require table and/or schema include patterns to                                match at least one entity each   --use-set-session-authorization                                use SET SESSION AUTHORIZATION commands instead of                                ALTER OWNER commands to set ownership Connection options:   -h, --host=HOSTNAME      database server host or socket directory   -p, --port=PORT          database server port number   -U, --username=NAME      connect as specified database user   -w, --no-password        never prompt for password   -W, --password           force password prompt (should happen automatically)   --role=ROLENAME          do SET ROLE before restore The options -I, -n, -N, -P, -t, -T, and --section can be combined and specified multiple times to select multiple objects.

pg_restore option ları çok önemli burada bir çok faydalı otion bulunmakta , örneğin düzenli full dump alıyorsunuz ve bunları sıkıştırıp belli bir tarihe kadar arşivliyorsunuz , size geçen haftanın bir tablosu gerekti diyelim , geçen hafta alınan dump dosyasından sadece o tabloyu pg_restore ile alıp kayıtları inceleyebilirsiniz.

COPY KOMUTU

COPY komutu verileri PostgreSQL tabloları ile standart dosya sistemi dosyaları arasında taşımayı sağlayan bir komut sistemidir. COPY TO tablonun içeriğini İşletim sistemi üzerindeki bir dosyaya kopyalarken, COPY FROM komutu İşletim Sistemi üzerindeki bir dosyadan verileri okuyarak Veritabanı içerisindeki bir tabloya kopyalama işlemini yapar. COPY TO ayrıca herhangi bir SELECT sorgusunun sonuçlarını da OS üzerindeki bir dosyaya kopyalayabilir, yani sütun listesi belirtilirse.

• COPY komutu PostgreSQL sunucusuna doğrudan dosyadan okuma veya dosyaya yazma talimatı verir bu nedenler veritabanı erişimi ve OS ye yazma yetkileri olmalıdır
• COPY TO tarafından okunan tablo üzerinde SELECT ve COPY FROM ile değerlerin eklendiği tabloda INSERT yetkilerinin olması gerekir.
• COPY TO yalnızca tablolarla kullanılabilir, viewlerde kullanılamaz. Eğer bir viewin içeriğini kopyalamak istersek COPY komutunu sql sorgusu şeklinde verebiliriz
  • ( COPY (SELECT * FROM view_table) TO ‘view.copy’;)
• COPY komutu server üzerindeki dosyaları okur ve yazar.

postgres=# \h copy Command:     COPY Description: copy data between a file and a table Syntax: COPY table_name [ ( column_name [, ...] ) ]     FROM { 'filename' | PROGRAM 'command' | STDIN }     [ [ WITH ] ( option [, ...] ) ]     [ WHERE condition ] COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }     TO { 'filename' | PROGRAM 'command' | STDOUT }     [ [ WITH ] ( option [, ...] ) ] where option can be one of:     FORMAT format_name     FREEZE [ boolean ]     DELIMITER 'delimiter_character'     NULL 'null_string'     HEADER [ boolean ]     QUOTE 'quote_character'     ESCAPE 'escape_character'     FORCE_QUOTE { ( column_name [, ...] ) | * }     FORCE_NOT_NULL ( column_name [, ...] )     FORCE_NULL ( column_name [, ...] )     ENCODING 'encoding_name'

Örmek : postgres database public scheması altındaki employee tablosunu /postgres/ dizini altına isciler.copy adında bir dosyaya her bir column aralarında | işareti olacak şekilde (Delimiter) copy ile çıkaralım.

postgres=# \d employee                   Table "public.employee"      Column     |  Type   | Collation | Nullable | Default ----------------+---------+-----------+----------+---------  empno          | integer |           |          |  ename          | text    |           |          |  address        | text    |           |          |  salary         | integer |           |          |  account_number | text    |           |          | Policies (row security enabled): (none)

postgres=# COPY employee TO '/postgres/isciler.copy' (DELIMITER '|'); COPY 4 $ cd /postgres/ $ ls 14  isciler.copy $ more isciler.copy 1|john|2 down str|20000|HDFC-22001 2|clark|132 south avn|80000|HDFC-23029 3|soojle|Down st 17th|60000|ICICI-19022 4|Faruk|Ankara|80000|HDFC-067106

Aynı tabloyu aynı dizine farklı bir isimde csv olarak çıkaralım, ve kolon isimleride dosyada header olarak olsun;

postgres=# COPY employee TO '/postgres/employee.csv' CSV HEADER; COPY 4 $ ls 14  employee.csv  isciler.copy $ more employee.csv empno,ename,address,salary,account_number 1,john,2 down str,20000,HDFC-22001 2,clark,132 south avn,80000,HDFC-23029 3,soojle,Down st 17th,60000,ICICI-19022 4,Faruk,Ankara,80000,HDFC-067106

Peki bir tablonun belli kayıtlarını veya bazı kolonlarını dosyaya çıkarmak istersek, o zaman sorgu ile COPY kullanımını uygulamalıyız,

postgres=# COPY (Select empno,ename,address from employee where lower(ename) like 'fa%') TO '/postgres/sql_copy.csv' csv; $ vi sql_copy.csv 4,Faruk,Ankara

COPY ile aldığımız çıktıyı sıkıştırılmış olarak almak istersek;

postgres=# COPY employee TO PROGRAM 'gzip > /postgres/employee.csv.gz' CSV HEADER; COPY 4

Örnek; employee tablosunu kullanarak yeni bir empcsv adında bir tablo yapalım ve dışarıya aktardığımız csv dosyasını bu tabloya COPY komutunu kullanarak insert edelim;

postgres=# create table empcsv (like employee); CREATE TABLE postgres=# select * from empcsv ;  empno | ename | address | salary | account_number -------+-------+---------+--------+---------------- (0 rows) postgres=# COPY empcsv from '/postgres/employee.csv' CSV HEADER; COPY 4 postgres=# select * from empcsv ;  empno | ename  |    address    | salary | account_number -------+--------+---------------+--------+----------------      1 | john   | 2 down str    |  20000 | HDFC-22001      2 | clark  | 132 south avn |  80000 | HDFC-23029      3 | soojle | Down st 17th  |  60000 | ICICI-19022      4 | Faruk  | Ankara        |  80000 | HDFC-067106 (4 rows)

Bazen csv türü bir dosyayı PostgreSQL içerisindeki bir tabloya aktarırken bazen karakter problemi olabilir bunun için COPY komutunda ENCODING opsiyonu vardır,

postgres=# COPY empcsv from '/postgres/employee.csv' CSV HEADER;COPY 4 encoding 'UTF8';

Copy Komutunu client tarafından bir dosyayı almak istersek aşağıdaki şekilde komutu çalıştırabiliriz \ copy … from dediğimizde bağlı bulunduğumuz client tarafındaki dosyalar içerisinden yani network üzerinden bu dosyayı veritabanına aktaracak aynı şekilde \copy …. to dediğimizde ise veritabanındaki tabloları bizim client tarafındaki dizinlere yazacaktır.

Örnek : dump backup ve restore ile ilgili birkaç uygulama yaparak konuyu daha iyi anlayalım; 

1. daha önce eklemiş olduğumuz appdb (dvdrentall) veritabanını /postgres/backup dizini altında tar formatında sıkıştırarak bir dump backupını alaım.
2. Mevcut appdb yi drop edelim ve ardından adında boş bir veritabanı oluşturalım ve bu alınan dump dosyasını buraya önce psql ile ardından pg_restore ile restore edelim

1. adım;

$ pg_dump -p 5434 -F t appdb -v > /postgres/backup/dvdrental.tar pg_dump: last built-in OID is 16383 pg_dump: reading extensions pg_dump: identifying extension members pg_dump: reading schemas pg_dump: reading user-defined tables pg_dump: reading user-defined functions pg_dump: reading user-defined types ……. ……

2. Adım;

postgres=# drop database appdb; DROP DATABASE postgres=# create database appdb; CREATE DATABASE $ psql -p 5434 appdb < /postgres/backup/dvdrental.tar postgres=# drop database appdb; DROP DATABASE postgres=# create database dvdrental; CREATE DATABASE $ pg_restore -p 5434  -d dvdrental /postgres/backup/dvdrental.tar -v

psql ile farklı bir isimdeki veritabanına bu veritabanını restore edemedik ancak pg_restore ile dvdrental veritabanına restore başarıyla gerçekleştirildi.