[Evergreen-dev] Postgres Database Mysery

[Galen Charlton]

Hi Brian,

Some possibilities I can see include:

- although you're running Pg 10, possibly some settings related to WAL file
generation and archiving are still pointing at subdirectories of 9.6.bak
- similar to above, maybe directories related to WAL are configured to be
under 10/, but are actually symlinks to subdirectories under 9.6.bak
- the upgrade was done using pg_upgradecluster --link --method=upgrade,
which would create hard links between files in the original 9.6 cluster and
the new 10 one. In that case, removing the links from the old cluster will
likely be safe (after of course doing a full backup)

To check whether the third case would be a possibility, compare the output
of "du -hs" as applied to each data directory as compared to "du -lhs". If
"du -lhs" shows that each data directory is larger as compared to "du -hs",
that would suggest that there are hard links present.

It would be helpful to know which files under 9.6.bak are open according to
lsof. If you do an ls -l on them, look at the number following the
permissions:

-rw-r--r-- *2* gmc gmc 2097152 Jul 10 17:33 /tmp/link-test/a/foo

That is the number of hard links to the file, so if it is larger than 1,
that would strongly suggest that the "pg_upgardecluster --link
--method=upgrade" scenario is what's at play.

Also, what is the output of pg_lsclusters? If it is still showing an
inactive cluster named "9.6.bak", pg_dropcluster can be used to remove it.

Regards,

Galen

On Wed, Jul 10, 2024 at 3:02 PM Brian Holda via Evergreen-dev <
evergreen-dev at list.evergreen-ils.org> wrote:

> On my Evergreen Database server, in /var/lib/postgresql/
>
> I have the following 4 directories:
>
> 1) 9.6.bak/ = 111GB, edits happening every minute
> 2) 10/ = 58GB, last edited a couple hours ago
> 3) 9.6/ = 45MB, last edited 1 year ago
> 4) backups/ = 2.2GB, last edited 2AM today
>
> The stumper to me is the 9.6.bak. If we're using psql 10, and already have
> a backup directory updated daily, why are things actively writing to
> 9.6.bak/?
>
> We need to free up some storage, and I was tempted to get rid of 9.6.bak,
> but don't want to without figuring out what is writing to it (and why).
>
> If helpful, I find no instance of 9.6.bak in
> ps -ef | grep postgres
>
> But it did appear in
> lsof +D /var/lib/postgresql/9.6.bak
>
> Any clues?!
>
> Thanks,
> Brian
>
> Brian Holda
> Library Technology Manager
> Hekman Library
> Calvin University
> (616) 526-8673
>
> <https://library.calvin.edu/>
>
>
> _______________________________________________
> Evergreen-dev mailing list
> Evergreen-dev at list.evergreen-ils.org
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-dev
>


-- 
Galen Charlton
Implementation and IT Manager
Equinox Open Library Initiative
gmc at equinoxOLI.org
https://www.equinoxOLI.org
phone: 877-OPEN-ILS (673-6457)
direct: 770-709-5581
<http://evergreen-ils.org>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-dev/attachments/20240710/211234a8/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Outlook-e1dwtvnk.png
Type: image/png
Size: 5783 bytes
Desc: not available
URL: <http://list.evergreen-ils.org/pipermail/evergreen-dev/attachments/20240710/211234a8/attachment-0001.png>