{"id":16519,"date":"2021-07-02T11:18:34","date_gmt":"2021-07-02T09:18:34","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/"},"modified":"2021-07-02T11:18:34","modified_gmt":"2021-07-02T09:18:34","slug":"__trashed-3","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/","title":{"rendered":"What are these *.ready and *.done files for in PostgreSQL?"},"content":{"rendered":"<p>When you run PostgreSQL workloads in production you must have a backup and restore implementation. Even for development instances, which are like production for the developers, a well-tested backup and restore procedure sometimes must be in place. Community PostgreSQL comes with <a href=\"https:\/\/www.postgresql.org\/docs\/current\/app-pgbasebackup.html\" target=\"_blank\" rel=\"noopener\">pg_basebackup<\/a> to help you with creating a full consistent backup of your PostgreSQL cluster. If you want to be able to do point in time recovery (PITR) you need to archive the WAL segments which can later be used to roll forward from a base backup. WAL segments go to the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/storage-file-layout.html\" target=\"_blank\" rel=\"noopener\">pg_wal<\/a> directory, but if you have closer look into this directory you will see more than just the WAL segments in case you have enabled archiving.<\/p>\n<p><!--more--><\/p>\n<p>IF you look at a freshly initialized PostgreSQL cluster the content of pg_wal looks like this:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11:\/home\/postgres\/ [pgdev] ls -la $PGDATA\/pg_wal\ntotal 16396\ndrwx------  3 postgres postgres     4096 Jul  4 11:47 .\ndrwx------ 20 postgres postgres     4096 Jul  4 11:47 ..\n-rw-------  1 postgres postgres 16777216 Jul  4 11:52 000000010000000000000001\ndrwx------  2 postgres postgres     4096 Jul  4 11:47 archive_status\n<\/pre>\n<p>There is one WAL segment and a directory called &#8220;archived_status&#8221; which is empty right now:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11:\/home\/postgres\/ [pgdev] ls -la $PGDATA\/pg_wal\/archive_status\/\ntotal 8\ndrwx------ 2 postgres postgres 4096 Jul  4 11:47 .\ndrwx------ 3 postgres postgres 4096 Jul  4 11:47 ..\n<\/pre>\n<p>To see what&#8217;s going on in this directory we need to enable archiving and tell PostgreSQL what do when a WAL segment needs to be archived:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11:\/home\/postgres\/ [pgdev] mkdir \/tmp\/archived_wal\npostgres@debian11:\/home\/postgres\/ [pgdev] psql -c \"alter system set archive_mode='on'\" postgres\nALTER SYSTEM\npostgres@debian11:\/home\/postgres\/ [pgdev] psql -c \"alter system set archive_command='test ! -f \/tmp\/archived_wal\/%f &amp;&amp; cp %p \/tmp\/archived_wal\/%f'\" postgres\nALTER SYSTEM\n<\/pre>\n<p>Of course you should not archive to \/tmp and you also should not use cp, as this does not guarantee that the archived segment really is written to disk. For the scope of this post it is fine like that.<\/p>\n<p>Changing the archive command can be done online, but enabling or disabling archiving requires a restart:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11:\/home\/postgres\/ [pgdev] pg_ctl restart\n<\/pre>\n<p>Let&#8217;s see what happens if we force the current segment to be archived:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11:\/home\/postgres\/ [pgdev] psql -c \"select pg_switch_wal()\" postgres\n pg_switch_wal \n---------------\n 0\/167AF68\n(1 row)\npostgres@debian11:\/home\/postgres\/ [pgdev] ls -la \/tmp\/archived_wal\/\ntotal 16392\ndrwxr-xr-x  2 postgres postgres     4096 Jul  4 12:08 .\ndrwxrwxrwt 10 root     root         4096 Jul  4 12:07 ..\n-rw-------  1 postgres postgres 16777216 Jul  4 12:08 000000010000000000000001\n<\/pre>\n<p>As expected we see the archived segment in the directory we specified in the archive command. In addition PostgreSQL generated a &#8220;.done&#8221; file in the archive_status directory in pg_wal:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11:\/home\/postgres\/ [pgdev] ls -la $PGDATA\/pg_wal\/archive_status\/\ntotal 8\ndrwx------ 2 postgres postgres 4096 Jul  4 12:08 .\ndrwx------ 3 postgres postgres 4096 Jul  4 12:08 ..\n-rw------- 1 postgres postgres    0 Jul  4 12:08 000000010000000000000001.done\n<\/pre>\n<p>So, whenever a segment was successfully archived you get the corresponding &#8220;.done&#8221; file. When do you see &#8220;.ready&#8221; files then? The answer is simple: Exactly when the opposite happens: Archiving of a segment failed for whatever reason:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11:\/home\/postgres\/ [pgdev] psql -c \"alter system set archive_command='\/bin\/false'\" postgres\nALTER SYSTEM\npostgres@debian11:\/home\/postgres\/ [pgdev] psql -c \"select pg_reload_conf()\";\n pg_reload_conf \n----------------\n t\n(1 row)\npostgres@debian11:\/home\/postgres\/ [pgdev] psql -c \"select pg_switch_wal()\" postgres\n pg_switch_wal \n---------------\n 0\/2000160\n(1 row)\npostgres@debian11:\/home\/postgres\/ [pgdev] ls -la $PGDATA\/pg_wal\/archive_status\/\ntotal 8\ndrwx------ 2 postgres postgres 4096 Jul  4 12:13 .\ndrwx------ 3 postgres postgres 4096 Jul  4 12:12 ..\n-rw------- 1 postgres postgres    0 Jul  4 12:13 000000010000000000000002.ready\n<\/pre>\n<p>This file will be there until archiving succeeds again:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11:\/home\/postgres\/ [pgdev] psql -c \"alter system set archive_command='test ! -f \/tmp\/archived_wal\/%f &amp;&amp; cp %p \/tmp\/archived_wal\/%f'\" postgres\nALTER SYSTEM\npostgres@debian11:\/home\/postgres\/ [pgdev] psql -c \"select pg_reload_conf()\";\n pg_reload_conf \n----------------\n t\n(1 row)\npostgres@debian11:\/home\/postgres\/ [pgdev] ls -la $PGDATA\/pg_wal\/archive_status\/\ntotal 8\ndrwx------ 2 postgres postgres 4096 Jul  4 12:15 .\ndrwx------ 3 postgres postgres 4096 Jul  4 12:12 ..\n-rw------- 1 postgres postgres    0 Jul  4 12:13 000000010000000000000002.done\n<\/pre>\n<p>When you see many &#8220;.ready&#8221; files this can either mean your archive command is currently failing or load on the system is so high, that archiving can not keep up.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When you run PostgreSQL workloads in production you must have a backup and restore implementation. Even for development instances, which are like production for the developers, a well-tested backup and restore procedure sometimes must be in place. Community PostgreSQL comes with pg_basebackup to help you with creating a full consistent backup of your PostgreSQL cluster. [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-16519","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>What are these *.ready and *.done files for in PostgreSQL? - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What are these *.ready and *.done files for in PostgreSQL?\" \/>\n<meta property=\"og:description\" content=\"When you run PostgreSQL workloads in production you must have a backup and restore implementation. Even for development instances, which are like production for the developers, a well-tested backup and restore procedure sometimes must be in place. Community PostgreSQL comes with pg_basebackup to help you with creating a full consistent backup of your PostgreSQL cluster. [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-07-02T09:18:34+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/__trashed-3\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/__trashed-3\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"What are these *.ready and *.done files for in PostgreSQL?\",\"datePublished\":\"2021-07-02T09:18:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/__trashed-3\\\/\"},\"wordCount\":367,\"commentCount\":4,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/__trashed-3\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/__trashed-3\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/__trashed-3\\\/\",\"name\":\"What are these *.ready and *.done files for in PostgreSQL? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2021-07-02T09:18:34+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/__trashed-3\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/__trashed-3\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/__trashed-3\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What are these *.ready and *.done files for in PostgreSQL?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"What are these *.ready and *.done files for in PostgreSQL? - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/","og_locale":"en_US","og_type":"article","og_title":"What are these *.ready and *.done files for in PostgreSQL?","og_description":"When you run PostgreSQL workloads in production you must have a backup and restore implementation. Even for development instances, which are like production for the developers, a well-tested backup and restore procedure sometimes must be in place. Community PostgreSQL comes with pg_basebackup to help you with creating a full consistent backup of your PostgreSQL cluster. [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/","og_site_name":"dbi Blog","article_published_time":"2021-07-02T09:18:34+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"What are these *.ready and *.done files for in PostgreSQL?","datePublished":"2021-07-02T09:18:34+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/"},"wordCount":367,"commentCount":4,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/__trashed-3\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/","url":"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/","name":"What are these *.ready and *.done files for in PostgreSQL? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-07-02T09:18:34+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/__trashed-3\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/__trashed-3\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"What are these *.ready and *.done files for in PostgreSQL?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16519","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=16519"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16519\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16519"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16519"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16519"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16519"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}