Re: pg_xlog unbounded growth

[Andreas Kretschmer]

Hi,


Am 24.01.2018 um 12:48 schrieb Stefan Petrea:

We're using PostgreSQL 9.6.6 on a Ubuntu 16.04.3 LTS.
During some database imports(using pg_restore), we're noticing fast
and unbounded growth of pg_xlog up to the point where the
partition(280G in size for us) that stores it fills up and PostgreSQL
shuts down. The error seen in the logs:

 2018-01-17 01:46:23.035 CST [41671] LOG:  database system was shut down at 
2018-01-16 15:49:26 CST
 2018-01-17 01:46:23.038 CST [41671] FATAL:  could not write to file 
"pg_xlog/xlogtemp.41671": No space left on device
 2018-01-17 01:46:23.039 CST [41662] LOG:  startup process (PID 41671) 
exited with exit code 1
 2018-01-17 01:46:23.039 CST [41662] LOG:  aborting startup due to startup 
process failure
 2018-01-17 01:46:23.078 CST [41662] LOG:  database system is shut down

The config settings I thought were relevant are these ones (but I'm
also attaching the entire postgresql.conf if there are other ones that
I missed):

 wal_level=replica
 archive_command='exit 0;'
 min_wal_size=2GB
 max_wal_size=500MB
 checkpoint_completion_target = 0.7
 wal_keep_segments = 8


just to exclude some things out:

* is that only happens during pg_restore, or also during normal work?
* can you show us how pg_restore is invoked?
* how did you create the dump (same pg-version, which format)?
* can you change wal_level to minimal? (maybe that's not possible if it 
is in production und there are standbys)


Can you change your archive_command to '/bin/true' ? I'm not sure if 
that can be the reason for the your problem, but 'exit 0;' terminates 
the process, but archive_command should return true or false, not 
terminate.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

PG 10 hash index create times

[Rick Otten]

Since upgrading to PG 10 a few weeks ago I've been experimenting with hash
indexes.  One thing I've noticed is that they seem to take a _lot_ longer
to create than btree indexes, particularly on large tables.

I've got a moderately sized table of about 38M rows and the create index
using hash for an integer column (with about 300 unique values) has been
running for 12 hours now and still hasn't finished.  I have not
successfully installed a hash index on a larger table (of which I have
many) yet because the create index never seems to finish.

The create index thread will consume an entire CPU while doing this.  It
does not seem to be I/O bound.  It just crunches away burning cpu cycles
with no apparent end.

Is expected?