If you followed that last posts about DB Parameter Groups, Subnet Groups and Setting up the RDS instance you should have a running RDS instance. You should also be aware that changing parameters can not be done like you usually do it but you need to do that by changing the DB parameter groups. In this post we’ll look at how you can do that and, especially, what you should avoid.
That means nothing changed or needs to be applied as all parameters of the instance have the same values as specified in the DB Parameter Group. Let’s go and change one of the parameters using the AWS console:
The parameter we’ll change is “autovacuum_naptime“, and we’ll change it from 15 to 20:
A few moments later the DB Parameter Group is “in-sync” again and the parameter is applied to the RDS instance:
postgres=> show autovacuum_naptime ; autovacuum_naptime -------------------- 20s (1 row)
Using the AWS command line utilities for these tasks usually is much easier and tasks can be automated. Changing the same parameter on the command line:
$ aws rds modify-db-parameter-group --db-parameter-group-name dbi-dwe-pg12 --parameters="ParameterName=autovacuum_naptime, ParameterValue=10, ApplyMethod=immediate"
You can already spot an important bit in the command: ApplyMethod=immediate. For dynamic parameters you have the choice to apply a new value immediately or “pending-reboot”. What happens if we change a static parameter using the AWS console?
Rebooting the instance applies the parameter and the DB Parameter Group is in sync again:
postgres=> show autovacuum_naptime ; FATAL: terminating connection due to administrator command SSL connection has been closed unexpectedly The connection to the server was lost. Attempting reset: Succeeded. SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) postgres=> show autovacuum_naptime ; autovacuum_naptime -------------------- 10s (1 row) postgres=> show max_locks_per_transaction ; max_locks_per_transaction --------------------------- 128 (1 row)
What you always need to be careful about it the instance type. Currently the instance is running db.m5.xlarge, which means we should have 16GB of memory available. Checking the current setting of shared_buffers we see that PostgreSQL uses 3GB from that 16GB for caching:
postgres=> show shared_buffers; shared_buffers ---------------- 3936960kB (1 row) postgres=> select 3936960/1024/1024; ?column? ---------- 3 (1 row)
We’ve managed to create a configuration set that will prevent the RDS from starting up. On the one hand this is expected, on the other hand I would have expected that there are some sanity checks in the background that prevent you from doing that. Maybe the reason AWS is not checking that is, that DB Parameter Groups can be used by several instances which all can run on different instance types. So, be careful, when you have a DB Parameter Group you are using for more than one instance and you want to change settings like shared_buffers. Keep in mind that you need check the instance type, because that defines your amount of memory and CPUs.
By using the command line utilities we can also check the PostgreSQL log file, which confirms why the instance is not able to start up:
dwe@dwe:~/Documents/aws$ aws rds describe-db-log-files --db-instance-identifier dwe-pg DESCRIBEDBLOGFILES 1586431187000 error/postgres.log 13626 DESCRIBEDBLOGFILES 1586267878000 error/postgresql.log.2020-04-07-13 8632 DESCRIBEDBLOGFILES 1586269968000 error/postgresql.log.2020-04-07-14 6211 DESCRIBEDBLOGFILES 1586426381000 error/postgresql.log.2020-04-09-09 8236 DESCRIBEDBLOGFILES 1586429793000 error/postgresql.log.2020-04-09-10 6096 DESCRIBEDBLOGFILES 1586431182000 error/postgresql.log.2020-04-09-11 4024 dwe@dwe:~/Documents/aws$ aws rds download-db-log-file-portion --db-instance-identifier dwe-pg --log-file-name error/postgres.log | tail -15 2020-04-09 11:19:46 UTC::@::LOG: database system is shut down 2020-04-09 11:19:47.453 GMT  LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2020-04-09 11:19:47.453 GMT  LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2020-04-09 11:19:47 UTC::@::LOG: database system is shut down Postgres Shared Memory Value: 35386589184 bytes 2020-04-09 11:19:47.482 GMT  LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2020-04-09 11:19:47.482 GMT  LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2020-04-09 11:19:47 UTC::@::LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit 2020-04-09 11:19:47 UTC::@::LOG: listening on IPv4 address "0.0.0.0", port 5432 2020-04-09 11:19:47 UTC::@::LOG: listening on IPv6 address "::", port 5432 2020-04-09 11:19:47 UTC::@::LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2020-04-09 11:19:47 UTC::@::FATAL: could not map anonymous shared memory: Cannot allocate memory 2020-04-09 11:19:47 UTC::@::HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 35386589184 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections. 2020-04-09 11:19:47 UTC::@::LOG: database system is shut down
A bit strange is, that there is no recovery.conf in PostgreSQL 12 but AWS somehow still is referencing that somewhere.
So far for changing parameters. In the next post we’ll look at backup and restore.