How to migrate PostgreSQL users to RDS

While doing the migration from self hosted PostgreSQL to RDS data migration can be done via dump and restore.

But if we try to restore the users data it will fail with the following error.

ERROR: must be superuser to alter superusers

This happens because RDS will not allow superuser privilege to any user as its a cloud service. Roles like SuperUser and Replication are not supported in RDS.

This article explains the steps to migrate PostgreSQL users to RDS without any error.

Step1: Dump the user data.

#pg_dumpall -g > users.sql

Step2: Select the user queries and save to another file.

#awk '/CREATE/' users.sql > migrate.sql

Step3: Remove the superuser and replication roles.

#sed -i -e's/NOSUPERUSER//g; s/SUPERUSER//g; s/NOREPLICATION//g; s/REPLICATION//g' migrate.sql

Step4: Export superusers from current servers and grant rds_superuser privilege.

#psql -d postgres -t -c"select 'grant rds_superuser to '||rolname ||';' from pg_roles where rolsuper='t';" -P "footer=off" >> migrate.sql

Step5: Restore the user data to RDS.

#psql -h rds -U user -d postgres < migrate.sql

Where rds need to be replaced with RDS endpoint and user need to be replaced with the RDS PostgreSQL admin user.

That's all…