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:
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
Importing to AWS RDS PostgreSQL:
-- 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