There are instances when one needs to migrate data from a Postgres database to a different PostgreSQL database version or even worse to a restrictive PostgreSQL instance of cloud providers like #AWS-RDS.
When migrating to a different version of Postgres after you used to run on a frozen production version for long time it may be the case that things changed so we cannot do a simple data files copy migration. In this case a logical migration of data will be necessary.
The same when moving from a self hosted Postgres instance to a cloud instance one has to account for the fact that the majority of the cloud providers sadly use their own “versions” of PostgreSQL. For example in case of Amazon hosted Aurora PostgreSQL instances are in fact a restricted version of Postgres that lacks some standard features of a self hosted Posstgres instance. See Aurora PostgreSQL guide. In this case a logical migration of data will be necessary.
The following are the steps to perform a logical migration of database sandbox with a role web used by my_web application. All SQL commands are executed from a pg_admin connected to the remote AWS-RDS instance.
STEP1: Delete and recreate database sandbox on AWS-RDS (owner root)
STEP2: Create the web role
STEP 3: Grant ledger role to root
GRANT ledger TO root;
STEP 4: Configure and start a “my_web” application environment on the application server configured with the new AWS-RDS instance as the database. The schema of the database will be automatically created by our database abstraction layer (migration scripts, hibernate etc.).
STEP 5: After the schema is created stop the “my_web” application environment. We have to do this to have a consistent database.
STEP 6: Clean-up the temporary migration data. Sometimes when using migration scripts we log the migration in some temp tables. We have to make sure we truncate them.
truncate table web.migration cascade;
STEP 7: Dump content of database sandbox in binary format, data only. Run the command on the database server from where data will be migrated.
pg_dump -Fc --data-only -v -h localhost -U root sandbox > sandbox.dmp
STEP 8: Restore the binary content of the sandbox database on AWS-RDS. Run the command on the database server from where data will be migrated. The command will “pump” the binary data from the dump file to the remote AWS-RDS instance.
pg_restore -v -h xxx.rds.amazonaws.com -U root -d sandbox sandbox.dmp
STEP 9: To restore only one table if some issue occurred, or we just want to add a missing table execute the following. Run the command on the database server from where data will be migrated.
pg_restore -v -h xxx.rds.amazonaws.com -U root -d sandbox --schema=web --table=ApplicationUser sandbox.dmp
STEP 10: At this point all datab should be migrated. To confirm all is OK, start “my_web” application environment on the application server configured with the new AWS-RDS instance as the database.
Using the above method we end up with a compatible schema that may be different on the destination database than on the original migrated database, but have the same actual data.
Note that besides the tables binary content, also all the functions, constraints, sequences will be correctly migrated.