0

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:

  1. use pg_dumpall > db.out on my old on-prem db
  2. save that db.out file to our windows VM file server, which I have access to via a mapped drive on my workstation.
  3. 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.
  4. use psql -f db.out postgres to 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?

Link: http://www.postgresql.org/docs/current/static/app-pg-dumpall.html

(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.

thanks!

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.

mountainclimber11
  • 131
  • 1
  • 9

1 Answers1

0

Dumping the database to sql, transferring it to the remote machine, then importing the database via sql, as you have done is a perfectly normal way to achieve what you want. The on disk structure really shouldn't be an issue.

user9517
  • 115,471
  • 20
  • 215
  • 297