Upgrading Aurora Postgres from 10.16 to 11.11

1

I failed to upgrade postgres engine from 10.16 to 11.11 with error message which suggest me to change wal_level to logical or higher, but I can't find a way to change that setting, does anyone have experience with this?

this is the full message:

-----------------------------------------------------------------  
pg_upgrade run on Tue Jul 20 08:31:19 2021  
-----------------------------------------------------------------  
  
sh: /rdsdbbin/aurora-11.9.3.4.3.8395.0/bin/curl: /apollo/sbin/envroot: bad interpreter: No such file or directory  
command: \[ -f /rdsdbbin/aurora-10.14.2.7.3.209608.0/bin/setup_env.sh ] && source /rdsdbbin/aurora-10.14.2.7.3.209608.0/bin/setup_env.sh ; "/rdsdbbin/aurora-10.14.2.7.3.209608.0/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/rdsdbdata/db_old" -o "-p 5432 -b --config_file=/rdsdbdata/config_old/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/rdsdbdata/log/upgrade'" start >> "pg_upgrade_server.log" 2>&1  
waiting for server to start....2021-07-20 08:31:39.978 GMT \[11484] LOG: skipping missing configuration file "/rdsdbdata/db_old/postgresql.auto.conf"  
2021-07-20 08:31:39.984 GMT \[11484] LOG: pgaudit extension initialized  
2021-07-20 08:31:39.993 GMT \[11484] LOG: listening on Unix socket "/rdsdbdata/log/upgrade/.s.PGSQL.5432"  
.2021-07-20 08:31:40.998 GMT \[11484] LOG: Waiting for runtime initialization complete...  
2021-07-20 08:31:41.050 GMT \[11509] LOG: setting shmemname /dev/shm/aurora-postgres-buffer-cache  
.2021-07-20 08:31:42.220 GMT \[12016] LOG: database system was shut down at 2021-07-20 08:30:28 GMT  
2021-07-20 08:31:42.231 GMT \[12016] FATAL: logical replication slot "q4qxxeimi2gz6hhd_00026367_4c69119a_f0a5_4aa7_9e2f_7c2ea95c59df" exists, but wal_level < logical  
2021-07-20 08:31:42.231 GMT \[12016] HINT: Change wal_level to be logical or higher.  
2021-07-20 08:31:42.233 GMT \[11484] LOG: startup process (PID 12016) exited with exit code 1  
2021-07-20 08:31:42.233 GMT \[11484] LOG: aborting startup due to startup process failure  
2021-07-20 08:31:42.233 GMT \[11484] LOG: database system is shut down  
stopped waiting  
pg_ctl: could not start server  
Examine the log output.  
pyc19
asked a year ago169 views
4 Answers
2

Hi,

Currently replication slots must be explicitly dropped before a major version upgrade in Aurora Postgres.

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html has some additional information that may be helpful, but let me know if you have other questions/concerns.

Jordan
answered a year ago
0

Does this mean that I have to stop all DMS tasks during the upgrade process?

pyc19
answered a year ago
0

We have more than 20 databases in the instance, and we use DMS to sync data between services. Do you have any suggestions for doing a major version upgrade?

We have already tried the DMS approach. Create a new 11.11 instance, and sync data from the old instance to the new one via DMS. this approach has limitations, I have to manually migrate some data, and change some table schemas. It's far too complicated.

pyc19
answered a year ago
0

Hello,

Sorry this response took so long. I was doing my own testing to reproduce the issue.

Unfortunately you will have to either delete the DMS replication tasks so the slots get dropped, or you can stop them and manually drop the replication slots and then perform the major version upgrade.

After upgrading, if your task is an ongoing replication task vs. a full load and ongoing replication, you can restart and not resume it. DMS will begin replicating new changes over. If you deleted the task before the upgrade, you will need to create a new one. Note that if there were writes that happened on the source replicated databases/tables in between dropping the replication slots and starting the task again, those will not be replicated over. The safer choice would be to do another full load (truncating or dropping the target table) + ongoing replication if that's feasible with your data set size.

I've created a feature request to support replication slots across major version upgrades that we will try to prioritize. Thank you for the question and input on the feature.

Jordan
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions