From: | Craig Ringer <craig(at)2ndquadrant(dot)com> |
---|---|
To: | Nicolas Lutic <n(dot)lutic(at)loxodata(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: PITR on DROP DATABASE, deleting of the database directory despite the recovery_target_time set before. |
Date: | 2019-11-19 00:40:39 |
Message-ID: | CAMsr+YGsh46i-muFB3Wm7d900q9Wgr5G+xsKd6h4488zy0_X8g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, 18 Nov 2019 at 18:48, Nicolas Lutic <n(dot)lutic(at)loxodata(dot)com> wrote:
> Dear Hackers,
>
> After a drop database
>
with FORCE?
> , he tried to recover the data on the last inserted transaction by using
> the recovery_target_time.
> The issue is the database is present in the system catalog but the
> directory was still deleted.
> Here the technical information of the database
> version 11
> default postgresql.conf except for this options
> wal_level = replica
> archive_mode = on
> archive_command = 'cp %p /tmp/wal_archive/%f '
> log_statement = 'all'
> log_min_messages = debug5
>
>
> The following method was used
>
> - create cluster
>
>
> - create database
>
>
> - create 1 table
>
>
> - create 1 index on 1 column
>
>
> - insert 1 rows
>
>
> - backup with pg_base_backup
>
>
> - insert 2 rows
>
> autocommit?
>
>
>
> - drop database
>
> force?
>
> - Change recovery behaviour in that case to prevent all xact
> operation to perform until COMMIT timestamp is checked against
> recovery_time bound (but it seems to be difficult as state
> https://www.postgresql.org/message-id/flat/20141125160629.GC21475%40msg.df7cb.de
> which also identifies the problem and tries to give some solutions. Maybe
> another way, as a trivial guess (all apologises) is to buffer immediate
> xacts until we have the commit for each and apply the whole buffer xact
> once the timestamp known (and checked agains recovery_target_time value);
>
>
> - The other way to improve this is to update PostgreSQL
> documentation by specifying that recovery_target_time cannot be used
> in this case. There should be multiple places where it can be stated.
> The best one (if only one) seems to be in
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/config.sgml;h=
> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/config.sgml;h=f83770350eda5625179526300c652f23ff29c9fe;hb=HEAD#l3400>
>
>
If this only happens when a DB is dropped under load with force, I lean
toward just documenting it as a corner case.
--
Craig Ringer http://www.2ndQuadrant.com/
2ndQuadrant - PostgreSQL Solutions for the Enterprise
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2019-11-19 01:26:37 | Re: [HACKERS] [WIP] Effective storage of duplicates in B-tree index. |
Previous Message | Craig Ringer | 2019-11-19 00:23:30 | Re: physical slot xmin dependency on logical slot? |