Re: regex failing

[Sergey Fukanchik]

On Tue, 27 Jun 2023 at 14:59, Zahir Lalani  wrote:
>
> Hi All
>
>
>
> Got a weird one. I am using the regex below to case match numeric only values.
>
>
>
> '^([0-9]+[.]?[0-9]*)$'
>
>
>
> This works well by and large but not for a value like “1234:567”. This seems 
> to match positive and then fails due to not being an actual number in the 
> subsequent cast.
>
>
>
> Any ideas? (PG13)
>
>
>
> Z
>
>
When executed from psql it works ok. What tool did you use to run your
query? I would suspect that the tool does interpret square brackets
somehow and your regex actually becomes  '^([0-9]+.?[0-9]*)$' which
matches any character between digits.

If you enable query logging in your system - either uncomment
log_statement = 'all' in postgresql.conf  or run ALTER SYSTEM SET
log_statement = 'all'; and restart, then you will find the actual
query in server's log file:
2023-06-27 16:12:35.221 EDT [23580] LOG:  statement: select '1234:56'
~ '^([0-9]+[.]?[0-9]*)$';

make sure it remains the same.
--
Sergey

Re: Orphan files filling root partition after crash

[Sergey Fukanchik]

Hi Dimitrios,
Do you have wal archiving enabled?
$PGDATA/pg_wal/ is a bit different from tablespaces (including
"default" one). It stores transaction journal.
Instructions are here:
https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-ARCHIVING-WAL
Some more info here:
https://www.postgresql.org/docs/current/wal-intro.html and here
https://www.postgresql.org/docs/current/wal-configuration.html
---
Sergey

On Wed, 28 Feb 2024 at 14:18, Dimitrios Apostolou  wrote:
>
> Hello list,
>
> yesterday I was doing:
>
> ALTER TABLE x ADD UNIQUE ... , ADD PRIMARY KEY ...;
>
> The table is almost a billion rows long but lies in its own TABLESPACE
> that has plenty of space.  But apparently the ALTER TABLE command is
> writing a lot to the default tablespace (not the temp_tablespace, that is
> already moved to a different partition).
>
> That quickly filled up the 50GB free space in my root partition:
>
>
> 20:18:04.222 UTC [94144] PANIC:  could not write to file 
> "pg_wal/xlogtemp.94144": No space left on device
> [...]
> 20:19:11.578 UTC [94140] LOG:  WAL writer process (PID 94144) was terminated 
> by signal 6: Aborted
> 20:19:11.578 UTC [94140] LOG:  terminating any other active server processes
>
>
> After postgresql crashed and restarted, the disk space in the root
> partition was still not freed! I believe this is because of "orphaned
> files" as discussed in mailing list thread [1].
>
> [1] 
> https://www.postgresql.org/message-id/CAN-RpxDBA7HbTsJPq4t4VznmRFJkssP2SNEMuG%3DoNJ%2B%3DsxLQew%40mail.gmail.com
>
> I ended up doing some risky actions to remediate the problem: Find the
> filenames that have no identically named "oid" in pg_class, and delete
> (move to backup) the biggest ones while the database is stopped.
> Fortunately the database started up fine after that!
>
> So what is the moral of the story? How to guard against this?
>
> Why did the database write so much to the default tablespace, even when
> both the table and the temp tablespace are elsewhere?  Also should one
> always keep the default tablespace away from the wal partition? (I guess
> it would have helped to avoid the crash, but the ALTER TABLE command would
> have still run out of space, so I'm not sure if the orphan files would
> have been avoided)?
>
> Needless to say, I would have hoped the database cleaned-up after itself
> even after an uncontrolled crash, or that it provided some tools for the
> job. (I tried VACUUM FULL on the table, but the orphaned files did not go
> away).
>
> My postgresql version is 16.2 installed on Ubuntu.
>
> Thank you,
> Dimitris
>
>
>


-- 
Sergey

Re: Disk is filling up with large files. How can I clean?

[Sergey Fukanchik]

Another possibility is orphaned files.
See 
https://www.dbi-services.com/blog/can-there-be-orphaned-data-files-in-postgresql/
and https://github.com/bdrouvot/pg_orphaned
--
Sergey

On Wed, 9 Oct 2024 at 19:02, Greg Sabino Mullane  wrote:
>
> On Wed, Oct 9, 2024 at 4:10 AM Mikael Petterson  
> wrote:
>>
>> Hi,
>>
>> I find our disk is filling up.
>>
>> sudo find /var/lib -type f -size +100M -exec ls -lh {} \; | awk '{ print $9 
>> ": " $5 }'
>
> ...
>
> Those files only add up to about 30GB. That's pretty small these days : time 
> for a bigger disk? Or perhaps the space is elsewhere: probably want to do 
> something like
> sudo find / -xdev -maxdepth 2 -exec du -sh {} \; | grep -E 'G|M' | sort -g
>
> Cheers,
> Greg
>


-- 
Sergey