Re: Safe to delete files?
On 4/12/19 3:11 PM, Paul van der Linden wrote: Hi, For my process, I needed to drop all the tables in a tablespace except one which I truncated. After that I would have expected to have a couple of KB max in that folder, but there was about 200GB in it. Did you vacuum afterwards? There were 2 sets of files (, .1 .. .99, and the same for id2). Tried the various options from https://blog.2ndquadrant.com/postgresql-filename-to-table/ and oid2name (with -i), to trace it back to a table but all came up empty. Now this folder has a bit of a history spanning several postgres versions and upgrades, and sometime in the past one of the upgrades went horribly wrong, so my first thought was that this was possibly some leftovers from that mishap, but the filetimes were a bit later than that. Also hard to tell because those tables are used as write-once, read-alot so could not base the last usage on filedate. Normally I probably would dare to risk deleting those files, but after the dropping and truncating, the 2 files without extension had the time of drop/truncate and were 0 bytes in length (unfortunately I didn't check the filesize before drop/truncating). Are there other options to see if these files are leftovers from previous stuff and not used by postgres (so i can safely delete them)? Postgres 11, just one used database on it (the other one being a postgis template), running on windows server 2012. In replies please use reply to all... -- Angular momentum makes the world go 'round.
Re: Safe to delete files?
On 4/12/19 1:11 PM, Paul van der Linden wrote: Hi, For my process, I needed to drop all the tables in a tablespace except one which I truncated. After that I would have expected to have a couple of KB max in that folder, but there was about 200GB in it. There were 2 sets of files (, .1 .. .99, and the same for id2). Can you show the actual dir listing? Tried the various options from https://blog.2ndquadrant.com/postgresql-filename-to-table/ and oid2name (with -i), to trace it back to a table but all came up empty. Now this folder has a bit of a history spanning several postgres versions and upgrades, and sometime in the past one of the upgrades went horribly wrong, so my first thought was that this was possibly some leftovers from that mishap, but the filetimes were a bit later than that. Also hard to tell because those tables are used as write-once, read-alot so could not base the last usage on filedate. Normally I probably would dare to risk deleting those files, but after the dropping and truncating, the 2 files without extension had the time of drop/truncate and were 0 bytes in length (unfortunately I didn't check the filesize before drop/truncating). Are there other options to see if these files are leftovers from previous stuff and not used by postgres (so i can safely delete them)? Postgres 11, just one used database on it (the other one being a postgis template), running on windows server 2012. In replies please use reply to all... -- Adrian Klaver adrian.kla...@aklaver.com
Safe to delete files?
Hi, For my process, I needed to drop all the tables in a tablespace except one which I truncated. After that I would have expected to have a couple of KB max in that folder, but there was about 200GB in it. There were 2 sets of files (, .1 .. .99, and the same for id2). Tried the various options from https://blog.2ndquadrant.com/postgresql-filename-to-table/ and oid2name (with -i), to trace it back to a table but all came up empty. Now this folder has a bit of a history spanning several postgres versions and upgrades, and sometime in the past one of the upgrades went horribly wrong, so my first thought was that this was possibly some leftovers from that mishap, but the filetimes were a bit later than that. Also hard to tell because those tables are used as write-once, read-alot so could not base the last usage on filedate. Normally I probably would dare to risk deleting those files, but after the dropping and truncating, the 2 files without extension had the time of drop/truncate and were 0 bytes in length (unfortunately I didn't check the filesize before drop/truncating). Are there other options to see if these files are leftovers from previous stuff and not used by postgres (so i can safely delete them)? Postgres 11, just one used database on it (the other one being a postgis template), running on windows server 2012. In replies please use reply to all...
Re: multiple indexes on the same column
Got it! Thanks Andres and Tom! Tiff On Fri, Apr 12, 2019 at 1:07 PM Tom Lane wrote: > Andres Freund writes: > > On 2019-04-12 09:51:51 -0400, Tom Lane wrote: > >> Tiffany Thang writes: > >>> Can someone explain the use of creating multiple indexes on the same > >>> column? > > >> There is none, unless the indexes have different properties (e.g. > >> different opclasses and/or index AMs). > > > Well, it can be beneficial to create a new index concurrently, and then > > drop the old one concurrently. > > Right, but in that situation there's no intent to keep both indexes > in place. You're just putting up with extra overhead temporarily > as a means to avoid taking an exclusive lock. > > regards, tom lane >
Re: multiple indexes on the same column
Andres Freund writes: > On 2019-04-12 09:51:51 -0400, Tom Lane wrote: >> Tiffany Thang writes: >>> Can someone explain the use of creating multiple indexes on the same >>> column? >> There is none, unless the indexes have different properties (e.g. >> different opclasses and/or index AMs). > Well, it can be beneficial to create a new index concurrently, and then > drop the old one concurrently. Right, but in that situation there's no intent to keep both indexes in place. You're just putting up with extra overhead temporarily as a means to avoid taking an exclusive lock. regards, tom lane
Re: multiple indexes on the same column
Hi, On 2019-04-12 09:51:51 -0400, Tom Lane wrote: > Tiffany Thang writes: > > Can someone explain the use of creating multiple indexes on the same > > column? > > There is none, unless the indexes have different properties (e.g. > different opclasses and/or index AMs). Well, it can be beneficial to create a new index concurrently, and then drop the old one concurrently. Before v12 that's the only way to recreate an index during production, if it e.g. bloated. Greetings, Andres Freund
Re: Fwd: Postgresql with nextcloud in Windows Server
On 4/11/19 1:45 AM, 김준형 wrote: 1. It spends more resources but I think this setting endure that problem. Actually, after this setting, Windows server(include PostgreSQL server) endures that problem more. But I know it's not a solution. 2. No. MS-SQL SERVER, PostgreSQL Server, and Tomcat. What do you think of appropriate value of max_wal_size? Have no idea, mostly because the root cause of your issue has not been established. 3. I'll check these links until next weekend. Maybe I'll spend long time to adapt this suggestions. -- Adrian Klaver adrian.kla...@aklaver.com
Re: multiple indexes on the same column
Tiffany Thang writes: > Can someone explain the use of creating multiple indexes on the same > column? There is none, unless the indexes have different properties (e.g. different opclasses and/or index AMs). I'd suggest reading https://www.postgresql.org/docs/current/indexes.html regards, tom lane
Re: multiple indexes on the same column
On Fri, 12 Apr 2019 at 11:54, Tiffany Thang wrote: > Can you provide a scenario where creating multiple indexes on the same > column would be beneficial? > When you have too much disk space? When your table writes are too fast?
multiple indexes on the same column
Hi, Can someone explain the use of creating multiple indexes on the same column? How would the optimizer determine which index to use? From my brief testing, the optimizer picked the latest created index, testidx3. Can you provide a scenario where creating multiple indexes on the same column would be beneficial? create table test (a int, b int); create index testidx1 on test (a); create index testidx2 on test (a); create index testidx3 on test (a); Thanks. Tiff
Re: When do vacuumed pages/tuples become available for reuse?
On 04/12/2019 08:39 AM, Michael Lewis wrote: Way to many indexes. I'm going to have a hard time convincing our programmers to get rid of any of them ) You can create (concurrently) an identical index with a new name, then drop old version concurrently and repeat for each. It doesn't help you figure out the root cause and how to prevent it from happening again, but gets you to a fresh start at least. Thanks for the tip. I believe other than increasing load temporarily creating a new index, dropping the old one and renaming the new one to the old one are transparent and (almost) lock-less operations? What would happen to user queries during DROP INDEX? Would they block on it, or, being unable to read it without blocking, will they pick the new one based on the same column(s)? And, likewise, is ALTER INDEX ... RENAME ... an instant operation, I hope?
