I have an on-prem SUSE 12 sp3 VM with PostgreSQL 10 database that is about 45GB.
I also have a Google Cloud Platform (GCP) image/instance of a SUSE 12 sp3 server with PostgreSQL 10 database, with no db data in it yet.
The GCP directory structure and setup is slightly different from the on-prem VM file directory and setup, but they are both PostgreSQL 10. I like the GCP setup/install/directory structure better, so I don't want to change it.
I want to transfer as much of the data from the on-prem db to the GCP db. The databases should be identical when I am finished, but the directory structure will be different.
I am new to GCP. This is a test drive. My new db needs to be on Regional storage (not nearline or coldline or multi-regional).
My current strategy is to:
pg_dumpall > db.outon my old on-prem db
- save that
db.outfile to our windows VM file server, which I have access to via a mapped drive on my workstation.
- on the GCP console via my workstation go to: Storage, select my bucket, upload files, and upload the db.out file to the bucket my instances is in.
psql -f db.out postgresto load the data, roles etc. into my new GCP instance.
Is that a solid way to do it? Will that work despite the differing directory structures? Any better way to do it? Tricks or pitfalls?
(I'm still searching for the helpful GCP link, hence the question.)
Note, by "directory structure" I am referring to the server directory structure. The directory structure changed because I installed PostgreSQL 10 differently (the way I want it) on on my GCP instance.
PS - This was on SO, but deleted it there an moved the question here hoping to get more traction. I see other questions being moved or transferred, but I didn't notice a way to do that, at least with my current cred level. Also, I was going to put it on Stack Exchange site Database Administrators until I saw only two people were following the Google-Cloud tag there. Suggestions welcome in this regard.