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