## 背景
對于商業數據庫來說,備份的功能一般都非常的全面。?
比如Oracle,它的備份工具rman是非常強大的,很多年前就已經支持全量、增量、歸檔的備份模式,支持壓縮等。?
還支持元數據存儲到數據庫中,管理也非常的方便,例如保留多少歸檔,備份集的管理也很方便,例如要恢復到什么時間點,將此前的備份清除等等。?
對于開源數據庫來說,支持向商業版本這么豐富功能的比較少,PostgreSQL算是非常完善的一個。?
PostgreSQL作為最高級的開源數據庫,備份方面已經向商業數據庫看齊。?
目前PostgreSQL已經支持類似Oracle的rman備份工具的功能,支持全量、增量、歸檔三種備份模式,支持壓縮,支持備份集的管理等。?
有了塊級增量備份,對于那種非常龐大的數據庫,備份起來就不像只支持全量和歸檔的模式那么吃力了。
## PostgreSQL增量備份是怎么做到的呢?
一個數據頁的框架如下
~~~
* +----------------+---------------------------------+
* | PageHeaderData | linp1 linp2 linp3 ... |
* +-----------+----+---------------------------------+
* | ... linpN | |
* +-----------+--------------------------------------+
* | ^ pd_lower |
* | |
* | v pd_upper |
* +-------------+------------------------------------+
* | | tupleN ... |
* +-------------+------------------+-----------------+
* | ... tuple3 tuple2 tuple1 | "special space" |
* +--------------------------------+-----------------+
~~~
數據頁頭部的數據結構
~~~
typedef struct PageHeaderData
{
/* XXX LSN is member of *any* block, not only page-organized ones */
PageXLogRecPtr pd_lsn; /* LSN: next byte after last byte of xlog
* record for last change to this page */
uint16 pd_checksum; /* checksum */
uint16 pd_flags; /* flag bits, see below */
LocationIndex pd_lower; /* offset to start of free space */
LocationIndex pd_upper; /* offset to end of free space */
LocationIndex pd_special; /* offset to start of special space */
uint16 pd_pagesize_version;
TransactionId pd_prune_xid; /* oldest prunable XID, or zero if none */
ItemIdData pd_linp[FLEXIBLE_ARRAY_MEMBER]; /* line pointer array */
} PageHeaderData;
~~~
因為如果對象是持久化的,那么它的所有變更都會記錄REDO,數據頁頭部的pd_lsn表示該數據頁最后一次變化時,變化產生的REDO在xlog file中的結束位置.?
即如果xlog flush的xlog地址位 大于或等于 此頁pd_lsn,那么這個頁的更改就可以認為是可靠的。
~~~
* pd_lsn - identifies xlog record for last change to this page.
* pd_checksum - page checksum, if set.
* pd_flags - flag bits.
* pd_lower - offset to start of free space.
* pd_upper - offset to end of free space.
* pd_special - offset to start of special space.
* pd_pagesize_version - size in bytes and page layout version number.
* pd_prune_xid - oldest XID among potentially prunable tuples on page.
~~~
好了,既然每次塊的變化都包含了LSN的修改,那么也即是說,我們可以通過第一次備份開始時的全局LSN,以及當前需要備份的數據的page LSN來判斷此頁是否發生過修改。
如果修改了就備份,沒修改,就不需要備份, 從而實現數據庫的塊級增量備份。
## pg_rman 介紹
pg_rman是一個[開源的PostgreSQL備份管理軟件](https://github.com/ossc-db/pg_rman),類似Oracle的RMAN。?
[documentation見](http://ossc-db.github.io/pg_rman/index.html)
pg_rman使用的是pg_start_backup(), copy, pg_stop_backup()的備份模式。?
pg_rman跑的不是流復制協議,而是文件拷貝,所以pg_rman必須和數據庫節點跑在一起。?
如果在standby節點跑pg_rman,pg_rman則需要通過網絡連接到主節點執行pg_start_backup和pg_stop_backup。?
pg_rman的用法非常簡單,支持以下幾種運行模式。
| init | Initialize a backup catalog. |
| backup | Take an online backup. |
| restore | Do restore. |
| show | Show backup history. The detail option shows with additional information of each backups. |
| validate | Validate backup files. Backups without validation cannot be used for restore and incremental backup. |
| delete | Delete backup files. |
| purge | Remove deleted backups from backup catalog. |
## 使用pg_rman的前提
開啟歸檔?
配置csvlog?
建議的配置
~~~
postgres=# show log_destination ;
log_destination
-----------------
csvlog
(1 row)
postgres=# SHOW log_directory ;
log_directory
---------------
pg_log
(1 row)
postgres=# SHOW archive_command ;
archive_command
--------------------------------------------
cp %p /data04/digoal/arc_log/%f
(1 row)
~~~
## 初始化pg_rman backup catalog
首先需要初始化一個backup catalog,實際上就是需要一個目錄,這個目錄將用于存放備份的文件。?
同時這個目錄也會存放一些元數據,例如備份的配置文件,數據庫的systemid,時間線文件歷史等等。?
初始化命令需要兩個參數,分別為備份目標目錄,以及數據庫的$PGDATA
~~~
$ mkdir /data05/digoal/pgbbk
$ /home/digoal/pgsql9.5/bin/pg_rman init -B /data05/digoal/pgbbk -D /data04/digoal/pg_root
INFO: ARCLOG_PATH is set to '/data04/digoal/arc_log'
INFO: SRVLOG_PATH is set to '/data04/digoal/pg_root/pg_log'
~~~
生成備份元數據如下
~~~
[digoal@iZ28tqoemgtZ ~]$ cd /data05/digoal/pgbbk/
[digoal@iZ28tqoemgtZ pgbbk]$ ll
total 16
drwx------ 4 digoal digoal 4096 Aug 26 19:29 backup
-rw-rw-r-- 1 digoal digoal 82 Aug 26 19:29 pg_rman.ini
-rw-rw-r-- 1 digoal digoal 40 Aug 26 19:29 system_identifier
drwx------ 2 digoal digoal 4096 Aug 26 19:29 timeline_history
~~~
生成的配置文件
~~~
$ cat pg_rman.ini
ARCLOG_PATH='/data04/digoal/arc_log'
SRVLOG_PATH='/data04/digoal/pg_root/pg_log'
~~~
你可以把將來要使用的配置寫在這個配置文件中,或者寫在pg_rman的命令行中。?
我后面的測試會直接使用命令行參數。?
生成的數據庫system id,用于區分備份的數據庫是不是一個數據庫,防止被沖。
~~~
$ cat system_identifier
SYSTEM_IDENTIFIER='6318621837015461309'
~~~
與控制文件中存儲的system id一致。
注意?
pg_rman只從postgresql.conf取log_directory和archive_command參數的值。?
如果你的PostgreSQL的配置文件是include的或者配置在postgresql.auto.conf中,這兩個值將不準確。?
所以建議你僅僅把參數配置在postgresql.conf中,而不要使用其他配置文件。
## pg_rman 命令行用法
~~~
pg_rman manage backup/recovery of PostgreSQL database.
Usage:
pg_rman OPTION init
pg_rman OPTION backup
pg_rman OPTION restore
pg_rman OPTION show [DATE]
pg_rman OPTION show detail [DATE]
pg_rman OPTION validate [DATE]
pg_rman OPTION delete DATE
pg_rman OPTION purge
Common Options:
-D, --pgdata=PATH location of the database storage area
-A, --arclog-path=PATH location of archive WAL storage area
-S, --srvlog-path=PATH location of server log storage area
-B, --backup-path=PATH location of the backup storage area
-c, --check show what would have been done
-v, --verbose show what detail messages
-P, --progress show progress of processed files
Backup options:
-b, --backup-mode=MODE full, incremental, or archive
-s, --with-serverlog also backup server log files
-Z, --compress-data compress data backup with zlib
-C, --smooth-checkpoint do smooth checkpoint before backup
-F, --full-backup-on-error switch to full backup mode
if pg_rman cannot find validate full backup
on current timeline
NOTE: this option is only used in --backup-mode=incremental or archive.
--keep-data-generations=NUM keep NUM generations of full data backup
--keep-data-days=NUM keep enough data backup to recover to N days ago
--keep-arclog-files=NUM keep NUM of archived WAL
--keep-arclog-days=DAY keep archived WAL modified in DAY days
--keep-srvlog-files=NUM keep NUM of serverlogs
--keep-srvlog-days=DAY keep serverlog modified in DAY days
--standby-host=HOSTNAME standby host when taking backup from standby
--standby-port=PORT standby port when taking backup from standby
Restore options:
--recovery-target-time time stamp up to which recovery will proceed
--recovery-target-xid transaction ID up to which recovery will proceed
--recovery-target-inclusive whether we stop just after the recovery target
--recovery-target-timeline recovering into a particular timeline
--hard-copy copying archivelog not symbolic link
Catalog options:
-a, --show-all show deleted backup too
Delete options:
-f, --force forcibly delete backup older than given DATE
Connection options:
-d, --dbname=DBNAME database to connect
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-w, --no-password never prompt for password
-W, --password force password prompt
Generic options:
-q, --quiet don't show any INFO or DEBUG messages
--debug show DEBUG messages
--help show this help, then exit
--version output version information, then exit
Read the website for details. <http://github.com/ossc-db/pg_rman>
Report bugs to <http://github.com/ossc-db/pg_rman/issues>.
~~~
## 全量備份
輸入必要的參數或option
~~~
$ export PGPASSWORD=postgres
$ /home/digoal/pgsql9.5/bin/pg_rman backup \
-B /data05/digoal/pgbbk \
-D /data04/digoal/pg_root \
-b full \
-s \
-Z \
-C \
--keep-data-days=10 \
--keep-arclog-files=15 \
--keep-arclog-days=10 \
--keep-srvlog-files=10 \
--keep-srvlog-days=15 \
-h 127.0.0.1 -p 1921 -U postgres -d postgres
~~~
結果
~~~
INFO: copying database files
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
HINT: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 15, keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2016-08-16 00:00:00"
INFO: start deleting old server files from SRVLOG_PATH (keep files = 10, keep days = 15)
INFO: the threshold timestamp calculated by keep days is "2016-08-11 00:00:00"
INFO: start deleting old backup (keep after = 2016-08-16 00:00:00)
INFO: does not include the backup just taken
WARNING: backup "2016-08-26 19:39:32" is not taken int account
DETAIL: This is not valid backup.
~~~
## 校驗備份集
備份時pg_rman會記錄每個備份文件的crc,以便validate進行校驗。?
例如某個備份集
~~~
$ less /data05/digoal/pgbbk/20160826/195809/file_database.txt
.s.PGSQL.1921 ? 0 0 0777 2016-08-26 19:27:05
.s.PGSQL.1921.lock f 55 590164837 0600 2016-08-26 19:27:05
PG_VERSION f 12 3872055064 0600 2016-07-28 10:03:42
backup_label f 167 2985542389 0600 2016-08-26 19:58:42
backup_label.old f 155 4273989468 0600 2016-08-23 19:43:32
base d 0 0 0700 2016-08-23 10:28:32
base/1 d 0 0 0700 2016-08-24 16:17:02
base/1/112 f 57 1147028285 0600 2016-07-28 10:03:42
base/1/113 f 57 1147028285 0600 2016-07-28 10:03:42
base/1/1247 F 8178 1875285513 0600 2016-07-29 13:51:29
base/1/1247_fsm f 139 3668812536 0600 2016-07-28 10:03:43
~~~
解釋:
路徑,文件類型,大小,CRC校驗值,權限,時間,第四列即crc校驗值?
每次備份完,必須要做一次校驗,否則備份集不可用用來恢復,增量備份時也不會用它來做增量比較。
~~~
$ /home/digoal/pgsql9.5/bin/pg_rman validate -B /data05/digoal/pgbbk
INFO: validate: "2016-08-26 19:39:50" backup, archive log files and server log files by CRC
INFO: backup "2016-08-26 19:39:50" is valid
~~~
每個備份集都包含了一個備份狀態文件,如下
~~~
cat /data05/digoal/pgbbk/20160826/201955/backup.ini
# configuration
BACKUP_MODE=INCREMENTAL
FULL_BACKUP_ON_ERROR=false
WITH_SERVERLOG=true
COMPRESS_DATA=true
# result
TIMELINEID=1
START_LSN=46/df000108
STOP_LSN=46/df000210
START_TIME='2016-08-26 20:19:55'
END_TIME='2016-08-26 20:20:48'
RECOVERY_XID=3896508593
RECOVERY_TIME='2016-08-26 20:20:47'
TOTAL_DATA_BYTES=6196524307
READ_DATA_BYTES=3199287520
READ_ARCLOG_BYTES=33554754
READ_SRVLOG_BYTES=0
WRITE_BYTES=125955
BLOCK_SIZE=8192
XLOG_BLOCK_SIZE=8192
STATUS=OK
~~~
這個文件中包含了很重要的信息,比如LSN,后面LSN將用于比對增量備份時對比數據塊的LSN是否發生了變化,是否需要備份。
## 增量備份
~~~
$ export PGPASSWORD=postgres
$ /home/digoal/pgsql9.5/bin/pg_rman backup \
-B /data05/digoal/pgbbk \
-D /data04/digoal/pg_root \
-b incremental \
-s \
-Z \
-C \
--keep-data-days=10 \
--keep-arclog-files=15 \
--keep-arclog-days=10 \
--keep-srvlog-files=10 \
--keep-srvlog-days=15 \
-h 127.0.0.1 -p 1921 -U postgres -d postgres
~~~
增量備份輸出
~~~
INFO: copying database files
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
HINT: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 15, keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2016-08-16 00:00:00"
INFO: start deleting old server files from SRVLOG_PATH (keep files = 10, keep days = 15)
INFO: the threshold timestamp calculated by keep days is "2016-08-11 00:00:00"
INFO: start deleting old backup (keep after = 2016-08-16 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2016-08-26 19:39:50" should be kept
DETAIL: This is taken after "2016-08-16 00:00:00".
WARNING: backup "2016-08-26 19:39:32" is not taken int account
DETAIL: This is not valid backup.
~~~
## 校驗備份集
~~~
$ /home/digoal/pgsql9.5/bin/pg_rman validate -B /data05/digoal/pgbbk
INFO: validate: "2016-08-26 19:43:20" backup, archive log files and server log files by CRC
INFO: backup "2016-08-26 19:43:20" is valid
~~~
## 列出備份集
~~~
$ /home/digoal/pgsql9.5/bin/pg_rman show -B /data05/digoal/pgbbk
==========================================================
StartTime Mode Duration Size TLI Status
==========================================================
2016-08-26 19:43:20 INCR 0m 54kB 1 OK
2016-08-26 19:39:50 FULL 1m 245MB 1 OK
~~~
可以看到增量非常小,因為很少變化的塊。?
接下來更新一張大表的某一條記錄,再看看。
~~~
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------+-------+----------+---------+-------------
public | hll_test | table | postgres | 208 kB |
public | t | table | postgres | 3050 MB |
public | tbl1 | table | postgres | 226 MB |
public | tbl2 | table | postgres | 63 MB |
public | test | table | postgres | 120 MB |
(5 rows)
postgres=# with t1 as (select id from t where id between 1 and 1000 limit 10) update t set info='new' where id in (select * from t1);
UPDATE 10
~~~
更新后做一個增量備份
~~~
$ /home/digoal/pgsql9.5/bin/pg_rman backup -B /data05/digoal/pgbbk -D /data04/digoal/pg_root -b incremental -s -Z -C --keep-data-days=10 --keep-arclog-files=15 --keep-arclog-days=10 --keep-srvlog-files=10 --keep-srvlog-days=15 -h 127.0.0.1 -p 1921 -U postgres -d postgres
INFO: copying database files
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
HINT: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 15, keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2016-08-16 00:00:00"
INFO: start deleting old server files from SRVLOG_PATH (keep files = 10, keep days = 15)
INFO: the threshold timestamp calculated by keep days is "2016-08-11 00:00:00"
INFO: start deleting old backup (keep after = 2016-08-16 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2016-08-26 19:58:09" should be kept
DETAIL: This is taken after "2016-08-16 00:00:00".
WARNING: backup "2016-08-26 19:56:54" is not taken int account
DETAIL: This is not valid backup.
INFO: backup "2016-08-26 19:43:20" should be kept
DETAIL: This is taken after "2016-08-16 00:00:00".
INFO: backup "2016-08-26 19:39:50" should be kept
DETAIL: This is taken after "2016-08-16 00:00:00".
WARNING: backup "2016-08-26 19:39:32" is not taken int account
DETAIL: This is not valid backup.
~~~
校驗備份集
~~~
[digoal@iZ28tqoemgtZ pg_rman]$ /home/digoal/pgsql9.5/bin/pg_rman validate -B /data05/digoal/pgbbk
INFO: validate: "2016-08-26 20:19:55" backup, archive log files and server log files by CRC
INFO: backup "2016-08-26 20:19:55" is valid
~~~
輸出當前備份
~~~
[digoal@iZ28tqoemgtZ pg_rman]$ /home/digoal/pgsql9.5/bin/pg_rman show -B /data05/digoal/pgbbk
==========================================================
StartTime Mode Duration Size TLI Status
==========================================================
2016-08-26 20:19:55 INCR 0m 125kB 1 OK
2016-08-26 19:58:09 FULL 11m 3094MB 1 OK
2016-08-26 19:56:54 FULL 1m 0B 0 ERROR
2016-08-26 19:43:20 INCR 0m 54kB 1 OK
2016-08-26 19:39:50 FULL 1m 245MB 1 OK
2016-08-26 19:39:32 FULL 0m 0B 0 ERROR
~~~
增量備份的文件非常小,因為變化的數據塊很少。
## 按指定時間從catalog刪除備份集
例如我只需要我的備份集能恢復到2016-08-26 19:59:00,在這個時間點以前,不需要用來恢復到這個時間點的備份全刪掉。
~~~
$ /home/digoal/pgsql9.5/bin/pg_rman delete "2016-08-26 19:59:00" -B /data05/digoal/pgbbk
WARNING: cannot delete backup with start time "2016-08-26 19:58:09"
DETAIL: This is the latest full backup necessary for successful recovery.
INFO: delete the backup with start time: "2016-08-26 19:56:54"
INFO: delete the backup with start time: "2016-08-26 19:43:20"
INFO: delete the backup with start time: "2016-08-26 19:39:50"
INFO: delete the backup with start time: "2016-08-26 19:39:32"
~~~
保留的備份集合可以將數據庫恢復到2016-08-26 19:59:00
~~~
$ /home/digoal/pgsql9.5/bin/pg_rman show -B /data05/digoal/pgbbk
==========================================================
StartTime Mode Duration Size TLI Status
==========================================================
2016-08-26 20:19:55 INCR 0m 125kB 1 OK
2016-08-26 19:58:09 FULL 11m 3094MB 1 OK
~~~
## 物理刪除已從catalog刪除的備份集
~~~
$ /home/digoal/pgsql9.5/bin/pg_rman purge -B /data05/digoal/pgbbk
INFO: DELETED backup "2016-08-26 19:56:54" is purged
INFO: DELETED backup "2016-08-26 19:43:20" is purged
INFO: DELETED backup "2016-08-26 19:39:50" is purged
INFO: DELETED backup "2016-08-26 19:39:32" is purged
~~~
## 恢復
pg_rman數據恢復時的兩個必要要素?
1\. 新的$PGDATA?
2\. 備份目錄?
命令的選項也很簡單,甚至可以不指定任何option
~~~
Restore options:
The parameters which are started with –recovery are same as parameters in recovery.conf. See also “Recovery Configuration” for details.
~~~
接下來的幾個配置,與recovery.conf的意思對齊。
如果不指定時間線,則使用$PGDATA/global/pg_control,如果沒有$PGDATA/global/pg_control,則使用最新的全量備份集的時間線。
~~~
--recovery-target-timeline TIMELINE
Specifies recovering into a particular timeline. If not specified, the current timeline from ($PGDATA/global/pg_control) is used.
~~~
如果不指定,則恢復到最新時間
~~~
--recovery-target-time TIMESTAMP
This parameter specifies the time stamp up to which recovery will proceed. If not specified, continue recovery to the latest time.
~~~
如果不指定,則恢復到最新xid
~~~
--recovery-target-xid XID
This parameter specifies the transaction ID up to which recovery will proceed. If not specified, continue recovery to the latest xid.
~~~
如果不指定,則默認使用true,即恢復到包含恢復目標XID的commit record為止,或者第一筆commit record ts>指定ts的 commit redo record為止;?
如果是false則不apply恢復目標XID的commit record,或者不apply第一筆commit record ts>=指定ts的 commit redo record。
~~~
--recovery-target-inclusive
Specifies whether we stop just after the specified recovery target (true), or just before the recovery target (false). Default is true.
~~~
是否使用硬鏈接復制archive log,而不需要拷貝文件
~~~
The following parameter determines the behavior of restore.
--hard-copy
The archive WAL are copied to archive WAL storage area. If not specified, pg_rman makes symbolic link to archive WAL where are in the backup catalog directory.
~~~
## 例子
恢復時,需要注意,你可以選擇原地恢復(覆蓋式),或者使用新的$PGDATA作為恢復目標。
但是請注意,不管是哪種恢復方式,如果在本機恢復的話,pg_rman可能會覆蓋原有的數據文件,arch, pg_xlog目錄中的文件,所以,如果你要保留原數據,建議先將原數據目錄重命名。?
1\. 原地恢復?
2\. 使用新的$PGDATA恢復
在本機恢復的例子?
1\. 停庫
~~~
pg_ctl stop -m fast -D /data04/digoal/pg_root_1922
~~~
2\. 重命名原數據相關目錄
~~~
$PGDATA
mv /data04/digoal/pg_root_1922 /data04/digoal/old_pg_root_1922
PG_XLOG
mv /data05/digoal/pg_xlog_1922 /data05/digoal/old_pg_xlog_1922
表空間
mv /data02/digoal/tbs1_1922 /data02/digoal/old_tbs1_1922
歸檔目錄,除了要重命名,還需要新建一個原目錄
mv /data04/digoal/arc_log1922 /data04/digoal/old_arc_log1922
mkdir /data04/digoal/arc_log1922
...
~~~
3\. pg_rman restore
~~~
pg_rman restore -B /data05/digoal/pgstdbak -D /data04/digoal/pg_restore_root
WARNING: could not open pg_controldata file "/data04/digoal/pg_restore_root/global/pg_control": No such file or directory
WARNING: could not open pg_controldata file "/data04/digoal/pg_restore_root/global/pg_control": No such file or directory
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 1
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2016-08-29 15:05:32"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2016-08-29 15:05:32" backup, archive log files and server log files by SIZE
INFO: backup "2016-08-29 15:05:32" is valid
INFO: restoring database files from the full mode backup "2016-08-29 15:05:32"
INFO: searching incremental backup to be restored
INFO: validate: "2016-08-29 15:13:10" backup, archive log files and server log files by SIZE
INFO: backup "2016-08-29 15:13:10" is valid
INFO: restoring database files from the incremental mode backup "2016-08-29 15:13:10"
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2016-08-29 15:13:10" is valid
INFO: restoring WAL files from backup "2016-08-29 15:13:10"
INFO: restoring online WAL files and server log files
INFO: generating recovery.conf
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.
~~~
4\. 修改postgresql.conf, recovery.conf?
根據實際情況調整,本例不修改
~~~
cd /data04/digoal/pg_restore_root/
digoal@iZ28tqoemgtZ-> ll
total 124K
-rw------- 1 digoal digoal 193 Aug 29 17:05 backup_label.old
drwx------ 7 digoal digoal 4.0K Aug 29 17:05 base
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 global
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_clog
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_commit_ts
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_dynshmem
-rw------- 1 digoal digoal 4.4K Aug 29 17:05 pg_hba.conf
-rw------- 1 digoal digoal 1.6K Aug 29 17:05 pg_ident.conf
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_log
drwx------ 4 digoal digoal 4.0K Aug 29 17:05 pg_logical
drwx------ 4 digoal digoal 4.0K Aug 29 17:05 pg_multixact
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_notify
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_replslot
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_serial
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_snapshots
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_stat
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_stat_tmp
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_subtrans
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_tblspc
drwx------ 2 digoal digoal 4.0K Aug 29 17:05 pg_twophase
-rw------- 1 digoal digoal 4 Aug 29 17:05 PG_VERSION
lrwxrwxrwx 1 digoal digoal 27 Aug 29 17:05 pg_xlog -> /data05/digoal/pg_xlog_1922
-rw------- 1 digoal digoal 88 Aug 29 17:05 postgresql.auto.conf
-rw------- 1 digoal digoal 22K Aug 29 17:05 postgresql.conf
-rw------- 1 digoal digoal 44 Aug 29 17:05 postmaster.opts
-rw-r--r-- 1 digoal digoal 130 Aug 29 17:06 recovery.conf
digoal@iZ28tqoemgtZ-> cd pg_tblspc/
digoal@iZ28tqoemgtZ-> ll
total 0
lrwxrwxrwx 1 digoal digoal 24 Aug 29 17:05 16719 -> /data02/digoal/tbs1_1922
cd ..
vi postgresql.conf
archive_command = 'cp %p /data04/digoal/arc_log1922/%f'
vi recovery.conf
# recovery.conf generated by pg_rman 1.3.2
restore_command = 'cp /data04/digoal/arc_log1922/%f %p'
recovery_target_timeline = '1'
~~~
5\. 如果備份集的時間線發生了變化,需要先手工拷貝到歸檔目錄,再執行restore
~~~
digoal@iZ28tqoemgtZ-> pg_rman restore -B /data05/digoal/pgstdbak -D /data04/digoal/pg_restore_root
WARNING: could not open pg_controldata file "/data04/digoal/pg_restore_root/global/pg_control": No such file or directory
WARNING: could not open pg_controldata file "/data04/digoal/pg_restore_root/global/pg_control": No such file or directory
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 2
INFO: calculating timeline branches to be used to recovery target point
ERROR: could not open destination file "/data04/digoal/arc_log1922/00000002.history": No such file or directory
digoal@iZ28tqoemgtZ-> mkdir /data04/digoal/arc_log1922
digoal@iZ28tqoemgtZ-> cp /data05/digoal/pgstdbak/timeline_history/00000002.history /data04/digoal/arc_log1922/
digoal@iZ28tqoemgtZ-> pg_rman restore -B /data05/digoal/pgstdbak -D /data04/digoal/pg_restore_root
WARNING: could not open pg_controldata file "/data04/digoal/pg_restore_root/global/pg_control": No such file or directory
WARNING: could not open pg_controldata file "/data04/digoal/pg_restore_root/global/pg_control": No such file or directory
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 2
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2016-08-29 17:14:20"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2016-08-29 17:14:20" backup and archive log files by SIZE
INFO: backup "2016-08-29 17:14:20" is valid
INFO: restoring database files from the full mode backup "2016-08-29 17:14:20"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2016-08-29 17:14:20" is valid
INFO: restoring WAL files from backup "2016-08-29 17:14:20"
INFO: restoring online WAL files and server log files
INFO: generating recovery.conf
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.
digoal@iZ28tqoemgtZ-> cd /data04/digoal/arc_log1922
digoal@iZ28tqoemgtZ-> ll
total 16K
lrwxrwxrwx 1 digoal digoal 71 Aug 29 17:18 00000002000000470000000C -> /data05/digoal/pgstdbak/20160829/171420/arclog/00000002000000470000000C
lrwxrwxrwx 1 digoal digoal 71 Aug 29 17:18 00000002000000470000000D -> /data05/digoal/pgstdbak/20160829/171420/arclog/00000002000000470000000D
lrwxrwxrwx 1 digoal digoal 87 Aug 29 17:18 00000002000000470000000D.00000028.backup -> /data05/digoal/pgstdbak/20160829/171420/arclog/00000002000000470000000D.00000028.backup
-rw------- 1 digoal digoal 42 Aug 29 17:18 00000002.history
~~~
6\. 啟動恢復目標數據庫
~~~
digoal@iZ28tqoemgtZ-> pg_ctl start -D /data04/digoal/pg_restore_root
server starting
digoal@iZ28tqoemgtZ-> LOG: 00000: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".
LOCATION: SysLogger_Start, syslogger.c:622
digoal@iZ28tqoemgtZ-> psql -h 127.0.0.1 -p 1922
psql (9.5.3)
Type "help" for help.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
--------------------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
contrib_regression | postgres | UTF8 | C | C | | 7137 kB | pg_default |
db1 | postgres | UTF8 | C | C | | 111 MB | tbs1 |
postgres | postgres | UTF8 | C | C | | 797 MB | pg_default | default administrative connection database
template0 | postgres | UTF8 | C | C | =c/postgres +| 7137 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C | C | =c/postgres +| 7137 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(5 rows)
~~~
## 注意事項
1. 小心覆蓋原有的部分
2. 建議先將原有的目錄重命名,或者在其他機器恢復,
3. 軟鏈接的處理,會恢復到目標,并且重新建立軟鏈接,所以目錄結構必須與備份時保持一致。
4. 用戶可以指定$PGDATA,恢復到新的目標目錄,但是arch_log, 表空間, pg_xlog目錄無法指定新的位置,所以原地還原時,必須注意這些目錄可能被覆蓋,先重命名是比較好的手段。
## pg_rman 可以優化的地方
1. 檢查哪些要恢復的塊與目標塊的CRC是否一致,如果一致,不需要拷貝,減少WRITE。
## pg_rman 軟件限制
### pg_rman的使用限制
pg_rman has the following restrictions.
1. Requires to read database cluster directory and write backup catalog directory.?
For example, you need to mount the disk where backup catalog is placed with NFS from database server.?
實際上不是必須的,如果沒有指定原來的$PGDATA,則使用備份集的元數據。
2. Block sizes of pg_rman and server should be matched. BLCKSZ and XLOG_BLCKSZ also should be matched.?
編譯pg_rman時,最好使用啟動數據集的集群軟件的pg_config。 確保塊大小一致。?
因為需要做塊的校驗。 讀取LSN等,都與塊大小有關。
3. If there are some unreadable files/directories in database cluster directory, WAL directory or archived WAL directory, the backup or restore would be failed.
4. When taking an incremental backup, pg_rman check the timeline ID of the target database whether it is the same with the one of the full backup in backup list.?
But, pg_rman does not check whether the data itself is same with the full backup in backup list.?
So, you can take an incremental backup over the full backup against the database which has the same timeline ID but has different data.
### 從standby備份時的軟件限制
Getting backup from standby-site, pg_rman has the follow restrictions too.
1. The environment of replication should be built right, or the backup will not finish.
2. You can’t get backups on master and standby at the same time.?
因為pg_rman使用的是exclusive bakcup,(pg_start_backup),所以同一時間,只能跑一次pg_start_backup。?
pg_basebackup則使用的是shared backup,可以跑多個。
3. You can’t get backups on multi standbys at the same time too.?
道理同上
4. Basically, the backup from standby-site is used for restoring on MASTER.?
pg_rman doesn’t treat the backup as restoring on standby automatically.
5. If you want to restore the backup on STANDBY, you have to manage archive logs with your self.?
因為備庫不歸檔,所以從standby備份時,需要解決歸檔備份的問題。?
我在前面的文檔中已經提及,包括解決思路。
### 如果使用快照備份,有哪些軟件限制
When using storage snapshot, pg_rman has the following restrictions too.
1. If your snapshot does not have any file update time, incremental backup is same with full backup.
2. Because pg_rman judges performing full backup or incremental backup by update time for files.?
If files don’t have update time because of storage snapshot specification, pg_rman performs full backup every time.
3. You can’t backup for one side works storage with split mirror snapshot.
4. Before you execute pg_rman, you should perform storage “RESYNC”.
5. After pg_rman performs backup with split mirror snapshot, storeage will be “SPLITTED”(works on one side).?
pg_rman perform SPLIT command for getting snapshot, but doesn’t perform RESYNC command.
6. You cant’t get snapshot from different vendor storages in a time.
7. You cant’t use some vendor storages which have different commands for getting snapshot.
8. The script and commands for getting storage snapshot should be executable.
9. It’s expected to have authority of root for getting snapshot or mounting volumes.?
So a user, performs pg_rman, is granted to execute any commands in the script.
10. If you use LVM(Logical Volume Manager), it’s needed root authority for mount, umount, lvcreate, lvremove, lvscan commands.?
You should granted to these commands with sudo command to non-password executable.
## pg_rman 源碼淺析
1. 增量備份代碼
上次備份以來,數據塊的LSN是否發生了變化,如果自從上次備份的start_lsn以來沒有發生變化,則不備份。
代碼舉例
~~~
else
{
pgBackupGetPath(prev_backup, prev_file_txt, lengthof(prev_file_txt),
DATABASE_FILE_LIST);
prev_files = dir_read_file_list(pgdata, prev_file_txt);
/*
* Do backup only pages having larger LSN than previous backup.
*/
lsn = &prev_backup->start_lsn;
xlogid = (uint32) (*lsn >> 32);
xrecoff = (uint32) *lsn;
elog(DEBUG, _("backup only the page updated after LSN(%X/%08X)"),
xlogid, xrecoff);
}
...
/* backup files from non-snapshot */
pgBackupGetPath(¤t, path, lengthof(path), DATABASE_DIR);
backup_files(pgdata, path, files, prev_files, lsn, current.compress_data, NULL);
~~~
1. 備份結果backup.ini相關代碼
~~~
# configuration
BACKUP_MODE=FULL
FULL_BACKUP_ON_ERROR=false
WITH_SERVERLOG=true
COMPRESS_DATA=true
# result
TIMELINEID=1
START_LSN=43/d5000028
STOP_LSN=43/d5000168
START_TIME='2016-08-26 15:43:39'
END_TIME='2016-08-26 15:44:27'
RECOVERY_XID=3896508572
RECOVERY_TIME='2016-08-26 15:44:18'
TOTAL_DATA_BYTES=823571731
READ_DATA_BYTES=823571731
READ_ARCLOG_BYTES=234881668
READ_SRVLOG_BYTES=218248
WRITE_BYTES=206009921
BLOCK_SIZE=8192
XLOG_BLOCK_SIZE=8192
STATUS=OK
~~~
對應的數據結構
~~~
/*
* pg_rman takes backup into the directory $BACKUP_PATH/<date>/<time>.
*
* status == -1 indicates the pgBackup is invalid.
*/
typedef struct pgBackup
{
/* Backup Level */
BackupMode backup_mode;
bool with_serverlog;
bool compress_data;
bool full_backup_on_error;
/* Status - one of BACKUP_STATUS_xxx */
BackupStatus status;
/* Timestamp, etc. */
TimeLineID tli;
XLogRecPtr start_lsn;
XLogRecPtr stop_lsn;
time_t start_time;
time_t end_time;
time_t recovery_time;
uint32 recovery_xid;
/* Size (-1 means not-backup'ed) */
int64 total_data_bytes;
int64 read_data_bytes;
int64 read_arclog_bytes;
int64 read_srvlog_bytes;
int64 write_bytes;
/* data/wal block size for compatibility check */
uint32 block_size;
uint32 wal_block_size;
/* if backup from standby or not */
bool is_from_standby;
} pgBackup;
~~~
備份開始時記錄pg_start_backup調用返回的lsn,寫入backup->start_lsn
~~~
/*
* Notify start of backup to PostgreSQL server.
*/
static void
pg_start_backup(const char *label, bool smooth, pgBackup *backup)
{
PGresult *res;
const char *params[2];
params[0] = label;
elog(DEBUG, "executing pg_start_backup()");
reconnect();
/* Assumes PG version >= 8.4 */
/* 2nd argument is 'fast' (IOW, !smooth) */
params[1] = smooth ? "false" : "true";
res = execute("SELECT * from pg_xlogfile_name_offset(pg_start_backup($1, $2))", 2, params);
if (backup != NULL)
get_lsn(res, &backup->tli, &backup->start_lsn);
elog(DEBUG, "backup start point is (WAL file: %s, xrecoff: %s)",
PQgetvalue(res, 0, 0), PQgetvalue(res, 0, 1));
PQclear(res);
disconnect();
}
~~~
備份停止,調用pg_stop_backup,從返回結果中取出LSN,寫入backup->stop_lsn
~~~
/*
* Notify end of backup to PostgreSQL server.
*/
static void
pg_stop_backup(pgBackup *backup)
{
elog(DEBUG, "executing pg_stop_backup()");
wait_for_archive(backup,
"SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup())");
}
static void
wait_for_archive(pgBackup *backup, const char *sql)
{
PGresult *res;
char ready_path[MAXPGPATH];
int try_count;
reconnect();
res = execute(sql, 0, NULL);
if (backup != NULL)
{
get_lsn(res, &backup->tli, &backup->stop_lsn);
elog(DEBUG, "backup end point is (WAL file: %s, xrecoff: %s)",
PQgetvalue(res, 0, 0), PQgetvalue(res, 0, 1));
}
/* get filename from the result of pg_xlogfile_name_offset() */
elog(DEBUG, "waiting for %s is archived", PQgetvalue(res, 0, 0));
snprintf(ready_path, lengthof(ready_path),
"%s/pg_xlog/archive_status/%s.ready", pgdata, PQgetvalue(res, 0, 0));
PQclear(res);
res = execute(TXID_CURRENT_SQL, 0, NULL);
if(backup != NULL)
{
get_xid(res, &backup->recovery_xid);
backup->recovery_time = time(NULL);
}
disconnect();
/* wait until switched WAL is archived */
try_count = 0;
while (fileExists(ready_path))
{
sleep(1);
if (interrupted)
ereport(FATAL,
(errcode(ERROR_INTERRUPTED),
errmsg("interrupted during waiting for WAL archiving")));
try_count++;
if (try_count > TIMEOUT_ARCHIVE)
ereport(ERROR,
(errcode(ERROR_ARCHIVE_FAILED),
errmsg("switched WAL could not be archived in %d seconds",
TIMEOUT_ARCHIVE)));
}
elog(DEBUG, "WAL file contains backup end point is archived after %d seconds waiting",
try_count);
}
~~~
validate 時,改backup.ini的STATUS字段
~~~
validate.c
for (i = 0; i < parray_num(backup_list); i++)
{
pgBackup *backup = (pgBackup *)parray_get(backup_list, i);
/* clean extra backups (switch STATUS to ERROR) */
if(!another_pg_rman &&
(backup->status == BACKUP_STATUS_RUNNING ||
backup->status == BACKUP_STATUS_DELETING))
{
backup->status = BACKUP_STATUS_ERROR;
pgBackupWriteIni(backup);
}
/* Validate completed backups only. */
if (backup->status != BACKUP_STATUS_DONE)
continue;
/* validate with CRC value and update status to OK */
pgBackupValidate(backup, false, false, (HAVE_DATABASE(backup)));
}
...
/* update status to OK */
if (corrupted)
backup->status = BACKUP_STATUS_CORRUPT;
else
backup->status = BACKUP_STATUS_OK;
pgBackupWriteIni(backup);
~~~
## 注意
1.備份參數 -C 表示無縫checkpoint, 所以可能很慢,視checkpoint_completion_target和segment_size的配置。?
如果你發現pg_rman開始很慢,可以把-C去掉,速度就快了,但是可能在高峰時,造成沖擊。?
建議高峰是不要備份。?
2.BUG?
unix socket 是$PGDATA時, validate會報錯
~~~
pg_rman validate
INFO: validate: "2016-08-26 16:19:25" backup, archive log files and server log files by CRC
ERROR: invalid type '?' found in "/data05/digoal/pgbak/20160826/161925/file_database.txt"
vi /data05/digoal/pgbak/20160826/161925/file_database.txt
.s.PGSQL.1921 ? 0 0 0777 2016-08-26 15:35:05
~~~
修改一下dir.c的代碼即可修復這個問題,修改如下
~~~
if (strncmp(path, ".s.PGSQL", 7) != 0 && type != 'f' && type != 'F' && type != 'd' && type != 'l')
ereport(ERROR,
(errcode(ERROR_CORRUPTED),
errmsg("invalid type '%c' found in \"%s\"", type, file_txt)));
~~~
祝大家玩得開心,歡迎隨時來?阿里云促膝長談業務需求?,恭候光臨。
阿里云的小伙伴們加油,努力?做好內核與服務,打造最貼地氣的云數據庫?。
- 數據庫內核月報目錄
- 數據庫內核月報 - 2016/09
- MySQL · 社區貢獻 · AliSQL那些事兒
- PetaData · 架構體系 · PetaData第二代低成本存儲體系
- MySQL · 社區動態 · MariaDB 10.2 前瞻
- MySQL · 特性分析 · 執行計劃緩存設計與實現
- PgSQL · 最佳實踐 · pg_rman源碼淺析與使用
- MySQL · 捉蟲狀態 · bug分析兩例
- PgSQL · 源碼分析 · PG優化器淺析
- MongoDB · 特性分析· Sharding原理與應用
- PgSQL · 源碼分析 · PG中的無鎖算法和原子操作應用一則
- SQLServer · 最佳實踐 · TEMPDB的設計
- 數據庫內核月報 - 2016/08
- MySQL · 特性分析 ·MySQL 5.7新特性系列四
- PgSQL · PostgreSQL 邏輯流復制技術的秘密
- MySQL · 特性分析 · MyRocks簡介
- GPDB · 特性分析· Greenplum 備份架構
- SQLServer · 最佳實踐 · RDS for SQLServer 2012權限限制提升與改善
- TokuDB · 引擎特性 · REPLACE 語句優化
- MySQL · 專家投稿 · InnoDB物理行中null值的存儲的推斷與驗證
- PgSQL · 實戰經驗 · 旋轉門壓縮算法在PostgreSQL中的實現
- MySQL · 源碼分析 · Query Cache并發處理
- PgSQL · 源碼分析· pg_dump分析
- 數據庫內核月報 - 2016/07
- MySQL · 特性分析 ·MySQL 5.7新特性系列三
- MySQL · 特性分析 · 5.7 代價模型淺析
- PgSQL · 實戰經驗 · 分組TOP性能提升44倍
- MySQL · 源碼分析 · 網絡通信模塊淺析
- MongoDB · 特性分析 · 索引原理
- SQLServer · 特性分析 · XML與JSON應用比較
- MySQL · 最佳實戰 · 審計日志實用案例分析
- MySQL · 性能優化 · 條件下推到物化表
- MySQL · 源碼分析 · Query Cache內部剖析
- MySQL · 捉蟲動態 · 備庫1206錯誤問題說明
- 數據庫內核月報 - 2016/06
- MySQL · 特性分析 · innodb 鎖分裂繼承與遷移
- MySQL · 特性分析 ·MySQL 5.7新特性系列二
- PgSQL · 實戰經驗 · 如何預測Freeze IO風暴
- GPDB · 特性分析· Filespace和Tablespace
- MariaDB · 新特性 · 窗口函數
- MySQL · TokuDB · checkpoint過程
- MySQL · 特性分析 · 內部臨時表
- MySQL · 最佳實踐 · 空間優化
- SQLServer · 最佳實踐 · 數據庫實現大容量插入的幾種方式
- 數據庫內核月報 - 2016/05
- MySQL · 引擎特性 · 基于InnoDB的物理復制實現
- MySQL · 特性分析 · MySQL 5.7新特性系列一
- PostgreSQL · 特性分析 · 邏輯結構和權限體系
- MySQL · 特性分析 · innodb buffer pool相關特性
- PG&GP · 特性分析 · 外部數據導入接口實現分析
- SQLServer · 最佳實踐 · 透明數據加密在SQLServer的應用
- MySQL · TokuDB · 日志子系統和崩潰恢復過程
- MongoDB · 特性分析 · Sharded cluster架構原理
- PostgreSQL · 特性分析 · 統計信息計算方法
- MySQL · 捉蟲動態 · left-join多表導致crash
- 數據庫內核月報 - 2016/04
- MySQL · 參數故事 · innodb_additional_mem_pool_size
- GPDB · 特性分析 · Segment事務一致性與異常處理
- GPDB · 特性分析 · Segment 修復指南
- MySQL · 捉蟲動態 · 并行復制外鍵約束問題二
- PgSQL · 性能優化 · 如何瀟灑的處理每天上百TB的數據增量
- Memcached · 最佳實踐 · 熱點 Key 問題解決方案
- MongoDB · 最佳實踐 · 短連接Auth性能優化
- MySQL · 最佳實踐 · RDS 只讀實例延遲分析
- MySQL · TokuDB · TokuDB索引結構--Fractal Tree
- MySQL · TokuDB · Savepoint漫談
- 數據庫內核月報 - 2016/03
- MySQL · TokuDB · 事務子系統和 MVCC 實現
- MongoDB · 特性分析 · MMAPv1 存儲引擎原理
- PgSQL · 源碼分析 · 優化器邏輯推理
- SQLServer · BUG分析 · Agent 鏈接泄露分析
- Redis · 特性分析 · AOF Rewrite 分析
- MySQL · BUG分析 · Rename table 死鎖分析
- MySQL · 物理備份 · Percona XtraBackup 備份原理
- GPDB · 特性分析· GreenPlum FTS 機制
- MySQL · 答疑解惑 · 備庫Seconds_Behind_Master計算
- MySQL · 答疑解惑 · MySQL 鎖問題最佳實踐
- 數據庫內核月報 - 2016/02
- MySQL · 引擎特性 · InnoDB 文件系統之文件物理結構
- MySQL · 引擎特性 · InnoDB 文件系統之IO系統和內存管理
- MySQL · 特性分析 · InnoDB transaction history
- PgSQL · 會議見聞 · PgConf.Russia 2016 大會總結
- PgSQL · 答疑解惑 · PostgreSQL 9.6 并行查詢實現分析
- MySQL · TokuDB · TokuDB之黑科技工具
- PgSQL · 性能優化 · PostgreSQL TPC-C極限優化玩法
- MariaDB · 版本特性 · MariaDB 的 GTID 介紹
- MySQL · 特性分析 · 線程池
- MySQL · 答疑解惑 · mysqldump tips 兩則
- 數據庫內核月報 - 2016/01
- MySQL · 引擎特性 · InnoDB 事務鎖系統簡介
- GPDB · 特性分析· GreenPlum Primary/Mirror 同步機制
- MySQL · 專家投稿 · MySQL5.7 的 JSON 實現
- MySQL · 特性分析 · 優化器 MRR & BKA
- MySQL · 答疑解惑 · 物理備份死鎖分析
- MySQL · TokuDB · Cachetable 的工作線程和線程池
- MySQL · 特性分析 · drop table的優化
- MySQL · 答疑解惑 · GTID不一致分析
- PgSQL · 特性分析 · Plan Hint
- MariaDB · 社區動態 · MariaDB on Power8 (下)
- 數據庫內核月報 - 2015/12
- MySQL · 引擎特性 · InnoDB 事務子系統介紹
- PgSQL · 特性介紹 · 全文搜索介紹
- MongoDB · 捉蟲動態 · Kill Hang問題排查記錄
- MySQL · 參數優化 ·RDS MySQL參數調優最佳實踐
- PgSQL · 特性分析 · 備庫激活過程分析
- MySQL · TokuDB · 讓Hot Backup更完美
- PgSQL · 答疑解惑 · 表膨脹
- MySQL · 特性分析 · Index Condition Pushdown (ICP)
- MariaDB · 社區動態 · MariaDB on Power8
- MySQL · 特性分析 · 企業版特性一覽
- 數據庫內核月報 - 2015/11
- MySQL · 社區見聞 · OOW 2015 總結 MySQL 篇
- MySQL · 特性分析 · Statement Digest
- PgSQL · 答疑解惑 · PostgreSQL 用戶組權限管理
- MySQL · 特性分析 · MDL 實現分析
- PgSQL · 特性分析 · full page write 機制
- MySQL · 捉蟲動態 · MySQL 外鍵異常分析
- MySQL · 答疑解惑 · MySQL 優化器 range 的代價計算
- MySQL · 捉蟲動態 · ORDER/GROUP BY 導致 mysqld crash
- MySQL · TokuDB · TokuDB 中的行鎖
- MySQL · 捉蟲動態 · order by limit 造成優化器選擇索引錯誤
- 數據庫內核月報 - 2015/10
- MySQL · 引擎特性 · InnoDB 全文索引簡介
- MySQL · 特性分析 · 跟蹤Metadata lock
- MySQL · 答疑解惑 · 索引過濾性太差引起CPU飆高分析
- PgSQL · 特性分析 · PG主備流復制機制
- MySQL · 捉蟲動態 · start slave crash 診斷分析
- MySQL · 捉蟲動態 · 刪除索引導致表無法打開
- PgSQL · 特性分析 · PostgreSQL Aurora方案與DEMO
- TokuDB · 捉蟲動態 · CREATE DATABASE 導致crash問題
- PgSQL · 特性分析 · pg_receivexlog工具解析
- MySQL · 特性分析 · MySQL權限存儲與管理
- 數據庫內核月報 - 2015/09
- MySQL · 引擎特性 · InnoDB Adaptive hash index介紹
- PgSQL · 特性分析 · clog異步提交一致性、原子操作與fsync
- MySQL · 捉蟲動態 · BUG 幾例
- PgSQL · 答疑解惑 · 詭異的函數返回值
- MySQL · 捉蟲動態 · 建表過程中crash造成重建表失敗
- PgSQL · 特性分析 · 談談checkpoint的調度
- MySQL · 特性分析 · 5.6 并行復制恢復實現
- MySQL · 備庫優化 · relay fetch 備庫優化
- MySQL · 特性分析 · 5.6并行復制事件分發機制
- MySQL · TokuDB · 文件目錄談
- 數據庫內核月報 - 2015/08
- MySQL · 社區動態 · InnoDB Page Compression
- PgSQL · 答疑解惑 · RDS中的PostgreSQL備庫延遲原因分析
- MySQL · 社區動態 · MySQL5.6.26 Release Note解讀
- PgSQL · 捉蟲動態 · 執行大SQL語句提示無效的內存申請大小
- MySQL · 社區動態 · MariaDB InnoDB表空間碎片整理
- PgSQL · 答疑解惑 · 歸檔進程cp命令的core文件追查
- MySQL · 答疑解惑 · open file limits
- MySQL · TokuDB · 瘋狂的 filenum++
- MySQL · 功能分析 · 5.6 并行復制實現分析
- MySQL · 功能分析 · MySQL表定義緩存
- 數據庫內核月報 - 2015/07
- MySQL · 引擎特性 · Innodb change buffer介紹
- MySQL · TokuDB · TokuDB Checkpoint機制
- PgSQL · 特性分析 · 時間線解析
- PgSQL · 功能分析 · PostGIS 在 O2O應用中的優勢
- MySQL · 引擎特性 · InnoDB index lock前世今生
- MySQL · 社區動態 · MySQL內存分配支持NUMA
- MySQL · 答疑解惑 · 外鍵刪除bug分析
- MySQL · 引擎特性 · MySQL logical read-ahead
- MySQL · 功能介紹 · binlog拉取速度的控制
- MySQL · 答疑解惑 · 浮點型的顯示問題
- 數據庫內核月報 - 2015/06
- MySQL · 引擎特性 · InnoDB 崩潰恢復過程
- MySQL · 捉蟲動態 · 唯一鍵約束失效
- MySQL · 捉蟲動態 · ALTER IGNORE TABLE導致主備不一致
- MySQL · 答疑解惑 · MySQL Sort 分頁
- MySQL · 答疑解惑 · binlog event 中的 error code
- PgSQL · 功能分析 · Listen/Notify 功能
- MySQL · 捉蟲動態 · 任性的 normal shutdown
- PgSQL · 追根究底 · WAL日志空間的意外增長
- MySQL · 社區動態 · MariaDB Role 體系
- MySQL · TokuDB · TokuDB數據文件大小計算
- 數據庫內核月報 - 2015/05
- MySQL · 引擎特性 · InnoDB redo log漫游
- MySQL · 專家投稿 · MySQL數據庫SYS CPU高的可能性分析
- MySQL · 捉蟲動態 · 5.6 與 5.5 InnoDB 不兼容導致 crash
- MySQL · 答疑解惑 · InnoDB 預讀 VS Oracle 多塊讀
- PgSQL · 社區動態 · 9.5 新功能BRIN索引
- MySQL · 捉蟲動態 · MySQL DDL BUG
- MySQL · 答疑解惑 · set names 都做了什么
- MySQL · 捉蟲動態 · 臨時表操作導致主備不一致
- TokuDB · 引擎特性 · zstd壓縮算法
- MySQL · 答疑解惑 · binlog 位點刷新策略
- 數據庫內核月報 - 2015/04
- MySQL · 引擎特性 · InnoDB undo log 漫游
- TokuDB · 產品新聞 · RDS TokuDB小手冊
- PgSQL · 社區動態 · 說一說PgSQL 9.4.1中的那些安全補丁
- MySQL · 捉蟲動態 · 連接斷開導致XA事務丟失
- MySQL · 捉蟲動態 · GTID下slave_net_timeout值太小問題
- MySQL · 捉蟲動態 · Relay log 中 GTID group 完整性檢測
- MySQL · 答疑釋惑 · UPDATE交換列單表和多表的區別
- MySQL · 捉蟲動態 · 刪被引用索引導致crash
- MySQL · 答疑釋惑 · GTID下auto_position=0時數據不一致
- 數據庫內核月報 - 2015/03
- MySQL · 答疑釋惑· 并發Replace into導致的死鎖分析
- MySQL · 性能優化· 5.7.6 InnoDB page flush 優化
- MySQL · 捉蟲動態· pid file丟失問題分析
- MySQL · 答疑釋惑· using filesort VS using temporary
- MySQL · 優化限制· MySQL index_condition_pushdown
- MySQL · 捉蟲動態·DROP DATABASE外鍵約束的GTID BUG
- MySQL · 答疑釋惑· lower_case_table_names 使用問題
- PgSQL · 特性分析· Logical Decoding探索
- PgSQL · 特性分析· jsonb類型解析
- TokuDB ·引擎機制· TokuDB線程池
- 數據庫內核月報 - 2015/02
- MySQL · 性能優化· InnoDB buffer pool flush策略漫談
- MySQL · 社區動態· 5.6.23 InnoDB相關Bugfix
- PgSQL · 特性分析· Replication Slot
- PgSQL · 特性分析· pg_prewarm
- MySQL · 答疑釋惑· InnoDB丟失自增值
- MySQL · 答疑釋惑· 5.5 和 5.6 時間類型兼容問題
- MySQL · 捉蟲動態· 變量修改導致binlog錯誤
- MariaDB · 特性分析· 表/表空間加密
- MariaDB · 特性分析· Per-query variables
- TokuDB · 特性分析· 日志詳解
- 數據庫內核月報 - 2015/01
- MySQL · 性能優化· Group Commit優化
- MySQL · 新增特性· DDL fast fail
- MySQL · 性能優化· 啟用GTID場景的性能問題及優化
- MySQL · 捉蟲動態· InnoDB自增列重復值問題
- MySQL · 優化改進· 復制性能改進過程
- MySQL · 談古論今· key分區算法演變分析
- MySQL · 捉蟲動態· mysql client crash一例
- MySQL · 捉蟲動態· 設置 gtid_purged 破壞AUTO_POSITION復制協議
- MySQL · 捉蟲動態· replicate filter 和 GTID 一起使用的問題
- TokuDB·特性分析· Optimize Table
- 數據庫內核月報 - 2014/12
- MySQL· 性能優化·5.7 Innodb事務系統
- MySQL· 踩過的坑·5.6 GTID 和存儲引擎那會事
- MySQL· 性能優化·thread pool 原理分析
- MySQL· 性能優化·并行復制外建約束問題
- MySQL· 答疑釋惑·binlog event有序性
- MySQL· 答疑釋惑·server_id為0的Rotate
- MySQL· 性能優化·Bulk Load for CREATE INDEX
- MySQL· 捉蟲動態·Opened tables block read only
- MySQL· 優化改進· GTID啟動優化
- TokuDB· Binary Log Group Commit with TokuDB
- 數據庫內核月報 - 2014/11
- MySQL· 捉蟲動態·OPTIMIZE 不存在的表
- MySQL· 捉蟲動態·SIGHUP 導致 binlog 寫錯
- MySQL· 5.7改進·Recovery改進
- MySQL· 5.7特性·高可用支持
- MySQL· 5.7優化·Metadata Lock子系統的優化
- MySQL· 5.7特性·在線Truncate undo log 表空間
- MySQL· 性能優化·hash_scan 算法的實現解析
- TokuDB· 版本優化· 7.5.0
- TokuDB· 引擎特性· FAST UPDATES
- MariaDB· 性能優化·filesort with small LIMIT optimization
- 數據庫內核月報 - 2014/10
- MySQL· 5.7重構·Optimizer Cost Model
- MySQL· 系統限制·text字段數
- MySQL· 捉蟲動態·binlog重放失敗
- MySQL· 捉蟲動態·從庫OOM
- MySQL· 捉蟲動態·崩潰恢復失敗
- MySQL· 功能改進·InnoDB Warmup特性
- MySQL· 文件結構·告別frm文件
- MariaDB· 新鮮特性·ANALYZE statement 語法
- TokuDB· 主備復制·Read Free Replication
- TokuDB· 引擎特性·壓縮
- 數據庫內核月報 - 2014/09
- MySQL· 捉蟲動態·GTID 和 DELAYED
- MySQL· 限制改進·GTID和升級
- MySQL· 捉蟲動態·GTID 和 binlog_checksum
- MySQL· 引擎差異·create_time in status
- MySQL· 參數故事·thread_concurrency
- MySQL· 捉蟲動態·auto_increment
- MariaDB· 性能優化·Extended Keys
- MariaDB·主備復制·CREATE OR REPLACE
- TokuDB· 參數故事·數據安全和性能
- TokuDB· HA方案·TokuDB熱備
- 數據庫內核月報 - 2014/08
- MySQL· 參數故事·timed_mutexes
- MySQL· 參數故事·innodb_flush_log_at_trx_commit
- MySQL· 捉蟲動態·Count(Distinct) ERROR
- MySQL· 捉蟲動態·mysqldump BUFFER OVERFLOW
- MySQL· 捉蟲動態·long semaphore waits
- MariaDB·分支特性·支持大于16K的InnoDB Page Size
- MariaDB·分支特性·FusionIO特性支持
- TokuDB· 性能優化·Bulk Fetch
- TokuDB· 數據結構·Fractal-Trees與LSM-Trees對比
- TokuDB·社區八卦·TokuDB團隊