public inbox for [email protected]
help / color / mirror / Atom feedPostgresql Database and PG_WAL locations
8+ messages / 5 participants
[nested] [flat]
* Postgresql Database and PG_WAL locations
@ 2024-08-29 18:18 Henry Ashu <[email protected]>
2024-08-29 18:21 ` Re: Postgresql Database and PG_WAL locations Matthew Tice <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Henry Ashu @ 2024-08-29 18:18 UTC (permalink / raw)
To: [email protected]
I have a database that's about 2TB in size, and I want to place the
database files in a separate mount point(PGDATA) and the log files in a
different mount point(PG_WAL). What's your take on this?.
Thank you
*Henry Ashu*
Database Administrator
*o:* 503.672.5114 | *f:* 800.551.8821 | DAT.com
[image: DAT Solutions] <https://www.dat.com/; [image: Like us on
Facebook] <https://www.facebook.com/loadboards; [image: Follow us on
Twitter] <https://twitter.com/loadboards; [image: Connect with us on
LinkedIn] <https://www.linkedin.com/company/datsolutions; [image: Visit
our YouTube Channel] <https://www.youtube.com/datloadboards; [image: Visit
us on Instagram] <https://instagram.com/dat_solutions;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql Database and PG_WAL locations
2024-08-29 18:18 Postgresql Database and PG_WAL locations Henry Ashu <[email protected]>
@ 2024-08-29 18:21 ` Matthew Tice <[email protected]>
2024-08-29 19:02 ` Re: Postgresql Database and PG_WAL locations Ron Johnson <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Matthew Tice @ 2024-08-29 18:21 UTC (permalink / raw)
To: ; +Cc: [email protected]
> On Aug 29, 2024, at 12:18 PM, Henry Ashu <[email protected]> wrote:
>
> I have a database that's about 2TB in size, and I want to place the database files in a separate mount point(PGDATA) and the log files in a different mount point(PG_WAL). What's your take on this?.
Take a look at https://wiki.postgresql.org/wiki/Installation_and_Administration_Best_practices
Essentially, yes, you will want your WAL and data stored on different devices (or the very least, different partitions).
> Thank you
>
> Henry Ashu
>
> Database Administrator
> o: 503.672.5114 | f: 800.551.8821 | DAT.com
>
> <https://www.dat.com/; <https://www.facebook.com/loadboards; <https://twitter.com/loadboards; <https://www.linkedin.com/company/datsolutions; <https://www.youtube.com/datloadboards; <https://instagram.com/dat_solutions;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql Database and PG_WAL locations
2024-08-29 18:18 Postgresql Database and PG_WAL locations Henry Ashu <[email protected]>
2024-08-29 18:21 ` Re: Postgresql Database and PG_WAL locations Matthew Tice <[email protected]>
@ 2024-08-29 19:02 ` Ron Johnson <[email protected]>
2024-08-29 19:11 ` Re: Postgresql Database and PG_WAL locations Matthew Tice <[email protected]>
2024-08-29 19:14 ` Re: Postgresql Database and PG_WAL locations Scott Ribe <[email protected]>
2024-09-02 10:30 ` Re: Postgresql Database and PG_WAL locations Laurenz Albe <[email protected]>
0 siblings, 3 replies; 8+ messages in thread
From: Ron Johnson @ 2024-08-29 19:02 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Thu, Aug 29, 2024 at 2:22 PM Matthew Tice <[email protected]> wrote:
> On Aug 29, 2024, at 12:18 PM, Henry Ashu <[email protected]> wrote:
>
> I have a database that's about 2TB in size, and I want to place the
> database files in a separate mount point(PGDATA) and the log files in a
> different mount point(PG_WAL). What's your take on this?.
>
>
> Take a look at
> https://wiki.postgresql.org/wiki/Installation_and_Administration_Best_practices
>
> Essentially, yes, you will want your WAL and data stored on different
> devices (or the very least, different partitions).
>
Is that recommendation still valid? After all, that was written when *15
years old* Sun Studio 12 was still pertinent. Times have changed since
then. Disks are much, *much* bigger.
--
Death to America, and butter sauce.
Iraq lobster!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql Database and PG_WAL locations
2024-08-29 18:18 Postgresql Database and PG_WAL locations Henry Ashu <[email protected]>
2024-08-29 18:21 ` Re: Postgresql Database and PG_WAL locations Matthew Tice <[email protected]>
2024-08-29 19:02 ` Re: Postgresql Database and PG_WAL locations Ron Johnson <[email protected]>
@ 2024-08-29 19:11 ` Matthew Tice <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Matthew Tice @ 2024-08-29 19:11 UTC (permalink / raw)
To: ; +Cc: Pgsql-admin <[email protected]>
> On Aug 29, 2024, at 1:02 PM, Ron Johnson <[email protected]> wrote:
>
> On Thu, Aug 29, 2024 at 2:22 PM Matthew Tice <[email protected] <mailto:[email protected]>> wrote:
>>> On Aug 29, 2024, at 12:18 PM, Henry Ashu <[email protected] <mailto:[email protected]>> wrote:
>>>
>>> I have a database that's about 2TB in size, and I want to place the database files in a separate mount point(PGDATA) and the log files in a different mount point(PG_WAL). What's your take on this?.
>>>
>>
>> Take a look at https://wiki.postgresql.org/wiki/Installation_and_Administration_Best_practices
>>
>> Essentially, yes, you will want your WAL and data stored on different devices (or the very least, different partitions).
>
>
> Is that recommendation still valid? After all, that was written when 15 years old Sun Studio 12 was still pertinent. Times have changed since then. Disks are much, much bigger.
Good point. I didn’t even notice that disclaimer at the top "Seek knowledge elsewhere, as the material in here predates Postgres 9 it seems.”…
But regarding the wal/data separation, I still think it’s relevant (isolating the IO between data files and wal). But I guess like anything else it depends on your workload (and, in this case, your storage subsystem capabilities).
>
> --
> Death to America, and butter sauce.
> Iraq lobster!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql Database and PG_WAL locations
2024-08-29 18:18 Postgresql Database and PG_WAL locations Henry Ashu <[email protected]>
2024-08-29 18:21 ` Re: Postgresql Database and PG_WAL locations Matthew Tice <[email protected]>
2024-08-29 19:02 ` Re: Postgresql Database and PG_WAL locations Ron Johnson <[email protected]>
@ 2024-08-29 19:14 ` Scott Ribe <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Scott Ribe @ 2024-08-29 19:14 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
> On Aug 29, 2024, at 1:02 PM, Ron Johnson <[email protected]> wrote:
>
> Is that recommendation still valid? After all, that was written when 15 years old Sun Studio 12 was still pertinent. Times have changed since then. Disks are much, much bigger.
We still have WAL and data having the same data written (at somewhat different times), so it's still useful to give them separate bandwidth to storage--for databases with high write loads.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql Database and PG_WAL locations
2024-08-29 18:18 Postgresql Database and PG_WAL locations Henry Ashu <[email protected]>
2024-08-29 18:21 ` Re: Postgresql Database and PG_WAL locations Matthew Tice <[email protected]>
2024-08-29 19:02 ` Re: Postgresql Database and PG_WAL locations Ron Johnson <[email protected]>
@ 2024-09-02 10:30 ` Laurenz Albe <[email protected]>
2024-09-02 13:15 ` Re: Postgresql Database and PG_WAL locations Ron Johnson <[email protected]>
2 siblings, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2024-09-02 10:30 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; Pgsql-admin <[email protected]>
On Thu, 2024-08-29 at 15:02 -0400, Ron Johnson wrote:
> On Thu, Aug 29, 2024 at 2:22 PM Matthew Tice <[email protected]> wrote:
> > > On Aug 29, 2024, at 12:18 PM, Henry Ashu <[email protected]> wrote:
> > >
> > > I have a database that's about 2TB in size, and I want to place the database
> > > files in a separate mount point(PGDATA) and the log files in a different mount
> > > point(PG_WAL). What's your take on this?.
> >
> >
> > Take a look at https://wiki.postgresql.org/wiki/Installation_and_Administration_Best_practices
> >
> > Essentially, yes, you will want your WAL and data stored on different devices
> > (or the very least, different partitions).
>
> Is that recommendation still valid? After all, that was written when 15 years old
> Sun Studio 12 was still pertinent. Times have changed since then. Disks are much, much bigger.
I think the advice is still valid.
Today you'd have different filesystems on different logical volumes rather
than different physical disks, but it is still a good idea to separate data and WAL,
so that they cannot fill up each other's file system.
I'd actually define a third file system for the PostgreSQL log files, for
the same reason.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql Database and PG_WAL locations
2024-08-29 18:18 Postgresql Database and PG_WAL locations Henry Ashu <[email protected]>
2024-08-29 18:21 ` Re: Postgresql Database and PG_WAL locations Matthew Tice <[email protected]>
2024-08-29 19:02 ` Re: Postgresql Database and PG_WAL locations Ron Johnson <[email protected]>
2024-09-02 10:30 ` Re: Postgresql Database and PG_WAL locations Laurenz Albe <[email protected]>
@ 2024-09-02 13:15 ` Ron Johnson <[email protected]>
2024-09-02 13:38 ` Re: Postgresql Database and PG_WAL locations Laurenz Albe <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Ron Johnson @ 2024-09-02 13:15 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Mon, Sep 2, 2024 at 6:30 AM Laurenz Albe <[email protected]>
wrote:
> On Thu, 2024-08-29 at 15:02 -0400, Ron Johnson wrote:
> > On Thu, Aug 29, 2024 at 2:22 PM Matthew Tice <[email protected]> wrote:
> > > > On Aug 29, 2024, at 12:18 PM, Henry Ashu <[email protected]> wrote:
> > > >
> > > > I have a database that's about 2TB in size, and I want to place the
> database
> > > > files in a separate mount point(PGDATA) and the log files in a
> different mount
> > > > point(PG_WAL). What's your take on this?.
> > >
> > >
> > > Take a look at
> https://wiki.postgresql.org/wiki/Installation_and_Administration_Best_practices
> > >
> > > Essentially, yes, you will want your WAL and data stored on different
> devices
> > > (or the very least, different partitions).
> >
> > Is that recommendation still valid? After all, that was written when 15
> years old
> > Sun Studio 12 was still pertinent. Times have changed since then.
> Disks are much, much bigger.
>
> I think the advice is still valid.
>
> Today you'd have different filesystems on different logical volumes rather
> than different physical disks,
None of our disks are physical; they're all SAN LUNs.
> but it is still a good idea to separate data and WAL,
> so that they cannot fill up each other's file system.
>
Regular checkpoints, transactions(*) that don't stay open for hours or
days, and monitoring replication to ensure that it keeps replicating data
instead of piling up on the primary server all solve that problem
Honestly... it's been *YEARS* since I've seen that problem.
Besides, "disks are cheap", right?
*COPY statements don't count.
I'd actually define a third file system for the PostgreSQL log files, for
> the same reason.
>
I set log_directory to /var/log/postgresql because logs go in
/var/log. 😀 (pg_basebackup won't replicate it, which is also handy.) On
a separate partition so as to isolate PG data from other application and OS
data (and lets me manage capacity via a script).
Ditto the PgBackrest directory: isolate PG data from other application and
OS data.
We'd have to put them on separate mount points anyway, since the VM build
process doesn't like large / and /boot disks.
--
Death to America, and butter sauce.
Iraq lobster!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql Database and PG_WAL locations
2024-08-29 18:18 Postgresql Database and PG_WAL locations Henry Ashu <[email protected]>
2024-08-29 18:21 ` Re: Postgresql Database and PG_WAL locations Matthew Tice <[email protected]>
2024-08-29 19:02 ` Re: Postgresql Database and PG_WAL locations Ron Johnson <[email protected]>
2024-09-02 10:30 ` Re: Postgresql Database and PG_WAL locations Laurenz Albe <[email protected]>
2024-09-02 13:15 ` Re: Postgresql Database and PG_WAL locations Ron Johnson <[email protected]>
@ 2024-09-02 13:38 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Laurenz Albe @ 2024-09-02 13:38 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; Pgsql-admin <[email protected]>
On Mon, 2024-09-02 at 09:15 -0400, Ron Johnson wrote:
> > but it is still a good idea to separate data and WAL,
> > so that they cannot fill up each other's file system.
>
> Regular checkpoints, transactions(*) that don't stay open for hours
> or days, and monitoring replication to ensure that it keeps replicating
> data instead of piling up on the primary server all solve that problem
Right, if everything is working as it should, monitoring is in place
etc. this will never happen.
But I believe that paranoia is a virtue for the DBA, and it is better
to have a second line of defense than a crash of the database.
> Honestly... it's been YEARS since I've seen that problem.
I see it all the time (on systems I didn't set up).
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-09-02 13:38 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-29 18:18 Postgresql Database and PG_WAL locations Henry Ashu <[email protected]>
2024-08-29 18:21 ` Matthew Tice <[email protected]>
2024-08-29 19:02 ` Ron Johnson <[email protected]>
2024-08-29 19:11 ` Matthew Tice <[email protected]>
2024-08-29 19:14 ` Scott Ribe <[email protected]>
2024-09-02 10:30 ` Laurenz Albe <[email protected]>
2024-09-02 13:15 ` Ron Johnson <[email protected]>
2024-09-02 13:38 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox