Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pw12b-0004PE-Hb for pgsql-general@arkaria.postgresql.org; Mon, 08 May 2023 13:36:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pw12Z-0007Qr-Nu for pgsql-general@arkaria.postgresql.org; Mon, 08 May 2023 13:36:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pw12Z-0007PO-C4 for pgsql-general@lists.postgresql.org; Mon, 08 May 2023 13:36:35 +0000 Received: from mail-yb1-xb34.google.com ([2607:f8b0:4864:20::b34]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pw12W-001N5u-NX for pgsql-general@lists.postgresql.org; Mon, 08 May 2023 13:36:34 +0000 Received: by mail-yb1-xb34.google.com with SMTP id 3f1490d57ef6-b9a6f17f2b6so24927022276.1 for ; Mon, 08 May 2023 06:36:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1683552990; x=1686144990; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=BSf2S8mZ7NLb7RCrB62Zvalb8DZSlP6kpJmkhmBAu1U=; b=PPP4/AVniw1XkIaDLyOXAlRzx4t89PMFxpYNGeoVoGw1P19AQsYnIzaW559pzYjkm0 o35TFz3q5KW4rcuVulm8nTgX/UThygNbLpLbeeZyzUfC0yEJII+T3TX8sVqa64LW+019 l29PORRyG/FWfj3H2aij1h9ViZOur9uufYu5+qRooryzFzR9zxlNFUZXP070MBImcpjZ BSqGWObgTA1lFanElyYObGzjYEnsJiowmpv3+4+tSXHUd566mnW+/gDCH1OuaJnp0LjJ QhsulGTmsVW5sakObx+zeM+GN/OG2DIg3oIY1xaKNMDSsNmtXbl9UccyjRSO/HjffBLO uCsw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1683552990; x=1686144990; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=BSf2S8mZ7NLb7RCrB62Zvalb8DZSlP6kpJmkhmBAu1U=; b=ZIvelWuY3VDbbgdu5aS9npHigQv3Otjn/ZKyvwrY+1a7prGoEqkm5EEWFTntKMg+sH HT4tEX3WQ1DWkgQ8INsvUSVuNCkcGbtl1THY9xTdxyp2P6S/tM8kAo8HYow94tJkpWcS oked0W3kZwQ30/y71CvmU13gDJEmPNnEHSFs7ZEaDqehj80LhI0KhChmMh/TV8aeKwIm fHA9aqk0nHcCS4mketIdAIrl2+quyb5UwKufy6jkJ/c78be4p5ExOjgec2L0McsWjxuL bJ3+4ocZkBeVDL6t27VylBDQyWdKuJRs2hDRj+MyvqWwz/tqU3O/EM/+Zpfm0oUWI4wd OG4w== X-Gm-Message-State: AC+VfDyJV7AskScXTzy3iKkS0VULlhSbdqyQcXnGPc1y91F+2GvkUsqk tOYVGoyRBK5bFz5PE300RGmBnvf6Tss= X-Google-Smtp-Source: ACHHUZ5sob1D3+9uuTzHkLJdTTrV10i6BomlR0jnJ2DvJJkFT5Q9dQXlbxWfsAMtVBhxhanMgHQEPQ== X-Received: by 2002:a81:1711:0:b0:559:debb:b3d5 with SMTP id 17-20020a811711000000b00559debbb3d5mr12258139ywx.24.1683552990663; Mon, 08 May 2023 06:36:30 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:c103:a15d:76e:31fc? ([2600:1700:bd2c:b810:c103:a15d:76e:31fc]) by smtp.googlemail.com with ESMTPSA id d201-20020a0ddbd2000000b0055a777e3c50sm2471768ywe.62.2023.05.08.06.36.29 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 08 May 2023 06:36:30 -0700 (PDT) Message-ID: Date: Mon, 8 May 2023 08:36:28 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: Additive backup and restore? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 5/8/23 05:24, Age Apache wrote: > I am designing a database for a web application. In the near future I will > require past data for Audit, Security and Analysis purpose. But storing > all that data will increase the database size. What are some well known > approaches to archiving data for later use without increasing the database > size? > > One approach I am thinking of is compressed additive backup Additive? Why not one compressed archive file per month per table? > and restore i.e. copy the rows of the tables that will be required later > and store them in a compressed form, and then delete those rows from the > table. And when the data is required then restore them from the backup > files in an additive way. > > Is there an easy way to achieve this, say via pg_dump? If the tables are partitioned on the relevant date field, then archiving old data will be "trivially" easy. If not, then it'll still be "easy": - COPY TO a file - compress it - store it somewhere. Reverse to "de-archive" the data. -- Born in Arizona, moved to Babylonia.