Data migration in PostgreSQL

27 December 2021

Database data Migrations from lower version of django to higher version of django in PostgreSQL.

• Enter into the postgresql shell by the following command.
  • Sudo -su postgres psql
• After that need to create a database by typing create database database_name;
• Then need to grant the permission by typing Grant all privileges to database database_name to user_name;
• Comment out lines for python manage.py migrate to go through and migrate models to the database.
• Then to quit from the shell and run the command after activating the virtual env and place the database inside settings.py by typing python manage.py migrate.
• After that tables will get created into the new database after doing migrations.
• Then we need to install pg-admin-4 into our local machine.
• Then we need to open the query tool from the production database and then we need to export as a csv file.
• Then in the excel sheet we need to delete the header first row and save the file.
• Then to quit from the shell and run the command after activating the virtual env and place the database inside settings.py by typing python manage.py migrate.
• After that tables will get created into the new database after doing migrations.
• Then we need to install pg-admin-4 into our local machine.
• Then we need to open the query tool from the production database and then we need to export as a csv file.
• Then in the excel sheet we need to delete the header first row and save the file.
• After that inside pgadmin-4 we need to go inside the new database inside the schema and right click on the table we need to import the data and select the import/export option.
• One dialog box will open and we need to select the csv file and click on the import button so that data will get transformed from production database to new database.
• Sometimes it will throw different errors like Null, foreign key constraint and timestamp column cannot be null.
• So to resolve that error we need to search different things for postgresql so that data will get transferred easily.
• If there are so many columns and are mismatched then we need to check the migrations and also data is not getting transferred easily. There is also a second option we ndd to run the pg_dump command.
  • pg_dump -t table_to_copy source_db | psql target_db
• For this command we need to make a superuser for you local system inside the postgresql shell
• Then by this command we can copy the data from one database table to another database table.