![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>Dans le m\u00eame esprit que ce qui avait \u00e9t\u00e9 fait pour MySQL dans cet \u00e9pisode<\/a>, nous allons balayer les possibilit\u00e9s qu’offre la solution Cloud SQL PostgreSQL <\/em> du cloud de Moutain View. <\/p>\n Pour la revue du d\u00e9cor (briques de service, organisation territoriale des r\u00e9gions, tiers et stockage, etc…) se r\u00e9f\u00e9rer \u00e0 l’article pr\u00e9-cit\u00e9. Ces notions s’appliquent aux 3 PaaS Cloud SQL que sont MySQL, PostgreSQL et le petit dernier SQL Server (la GA est sortie en f\u00e9vrier l’ann\u00e9e derni\u00e8re<\/a>), donc on ne reviendra pas dessus. Dans cet article, nous allons surtout voir quelles peuvent \u00eatre les diff\u00e9rences notables d’utilisation de PostgreSQL entre une instance on-prem <\/em>et une instance Cloud SQL. <\/p>\n Pour ce qui est des versions, Cloud SQL supporte PostgreSQL 13 depuis novembre 2020, et \u00e9tait le premier clouder majeur \u00e0 adopter la derni\u00e8re release PostgreSQL puisqu’Amazon n’a annonc\u00e9 la disponibilit\u00e9 qu’en f\u00e9vrier de cette ann\u00e9e<\/a>, et Azure ne va pas au del\u00e0 de la version 11 en Single Server<\/a> encore aujourd’hui (toutefois 12 et 13 sont disponible en Flexible et Hyperscale). <\/p>\n 47 extensions<\/a> sont support\u00e9es parmi lesquelles les principales : Gin, GiST, hstore, intarray, pgaudit, pgcrypto, pgstattuple, l’incontournable pg_stat_statements et \u00e9videmment plpgsql. A noter que CREATE EXTENSION est la seule commande qui n\u00e9cessite les privil\u00e8ges SUPERUSER qui soit autoris\u00e9e. <\/p>\n Pour avoir l’exhaustivit\u00e9:<\/p>\n Pour activer une extension, par exemple pg_stat_statements<\/a>:<\/p>\n Et configurer le nombre max de requ\u00eates retenues dans la vue:<\/p>\n Quant aux limitations, on peut retrouver notamment: Les param\u00e8tres de configuration de PostgreSQL sont configurables via les Database Flags<\/a>. Comme ALTER SYSTEM est d\u00e9sactiv\u00e9 il faudra passer soit par la console, soit la ligne de commande gloud, ou une API REST. Il existe 134 Database Flags en version POSTGRESQL_13:<\/p>\n Attention \u00e0 l’amalgame: un select sur pg_settings remontera nettement plus de param\u00e8tres que de flags configurables, ils ne seront tout simplement pas modifiables sur GCP. Par exemple s’il est possible de modifier work_mem <\/em>:<\/p>\n On ne peut en revanche pas toucher \u00e0 d’autres param\u00e8tres comme shared_buffers <\/em>…<\/p>\n …ou max_connections <\/em>qui sont directement associ\u00e9s au type de tiers disponible: Google recommande d’utilisation cloud SQL Proxy<\/a> pour se connecter de mani\u00e8re s\u00e9curis\u00e9e sur les PaaS Cloud SQL. Il s’agit d’un middleware ex\u00e9cut\u00e9 du c\u00f4t\u00e9 applicatif, qui s’interconnecte avec une autre couche middleware c\u00f4t\u00e9 PaaS: Cloud SQL Proxy se connecte au PaaS avec un compte de service d\u00e9clar\u00e9 dans la partie IAM de gestion des identit\u00e9s de GCP, et ce compte doit disposer au moins du privil\u00e8ge cloudsql.instances.connect<\/em>, qui peut \u00eatre obtenu via les r\u00f4les Cloud SQL Admin, Editor ou Client selon ce qui est requis pour l’application. Une cl\u00e9 au format JSON (-credential_file<\/em>) est g\u00e9n\u00e9r\u00e9e et doit \u00eatre d\u00e9pos\u00e9e sur la machine cliente depuis laquelle le programme Cloud SQL Proxy sera ex\u00e9cut\u00e9:<\/p>\n Une fois le proxy lanc\u00e9 (il est conseill\u00e9 de l’int\u00e9grer dans un service type systemd<\/em>, ou dans un container), on peut ensuite lancer la connexion via un psql classique ou un autre programme client, redirig\u00e9 vers le proxy qui tourne en localhost:<\/p>\n Ca peut para\u00eetre lourd, mais cela permet de centraliser les acc\u00e8s aux PaaS Cloud SQL d’un m\u00eame Project avec un seul service Cloud SQL Proxy. Evidemment ce mode supporte l’utilisation de certificats SSL ce qui sera conseill\u00e9 si les acc\u00e8s clients sont initi\u00e9s depuis l’ext\u00e9rieur et sans VPN. <\/p>\n Deux types de sauvegardes disponibles, automatiques <\/strong>et \u00e0 la demande<\/strong> ou manuels<\/em>. <\/p>\n Les backups automatiques sont activ\u00e9s au niveau du PaaS, soit \u00e0 la cr\u00e9ation soit plus tard via une modification de param\u00e8tre instance. Ils sont ex\u00e9cut\u00e9s lors de fen\u00eatres de maintenance de 4 heures, et sont conserv\u00e9s soit sur une p\u00e9riode soit en fonction d’un nombre de backups (7 par d\u00e9faut, max 365):<\/p>\n A noter que leur localisation est \u00e9galement param\u00e9trable, si on souhaite favoriser une r\u00e9gion plut\u00f4t qu’une autre en fonction de contraintes l\u00e9gales. L’activation de la restauration en Point-In-Time active la sauvegarde des WALs en plus, avec une r\u00e9tention en jours (7 par d\u00e9faut). Les journaux ant\u00e9rieurs au dernier backup complet effectu\u00e9 sont automatiquement supprim\u00e9s. <\/p>\n Il est aussi possible d’effectuer des backups \u00e0 la demande avec gcloud (ou la console ou API REST):<\/p>\n Sinon, un DESCRIBE sur chaque BACKUP_ID permettra de faire la distinction :<\/p>\n Mais qu’ils soient automatiques ou manuels, ils ne sont pas exportables<\/strong>, c’est \u00e0 dire que l’on ne peut pas r\u00e9cup\u00e9rer les fichiers r\u00e9sultants d’un backup. Et comme lorsque l’on supprime un PaaS, tous les backups sont supprim\u00e9s, il faudra pouvoir exporter les donn\u00e9es pour se donner une solution de retour arri\u00e8re en cas de probl\u00e8me, soit via un client pg_dump classique :<\/p>\n … soit via gcloud dans un bucket GS (qui ne sortira ni fonction, ni proc\u00e9dure stock\u00e9e, ni trigger contrairement \u00e0 pg_dump), le compte de service du PaaS devra disposer des privil\u00e8ges storage.legacyBucketWriter<\/em> et storage.legacyObjectReader<\/em> sur le bucket en question:<\/p>\n On peut restaurer sur le PaaS courant ou cr\u00e9er un autre PaaS avec un backup. Pour la restauration d’un backup complet :<\/p>\n Par contre si on veut restaurer en Point-in-Time, il faudra restaurer sur une nouvelle instance que l’on va cloner:<\/p>\n L’ancienne instance reste accessible. <\/p>\n Quant aux exports, soit rejouer avec pg_restore ou psql selon le format de sortie (custom <\/em>ou plain text<\/em> respectivement), ou r\u00e9importer la sauvegarde avec gcloud:<\/p>\n Avec psql : <\/p>\n Avec gcloud:<\/p>\n Note<\/strong>: Pour les imports et les exports customs de donn\u00e9es dans des fichiers plats ou CSV, noter que la commande COPY n\u00e9cessite le privil\u00e8ge SUPERUSER donc elle n’est pas utilisable dans un script. Elle fonctionnera seulement si les donn\u00e9es sont fournies sur l’entr\u00e9e standard (stdin<\/em>). Pour contourner ce probl\u00e8me sous le prompt psql, il est possible d’utiliser la m\u00e9ta-commande \\copy<\/strong> :<\/p>\n Comme les autres services manag\u00e9s chez les concurrents, il existe une fen\u00eatre de maintenance impos\u00e9e<\/em> en quelque sorte qui permet de faire passer les mises \u00e0 jour. Durant cette p\u00e9riode l’acc\u00e8s au PaaS est coup\u00e9, le doc indique une coupure de 60 secondes en moyenne<\/a>, mais la vraie difficult\u00e9 est de pouvoir pr\u00e9voir cette coupure, d’autant que les instances r\u00e9plicas ne peuvent \u00eatre sollicit\u00e9s sur cette p\u00e9riode car ils sont coup\u00e9s eux-aussi. <\/p>\n Comme sur RDS, il est possible de planifier les fen\u00eatres de maintenance sur des p\u00e9riodes attendues pour \u00e9viter qu’elles n’arrivent de mani\u00e8re al\u00e9atoire. <\/p>\n Il est \u00e9galement possible de planifier une p\u00e9riode durant laquelle la maintenance ne peut pas s’appliquer avec un maximum de 90 jours, par exemple pour couvrir une p\u00e9riode d’activit\u00e9 de vente particuli\u00e8rement importante pour votre chiffre d’affaires (ventes priv\u00e9es, soldes, p\u00e9riode de f\u00eates, etc…). La p\u00e9riode d’exclusion peut \u00eatre r\u00e9currente tous les ans si on n’indique pas l’ann\u00e9e dans les param\u00e8tres mais seulement une date au format mm-dd<\/em>: <\/p>\n On peut enfin s’abonner \u00e0 des notifications pour \u00eatre pr\u00e9venu d’une coupure prochaine, via email, dans les pr\u00e9f\u00e9rences de l’utilisateur :<\/p>\n L’acc\u00e8s aux journaux de PostgreSQL peut se faire soit via la console soit via gcloud loggging :<\/p>\n Avec un outil comme jq<\/a>, il devient possible de r\u00e9cup\u00e9rer pr\u00e9cis\u00e9ment des informations contenues dans le blob JSON. Une autre solution peut consister \u00e0 exporter les logs sur un autre stockage (Cloud Storage, BigTable ou Elastic Cloud par exemple) via des sinks<\/a>.<\/p>\n La console permet d’acc\u00e9der \u00e0 8 compteurs de base:<\/p>\n La brique Cloud Monitoring permet d’aller un peu plus loin et se cr\u00e9er des dashboards plus complets avec davantage de m\u00e9triques: Query Insights <\/a>donne une premi\u00e8re vision des requ\u00eates en ex\u00e9cution si l’instance est activ\u00e9e avec la trace Insight :<\/p>\n Elle est non payante si on y acc\u00e8de via la console, et avec une semaine de r\u00e9tention. Elle pr\u00e9sente les requ\u00eates avec leur temps d’ex\u00e9cution, le nombre de calls et le nombre moyen de lignes retourn\u00e9es. Deux choses \u00e0 noter cependant: L’autre bonne nouvelle c’est que pg_stat_statements <\/a>est \u00e9galement disponible et que les actions SUPERUSER-only sont autoris\u00e9es (modifier les param\u00e8tres GUC de PGSS ou appeler pg_stat_statements_reset()). On peut donc se construire un outil ou porter un existant sur des PaaS GCP sans trop de probl\u00e8mes \u00e0 priori. <\/p>\n Pour planifier des t\u00e2ches hors backups type vacuum full, etc… on pourra utiliser la brique Cloud Scheduler<\/a> qui permet via des Cloud Functions serverless et des jobs de lancer des t\u00e2ches planifi\u00e9es. <\/p>\n On ne rentrera pas dans les d\u00e9tails, l\u00e0 encore \u00e7a pourra faire l’objet d’un post d\u00e9di\u00e9, mais globalement lorsque l’on d\u00e9ploie un PaaS PostgreSQL, il est possible d’activer une haute disponibilit\u00e9 au niveau r\u00e9gional. Une instance standby sera cr\u00e9\u00e9 dans une autre zone de la m\u00eame r\u00e9gion, et la synchronisation sera assur\u00e9e au niveau blocs disques. <\/p>\n Cloud SQL \u00e9tant une ressource r\u00e9gionale, il n’est pas possible de cr\u00e9\u00e9er une standby dans une autre r\u00e9gion, donc pour se pr\u00e9munir d’une indisponibilit\u00e9 de toute une r\u00e9gion, la solution est de cr\u00e9er un read-replica dans une autre r\u00e9gion, mais qui sera r\u00e9pliqu\u00e9 de mani\u00e8re asynchrone:<\/p>\n Dans le cas d’une indisponibilit\u00e9 de zone, l’utilisation de cloud_sql_proxy permettra une bascule transparente vers la standby. Toutefois en cas de perte de la r\u00e9gion, la bascule vers le read r\u00e9plica devra se faire manuellement via une proc\u00e9dure<\/a>, qui consistera globalement \u00e0 : Contrairement \u00e0 Cloud SQL pour MySQL<\/a>, Cloud SQL pour PostgreSQL ne supporte pas la r\u00e9plication depuis une source externe, donc l’option de migrer un cluster PostgreSQL on-prem via la r\u00e9plicaiton est exclu. Il reste la solution d’importer directement un pg_dump dans une instance PaaS mais cela supposera une indisponibilit\u00e9 plus grande et qui sera fonction du volume \u00e0 manipuler. Autre solution, utiliser le Cloud Migration Service <\/a>mais dont l’\u00e9tendue des fonctionnalit\u00e9s d\u00e9passe largement le scope de cet article.<\/p>\n Lorsque j’avais \u00e9valu\u00e9 la premi\u00e8re g\u00e9n\u00e9ration de PaaS PostgreSQL sur GCP en 2018, j’avais trouv\u00e9 pas mal de lacunes, presque rien pour monitorer, une faible surface de configuration. <\/p>\n Le moins qu’on puisse dire c’est que depuis des efforts ont \u00e9t\u00e9 faits pour r\u00e9duire la fracture entre les capacit\u00e9s on-prem et les capacit\u00e9s d’un PaaS \u00e9quivalent: extensions, interfaces, versions, param\u00e8tres GUC, tout y passe. Le seul couac qui reste encore c’est de franchir la premi\u00e8re marche (la migration vers le PaaS) surtout si l’on a une grande quantit\u00e9 de donn\u00e9es \u00e0 migrer. Il faudra tester l’assistant de migration, peut \u00eatre dans un prochain \u00e9pisode !<\/p>\n A+ ~David<\/p>\n Dans le m\u00eame esprit que ce qui avait \u00e9t\u00e9 fait pour MySQL dans cet \u00e9pisode, nous allons balayer les possibilit\u00e9s qu’offre la solution Cloud SQL PostgreSQL du cloud de Moutain View. Pour la revue du d\u00e9cor (briques de service, organisation… Continuer la lecture Versions, limitations et extensions support\u00e9es:<\/h1>\n
\r\npostgres=> select setting from pg_settings where name='cloudsql.supported_extensions' ;\r\n-[ RECORD 1 ]---------------------------------------------------------------------------------------\r\nsetting | address_standardizer:3.0.2, address_standardizer_data_us:3.0.2, bloom:1.0, btree_gin:1.3, \r\nbtree_gist:1.5, citext:1.6, cube:1.4, dblink:1.2, dict_int:1.0, dict_xsyn:1.0, earthdistance:1.1,\r\n fuzzystrmatch:1.1, hll:2.14, hstore:1.7, intagg:1.1, intarray:1.3, ip4r:2.4, isn:1.2, lo:1.1, \r\nltree:1.2, pageinspect:1.8, pg_buffercache:1.3, pg_freespacemap:1.2, pg_partman:4.4.0, \r\npg_prewarm:1.2, pg_repack:1.4.6, pg_similarity:1.0, pg_stat_statements:1.8, pg_trgm:1.5,\r\n pg_visibility:1.2, pgaudit:1.5, pgcrypto:1.3, pgfincore:1.2, pglogical:2.3.3, pgrowlocks:1.2, \r\npgstattuple:1.5, pgtap:1.1.0, plpgsql:1.0, plproxy:2.10.0, postgis:3.0.2, postgis_raster:3.0.2,\r\n postgis_sfcgal:3.0.2, postgis_tiger_geocoder:3.0.2, postgis_topology:3.0.2, postgres_fdw:1.0,\r\n prefix:1.2.0, sslinfo:1.2, tablefunc:1.0, tsm_system_rows:1.0, tsm_system_time:1.0, \r\nunaccent:1.1, uuid-ossp:1.1\r\n<\/pre>\n
\r\npostgres=> CREATE EXTENSION pg_stat_statements ;\r\nCREATE EXTENSION\r\n\r\npostgres=> select * from pg_extension ;\r\n-[ RECORD 1 ]--+-------------------\r\noid | 14026\r\nextname | plpgsql\r\nextowner | 10\r\nextnamespace | 11\r\nextrelocatable | f\r\nextversion | 1.0\r\nextconfig |\r\nextcondition |\r\n-[ RECORD 2 ]--+-------------------\r\noid | 16444\r\nextname | pg_stat_statements\r\nextowner | 16389\r\nextnamespace | 2200\r\nextrelocatable | t\r\nextversion | 1.8\r\nextconfig |\r\nextcondition |\r\n\r\n<\/pre>\n
\r\nC:\\Program Files (x86)\\Google\\Cloud SDK>gcloud sql instances patch cloudpg13 \\\r\n --database-flags pg_stat_statements.max=10000\r\nThe following message will be used for the patch API method.\r\n{"name": "cloudpg13", "project": "paas-postgresql-mysql", "settings": {"databaseFlags": [{"name": "pg_stat_statements.max", "value": "10000"}]}}\r\nWARNING: This patch modifies database flag values, which may require\r\nyour instance to be restarted. Check the list of supported flags -\r\nhttps:\/\/cloud.google.com\/sql\/docs\/postgres\/flags - to see if your\r\ninstance will be restarted when this patch is submitted.\r\n\r\nDo you want to continue (Y\/n)? Y\r\n\r\nPatching Cloud SQL instance...done.\r\nUpdated [https:\/\/sqladmin.googleapis.com\/sql\/v1beta4\/projects\/paas-postgresql-mysql\/instances\/cloudpg13].\r\n<\/pre>\n\r\npostgres=> select query, total_plan_time, total_plan_time, total_exec_time, calls, shared_blks_hit\r\npostgres-> from pg_stat_statements order by total_exec_time limit 10 ;\r\n-[ RECORD 1 ]---+------------------------------------------------------------------------------------\r\nquery | show max_connections\r\ntotal_plan_time | 0\r\ntotal_plan_time | 0\r\ntotal_exec_time | 0.008291\r\ncalls | 1\r\nshared_blks_hit | 0\r\n-[ RECORD 2 ]---+------------------------------------------------------------------------------------\r\nquery | show max_wal_size\r\ntotal_plan_time | 0\r\ntotal_plan_time | 0\r\ntotal_exec_time | 0.008558\r\ncalls | 1\r\nshared_blks_hit | 0\r\n(...)\r\n<\/pre>\n
\n– L’indisponibilit\u00e9 de LLVM\/JiT et de la r\u00e9plication logique pour les versions 12 et 13 pour l’instant.
\n– Toute commande n\u00e9cessitant un acc\u00e8s aux privil\u00e8ges SUPERUSER : ALTER SYSTEM, COPY (hors stdin), op\u00e9rations sur les backends, pg_switch_wal(), pg_reload_conf(), op\u00e9rations externes sur les fichiers, etc… la liste exhaustive se trouve dans la doc PostgreSQL<\/a>. <\/p>\nSurface de param\u00e9trage:<\/h1>\n
\r\n$ gcloud sql flags list \\\r\n --database-version=POSTGRES_13 \\\r\n --sort-by=NAME\r\nNAME TYPE DATABASE_VERSION ALLOWED_VALUES\r\nautovacuum BOOLEAN POSTGRES_9_6,POSTGRES_10,POSTGRES_11,POSTGRES_12,POSTGRES_13\r\nautovacuum_analyze_scale_factor FLOAT POSTGRES_9_6,POSTGRES_10,POSTGRES_11,POSTGRES_12,POSTGRES_13\r\nautovacuum_analyze_threshold INTEGER POSTGRES_9_6,POSTGRES_10,POSTGRES_11,POSTGRES_12,POSTGRES_13\r\nautovacuum_freeze_max_age INTEGER POSTGRES_9_6,POSTGRES_10,POSTGRES_11,POSTGRES_12,POSTGRES_13\r\n(...)\r\nvacuum_freeze_table_age INTEGER POSTGRES_9_6,POSTGRES_10,POSTGRES_11,POSTGRES_12,POSTGRES_13\r\nvacuum_multixact_freeze_min_age INTEGER POSTGRES_9_6,POSTGRES_10,POSTGRES_11,POSTGRES_12,POSTGRES_13\r\nvacuum_multixact_freeze_table_age INTEGER POSTGRES_9_6,POSTGRES_10,POSTGRES_11,POSTGRES_12,POSTGRES_13\r\nwork_mem INTEGER POSTGRES_9_6,POSTGRES_10,POSTGRES_11,POSTGRES_12,POSTGRES_13\r\n<\/pre>\n
\r\n$ gcloud sql instances patch cloudpg13 \\\r\n --database-flags work_mem=128\r\nThe following message will be used for the patch API method.\r\n{"name": "cloudpg13", "project": "paas-postgresql-mysql", "settings": {"databaseFlags": [{"name": "work_mem", "value": "128"}]}}\r\nWARNING: This patch modifies database flag values, which may require\r\nyour instance to be restarted. Check the list of supported flags -\r\nhttps:\/\/cloud.google.com\/sql\/docs\/postgres\/flags - to see if your\r\ninstance will be restarted when this patch is submitted.\r\n\r\nDo you want to continue (Y\/n)? Y\r\n\r\nPatching Cloud SQL instance...done.\r\nUpdated [https:\/\/sqladmin.googleapis.com\/sql\/v1beta4\/projects\/paas-postgresql-mysql\/instances\/cloudpg13].\r\n\r\n$ gcloud sql instances describe cloudpg13\r\n(...)\r\n databaseFlags:\r\n - name: work_mem\r\n value: '128'\r\n<\/pre>\npostgres=> show shared_buffers;\r\n shared_buffers\r\n----------------\r\n 1229MB\r\n(1 row)\r\n\r\npostgres=> alter system set shared_buffers to '1400MB' ;\r\nERROR: must be superuser to execute ALTER SYSTEM command\r\n\r\n<\/pre>\n
\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2021\/06\/maxconnections.png\")
<\/p>\nConnectivit\u00e9:<\/h1>\n
\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2021\/06\/cloudsqlproxy-1024x416.png\")
\nIl faudra r\u00e9cup\u00e9rer le programme client<\/a> en fonction du type de client (Linux, windows,etc…). <\/p>\n$ cloud_sql_proxy_x64 \\\r\n -credential_file=paas-postgresql-mysql-0a7b74d61a31.json \\\r\n -instances="paas-postgresql-mysql:europe-west1:cloudpg13=tcp:5432"\r\n2021\/06\/05 15:39:12 using credential file for authentication; email=xxxxxxxxxxxx@paas-postgresql-mysql.iam.gserviceaccount.com\r\n2021\/06\/05 15:39:13 Listening on 127.0.0.1:5432 for paas-postgresql-mysql:europe-west1:cloudpg13\r\n2021\/06\/05 15:39:13 Ready for new connections\r\n2021\/06\/05 15:39:24 New connection for "paas-postgresql-mysql:europe-west1:cloudpg13"\r\n<\/pre>\n
$ psql "host=127.0.0.1 sslmode=disable dbname=postgres user=postgres"\r\nPassword for user postgres:\r\npsql (13.3, server 13.2)\r\npostgres=> select version() ;\r\n version\r\n------------------------------------------------------------------------------------------\r\n PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by Debian clang version 10.0.1 , 64-bit\r\n(1 row)\r\n<\/pre>\n
Sauvegarde et restauration:<\/h1>\n
Sauvegardes automatiques<\/h2>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2021\/06\/autobackup-1.png\")
<\/p>\nSauvegardes \u00e0 la demande<\/h2>\n
\r\n$ gcloud sql backups create --instance=cloudpg13 --location=europe-west1 --description=MANUAL\r\nBacking up Cloud SQL instance...done.\r\n[https:\/\/sqladmin.googleapis.com\/sql\/v1beta4\/projects\/paas-postgresql-mysql\/instances\/cloudpg13] backed up.\r\n\r\n$ gcloud sql backups list --instance=cloudpg13\r\nID WINDOW_START_TIME ERROR STATUS INSTANCE\r\n1623055883991 2021-06-07T08:51:23.991+00:00 - SUCCESSFUL cloudpg13\r\n1623055751008 2021-06-07T08:49:11.008+00:00 - SUCCESSFUL cloudpg13\r\n1622640837128 2021-06-02T13:33:57.128+00:00 - SUCCESSFUL cloudpg13\r\n\r\n$ gcloud sql backups list --instance=cloudpg13 --filter="type=AUTOMATED"\r\nID WINDOW_START_TIME ERROR STATUS INSTANCE\r\n1622640837128 2021-06-02T13:33:57.128+00:00 - SUCCESSFUL cloudpg13\r\n\r\n$ gcloud sql backups list --instance=cloudpg13 --filter="type=ON_DEMAND"\r\nID WINDOW_START_TIME ERROR STATUS INSTANCE\r\n1623055883991 2021-06-07T08:51:23.991+00:00 - SUCCESSFUL cloudpg13\r\n1623055751008 2021-06-07T08:49:11.008+00:00 - SUCCESSFUL cloudpg13\r\n<\/pre>\n
\r\n$ gcloud sql backups describe 1623055883991 --instance=cloudpg13\r\nbackupKind: SNAPSHOT\r\ndescription: MANUAL\r\nendTime: '2021-06-07T08:52:04.569Z'\r\nenqueuedTime: '2021-06-07T08:51:23.991Z'\r\nid: '1623055883991'\r\ninstance: cloudpg13\r\nkind: sql#backupRun\r\nlocation: europe-west1\r\nselfLink: https:\/\/sqladmin.googleapis.com\/sql\/v1beta4\/projects\/paas-postgresql-mysql\/instances\/cloudpg13\/backupRuns\/1623055883991\r\nstartTime: '2021-06-07T08:51:23.992Z'\r\nstatus: SUCCESSFUL\r\ntype: ON_DEMAND\r\nwindowStartTime: '2021-06-07T08:51:23.991Z'\r\n\r\n$ gcloud sql backups describe 1622640837128 --instance=cloudpg13\r\nbackupKind: SNAPSHOT\r\nendTime: '2021-06-02T13:35:18.283Z'\r\nenqueuedTime: '2021-06-02T13:33:57.128Z'\r\nid: '1622640837128'\r\ninstance: cloudpg13\r\nkind: sql#backupRun\r\nlocation: eu\r\nselfLink: https:\/\/sqladmin.googleapis.com\/sql\/v1beta4\/projects\/paas-postgresql-mysql\/instances\/cloudpg13\/backupRuns\/1622640837128\r\nstartTime: '2021-06-02T13:33:57.136Z'\r\nstatus: SUCCESSFUL\r\ntype: AUTOMATED\r\nwindowStartTime: '2021-06-02T13:33:57.128Z'\r\n<\/pre>\n
Exports de donn\u00e9es<\/h2>\n
\r\n$ pg_dump --host=127.0.0.1 --username=postgres --dbname=dvdrental > backup.dmp\r\nPassword:\r\n<\/pre>\n
\r\n# Creation du bucket\r\n$ gsutil mb -c NEARLINE -l europe-west1 -b on gs:\/\/cloudpg13backups\r\nCreating gs:\/\/cloudpg13backups\/...\r\n\r\n$ gsutil ls\r\ngs:\/\/cloudpg13backups\/\r\n\r\n# Attribution des droits au compte de service\r\n$ gsutil iam ch serviceAccount:xxxxxxxxxxx-xxxxxxxx@gcp-sa-cloud-sql.iam.gserviceaccount.com:legacyBucketWriter,legacyObjectReader gs:\/\/cloudpg13backups\r\n\r\n# Export des donn\u00e9es dans le bucket\r\n$ gcloud sql export sql cloudpg13 gs:\/\/cloudpg13backups\/cloudpg13.dvdrental.dmp.gz --database=dvdrental\r\nExporting Cloud SQL instance...done.\r\nExported [https:\/\/sqladmin.googleapis.com\/sql\/v1beta4\/projects\/paas-postgresql-mysql\/instances\/cloudpg13] to [gs:\/\/cloudpg13backups\/cloudpg13.dvdrental.dmp.gz].\r\n\r\n$ gsutil ls gs:\/\/cloudpg13backups\r\ngs:\/\/cloudpg13backups\/cloudpg13.dvdrental.dmp.gz\r\n\r\n# Et r\u00e9cup\u00e9ration en local ...\r\n$ gsutil cp gs:\/\/cloudpg13backups\/cloudpg13.dvdrental.dmp.gz cloudpg13.dvdrental.dmp.gz\r\nCopying gs:\/\/cloudpg13backups\/cloudpg13.dvdrental.dmp.gz...\r\n- [1 files][ 8.3 KiB\/ 8.3 KiB]\r\nOperation completed over 1 objects\/8.3 KiB.\r\n<\/pre>\n
Restauration:<\/h2>\n
\r\n$ gcloud sql backups describe 1623083406184 --instance=cloudpg13\r\nbackupKind: SNAPSHOT\r\ndescription: 2021-06-07 18:31\r\nendTime: '2021-06-07T16:30:46.801Z'\r\nenqueuedTime: '2021-06-07T16:30:06.184Z'\r\nid: '1623083406184'\r\ninstance: cloudpg13\r\nkind: sql#backupRun\r\nlocation: europe-west1\r\nselfLink: https:\/\/sqladmin.googleapis.com\/sql\/v1beta4\/projects\/paas-postgresql-mysql\/instances\/cloudpg13\/backupRuns\/1623083406184\r\nstartTime: '2021-06-07T16:30:06.185Z'\r\nstatus: SUCCESSFUL\r\ntype: ON_DEMAND\r\nwindowStartTime: '2021-06-07T16:30:06.184Z'\r\n\r\n$ gcloud sql backups restore 1623083406184 --restore-instance=cloudpg13 --backup-instance=cloudpg13\r\nAll current data on the instance will be lost when the backup is\r\nrestored.\r\n\r\nDo you want to continue (Y\/n)? Y\r\n\r\nRestoring Cloud SQL instance...done.\r\nRestored [https:\/\/sqladmin.googleapis.com\/sql\/v1beta4\/projects\/paas-postgresql-mysql\/instances\/cloudpg13].\r\n<\/pre>\n
\r\n$ gcloud sql instances clone cloudpg13 cloudpg13-202106071200 --point-in-time 2021-06-07T17:00:00.000Z\r\nCloning Cloud SQL instance...\\\r\nCreated [https:\/\/sqladmin.googleapis.com\/sql\/v1beta4\/projects\/paas-postgresql-mysql\/instances\/cloudpg13-202106071200].\r\nNAME DATABASE_VERSION LOCATION TIER PRIMARY_ADDRESS PRIVATE_ADDRESS STATUS\r\ncloudpg13-202106071200 POSTGRES_13 europe-west1-b db-custom-1-3840 35.187.59.124 - RUNNABLE\r\n<\/pre>\n
\r\npostgres=> create database dvdrental ;\r\nCREATE DATABASE\r\npostgres=> \\c dvdrental\r\npsql (13.3, server 13.2)\r\nYou are now connected to database "dvdrental" as user "postgres".\r\ndvdrental=> \\i backup.dmp\r\nCREATE TABLE\r\nALTER TABLE\r\nCOPY 200\r\nCOPY 603\r\nCOPY 16\r\nCOPY 600\r\nCOPY 109\r\nCOPY 599\r\nCOPY 1000\r\n(...)\r\n<\/pre>\n
\r\n$ gcloud sql import sql cloudpg13 gs:\/\/cloudpg13backups\/cloudpg13.dvdrental.dmp.gz \\\r\n --database=dvdrental --user=postgres\r\nData from [gs:\/\/cloudpg13backups\/cloudpg13.dvdrental.dmp.gz] will be\r\nimported to [cloudpg13].\r\n\r\nDo you want to continue (Y\/n)? Y\r\n\r\nImporting data into Cloud SQL instance...done.\r\nImported data from [gs:\/\/cloudpg13backups\/cloudpg13.dvdrental.dmp.gz] into [https:\/\/sqladmin.googleapis.com\/sql\/v1beta4\/projects\/paas-postgresql-mysql\/instances\/cloudpg13].\r\n<\/pre>\n
\r\ndvdrental=> COPY public.actor (actor_id, first_name, last_name, last_update) FROM '3057.dat';\r\nERROR: must be superuser or a member of the pg_read_server_files role to COPY from a file\r\nHINT: Anyone can COPY to stdout or from stdin. psql's \\copy command also works for anyone.\r\n\r\ndvdrental=> \\copy public.actor (actor_id, first_name, last_name, last_update) FROM '3057.dat';\r\nCOPY 200\r\n<\/pre>\n
Mise \u00e0 jour et maintenance:<\/h2>\n
\r\n$ gcloud sql instances patch cloudpg13 \\\r\n --maintenance-window-day=SUN \\\r\n --maintenance-window-hour=04\r\n<\/pre>\n
\r\n$ gcloud sql instances patch cloudpg13 \\\r\n --deny-maintenance-period-start-date 06-01 \\\r\n --deny-maintenance-period-end-date 06-30 \\\r\n --deny-maintenance-period-time 00:00:00\r\n<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2021\/06\/gloudpref.png\")
<\/p>\nSupervision :<\/h2>\n
\n
\r\n$ gcloud logging read resource.type="cloudsql_database" --project=paas-postgresql-mysql --limit=1 --format=json\r\n[\r\n {\r\n "insertId": "s=4a2d092448f2442196dbf937d53aa2f3;i=11b17e;b=8d370594a8c64ab6bf7936578969b58d;m=2f32331f4e;t=5c50feb23e9aa;x=56218d300d4ea42d-0-0@a1",\r\n "logName": "projects\/paas-postgresql-mysql\/logs\/cloudsql.googleapis.com%2Fpostgres.log",\r\n "receiveTimestamp": "2021-06-18T20:10:45.252620488Z",\r\n "resource": {\r\n "labels": {\r\n "database_id": "paas-postgresql-mysql:cloudpg13",\r\n "project_id": "paas-postgresql-mysql",\r\n "region": "europe"\r\n },\r\n "type": "cloudsql_database"\r\n },\r\n "severity": "INFO",\r\n "textPayload": "2021-06-18 20:10:43.733 UTC [52719]: [2-1] db=,user= LOG: \r\n automatic analyze of table \\"cloudsqladmin.public.heartbeat\\" system usage: \r\n CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s",\r\n "timestamp": "2021-06-18T20:10:43.733930Z"\r\n }\r\n]\r\n<\/pre>\n\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2021\/06\/gcloudperf.png\")
<\/p>\n\n
\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2021\/06\/cloudperf2.png\")
\nAinsi que se cr\u00e9er des alertes avec notification.<\/p>\n\n
\r\n$ gcloud sql instances create ... --insights-config-query-insights-enabled\r\n<\/pre>\n
\n– Elle est capable de normaliser les requ\u00eates et de g\u00e9n\u00e9rer un queryid (query_hash <\/em>pour gcloud) comme peut le faire pg_stat_statements:<\/p>\n\r\ndvdrental=> select count(1) from inventory where film_id = 995 ;\r\n count\r\n-------\r\n 6\r\n(1 row)\r\n\r\ndvdrental=> select count(1) from inventory where film_id = 1000 ;\r\n count\r\n-------\r\n 8\r\n(1 row)\r\n<\/pre>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2021\/06\/cloudperf7.png\")
\n– La deuxi\u00e8me il peut afficher un plan type avec le d\u00e9tail des op\u00e9rateurs: <\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2021\/06\/cloudperf4.png\")
\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2021\/06\/cloudperf5.png\")
<\/p>\nMCO hors backups : vacuums, gestion des partitions, etc…<\/h2>\n
Haute disponibilit\u00e9:<\/h2>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2021\/06\/cloud-sql-mysql-disaster-recovery-complete-failover-fallback-basic-architecture.png\")
\n(source <\/em>: https:\/\/cloud.google.com\/architecture\/intro-to-cloud-sql-disaster-recovery<\/a>)<\/p>\n
\n1) D\u00e9tacher le read-replica de son primaire en \u00e9chec.
\n2) Faire un promote sur le r\u00e9plica pour qu’il devienne primaire
\n3) S’assurer qu’aucune application ne peut plus de connecter \u00e0 l’ancien primaire ou standby dans la r\u00e9gion initiale, car il n’y a aucun garde-fou contre le split-brain
\n4) Faire pointer les applications sur le nouveau primaire dans la deuxi\u00e8me r\u00e9gion.
\n5) Potentiellement r\u00e9-impl\u00e9menter une standby r\u00e9gionale dans la deuxi\u00e8me r\u00e9gion, et un read r\u00e9plica dans une troisi\u00e8me r\u00e9gion pour se pr\u00e9munir d’une nouvelle perte de la r\u00e9gion 2.
\n Evidemment tout cela pourra \u00eatre automatis\u00e9 via gcloud ou Terraform<\/a> mais personnellement je ne suis pas un grand fan des solutions qui basculent toutes seules, m\u00eame on-premise. Il vaut mieux avoir une proc\u00e9dure robuste, que chaque \u00e9tape soit script\u00e9e mais garder la main sur l’encha\u00eenement me para\u00eet plus rassurant. <\/p>\nMigration on-prem vers Cloud SQL:<\/h2>\n
\nAttention \u00e0 la valeur de temp_file_limit <\/a> lors de la recr\u00e9ation des indexes. La valeur par d\u00e9faut pour mon PaaS en 13 est \u00e0 environ 2Gb alors qu’on-prem il n’y a pas de limitation. <\/p>\nBilan : <\/h2>\n
<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"