Uploading a table from your local PostgreSQL database to Google Cloud SQL

Photo by Caspar Camille Rubin on Unsplash

In this article, I’ll walk you through the process of taking a database dump, compressing it, and uploading it to Google Cloud SQL.

Step 1: Take a Database Dump

The first step in uploading your PostgreSQL table to Google Cloud SQL is to take a database dump of the table you want to upload. To take a dump, use the pg_dump command in your PostgreSQL installation. Here's how:

pg_dump -U <username> -h <hostname> <databasename> <tablename> > <dumpfile>.sql

Replace <username> with the username, you use to connect to your PostgreSQL instance, <hostname> with the hostname or IP address of the PostgreSQL instance, <databasename> with the name of the database, <tablename> with the name of the table you want to upload, and <dumpfile> with the name of the file you want to save the dump to.

This command will create a SQL file that contains the table’s schema and data. Note that you can also use the --schema-only and --data-only flags to only dump the table's schema or data, respectively.

Step 2: Compress the Dump

Next, you’ll want to compress the dump file to make it easier to upload to Google Cloud Storage. You can use the gzip command to compress the file. Here's how:

gzip <dumpfile>.sql

This will create a compressed file with a .gz extension.

Step 3: Upload the Dump to Google Cloud Storage

To upload the compressed dump file to Google Cloud Storage, you can use the gsutil command-line tool. Here's how:

1. First, you’ll need to authenticate with Google Cloud. Run the following command and follow the prompts to authenticate:

gcloud auth login

1. Once you’re authenticated, you can use gsutil to upload the compressed dump file. Here's the command:

gsutil cp <dumpfile>.sql.gz gs://<bucketname>/<dumpfile>.sql.gz

Replace <dumpfile> with the name of your compressed dump file and <bucketname> with the name of the Google Cloud Storage bucket you want to upload the file to.

Step 4: Import the Dump to Google Cloud SQL

Finally, you can import the dump file into your Google Cloud SQL instance. Here’s how:

1. In the Google Cloud Console, navigate to your Cloud SQL instance and click on the “Import” tab.
2. Select “Cloud Storage file” as the source.
3. Enter the path to your compressed dump file in the format gs://<bucketname>/<dumpfile>.sql.gz.
4. Choose the database you want to import the dump into.
5. Click “Import” to start the import process.

The import process may take some time, depending on the size of your dump file. Once the import is complete, you should see the table you uploaded in your Google Cloud SQL instance.

Conclusion

Uploading a table from your local PostgreSQL database to Google Cloud SQL can be a straightforward process if you follow these steps. By taking a dump, compressing it, and uploading it to Google Cloud Storage, you can easily import your table into your Cloud SQL instance and start working with your data in the cloud.