Think AWS support for RDS gives 100% coverage? Think again!

Just a friendly FYI that AWS do not have access to any of your PostgreSQL RDS database logs, such as postgresql.log.  This is important, if you just happen to hit PostgreSQL bug such as this one that was fixed in v11.3:

• Fix race conditions in management of dynamic shared memory (Thomas Munro)
  
  These could lead to “dsa_area could not attach to segment” or “cannot unpin a segment that is not pinned” errors.

Once this problem occurs, your RDS database will appear to be running, and can possibly be used to service some queries... but most queries will hang, and eventually your application servers will timeout causing application downtime.

Unfortunately from the AWS support perspective, the RDS database is up, it's using some CPU, and network, so there's no reason to suspect it's not healthy.  It's only if you see this message by looking in your postgresql.log that you will know what the problem is:

2018-01-01 12:34:56 UTC::@:[10234]:FATAL:  cannot unpin a segment that is not pinned

So if you run an application team, then please ensure you have a DBA somewhere on staff, because AWS doesn't have your back when you hit problems such as this.  You could always just turn it off and on again, but you will never find the root-cause and will be cursed to keep rebooting whenever things break at unpredictable times.

postgresql.log file grows infinitely, and never rotates... did your disk fill up previously?

An interesting (and currently undocumented) behaviour of PostgreSQL is that when there is an error writing to the postgresql.log configured in log_filename, that all future log rotations are disabled by this piece of code in syslogger.c:

/*
 * ENFILE/EMFILE are not too surprising on a busy system; just
 * keep using the old file till we manage to get a new one.
 * Otherwise, assume something's wrong with Log_directory and stop
 * trying to create files.
 */
 if (errno != ENFILE && errno != EMFILE)
 {
     ereport(LOG,
             (errmsg("disabling automatic rotation (use SIGHUP to re-enable)")));
     rotation_disabled = true;
 }

In most default configurations this will never be noticed, because the log_directory is on the same filesystem as the data_directory.  In that case the entire instance will crash, and once space is cleared then it will be restarted afresh with log rotation enabled.

If, however, you had the foresight to put your log_directory onto a different filesystem, then it can fill up without taking the entire cluster down.  Once you clear space then logging will resume, but both log_rotation_age, and log_rotation_size will be disabled:

if (Log_RotationAge > 0 && !rotation_disabled)
{
 /* Do a logfile rotation if it's time */
 now = (pg_time_t) time(NULL);
 if (now >= next_rotation_time)
        rotation_requested = time_based_rotation = true;
}

if (!rotation_requested && Log_RotationSize > 0 && !rotation_disabled)
{
 /* Do a rotation if file is too big */
 if (ftell(syslogFile) >= Log_RotationSize * 1024L)
 {
        rotation_requested = true;
        size_rotation_for |= LOG_DESTINATION_STDERR;
 }
 if (csvlogFile != NULL &&
        ftell(csvlogFile) >= Log_RotationSize * 1024L)
 {
        rotation_requested = true;
        size_rotation_for |= LOG_DESTINATION_CSVLOG;
 }
}

The result of being disabled means that one single log file will be written to, growing and growing until it once again fills the disk.  If you're relying on log_truncate_on_rotation to run, or cron jobs to cleanup old logs, then neither will work.

To correct this, once some space has been cleared then you just need to send a SIGHUP to the postmaster process.  This is safely done by calling pg_reload_conf(), but you could also use kill or pg_ctl reload if you prefer.

/*
 * If we had a rotation-disabling failure, re-enable rotation
 * attempts after SIGHUP, and force one immediately.
 */
if (rotation_disabled)
{
    rotation_disabled = false;
    rotation_requested = true;
}

After that your logging will go back to normal, and will rotate as you have configured in your postgresql.conf.