RPMs from postgresql.org break CentOS/RHEL RPMs

Hi! I have a system installed where one software is running a database using the PostgreSQL 9.2 packages included in the CentOS/RHEL 7 release. On the side, I am trying to install another software that is using the PostgreSQL 12 packages found at download.postgresql.org (EL-7-x86_64). However

Re: PostgreSQL 10 not archiving some WAL files

>On Wed, Mar 18, 2020 at 10:57:22AM -0300, Norberto Dellê wrote: > >> This setup worked very well for a long time, but since we upgraded > >> PostgreSQL to newer versions (10.x), sometimes it just skips archiving > some > >> wal files. This seems to happen mainly when the server is shut down. > >>

Re: RPMs from postgresql.org break CentOS/RHEL RPMs

On Thu, Mar 19, 2020 at 01:41:55PM +0100, Peter Krefting wrote: > I have a system installed where one software is running a database using the > PostgreSQL 9.2 packages included in the CentOS/RHEL 7 release. On the side, > I am trying to install another software that is using the PostgreSQL 12 > pa

RE: PG12 autovac issues

Hi Andres- Thanks for the reply, answers below. On Tue, Mar 17, 2020 at 8:19 PM Andres Freund wrote: > > Hi, > > On 2020-03-17 17:18:57 -0500, Justin King wrote: > > As you can see in this table, there are only ~80K rows, but billions > > of updates. What we have observed is that the frozenxid

Re: Re: PG12 autovac issues

On Wed, Mar 18, 2020 at 10:13 AM Adrian Klaver wrote: > > On 3/18/20 6:57 AM, Justin King wrote: > Please reply to list also > Ccing list > > > >>> Here are the settings, these are the only ones that are not set to > >>> default with the exception of a few tables that have been overridden > >>> wi

Re: Fwd: PG12 autovac issues

On Wed, Mar 18, 2020 at 1:40 PM Michael Lewis wrote: > > Do you have default fillfactor set on this table? If not, I would wonder if > reducing it to 50% or even 20% would allow many more HOT updates that would > reduce bloat. I don't believe we have a default fillfactor, but I'm still trying t

Re: Fwd: PG12 autovac issues

On Thu, Mar 19, 2020 at 9:31 AM Justin King wrote: > On Wed, Mar 18, 2020 at 1:40 PM Michael Lewis wrote: > > > > Do you have default fillfactor set on this table? If not, I would wonder > if reducing it to 50% or even 20% would allow many more HOT updates that > would reduce bloat. > > I don't

Re: Fwd: PG12 autovac issues

On Thu, Mar 19, 2020 at 11:02 AM Michael Lewis wrote: > > On Thu, Mar 19, 2020 at 9:31 AM Justin King wrote: >> >> On Wed, Mar 18, 2020 at 1:40 PM Michael Lewis wrote: >> > >> > Do you have default fillfactor set on this table? If not, I would wonder >> > if reducing it to 50% or even 20% would

Could postgres12 support millions of sequences? (like 10 million)

Hello, My schema requires a counter for each combination of 2 values. Something like: CREATE TABLE counter( group INT NOT NULL, element INT NOT NULL, seq_number INT NOT NULL default 0, CONSTRAINT PRIMARY KEY (group, element) ); For each entry in counter, aka for each (group, element) pair, the m

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 19, 2020, at 3:36 PM, pabloa98 wrote: > > Hello, > > My schema requires a counter for each combination of 2 values. Something like: > > CREATE TABLE counter( > group INT NOT NULL, > element INT NOT NULL, > seq_number INT NOT NULL default 0, > CONSTRAINT PRIMARY KEY (group, element)

Re: Invalid byte sequence errors on DB restore

On 3/15/20 11:18 PM, Samuel Smith wrote: My current DB backup routine is just to call pg_dump and pipe to gzip. We recently started to get a failure to restore (which is basically just using psql -f on the pg_dump file) with the following errors: invalid byte sequence for encoding "UTF8": 0xa0

Re: Could postgres12 support millions of sequences? (like 10 million)

On Thu, Mar 19, 2020 at 2:50 PM Rob Sargent wrote: > > > > On Mar 19, 2020, at 3:36 PM, pabloa98 wrote: > > > > Hello, > > > > My schema requires a counter for each combination of 2 values. Something > like: > > > > CREATE TABLE counter( > > group INT NOT NULL, > > element INT NOT NULL, > > seq_

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 19, 2020, at 4:13 PM, pabloa98 wrote: > > > > On Thu, Mar 19, 2020 at 2:50 PM Rob Sargent > wrote: > > > > On Mar 19, 2020, at 3:36 PM, pabloa98 > > wrote: > > > > Hello, > > > > My schema requires a counter for each comb

How does pg_basebackup manage to create a snapshot of the filesystem?

Hi, I'm currently trying to understand how backups work. In the documentation in section "25.2. File System Level Backup" it says that filesystem level backups can only be made when the database if offline yet pg_basebackup seems to do just that but works while the database is online. Am I misunder

Passwordcheck configuration

Hello, I have a requirement to set some password complexity for our database such as length of password, upper case, lower case, special characters, expiration limit, reuse, etc. I saw there was a module you can use for this called passwordcheck. Seems easy to install, but I don't see how you can

Re: How does pg_basebackup manage to create a snapshot of the filesystem?

> On Mar 19, 2020, at 15:19, Dennis Jacobfeuerborn > wrote: > I'm currently trying to understand how backups work. In the > documentation in section "25.2. File System Level Backup" it says that > filesystem level backups can only be made when the database if offline > yet pg_basebackup seems

Re: Could postgres12 support millions of sequences? (like 10 million)

On Thu, Mar 19, 2020 at 3:17 PM Rob Sargent wrote: > > > On Mar 19, 2020, at 4:13 PM, pabloa98 wrote: > > > > On Thu, Mar 19, 2020 at 2:50 PM Rob Sargent wrote: > >> >> >> > On Mar 19, 2020, at 3:36 PM, pabloa98 wrote: >> > >> > Hello, >> > >> > My schema requires a counter for each combinatio

Re: PG12 autovac issues

Hi, On 2020-03-19 10:23:48 -0500, Justin King wrote: > > From a single stats snapshot we can't actually understand the actual xid > > consumption - is it actually the xid usage that triggers the vacuums? > > We have looked at this and the xid consumption averages around 1250 > xid/sec -- this is

Re: PG12 autovac issues

On Thu, Mar 19, 2020 at 5:35 PM Andres Freund wrote: > > Hi, > > On 2020-03-19 10:23:48 -0500, Justin King wrote: > > > From a single stats snapshot we can't actually understand the actual xid > > > consumption - is it actually the xid usage that triggers the vacuums? > > > > We have looked at thi

Re: Passwordcheck configuration

Dave Hughes writes: > I have a requirement to set some password complexity for our database such > as length of password, upper case, lower case, special characters, > expiration limit, reuse, etc. Usually, if you have to do something like that, we recommend setting PG to use PAM authentication a

Re: Could postgres12 support millions of sequences? (like 10 million)

On Thu, Mar 19, 2020 at 3:33 PM pabloa98 wrote: > Table "counter" will have triggers that will create a sequence with a new > row is inserted called counter_1_1_seq, counter_2_1_seq, counter_1_3_seq, > etc that will be used to insert values in **another** table. It will be > used for nothing else

Re: PG12 autovac issues

Hi, On 2020-03-19 18:07:14 -0500, Justin King wrote: > On Thu, Mar 19, 2020 at 5:35 PM Andres Freund wrote: > > > > Hi, > > > > On 2020-03-19 10:23:48 -0500, Justin King wrote: > > > > From a single stats snapshot we can't actually understand the actual xid > > > > consumption - is it actually th

Re: Could postgres12 support millions of sequences? (like 10 million)

On 3/19/20 3:32 PM, pabloa98 wrote: On Thu, Mar 19, 2020 at 3:17 PM Rob Sargent > wrote: On Mar 19, 2020, at 4:13 PM, pabloa98 mailto:pablo...@gmail.com>> wrote: On Thu, Mar 19, 2020 at 2:50 PM Rob Sargent mailto:robjsarg...@gmail.com>> wrote:

Re: Could postgres12 support millions of sequences? (like 10 million)

On Thu, Mar 19, 2020 at 5:13 PM Adrian Klaver wrote: > On 3/19/20 3:32 PM, pabloa98 wrote: > > > > > > On Thu, Mar 19, 2020 at 3:17 PM Rob Sargent > > wrote: > > > > > > > >> On Mar 19, 2020, at 4:13 PM, pabloa98 >> > wrote: > >>

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 19, 2020, at 6:45 PM, pabloa98 wrote: > > > > On Thu, Mar 19, 2020 at 5:13 PM Adrian Klaver > wrote: > On 3/19/20 3:32 PM, pabloa98 wrote: > > > > > > On Thu, Mar 19, 2020 at 3:17 PM Rob Sargent > > >

Re: Could postgres12 support millions of sequences? (like 10 million)

On Thu, Mar 19, 2020 at 6:16 PM Rob Sargent wrote: > > > On Mar 19, 2020, at 6:45 PM, pabloa98 wrote: > > > > On Thu, Mar 19, 2020 at 5:13 PM Adrian Klaver > wrote: > >> On 3/19/20 3:32 PM, pabloa98 wrote: >> > >> > >> > On Thu, Mar 19, 2020 at 3:17 PM Rob Sargent > >

Re: Could postgres12 support millions of sequences? (like 10 million)

> On Mar 19, 2020, at 7:35 PM, pabloa98 wrote: > > > > On Thu, Mar 19, 2020 at 6:16 PM Rob Sargent > wrote: > > >> On Mar 19, 2020, at 6:45 PM, pabloa98 > > wrote: >> >> >> >> On Thu, Mar 19, 2020 at 5:13 PM Adrian Klaver >

Re: Could postgres12 support millions of sequences? (like 10 million)

On Thu, Mar 19, 2020, 5:48 PM David G. Johnston wrote: > However, one other consideration with sequences: do you care that > PostgreSQL will cache/pin (i.e., no release) every single sequence you > touch for the lifetime of the session? (I do not think DISCARD matters here > but I'm just guessing

Re: Could postgres12 support millions of sequences? (like 10 million)

Michael Lewis writes: > On Thu, Mar 19, 2020, 5:48 PM David G. Johnston > wrote: >> However, one other consideration with sequences: do you care that >> PostgreSQL will cache/pin (i.e., no release) every single sequence you >> touch for the lifetime of the session? (I do not think DISCARD matters

Re: Could postgres12 support millions of sequences? (like 10 million)

On 3/19/20 7:38 PM, Michael Lewis wrote: On Thu, Mar 19, 2020, 5:48 PM David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: However, one other consideration with sequences: do you care that PostgreSQL will cache/pin (i.e., no release) every single sequence you touch for t

Re: Could postgres12 support millions of sequences? (like 10 million)

On Thu, Mar 19, 2020 at 9:12 PM Adrian Klaver wrote: > On 3/19/20 7:38 PM, Michael Lewis wrote: > > > > > > On Thu, Mar 19, 2020, 5:48 PM David G. Johnston > > mailto:david.g.johns...@gmail.com>> wrote: > > > > However, one other consideration with sequences: do you care that > > PostgreS

Re: Could postgres12 support millions of sequences? (like 10 million)

I see. Any suggestion? It should behave like a sequence in the sense that concurrent transitions will get different numbers from this alternative sequence like solution. In our case, we will need to do a call nextval('some_seq') (or similar) from different processes no more than twice every minut

Re: Could postgres12 support millions of sequences? (like 10 million)

On Thursday, March 19, 2020, Tom Lane wrote: > Michael Lewis writes: > > On Thu, Mar 19, 2020, 5:48 PM David G. Johnston < > david.g.johns...@gmail.com> > > wrote: > >> However, one other consideration with sequences: do you care that > >> PostgreSQL will cache/pin (i.e., no release) every singl

Re: Could postgres12 support millions of sequences? (like 10 million)

On Thursday, March 19, 2020, pabloa98 wrote: > > >> We will use a CACHE 1. This is because when nextval('seq') is invoked, we > are hitting 3 or 4 more tables so the sequence will not be a performance > blocker (compared with all the operations in the transaction). > The other implementation deta