Greg Clough Blog

It appears that new patch versions of PostgreSQL RDS are being rolled out in AWS.. so we should expect an announcement soon: REGION: us-east-2   “DBEngineVersionDescription”: “PostgreSQL 10.11-R1”   “DBEngineVersionDescription”: “PostgreSQL 10.12-R1”   “DBEngineVersionDescription”: “PostgreSQL 10.13-R1”   “DBEngineVersionDescription”: “PostgreSQL 11.6-R1”   “DBEngineVersionDescription”: “PostgreSQL 11.7-R1”   “DBEngineVersionDescription”: “PostgreSQL 11.8-R1”   “DBEngineVersionDescription”: “PostgreSQL 12.2-R1”   “DBEngineVersionDescription”: “PostgreSQL […]

If you are using RDS Aurora PostgreSQL v10.12, v10.13, or v11.8, please be aware that they have been removed from the catalog and you can no longer create new clusters with these versions. Error: error creating RDS cluster: InvalidParameterCombination: Cannot find version 10.12 for aurora-postgresql status code: 400 Bug Details: The problem relates to “NOT […]

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 […]

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: if (errno != ENFILE && errno != EMFILE) { ereport(LOG, (errmsg(“disabling automatic rotation (use SIGHUP to re-enable)”))); rotation_disabled = […]

Vibhor Kumar did a great post on Leveraging Local Temporary Table for Global Temporary Table in EDB Postgres 9.6. The code example only covers the INSERT case, so here is an extension on that code which includes INSERT/UPDATE/DELETE: CREATE UNLOGGED TABLE gtt_test_definition (id int); CREATE VIEW gtt_test AS SELECT * FROM gtt_test_definition; CREATE OR REPLACE […]

Have you ever been tempted to “kill -9” a non-behaving PostgreSQL client session that won’t die via “pg_cancel_backend()” or “pg_terminate_backend()“? Let’s just say that at first you may believe (incorrectly) that it will affect only the single session you’ve killed, but in reality it’s an incredibly bad idea… as the result will be ALL of […]

It’s always something that comes up, even in today’s virtualised and containerised environments… “How can I run multiple PostgreSQL clusters on a single server?” If you’re used to doing RPM installs on RedHat/CentOS, then you’ll be aware that the default location for all database files is: /var/lib/pgsql/<version>/data And the related service name is: postgresql-<version> That […]

Do you have a HUGE table that never seems to vacuum frequently enough? In situations where you have very large tables, then relying on autovacuum_vacuum_scale_factor can mean many thousands or hundreds of thousands of rows are updated before a vacuum is triggered. One way to ensure you have regular vacuuming is to reduce autovacuum_vacuum_scale_factor = […]