Subject: Re: Backup

From: Andy Hartman <[email-masked]>
Date: Wed, 23 Oct 2024 10:07:02 -0400

I have been testing different backup methods suggested and wonder if I have
all my files used to load the DB .. could that be considered a backup...
obviously need to have schema backup in case of problem, but that seems to
be easily re-created

Thoughts?

On Fri, Oct 18, 2024 at 2:30 PM Adrian Klaver <[email-masked]>
wrote:

>
>
> On 10/18/24 11:25 AM, Andy Hartman wrote:
> > Windows Server 2022
> >
> > I would like a backup process similar to what I'm used to using in the
> > mssql world if that's  possible with Postgres... I will be
> > loading monthly archive data to Postgresql so that's why I probably only
> > need once a backup .. once loaded data is just viewed thru an app... no
> > updates..
> >
> > in mssql you create the .bak and easily restorable ....  that's what I'm
> > looking for with Postgres
> >
>
> Then pg_dump for database backup:
>
> https://www.postgresql.org/docs/current/app-pgdump.html
>
> and pg_dumpall for entire cluster backup:
>
> https://www.postgresql.org/docs/current/app-pg-dumpall.html
>
> > thanks for replies..
> >
> >
> >
> > On Fri, Oct 18, 2024 at 1:00 PM Adrian Klaver <[email-masked]
> > <mailto:[email-masked]>> wrote:
> >
> >     On 10/18/24 09:52, Andy Hartman wrote:
> >
> >     Reply to list also
> >     Ccing list.
> >
> >      > Windows 22 and Postgres 16.4
> >      >
> >     Is that Windows Server 2022 or Windows 10/11 with the 22H2 update?
> >
> >     You also need to provide a more detailed description of what you want
> >     the backup to do?
> >
> >      >
> >      >
> >      > On Fri, Oct 18, 2024 at 12:04 PM Adrian Klaver
> >      > <[email-masked] <mailto:[email-masked]>
> >     <mailto:[email-masked]
> >     <mailto:[email-masked]>>> wrote:
> >      >
> >      >     On 10/18/24 05:39, Andy Hartman wrote:
> >      >      > I would like to use the closest thing to a mssql backup...
> >      >      >
> >      >
> >      >     As in?
> >      >
> >      >     Searching on 'mssql backup' returns a  lot of choices. You
> >     will need to
> >      >     be more specific on what you want to achieve. Also be
> >     specific about
> >      >     what versions of OS and Postgres you are using.
> >      >
> >      >     --
> >      >     Adrian Klaver
> >      > [email-masked] <mailto:[email-masked]>
> >     <mailto:[email-masked] <mailto:[email-masked]
> >>
> >      >
> >
> >     --
> >     Adrian Klaver
> >     [email-masked] <mailto:[email-masked]>
> >
>
> --
> Adrian Klaver
> [email-masked]
>

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 23 Oct 2024 08:50:29 -0700

On 10/23/24 07:07, Andy Hartman wrote:
> I have been testing different backup methods suggested and wonder if I 
> have all my files used to load the DB .. could that be considered a 
> backup... obviously need to have schema backup in case of problem, but 
> that seems to be easily re-created
> 
> Thoughts?

Did you actually read the information at the links provided?

By default pg_dump and pg_dumpall will dump both the schema definitions 
as well as the data.

Yes it is a backup of the database(s) at the point in time you initiate 
the dump. If you want something different then you are probably looking 
for one of the replication methods. You still have not answered:

" You also need to provide a more detailed description of what you want
the backup to do?"

so it is hard to say what method is best for your situation.

---

From: Andy Hartman <[email-masked]>
Date: Wed, 23 Oct 2024 13:43:02 -0400

I'm using the DB as a History repository... I'm pulling info from a Mssql
db using a P/S script to a csv file then Import that to Postgres I only
keep 6 months live on the mssql db and roll everything else to Postgres
Arch DB.

  After I load this latest month to Postgres I just need to backup, or
should I just retain my .csv files as a Backup... I have 2 tables that I
load to Hist and one table holds the images...

THanks .

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 23 Oct 2024 12:47:01 -0700

On 10/23/24 10:43, Andy Hartman wrote:
> I'm using the DB as a History repository... I'm pulling info from a 
> Mssql db using a P/S script to a csv file then Import that to Postgres I 
> only keep 6 months live on the mssql db and roll everything else to 
> Postgres Arch DB.
> 
Your choice, though the factors I would consider would be:

1) The process you use to load the CSV files into Postgres and whether 
that is faster then loading the data from the dump file?

2) Loading from the CSV files directly would be month by month steps 
versus loading from the dump file which would be cumulative to the last 
backup.

Also, what format are the image files in and are they part of the CSV 
files or separate?

---

From: Andy Hartman <[email-masked]>
Date: Wed, 23 Oct 2024 19:59:26 -0400

The images are part of the csv file...