{"id":30356,"date":"2019-10-31T21:35:06","date_gmt":"2019-10-31T18:35:06","guid":{"rendered":"https:\/\/prohoster.info\/blog\/kak-my-ispolzovali-otlozhennuyu-replikatsiyu-dlya-avarijnogo-vosstanovleniya-s-postgresql\/"},"modified":"2019-10-31T21:35:06","modified_gmt":"2019-10-31T18:35:06","slug":"kak-my-ispolzovali-otlozhennuyu-replikatsiyu-dlya-avarijnogo-vosstanovleniya-s-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/kak-my-ispolzovali-otlozhennuyu-replikatsiyu-dlya-avarijnogo-vosstanovleniya-s-postgresql","title":{"rendered":"How we used delayed replication for disaster recovery with PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\"How
\nReplication is not a backup. Or is it? Here's how we used delayed replication for recovery after accidentally deleting labels.<\/p>\n

<\/p>\n

Infrastructure specialists<\/a><\/noindex> at GitLab are responsible for the operation GitLab.com<\/a><\/noindex> of the largest GitLab instance in existence. It hosts 3 million users and nearly 7 million projects, making it one of the largest open-source SaaS sites with dedicated architecture. Without a PostgreSQL database system, GitLab.com wouldn't get very far, and we do a lot to ensure resilience in case of failures that could lead to data loss. While such a disaster is unlikely, we are well-prepared and equipped with various backup and replication mechanisms.<\/p>\n

<\/p>\n

Replication is not a means of backing up databases (see below<\/a><\/noindex>). But now we'll see how quickly we can recover accidentally deleted data using delayed replication: I GitLab.com<\/a><\/noindex> user deleted a label<\/a><\/noindex> for the project gitlab-ce<\/code><\/a><\/noindex> and lost connection with merge requests and issues.<\/p>\n

<\/p>\n

With the delayed replica, we recovered the data in just 1.5 hours. Here's how it went.<\/p>\n

<\/a><\/noindex><\/p>\n

Point-in-Time Recovery with PostgreSQL<\/h3>\n

<\/p>\n

PostgreSQL has a built-in feature that restores the database state to a specific point in time. It's called Point-in-Time Recovery<\/a><\/noindex> (PITR) and uses the same mechanisms that keep the replica up-to-date: starting from a reliable snapshot of the entire database cluster (base backup), we apply a series of state changes up to a specified point in time.<\/p>\n

<\/p>\n

To use this feature for cold backups, we regularly create a base backup of the database and store it in an archive (GitLab archives live in Google Cloud Storage<\/a><\/noindex>). We also track database state changes by archiving the write-ahead log (write-ahead log<\/a><\/noindex>, WAL). With all this, we can perform PITR for disaster recovery: starting from a snapshot made before the error, we apply changes from the WAL archive up to the failure.<\/p>\n

<\/p>\n

What is delayed replication?<\/h3>\n

<\/p>\n

Delayed replication is the application of changes from the WAL with a delay. That is, the transaction occurred at the hour X<\/code>, but it will appear in the replica with a delay of d<\/code> the hour X + d<\/code>.<\/p>\n

<\/p>\n

In PostgreSQL, there are 2 ways to set up a physical database replica: recovery from archive and streaming replication. Recovery from archive<\/a><\/noindex>, in essence, works like PITR, but continuously: we constantly extract changes from the WAL archive and apply them to the replica. A streaming replication<\/a><\/noindex> directly pulls the WAL stream from the higher-level database host. We prefer restoration from the archive\u2014it is easier to manage and has normal performance that keeps pace with the working cluster.<\/p>\n

<\/p>\n

How to set up delayed recovery from the archive<\/h3>\n

<\/p>\n

Recovery options<\/a><\/noindex> are described in the file recovery.conf<\/code>. Example:<\/p>\n

<\/p>\n

standby_mode = 'on'\nrestore_command = '\\\/usr\\\/bin\\\/envdir \\\/etc\\\/wal-e.d\\\/env \\\/opt\\\/wal-e\\\/bin\\\/wal-e wal-fetch -p 4 \"%f\" \"%p\"'\nrecovery_min_apply_delay = '8h'\nrecovery_target_timeline = 'latest'<\/code><\/pre>\n
<\/p>\n
With these parameters, we set up a delayed replica with recovery from the archive. It uses wal-e<\/a><\/noindex> to extract WAL segments (restore_command<\/code>) from the archive, and changes will be applied after eight hours (recovery_min_apply_delay<\/code>). The replica will track the changes on the timeline in the archive, for example, due to failover in the cluster (recovery_target_timeline<\/code>).<\/p>\n
<\/p>\n
C recovery_min_apply_delay<\/code> You can set up streaming replication with delay, but there are a couple of pitfalls related to replication slots, hot standby feedback, and so on. The WAL archive helps avoid them.<\/p>\n
<\/p>\n
Parameter recovery_min_apply_delay<\/code> was introduced only in PostgreSQL 9.3. In earlier versions, to set up delayed replication, you needed to configure a combination of recovery management functions<\/a><\/noindex> (pg_xlog_replay_pause(), pg_xlog_replay_resume()<\/code>) or keep WAL segments in the archive during the delay period.<\/p>\n
<\/p>\n
How does PostgreSQL do this?<\/h3>\n
<\/p>\n
It is interesting to see how PostgreSQL implements delayed recovery. Let's take a look at recoveryApplyDelay(XlogReaderState)<\/code><\/a><\/noindex>. It is called from the main replay loop<\/a><\/noindex> for each record from the WAL.<\/p>\n
<\/p>\n
static bool\nrecoveryApplyDelay(XLogReaderState *record)\n{\n    uint8       xact_info;\n    TimestampTz xtime;\n    long        secs;\n    int         microsecs;\n\n    \/* nothing to do if no delay configured *\/\n    if (recovery_min_apply_delay <= 0)\n        return false;\n\n    \/* no delay is applied on a database not yet consistent *\/\n    if (!reachedConsistency)\n        return false;\n\n    \/*\n     * Is it a COMMIT record?\n     *\n     * We deliberately choose not to delay aborts since they have no effect on\n     * MVCC. We already allow replay of records that don't have a timestamp,\n     * so there is already opportunity for issues caused by early conflicts on\n     * standbys.\n     *\/\n    if (XLogRecGetRmid(record) != RM_XACT_ID)\n        return false;\n\n    xact_info = XLogRecGetInfo(record) & XLOG_XACT_OPMASK;\n\n    if (xact_info != XLOG_XACT_COMMIT &&\n        xact_info != XLOG_XACT_COMMIT_PREPARED)\n        return false;\n\n    if (!getRecordTimestamp(record, &xtime))\n        return false;\n\n    recoveryDelayUntilTime =\n        TimestampTzPlusMilliseconds(xtime, recovery_min_apply_delay);\n\n    \/*\n     * Exit without arming the latch if it's already past time to apply this\n     * record\n     *\/\n    TimestampDifference(GetCurrentTimestamp(), recoveryDelayUntilTime,\n                        &secs, &microsecs);\n    if (secs <= 0 && microsecs <= 0)\n        return false;\n\n    while (true)\n    {\n        \/\/ Shortened:\n        \/\/ Use WaitLatch until we reached recoveryDelayUntilTime\n        \/\/ and then\n        break;\n    }\n    return true;\n}<\/code><\/pre>\n
<\/p>\n
The essence is that the delay is based on the physical time recorded in the commit timestamp of the transaction (xtime<\/code>). As you can see, the delay is applied only to commits and does not affect other records \u2014 all changes are applied directly, and the commit is postponed, so we will see the changes only after the configured delay.<\/p>\n
<\/p>\n
How to use delayed replica for data recovery<\/h3>\n
<\/p>\n
Suppose we have a production database cluster and a replica with an eight-hour delay. Let\u2019s see how to recover data using the example of accidental deletion of labels<\/a><\/noindex>.<\/p>\n
<\/p>\n
When we found out about the issue, we paused the recovery from the archive<\/a><\/noindex> for the delayed replica:<\/p>\n
<\/p>\n
SELECT pg_xlog_replay_pause();<\/code><\/pre>\n
<\/p>\n
With the pause, we had no risk that the replica would repeat the request DELETE<\/code>. A useful thing if you need time to sort everything out.<\/p>\n
<\/p>\n
The point is that the delayed replica must reach the moment before the request DELETE<\/code>. We roughly knew the physical time of the deletion. We deleted recovery_min_apply_delay<\/code> and added recovery_target_time<\/code> downward API support (simultaneously with this in recovery.conf<\/code>. This way, the replica reaches the required moment without delays:<\/p>\n
<\/p>\n
recovery_target_time = '2018-10-12 09:25:00+00'<\/code><\/pre>\n
<\/p>\n
With timestamps, it\u2019s better to subtract a little excess to avoid mistakes. However, the more you subtract, the more data you lose. Again, if we miss the request DELETE<\/code>, everything will be deleted again and we'll have to start over (or take a cold backup for PITR).<\/p>\n
<\/p>\n
We restarted the delayed Postgres instance, and the WAL segments were replayed up to the specified time. You can track progress at this stage with the following query:<\/p>\n
<\/p>\n
SELECT\n  -- current location in WAL\n  pg_last_xlog_replay_location(),\n  -- current transaction timestamp (state of the replica)\n  pg_last_xact_replay_timestamp(),\n  -- current physical time\n  now(),\n  -- the amount of time still to be applied until recovery_target_time has been reached\n  '2018-10-12 09:25:00+00'::timestamptz - pg_last_xact_replay_timestamp() as delay;<\/code><\/pre>\n
<\/p>\n
If the timestamp no longer changes, recovery is complete. You can configure the action recovery_target_action<\/code><\/a><\/noindex>, to either stop, promote, or pause the instance after replay (by default, it pauses).<\/p>\n
<\/p>\n
The database has reverted to the state before that unfortunate query. Now, you can, for example, export the data. We exported the removed shortcut data and all relationships with tasks and merge requests and moved them to the working database. If the loss is significant, you can simply promote the replica and use it as the primary. However, this will cause all changes made after the point to which we restored to be lost.<\/p>\n
<\/p>\n
Instead of timestamps, it's better to use transaction IDs. It's useful to record these IDs, for example, for DDL operators (like DROP TABLE<\/code>), using log_statements = 'ddl'<\/code>. If we had the transaction ID, we would take recovery_target_xid<\/code> and replay everything up to the transaction before the query. DELETE<\/code>.<\/p>\n
<\/p>\n
Returning to work is very simple: remove all changes from recovery.conf<\/code> and restart Postgres. Soon there will again be an eight-hour delay in the replica, and we will be ready for future troubles.<\/p>\n
<\/p>\n
Advantages of recovery<\/h3>\n
<\/p>\n
With a delayed replica instead of a cold backup, there's no need to spend hours restoring the entire snapshot from the archive. For example, it takes us five hours to retrieve the entire base backup of 2 TB. Then we would also have to apply all the daily WAL to recover to the desired state (in the worst case).<\/p>\n
<\/p>\n
The delayed replica is superior to a cold backup for two reasons:<\/p>\n
<\/p>\n
    \n
  1. There\u2019s no need to retrieve the entire base backup from the archive.<\/li>\n
  2. There\u2019s a fixed eight-hour window of WAL segments to replay.<\/li>\n<\/ol>\n
    <\/p>\n
    We also constantly check if we can perform PITR from the WAL, and we would quickly notice any damages or other issues with the WAL archive by monitoring the lag of the delayed replica.<\/p>\n
    <\/p>\n
    In this example, it took us 50 minutes to recover, meaning the speed was 110 GB of WAL data per hour (the archive was still on AWS S3<\/a><\/noindex>). We resolved the issue and restored the data in 1.5 hours.<\/p>\n
    <\/p>\n
    Summary: Where delayed replication is useful (and where it isn't)<\/h3>\n
    <\/p>\n
    Use delayed replication as a first aid tool if you accidentally lose data and notice the problem within the configured delay.<\/p>\n
    <\/p>\n
    But keep in mind: replication is not a backup.<\/p><\/blockquote>\n
    Backups and replication have different purposes. A cold backup will be useful if you accidentally made DELETE<\/code> or DROP TABLE<\/code>. We create a backup from cold storage and restore the previous state of the table or the entire database. However, during this process, the DROP TABLE<\/code> is almost instantly replicated across all replicas in the working cluster, so standard replication won't help here. Replication itself keeps the database available when individual servers fail and distributes the load.<\/p>\n
    <\/p>\n
    Even with a delayed replica, we sometimes really need a cold backup in a safe place in case of a data center failure, hidden corruption, or other events that you might not notice immediately. Here, replication alone isn't sufficient.<\/p>\n
    <\/p>\n
    Note<\/strong>. On GitLab.com<\/a><\/noindex> we are currently protecting against data loss only at the system level and are not restoring data at the user level.<\/p>\n
    Source: habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
    \u0420\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044f \u2014 \u043d\u0435 \u0431\u044d\u043a\u0430\u043f. \u0418\u043b\u0438 \u043d\u0435\u0442? \u0412\u043e\u0442 \u043a\u0430\u043a \u043c\u044b \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043b\u0438 \u043e\u0442\u043b\u043e\u0436\u0435\u043d\u043d\u0443\u044e \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044e \u0434\u043b\u044f \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f, \u0441\u043b\u0443\u0447\u0430\u0439\u043d\u043e \u0443\u0434\u0430\u043b\u0438\u0432 \u044f\u0440\u043b\u044b\u043a\u0438. \u0421\u043f\u0435\u0446\u0438\u0430\u043b\u0438\u0441\u0442\u044b \u043f\u043e \u0438\u043d\u0444\u0440\u0430\u0441\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u0435 \u043d\u0430 GitLab \u043e\u0442\u0432\u0435\u0447\u0430\u044e\u0442 \u0437\u0430 \u0440\u0430\u0431\u043e\u0442\u0443 GitLab.com \u2014 \u0441\u0430\u043c\u043e\u0433\u043e \u0431\u043e\u043b\u044c\u0448\u043e\u0433\u043e \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0430 GitLab \u0432 \u043f\u0440\u0438\u0440\u043e\u0434\u0435. \u0417\u0434\u0435\u0441\u044c 3 \u043c\u0438\u043b\u043b\u0438\u043e\u043d\u0430 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0435\u0439 \u0438 \u043f\u043e\u0447\u0442\u0438 7 \u043c\u0438\u043b\u043b\u0438\u043e\u043d\u043e\u0432 \u043f\u0440\u043e\u0435\u043a\u0442\u043e\u0432, \u0438 \u044d\u0442\u043e \u043e\u0434\u0438\u043d \u0438\u0437 \u0441\u0430\u043c\u044b\u0445 \u043a\u0440\u0443\u043f\u043d\u044b\u0445 \u043e\u043f\u0435\u043d\u0441\u043e\u0440\u0441-\u0441\u0430\u0439\u0442\u043e\u0432 SaaS \u0441 \u0432\u044b\u0434\u0435\u043b\u0435\u043d\u043d\u043e\u0439 \u0430\u0440\u0445\u0438\u0442\u0435\u043a\u0442\u0443\u0440\u043e\u0439. \u0411\u0435\u0437 \u0441\u0438\u0441\u0442\u0435\u043c\u044b […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":22360,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-30356","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47 How We Used Delayed Replication for Disaster Recovery with PostgreSQL | ProHoster","description":".","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/kak-my-ispolzovali-otlozhennuyu-replikatsiyu-dlya-avarijnogo-vosstanovleniya-s-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041a\u0430\u043a \u043c\u044b \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043b\u0438 \u043e\u0442\u043b\u043e\u0436\u0435\u043d\u043d\u0443\u044e \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044e \u0434\u043b\u044f \u0430\u0432\u0430\u0440\u0438\u0439\u043d\u043e\u0433\u043e \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f \u0441 PostgreSQL | ProHoster","og:description":".","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/kak-my-ispolzovali-otlozhennuyu-replikatsiyu-dlya-avarijnogo-vosstanovleniya-s-postgresql","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T18:35:06+00:00","article:modified_time":"2019-10-31T18:35:06+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"30356","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-21 00:50:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 11:48:29","updated":"2026-01-21 00:50:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/30356","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=30356"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/30356\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/22360"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=30356"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=30356"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=30356"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}