PostgresDB
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
- 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