How Do I Manually Migrate PostgreSQL Database Accounts and Object Permissions?

Environment Preparation

1. Apply for an ECS in the subnet of the destination database. The recommended specifications are 2 vCPUs and 4 GB of memory, the recommended disk size is 40 GB, and the OS is CentOS 7.X. Ensure that the source database is connected to the ECS.
2. Install the PostgreSQL client tool of the destination database version. The following uses PostgreSQL 12 as an example.

   Install the RPM package of the PostgreSQL repository: yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

   Update the Yum cache: yum clean all; yum makecache

   Install the PostgreSQL 12 client package: yum install -y postgresql12

Migrating Database Accounts

1. Create an independent directory and go to the directory.

   mkdir -p ./sqls; cd ./sqls

2. Run the following command to export the SQL script for account migration from the source database as an account that has the SELECT permission on the system catalog pg_catalog.authid:

   PGPASSWORD=Source_database_password pg_dumpall -h Source_database_IP_address -p Source_database_port -U Source_database_username -r | grep -v "rdsAdmin" | grep -v -E '^(CREATE|ALTER) ROLE "?(rdsBackup|rdsMetric|rdsRepl|rdsgroup|root)'  | awk  '{gsub(" NOSUPERUSER","");gsub(" SUPERUSER","");gsub(/ GRANTED BY.*;/, ";");print}' > roles.sql

   

   1. Replace the connection information in the command with the source database details.
   2. If the source database account does not have the SELECT permission on the system catalog pg_catalog.authid, run the following command to export the account migration script and edit the script. Add Password (replace Password with the actual password) before the semicolon (;) in the CREATE ROLE statement to fill in the password information:

      PGPASSWORD=Source_database_password pg_dumpall -h Source_database_IP_address -p Source_database_port -U Source_database_username -r --no-role-passwords | grep -v "rdsAdmin" | grep -v -E '^(CREATE|ALTER) ROLE "?(rdsBackup|rdsMetric|rdsRepl|rdsgroup|root)'  | awk  '{gsub(" NOSUPERUSER","");gsub(" SUPERUSER","");gsub(/ GRANTED BY.*;/, ";");print}' > roles.sql

   3. In the to-the-cloud scenario, the destination database does not support superuser accounts.
   4. In the to-the-cloud scenario, the privileged account of the destination database is root. If the source database is a cloud database with a privileged account, replace the privileged account of the source database in the script with root. The following command is for your reference.

      sed -i -E 's/GRANT "?Source_database_privileged_account"? TO/GRANT root TO/g' roles.sql

   5. If the source database contains user-defined system accounts starting with pg_ and these accounts do not exist in the destination database, filter out the accounts. The reference command is as follows:

      sed -i -E '/ "?System_account_name"?[ ;]/d' roles.sql

   6. You can edit the roles.sql script as required.
3. Use the root user to run the SQL script exported from the source database in the destination database using psql. The reference command is as follows (replace the connection information in the command with the destination database details):

   PGPASSWORD=Destination_database_password psql -h Destination_database_IP_address -p Destination_database_port -U root -d postgres -f roles.sql > roles.log

Migrating Database Object Permissions

1. Create an independent directory and go to the directory.

   mkdir -p ./sqls; cd ./sqls

2. Run the following command to export the SQL script for migrating object permissions from the source database as a privileged account:

   PGPASSWORD=Source_database_password pg_dump h Source_database_IP_address -p Source_database_port -U Source_database_username -d Name_of_database_to_be_migrated -s --section=pre-data |  grep -E '^ALTER .* OWNER TO |^GRANT |^REVOKE ' | grep -v '"rdsAdmin"' | grep -v -E ' ON .* pg_| TO pg_| FROM pg_| SERVER .* | FOREIGN TABLE .* ' | awk '{gsub(" TO root WITH GRANT OPTION","TO root");print}' > ./privileges.sql

   

   1. Replace the connection information in the command with the source database details.
   2. If the source database has a user-defined system schema, filter out the objects in the schema. The reference command is as follows:

      sed -i -E '/ SCHEMA System_schema_name | ON .* System_schema_name\./d' ./privileges.sql

   3. If the source database has a user-defined system catalog, filter out the operations on the catalog. The reference command is as follows:

      sed -i -E '/ .* System_schema_name\.System_table_name /d' ./privileges.sql

   4. If the source database has a user-defined system account, filter out the operations on the account as required. The reference command is as follows:

      sed -i -E '/ TO  "?System_account_name"?[ ;]| FROM "?System_account_name"?[ ;]/d' ./privileges.sql

   5. You can edit the privileges.sql script as required.
3. Use the root user to run the SQL script exported from the source database in the destination database using psql. The reference command is as follows (replace the connection information in the command with the destination database details):

   PGPASSWORD=Destination_database_password psql -h Destination_database_IP_address -p Destination_database_port -U root -d Destination_database_name -f privileges.sql > privileges.log

Complete Example

The following is a complete example template. You can modify the template as required. Replace the connection information with the actual database details.

#!/bin/bash

SRC_DB_HOST = Source database IP address
SRC_DB_PORT = Source database port
SRC_DB_USER = Source database username
SRC_DB_PASS = Source database password
SRC_DB_NAME = Source database name
DEST_DB_HOST = Destination database IP address
DEST_DB_PORT = Destination database port
DEST_DB_USER = Destination database username
DEST_DB_PASS = Destination database password
DEST_DB_NAME = Destination database name

# Create an independent directory and go to the directory.
mkdir -p ./sqls
cd ./sqls

# Export the script for account migration from the source database.
PGPASSWORD=${SRC_DB_PASS} pg_dumpall -h ${SRC_DB_HOST} -p ${SRC_DB_PORT} -U ${SRC_DB_USER} -r | grep -v "rdsAdmin" | grep -v -E '^(CREATE|ALTER) ROLE "?(rdsBackup|rdsMetric|rdsRepl|rdsgroup|root)'  | awk  '{gsub(" NOSUPERUSER","");gsub(" SUPERUSER","");gsub(/ GRANTED BY.*;/, ";");print}' > roles.sql
# Change the privileged account to root based on the source database scenario.
sed -i -E 's/GRANT "?Source_database_privileged_account"? TO/GRANT root TO/g' roles.sql
# Filter out user-defined system accounts based on the source database scenario.
sed -i -E '/ "?System_account_name"?[ ;]/d' roles.sql
# Execute the account migration script in the destination database.
PGPASSWORD=${DEST_DB_PASS} psql -h ${DEST_DB_HOST} -p ${DEST_DB_PORT} -U root -d postgres -f roles.sql > roles.log

# Export the script for object permission migration from the source database.
PGPASSWORD=${SRC_DB_PASS} pg_dump -h ${SRC_DB_HOST} -p ${SRC_DB_PORT} -U ${SRC_DB_USER} -d ${SRC_DB_NAME} -s --section=pre-data | grep -E '^ALTER .* OWNER TO |^GRANT |^REVOKE ' | grep -v '"rdsAdmin"' | grep -v -E ' ON .* pg_| TO pg_| FROM pg_| SERVER .* | FOREIGN TABLE .* ' | awk '{gsub(" TO root WITH GRANT OPTION","TO root");print}' > ./privileges.sql
# Filter out user-defined system schemas based on the source database scenario.
sed -i -E '/ SCHEMA System_schema_name | ON .* System_schema_name\./d' ./privileges.sql
# Filter out user-defined system catalogs based on the source database scenario.
sed -i -E '/ .* System_schema_name\.System_table_name /d' ./privileges.sql
# Filter out user-defined system accounts based on the source database scenario.
sed -i -E '/ TO  "?System_account_name"?[ ;]| FROM "?System_account_name"?[ ;]/d' ./privileges.sql
# Execute the object permission migration script in the destination database.
PGPASSWORD=${DEST_DB_PASS} psql -h ${DEST_DB_HOST} -p ${DEST_DB_PORT} -U root -d ${DEST_DB_NAME} -f privileges.sql > privileges.log