Migrate PostgreSQL Users To AWS RDS PostgreSQL

According to PostgreSQL migration, we can migrate the databases and tables. I was migrating a Postgresql environment from EC2 to RDS Postgresql. I have completed the data migration. In many blogs, they have mentioned the steps to migrate PostgreSQL users to another server. But in RDS I couldn’t able to do that. I got the below error.

**ERROR**: must be superuser to alter superusers

It seems we can’t directly import the users because few users had superuser role. So we need to do some sanitization to migrate the users.

The roles that are not supported in RDS: #

• SuperUser
• Replication

So we need to remove these roles in the import file. Here are the complete steps for migrating PostgreSQL user to RDS.

Dump out the users: #

pg_dumpall -g > users.sql

Directly Import to another PostgreSQL servers(Ec2/On-Prem): #

awk '/CREATE/' users.sql > migrate.sql
psql -h another-server -d postgres -U adminuser < migrate.sql

Copy

Importing to AWS RDS PostgreSQL: #

awk '/CREATE/' users.sql > migrate.sql
-- Sanitize superuser, replication roles
sed -i -e's/NOSUPERUSER//g; s/SUPERUSER//g; s/NOREPLICATION//g; s/REPLICATION//g' migrate.sql

-- Import superusers from current servers and grant rds_superuser privilege.
psql -h localserver -d postgres -t -c"select 'grant rds_superuser to '||rolname ||';' from pg_roles where rolsuper='t';" -P "footer=off" >> migrate.sql

psql -h rds-endpoint -U adminuser -d postgres < migrate.sql