Re: Forcibly disconnect users from one database

2020-04-10 Thread Adrian Klaver
On 4/10/20 1:20 PM, Andrus wrote: Hi! Postgres 12 does not allow to drop database if it is in use. DROP DATABASE throws error: postgres@template1 ERROR:  database "mydb" is being accessed by other users postgres@template1 DETAIL:  There is 1 other session using the database. postgres@template

Re: Using of --data-checksums

2020-04-10 Thread Stephen Frost
Greetings, * Bruce Momjian (br...@momjian.us) wrote: > On Wed, Apr 8, 2020 at 11:54:34AM -0400, Stephen Frost wrote: > > * BGoebel (b.goe...@prisma-computer.de) wrote: > > > initdb --data-checksums "... help to detect corruption by the I/O system" > > > There is an (negligible?) impact on perform

Re: Using of --data-checksums

2020-04-10 Thread Bruce Momjian
On Wed, Apr 8, 2020 at 11:54:34AM -0400, Stephen Frost wrote: > Greetings, > > * BGoebel (b.goe...@prisma-computer.de) wrote: > > initdb --data-checksums "... help to detect corruption by the I/O system" > > There is an (negligible?) impact on performance, ok. > > > > Is there another reason N

Forcibly disconnect users from one database

2020-04-10 Thread Andrus
Hi! Postgres 12 does not allow to drop database if it is in use. DROP DATABASE throws error: postgres@template1 ERROR: database "mydb" is being accessed by other users postgres@template1 DETAIL: There is 1 other session using the database. postgres@template1 STATEMENT: DROP DATABASE "mydb"

Re: Huge number of pg_temp and pg_toast_temp schemas

2020-04-10 Thread Adrian Klaver
On 4/10/20 11:06 AM, Andrus wrote: Hi! Thank you. This is an unsurprising state, if you routinely have a hundred-plus connections that sometimes create temp tables.  Each such session needs schemas to keep its temp tables in.  The temp tables are deleted at session exit, but we don't bother to

Re: Huge number of pg_temp and pg_toast_temp schemas

2020-04-10 Thread Andrus
Hi! Thank you. This is an unsurprising state, if you routinely have a hundred-plus connections that sometimes create temp tables. Each such session needs schemas to keep its temp tables in. The temp tables are deleted at session exit, but we don't bother to remove the schema entries, figuring

Re: Huge number of pg_temp and pg_toast_temp schemas

2020-04-10 Thread Tom Lane
"Andrus" writes: > Hi! > Postgres 12 database contains huge number of pg_temp and pg_toast_temp > schemas named > pg_temp_1 .. pg_temp_126 > and > pg_toast_temp_1 .. pg_toast_temp_126 This is an unsurprising state, if you routinely have a hundred-plus connections that sometimes create temp tab

Huge number of pg_temp and pg_toast_temp schemas

2020-04-10 Thread Andrus
Hi! Postgres 12 database contains huge number of pg_temp and pg_toast_temp schemas named pg_temp_1 .. pg_temp_126 and pg_toast_temp_1 .. pg_toast_temp_126 There are total 2 * 126 = 252 unnessecary schemas in one database. Those schemas seems not contain any objects. Other databases in cl

Re: Different Lock Behavior With Create and Drop Foreign Key

2020-04-10 Thread Tom Lane
Laurenz Albe writes: > On Fri, 2020-04-10 at 01:40 +, Virendra Kumar wrote: >> [ $subject ] > That is because foreign keys are implemented with system triggers, some of > which > are defined on the target table. > Now CREATE TRIGGER does not require an ACCESS EXCLUSIVE lock, but DROP > TRIG

ext4 tuning parameters

2020-04-10 Thread Casey Meijer
I have a few questions about the following ext4 mount parameters: `data=writeback` `commit=n` `barrier=0` With regards to `data=writeback`: Is it safe for pg_wal? Is it safe for the main data dir? (and if not in general, is it safe for the main data dir as long as pg_wal is ordered?) With rega