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-pg-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"

Update config

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-pg-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"

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. 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-pg-instance

Developer information

This section is for developers that want to develop and run the app locally.

Test Environment Variables

Set environment variables:

export DB_USER=""
export DB_PASS=""
export DB_PROJECT=""
export DB_REGION=""
export DB_INSTANCE=""

Run tests

Run retrieval service unit tests:

gcloud builds submit --config retrieval_service/cloudsql.tests.cloudbuild.yaml \
    --substitutions _DATABASE_NAME=$DB_NAME,_DATABASE_USER=$DB_USER,_CLOUDSQL_REGION=$DB_REGION,_CLOUDSQL_INSTANCE=$DB_INSTANCE

Where $DB_NAME,$DB_USER,$DB_REGION,$DB_CLUSTER,$DB_INSTANCE are environment variables with your database values.