Re: pg_xlog unbounded growth

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