Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
We are migrating from Google-Cloud PGSQL managed database to a managed PGSQL server on Digital ocean.
The dilemma we are facing is that both Google and Digital ocean are managed so we have no access to directories so we can't dump then restore. So how would we go about this? Is there a command that copies the data directly from Google-Cloud PGSQL to Digital ocean?
Digital oceans resources assume we have access to a directory.
Install pg_dump and pg_restore or install entire PostgreSQL Binaries on your local machine or EC2, Droplet compute.
Create a dump file using pg_dump that contains the data to be loaded from GCP PG:
pg_dump -Fc -v --host=<IP or hostname> --username=<dbadmin> --dbname=<DB name> -f dbname.dump
Restore the data into the target Database for PostgreSQL using pg_restore :
After you've created the target database, you can use the pg_restore command and the -d, --dbname parameter to restore the data into the target database from the dump file.
pg_restore -v --no-owner --host==<IP or hostname> --port=<port> --username=< dbadmin > --dbname=<target database name> dbname.dump
--no-owner parameter causes all objects created during the restore to be owned by the user specified with --username. For more information , click on pg_restore.
pg_dumpandpg_restore, no need to have access to directories of managed services. Do installPostgreSQLbinaries in any VM's like local machine or EC2, Droplets and from there do as digitalocean.com/docs/databases/postgresql/how-to/…-Fcand-j #.and this is an efficient way to do migrate for managed services since we don't have access to thedirectory.norpg_dumpall.