How to Migrating PostgreSQL to AlloyDB-PostgreSQL from Using PostgreSQL Tools — pg_dump and pg_restore tool

Architecture for this migration

Three main steps is involved to complete the migration

Steps 1 . Verify Data in Source Instance for Migration

Steps 2 . Create a database DMP file using pg_dump

Steps 3 . Import DMP file using pg_restore

Verify Data in Source Instance for Migration

Use the following command to launch the PostgreSQL (psql) client.

sudo -u postgres psql

Input and run the following SQL command to see the HR related tables in the postgres database.

\dt

Run the following queries to determine the row counts for each table.

For example to verify details

select count (*) as countries_row_count from countries;
select count (*) as departments_row_count from departments;
select count (*) as employees_row_count from employees;
select count (*) as jobs_row_count from jobs;
select count (*) as locations_row_count from locations;
select count (*) as regions_row_count from regions;

Create a database DMP file using pg_dump

The pg_dump tool is installed by default with every PostgreSQL installation.

sudo -u postgres pg_dump -Fc postgres > pg14_source.DMP

Browse the directory to confirm the size and other details of the DMP file.

ls -l -h pg14_source.DMP

To simulate a routine database export/import, migrate the DMP file to a Cloud Storage bucket.

gsutil cp pg14_source.DMP gs://your-bucket-name/pg14_source.DMP

Import DMP file using pg_restore

VM Create and install the PostgreSQL client in name alloydb-client

Set the following environment variable, replacing ALLOYDB_ADDRESS with the Private IP address of the AlloyDB instance.

export ALLOYDB=ALLOYDB_Private IP ADDRESS.

install includes the database restore tool pg_restore

Run the following command to store the Private IP address of the AlloyDB instance on the AlloyDB client VM

echo $ALLOYDB > alloydbip.txt

psql -h $ALLOYDB -U postgres

Use the following command to launch the PostgreSQL (psql) client. You will be prompted to provide the postgres user’s password

Download the DMP file from the Cloud Storage bucket to the local directory

gsutil cp  gs://your-bucket-name/pg14_source.DMP pg14_source.DMP

Run the following command to create a TOC file that comments out all extension statements.

pg_restore -l  pg14_source.DMP | sed -E 's/(.* EXTENSION )/; \1/g' >  pg14_source_toc.toc

Now run the restore command to load the HR tables. You will be prompted for the postgres user’s password for Example

pg_restore -h $ALLOYDB -U postgres \
  -d postgres \
  -L pg14_source_toc.toc \
  pg14_source.DMP

Launch the PostgreSQL (psql) client again. You will be prompted for the postgres user’s password ex:put Password

psql -h $ALLOYDB -U postgres

Run the following command to confirm that the tables were loaded.

\dt

Run the following queries to determine the row counts for the migrated tables. The values will match the query outputs on the source instance.

For Example to run this query to verify data

select count (*) as countries_row_count from countries;
select count (*) as departments_row_count from departments;
select count (*) as employees_row_count from employees;
select count (*) as jobs_row_count from jobs;
select count (*) as locations_row_count from locations;
select count (*) as regions_row_count from regions;

End of the migration process ………………….

— — — — — — — — — — — End — — — — — — — — — — — — — — — — — — — — — — — —