Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t8O0w-003rxe-1Y for pgsql-admin@arkaria.postgresql.org; Tue, 05 Nov 2024 18:10:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t8O0t-00GnxK-Du for pgsql-admin@arkaria.postgresql.org; Tue, 05 Nov 2024 18:10:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t8O0t-00GnxC-1E for pgsql-admin@lists.postgresql.org; Tue, 05 Nov 2024 18:10:47 +0000 Received: from mail-ot1-x331.google.com ([2607:f8b0:4864:20::331]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t8O0o-000N5Q-SW for pgsql-admin@postgresql.org; Tue, 05 Nov 2024 18:10:47 +0000 Received: by mail-ot1-x331.google.com with SMTP id 46e09a7af769-7180ab89c58so2613834a34.1 for ; Tue, 05 Nov 2024 10:10:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1730830242; x=1731435042; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=XF0/7DAxBsj/Rj4mRA8IGqcIuv5b0E4zMMYtCIrS+JU=; b=E+erXiz22ncwvHEnxo885yJpDCGXVSpJPivhkZE6XFi1LarpWZzJhrNZH46wGFpQdR KR5sT+9kLfx59EwPssylRRBLvWicA6nkXBbchzX7KpRqcVKq49oiYidxjW6Oy3XYJHMh MFMB3ZlTKiALl06Zku09+7Cq7S3odIYEpQKzN3189rrEm0rSzKbVb317iSJPtSoz6OL9 /ni12NmACwQ343u0nMHrwHzsaScUU7cSe8p0YfS5nu/QqioNpma2Kttlz3ODR2lgB+gj vPTn/rhGbDfHT8MSV9by064vupK3rIQGt2BovUhpByEs7vj5SdmfFHA9PA+OjLs68595 onuQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1730830242; x=1731435042; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=XF0/7DAxBsj/Rj4mRA8IGqcIuv5b0E4zMMYtCIrS+JU=; b=QRwOOGq0eAAvpt4KzO8WB5balSf2WbtHp6qVBvL6aYxZRk+9NAuNTLMc6rVrevrXws 5o5NKR69aXz6wqQfGEym/FSC+0x+va8PfGXjJFYNP7ZGMcoFO9ORPkpDGGDnNRG7bZhz OnoGUt5gbI+KsIiimXbvom0I82kP4Suuk4Z2RslhE64H8SMOdw3mBHD42jmV7KT3P2m9 NFtNKofgqe08dlqLMSKNijBDynUibtAHYFd8pS1z9WULo+XO5HXjZI39hHoyqdIBDAvg kDooKHScI9TE2y4IeeXXw/80G+HPAzOBkeytKYBOmz2aVLTTcAIA2rCFmfPdJ3LJ9CPC R1/Q== X-Gm-Message-State: AOJu0Yx9VDtmoDsFqrWJ8t005MngArBVbGzB+4pPQDpKt6l+BjIk2m2l rurVpL6UqPBkO+lqslJeha5SKAlCBMjo8k/+4EpwpEU52v/0TOpWIA87nd6Uz05V/voltwmOkhc 23pDbfieblJOdG3bedz7aR4wjdxXnlQ== X-Google-Smtp-Source: AGHT+IFq4rHyvt1NgNwd+zW0S5l6IE8aH/T8xNMyfwhcXVJU00RINR2RJr4Aknfsr3BKRQMfAzueT6mQai16jrVuq/c= X-Received: by 2002:a05:6830:6d18:b0:717:f701:4842 with SMTP id 46e09a7af769-7189b559506mr21691938a34.28.1730830241661; Tue, 05 Nov 2024 10:10:41 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 5 Nov 2024 13:10:30 -0500 Message-ID: Subject: Re: PostgreSQL historical database To: pgsql-admin Content-Type: multipart/alternative; boundary="000000000000b8961406262e51a0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b8961406262e51a0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Nov 5, 2024 at 12:30=E2=80=AFPM Erik Serrano = wrote: > > Dear Sirs, > > I'll tell you a little about what I need. Normally, during the day, > records are made or recorded in the main database, which at the end of th= e > day are consolidated (accounting closings) and are recorded in the > database. In order not to make the main database grow without measure > (which will only maintain the range between 3 months to 1 year). For this > reason, this data must be transferred to another database so that it last= s > over time and can be consulted by other areas. (This action is done human= ly > every day of the year at the end of the day) > Therefore, the project seeks to be able to carry out this extraction of > the consolidated data to another database, but automatically. > > I was thinking of doing this with some triggers or with jobs that allow m= e > to carry out these actions. I also thought of creating a replication of > only the consolidated tables to the new historical database server, but I > have not yet defined the method. > > That's why I need to know if there is a tool that allows me to create thi= s > database. > psql in a bash script, run in a cron job. I wrote something quite similar that runs every month to: 1. copy old records into CSV files, 2. move them to S3 buckets, 3. delete the rows, 4. VACUUM and ANALYZE the tables. If there are FK relationships, you'll have to determine the proper order in which to delete them, but that's relatively easy and just has to be done once. (The only difference is that your Step 2 would be "copy them into the new database".) Depending on the number of rows per year, table partitioning might also simplify things. Or it could add complexity and slow things down; you'll have to test that yourself. > I hope this clarifies a little the scope of the new historical database. > > Thank you very much in advance > Regards > > > *Erik R. Serrano Saavedra* > * Data Base Administrator* > > > > El mar, 5 nov 2024 a las 12:37, Samed YILDIRIM () > escribi=C3=B3: > >> Hello Erik, >> >> It is not very clear for me what you are looking for. But, pg_bitemporal >> may be answer for you. I recommend to you to check the repository below.= If >> this is not what you want, can you elaborate a little more? >> >> https://github.com/hettie-d/pg_bitemporal >> >> Best regards. >> Samed YILDIRIM >> >> On Tue, 5 Nov 2024, 17:31 Erik Serrano, wrote: >> >>> Dear, >>> >>> Along with greetings, I would like to ask if there is any product, way >>> (architecture), system that allows me to create a historical database f= rom >>> a main transactional database in PostgreSQL. >>> >>> I thank you in advance for any contributions that help me to approach >>> this new project. >>> >>> Thank you very much, Guys, >>> Regards >>> >>> >>> >>> *Erik R. Serrano Saavedra* >>> * Data Base Administrator* >>> >>> >> --=20 Death to , and butter sauce. Don't boil me, I'm still alive. crustacean! --000000000000b8961406262e51a0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Nov 5, 2024 at 12:30=E2=80=AFPM E= rik Serrano <eserranos@gmail.com<= /a>> wrote:

Dear Sirs,

I'll tell you a little about what I nee= d. Normally, during the day, records are made or recorded in the main datab= ase, which at the end of the day are consolidated (accounting closings) and= are recorded in the database. In order not to make the main database grow = without measure (which will only maintain the range between 3 months to 1 y= ear). For this reason, this data must be transferred to another database so= that it lasts over time and can be consulted by other areas. (This action = is done humanly every day of the year at the end of the day)
Therefore, = the project seeks to be able to carry out this extraction of the consolidat= ed data to another database, but automatically.

I was thinking of do= ing this with some triggers or with jobs that allow me to carry out these a= ctions. I also thought of creating a replication of only the consolidated t= ables to the new historical database server, but I have not yet defined the= method.

That's why I need to know if there is a tool that allow= s me to create this database.
=

2. move them to S3 buckets,
3. delete the rows,=C2=A0
4. VACUUM and ANALYZE the tabl= es.

If there are FK relationships, you'll have= to determine the proper order in which to delete them, but that's rela= tively easy and just has to be done once.

(The onl= y difference is that your Step 2 would be "copy them into the new data= base".)

Depending on the number of rows per y= ear, table partitioning might also simplify things.=C2=A0 Or it could add c= omplexity and slow things down; you'll have to test that yourself.

<= div dir=3D"ltr">

I hope this clarifies a little th= e scope of the new historical database.

Thank you very much in advan= ce
Regards


<= font size=3D"4">Erik R. Serrano Saavedra
=C2=A0 =C2=A0 =C2=A0 Data Base Administrator



El mar, 5 nov 2024 a las 12:37, S= amed YILDIRIM (<sa= med@reddoc.net>) escribi=C3=B3:
Hello Erik,

It is not very clear for me what you are = looking for. But, pg_bitemporal may be answer for you. I recommend to you t= o check the repository below. If this is not what=C2=A0you want, can you el= aborate a little=C2=A0more?


Bes= t regards.
Samed YILDIRIM

On Tue, 5 Nov 2024, 17:31 Erik Serrano, &= lt;eserranos@gmail= .com> wrote:
Dear,

Along with greetings, I would like t= o ask if there is any product, way (architecture), system that allows me to= create a historical database from a main transactional database in Postgre= SQL.

I thank you in advance for any contributions that help me to ap= proach this new project.

Thank you very much, Guys,
Regards
=


Erik R. Serrano Saavedra
<= div>=C2=A0 =C2=A0 =C2=A0=C2=A0Data Base Ad= ministrator
=C2=A0


--
Death to <Redacted>, and butter sauce.
Don't b= oil me, I'm still alive.
<Redacted> crustacean!
--000000000000b8961406262e51a0--