Terminal Connect to GCP CloudSQL/Postgres

1
2
3
4
5
6
7
gcloud sql connect cloudsql-postgres-keyvault --user=proxyuser

psql -h postgres -U postgres

\c key_vault

SELECT * FROM vault;

Application Access GCP CloudSQL/Postgres from local

1
2
3
> curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64
> chmod +x cloud_sql_proxy
> ./cloud_sql_proxy -instances=[INSTANCE_CONNECTION_NAME]=tcp:5432

Application Access GCP Cloud SQL/Postgres from Kubernetes on GCP

• Google docs for this

• CloudSQL access proxy

• Create GCP credentials for proxy to access CloudSQL ```bash #1 Enable the CloudSQL API

#2.1 Create the service account. Replace [NAME] with your desired service account name.

gcloud iam service-accounts create [NAME]

#2.2 Grant permissions to the service account. Replace [PROJECT_ID] with your project ID.

gcloud projects add-iam-policy-binding [PROJECT_ID] –member “serviceAccount:[NAME]@[PROJECT_ID].iam.gserviceaccount.com” –role “roles/owner”

#2.3 Generate the key file. Replace [FILE_NAME] with a name for the key file.

gcloud iam service-accounts keys create [FILE_NAME].json –iam-account [NAME]@[PROJECT_ID].iam.gserviceaccount.com

#3 Create the user account proxyuser.

gcloud sql users create proxyuser host –instance=$INSTANCE_NAME –password=$PASSWORD

#4 Check the connection name to cloudsql instance

gcloud sql instances describe $INSTANCE_NAME

INSTANCE_CONNECTION_NAME: iotproxy-198021:europe-west2:key-vault-dev

1
2
3
4
5
6
7
8
9
10
11
- Create Kubernetes credential for pod/app to use proxy to access CloudSQL
```bash
#5 Create the cloudsql-instance-credentials Secret, using the key file you downloaded previously:
> kubectl create secret generic cloudsql-instance-credentials \
       --from-file=credentials.json=[path/to/FILE_NAME]

#6 Create the cloudsql-db-credentials Secret, using the name and password for the proxy user you created previously:
> kubectl create secret generic cloudsql-keyvault-credentials \
       --from-literal=username=postgres --from-literal=password=[PASSWORD]
       

• Update app Kubernetes deployment file:

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48
  49
  50
  51
  52
  53
  54
  55
  apiVersion: extensions/v1beta1
  kind: Deployment
  metadata:
  name: myapp
  labels:
    app: myapp
  spec:
  template:
    metadata:
      labels:
        app: myapp
    spec:
      containers:
        - name: app
          image: <CONTAINER-IMAGE>
          ports:
            - containerPort: 80
          # The following environment variables will contain the database host,
          # user and password to connect to the PostgreSQL instance.
          env:
            - name: POSTGRES_DB_HOST
              value: 127.0.0.1:5432
            # [START cloudsql_secrets]
            - name: POSTGRES_DB_USER
              valueFrom:
                secretKeyRef:
                  name: cloudsql-db-credentials
                  key: username
            - name: POSTGRES_DB_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: cloudsql-db-credentials
                  key: password
            # [END cloudsql_secrets]
        # Change <INSTANCE_CONNECTION_NAME> here to include your GCP
        # project, the region of your Cloud SQL instance and the name
        # of your Cloud SQL instance. The format is
        # $PROJECT:$REGION:$INSTANCE
        # [START proxy_container]
        - name: cloudsql-proxy
          image: gcr.io/cloudsql-docker/gce-proxy:1.11
          command: ["/cloud_sql_proxy",
                    "-instances=<INSTANCE_CONNECTION_NAME>=tcp:5432",
                    "-credential_file=/secrets/cloudsql/credentials.json"]
          volumeMounts:
            - name: cloudsql-instance-credentials
              mountPath: /secrets/cloudsql
              readOnly: true
        # [END proxy_container]
      # [START volumes]
      volumes:
        - name: cloudsql-instance-credentials
          secret:
            secretName: cloudsql-instance-credentials
      # [END volumes]
  

Postgres schema visualization

option 1:

1
2
3
4
5
6
7
8
9
10
11
12
13
download schemaspy jar from https://github.com/schemaspy/schemaspy/releases/download/v6.0.0-rc2/schemaspy-6.0.0-rc2.jar

download postgre jdbc jar from https://jdbc.postgresql.org/download/postgresql-42.2.2.jar

brew install graphviz --with-librsvg --with-pango

java -jar schemaspy-6.0.0-rc2.jar -t pgsql \
-s public -db rules_store_test -u postgres -p mysecretpassword \
-host localhost -o /tmp \
-dp /Users/zcui/Workspace/Tools/postgre/postgresql-42.2.2.jar

option 2:(not working yet)

1
2
docker run -it --rm -v /docs/data:/data mnuessler/schemaspy -hq \
 -t pgsql -host localhost -u postgres -p q1w2e3r4 -db key_vault -o /data/leads