cloudsql_postgres.md

Setup and configure Cloud SQL for Postgres

Before you begin

1. Make sure you have a Google Cloud project and billing is enabled.

2. Set your PROJECT_ID environment variable:

   export PROJECT_ID=<YOUR_PROJECT_ID>

3. Install the gcloud CLI.

4. Set gcloud project:

   gcloud config set project $PROJECT_ID

5. Enable APIs:

   gcloud services enable sqladmin.googleapis.com \
                          aiplatform.googleapis.com

6. Install python and set up a python virtual environment.

7. Make sure you have python version 3.11+ installed.

   python -V

8. Download and install postgres-client cli (psql).

9. Install the Cloud SQL Auth Proxy client.

Create a Cloud SQL for PostgreSQL instance

1. Set environment variables. For security reasons, use a different password for $DB_PASS and note it for future use:

   export DB_PASS=my-cloudsql-pass
   export DB_USER=postgres
   export INSTANCE=my-cloudsql-instance
   export REGION=us-central1

2. Create a PostgreSQL instance:

   gcloud sql instances create $INSTANCE \
       --database-version=POSTGRES_14 \
       --cpu=4 \
       --memory=16GB \
       --region=$REGION

3. Set password for postgres user:

   gcloud sql users set-password $DB_USER \
       --instance=$INSTANCE \
       --password=$DB_PASS

Connect to the Cloud SQL instance

1. Connect to instance using cloud sql proxy:

   ./cloud-sql-proxy $PROJECT_ID:$REGION:$INSTANCE

2. Verify you can connect to your instance with the psql tool. Enter password for Cloud SQL ($DB_PASS environment variable set above) when prompted:

   psql "host=127.0.0.1 port=5432 sslmode=disable user=$DB_USER"

Initialize data

1. While connected using psql, create a database and switch to it:

   CREATE DATABASE assistantdemo;
   \c assistantdemo

2. Install pgvector extension in the database:

   CREATE EXTENSION vector;

3. Change into the retrieval service directory:

   cd genai-databases-retrieval-app/retrieval_service

4. Install requirements:

   pip install -r requirements.txt

5. Make a copy of example-config.yml and name it config.yml.

   cp example-config.yml config.yml

6. Update config.yml with your database information.

   host: 0.0.0.0
   datastore:
     # Example for cloudsql_postgres.py provider
     kind: "cloudsql-postgres"
     # Update this with your project ID
     project: <PROJECT_ID>
     region: us-central1
     instance: my-cloudsql-instance
     # Update this with the database name
     database: "assistantdemo"
     # Update with database user, the default is `postgres`
     user: "postgres"
     # Update with database user password
     password: "my-cloudsql-pass"

7. Populate data into database:

   python run_database_init.py

Clean up resources

Clean up after completing the demo.

1. Delete the Cloud SQL instance:

   gcloud sql instances delete my-cloudsql-instance