Design a process to migrate from an-db1001 to a cloudnativePG cluster
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	brouberol
	Sep 17 2024, 12:50 PM

Description

When we migrate the airflow instances from "Puppet" to Kubernetes, we'd need to retain all of the data currently stored in an-db1001.

Cloudnative-pg supports a pg_basebackup bootstrap mode that allows it to copy the data of a live PG cluster via streaming replication (see https://cloudnative-pg.io/documentation/current/bootstrap/#bootstrap-from-another-cluster). However, this only works between 2 clusters of the same major version. We currently run PG 13 on an-db1001.

To enable streaming replication, we could:

upgrade an-db1001 to bookworm, which would allow us to upgrade to PG 15
deploy a cloudnative PG cluster based on PG 13, and perform subsequent upgrades after the migration

Details

Subject	Repo	Branch	Lines +/-
airflow-test-k8s: run PG normally now that the data has been imported	operations/deployment-charts	master	+0 -9
allow airflow-deploy to deploy PG clusters	operations/deployment-charts	master	+5 -0
Redeploy postgresql-airflow-test-k8s	operations/deployment-charts	master	+39 -39
cloudnative-pg-cluster: facilitate the import of an external database	operations/deployment-charts	master	+45 -5

Customize query in gerrit

Related Objects
Search...

Status	Assigned	Task
Open	None	T362788 Migrate Airflow to the dse-k8s cluster
Resolved	BTullis	T364386 Validate postgres operator and Ceph integration
Resolved	brouberol	T374950 Design a process to migrate from an-db1001 to a cloudnativePG cluster

Event Timeline

brouberol created this task.Sep 17 2024, 12:50 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 17 2024, 12:50 PM

It seems we could also rely on https://cloudnative-pg.io/documentation/current/database_import/ which would perform a pg_dump of the andb1001 DB and pg_restore on the cloudnative side. It would mean having to shut down writes (probably meaning having to shut down Airflow itself). However, it seems that it would work even if both DBs are not running the same major version.

As per PostgreSQL recommendation, we suggest that the PostgreSQL major version of the destination cluster is greater or equal than the one of the source cluster.

brouberol edited projects, added Data-Platform-SRE (2024.09.06 - 2024.09.27); removed Data-Platform-SRE.Sep 23 2024, 10:19 AM

Change #1074968 had a related patch set uploaded (by Brouberol; author: Brouberol):

[operations/deployment-charts@master] cloudnative-pg-cluster: facilitate the import of an external database

https://gerrit.wikimedia.org/r/1074968

gerritbot added a project: Patch-For-Review.Sep 23 2024, 10:30 AM

I've deployed the following configuration along with the chart version worked on in https://gerrit.wikimedia.org/r/c/1074968

brouberol@deploy1003:~/cloudnative-pg-import$ cat values-postgresql-test.yaml
cluster:
  initdb:
    import:
      host: an-db1001.eqiad.wmnet
      user: airflow-test-k8s
      dbname: airflow-test-k8s
      password: [REDACTED]

external_services:
  postgresql: [analytics]

We see that an import pod is scheduled first, after which the cluster itself is deployed:

brouberol@deploy1003:~/cloudnative-pg-import$ k get pod -w
NAME                                         READY   STATUS     RESTARTS   AGE
postgresql-test-1-import-fjkdf               0/1     Init:0/1   0          4s
postgresql-test-pooler-rw-5c4f795956-9sm2z   0/1     Init:0/1   0          4s
postgresql-test-pooler-rw-5c4f795956-rz5v7   0/1     Init:0/1   0          4s
postgresql-test-pooler-rw-5c4f795956-xnh2j   0/1     Init:0/1   0          4s
postgresql-test-pooler-rw-5c4f795956-xnh2j   0/1     PodInitializing   0          4s
postgresql-test-pooler-rw-5c4f795956-rz5v7   0/1     PodInitializing   0          4s
postgresql-test-pooler-rw-5c4f795956-9sm2z   0/1     PodInitializing   0          4s
postgresql-test-pooler-rw-5c4f795956-xnh2j   0/1     Running           0          5s
postgresql-test-pooler-rw-5c4f795956-rz5v7   0/1     Running           0          5s
postgresql-test-pooler-rw-5c4f795956-9sm2z   0/1     Running           0          5s
postgresql-test-pooler-rw-5c4f795956-xnh2j   1/1     Running           0          6s
postgresql-test-pooler-rw-5c4f795956-rz5v7   1/1     Running           0          6s
postgresql-test-pooler-rw-5c4f795956-9sm2z   1/1     Running           0          6s
postgresql-test-1-import-fjkdf               0/1     Init:0/1          0          10s
postgresql-test-1-import-fjkdf               0/1     Init:0/1          0          10s
postgresql-test-1-import-fjkdf               0/1     PodInitializing   0          11s
postgresql-test-1-import-fjkdf               1/1     Running           0          12s
postgresql-test-1-import-fjkdf               0/1     Completed         0          18s
postgresql-test-1-import-fjkdf               0/1     Completed         0          19s
postgresql-test-1-import-fjkdf               0/1     Completed         0          20s
postgresql-test-1                            0/1     Pending           0          0s

It logs the following:

brouberol@deploy1003:~$ k logs -f postgresql-test-1-import-fjkdf
{"level":"info","ts":"2024-09-23T11:52:42Z","msg":"Creating new data directory","logging_pod":"postgresql-test-1-import","pgdata":"/var/lib/postgresql/data/pgdata","initDbOptions":["--username","postgres","-D","/var/lib/postgresql/data/pgdata","--waldir","/var/lib/postgresql/wal/pg_wal","--encoding=UTF8","--lc-collate=C","--lc-ctype=C"]}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"initdb","msg":"The files belonging to this database system will be owned by user \"postgres\".\nThis user must also own the server process.\n\nThe database cluster will be initialized with this locale configuration:\n  provider:    libc\n  LC_COLLATE:  C\n  LC_CTYPE:    C\n  LC_MESSAGES: C.UTF-8\n  LC_MONETARY: C.UTF-8\n  LC_NUMERIC:  C.UTF-8\n  LC_TIME:     C.UTF-8\nThe default text search configuration will be set to \"english\".\n\nData page checksums are disabled.\n\ncreating directory /var/lib/postgresql/data/pgdata ... ok\ncreating directory /var/lib/postgresql/wal/pg_wal ... ok\ncreating subdirectories ... ok\nselecting dynamic shared memory implementation ... posix\nselecting default max_connections ... 100\nselecting default shared_buffers ... 128MB\nselecting default time zone ... Etc/UTC\ncreating configuration files ... ok\nrunning bootstrap script ... ok\nperforming post-bootstrap initialization ... ok\nsyncing data to disk ... ok\n\n\nSuccess. You can now start the database server using:\n\n    pg_ctl -D /var/lib/postgresql/data/pgdata -l logfile start\n\n","pipe":"stdout","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"initdb","msg":"initdb: warning: enabling \"trust\" authentication for local connections\ninitdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.\n","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","msg":"Installed configuration file","logging_pod":"postgresql-test-1-import","pgdata":"/var/lib/postgresql/data/pgdata","filename":"custom.conf"}
{"level":"info","ts":"2024-09-23T11:52:43Z","msg":"Configuration optimized for import","logging_pod":"postgresql-test-1-import","filename":"override.conf"}
{"level":"info","ts":"2024-09-23T11:52:43Z","msg":"Starting up instance","logging_pod":"postgresql-test-1-import","pgdata":"/var/lib/postgresql/data/pgdata","options":["start","-w","-D","/var/lib/postgresql/data/pgdata","-o","-c port=5432 -c unix_socket_directories=/controller/run","-t 40000000","-o","-c listen_addresses='127.0.0.1'"]}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_ctl","msg":"waiting for server to start....2024-09-23 11:52:43.444 UTC [36] LOG:  redirecting log output to logging collector process","pipe":"stdout","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_ctl","msg":"2024-09-23 11:52:43.444 UTC [36] HINT:  Future log output will appear in directory \"/controller/log\".","pipe":"stdout","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:43.444 UTC","process_id":"36","session_id":"66f1568b.24","session_line_num":"1","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"ending log output to stderr","hint":"Future log output will go to log destination \"csvlog\".","backend_type":"postmaster","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:43.444 UTC","process_id":"36","session_id":"66f1568b.24","session_line_num":"2","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"starting PostgreSQL 15.8 (Debian 15.8-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit","backend_type":"postmaster","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:43.444 UTC","process_id":"36","session_id":"66f1568b.24","session_line_num":"3","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"listening on IPv4 address \"127.0.0.1\", port 5432","backend_type":"postmaster","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:43.444 UTC","process_id":"36","session_id":"66f1568b.24","session_line_num":"4","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"listening on Unix socket \"/controller/run/.s.PGSQL.5432\"","backend_type":"postmaster","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:43.446 UTC","process_id":"40","session_id":"66f1568b.28","session_line_num":"1","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"database system was shut down at 2024-09-23 11:52:43 UTC","backend_type":"startup","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:43.450 UTC","process_id":"36","session_id":"66f1568b.24","session_line_num":"5","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"database system is ready to accept connections","backend_type":"postmaster","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_ctl","msg":" done","pipe":"stdout","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_ctl","msg":"server started","pipe":"stdout","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","msg":"Configuring new PostgreSQL instance","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","msg":"Executing post-init SQL instructions","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","msg":"Executing post-init template SQL instructions","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","msg":"executing Application instructions","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","msg":"starting microservice clone process","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","msg":"exporting database","logging_pod":"postgresql-test-1-import","databaseName":"airflow-test-k8s"}
{"level":"info","ts":"2024-09-23T11:52:43Z","msg":"Running pg_dump","logging_pod":"postgresql-test-1-import","cmd":"pg_dump","options":["-Fc","-f","/var/lib/postgresql/data/pgdata/dumps/airflow-test-k8s.dump","-d","host='an-db1001.eqiad.wmnet' passfile='/controller/external/external-database/pgpass' port='5432' user='airflow-test-k8s' dbname=airflow-test-k8s","-v","--section=pre-data","--section=data","--section=post-data"]}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: last built-in OID is 16383","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading extensions","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: identifying extension members","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading schemas","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined tables","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined functions","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined types","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading procedural languages","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined aggregate functions","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined operators","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined access methods","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined operator classes","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined operator families","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined text search parsers","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined text search templates","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined text search dictionaries","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined text search configurations","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined foreign-data wrappers","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined foreign servers","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading default privileges","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined collations","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading user-defined conversions","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading type casts","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading transforms","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading table inheritance information","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading event triggers","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: finding extension tables","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: finding inheritance relationships","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading column info for interesting tables","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: finding table default expressions","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: finding table check constraints","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: flagging inherited columns in subtables","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading partitioning data","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading indexes","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: flagging indexes in partitioned tables","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading extended statistics","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading constraints","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading triggers","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading rewrite rules","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading policies","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading row-level security policies","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading publications","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading publication membership of tables","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading publication membership of schemas","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading subscriptions","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading large objects","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: reading dependency data","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: saving encoding = UTF8","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: saving standard_conforming_strings = on","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: saving search_path = ","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: saving database definition","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.ab_permission\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.ab_permission_view\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.ab_permission_view_role\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.ab_register_user\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.ab_role\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.ab_user\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.ab_user_role\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.ab_view_menu\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.alembic_version\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.callback_request\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.connection\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dag\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dag_code\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dag_owner_attributes\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dag_pickle\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dag_priority_parsing_request\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dag_run\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dag_run_note\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dag_schedule_dataset_alias_reference\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dag_schedule_dataset_reference\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dag_tag\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dag_warning\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dagrun_dataset_event\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dataset\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dataset_alias\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dataset_alias_dataset\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dataset_alias_dataset_event\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dataset_dag_run_queue\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.dataset_event\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.import_error\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.job\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.log\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.log_template\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.rendered_task_instance_fields\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.serialized_dag\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:43Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.session\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.sla_miss\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.slot_pool\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.task_fail\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.task_instance\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.task_instance_history\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.task_instance_note\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.task_map\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.task_outlet_dataset_reference\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.task_reschedule\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.trigger\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.variable\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","logger":"pg_dump","msg":"pg_dump: dumping contents of table \"public.xcom\"","pipe":"stderr","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","msg":"dropping user-defined extensions from the target (empty) database","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:46Z","msg":"temporarily granting superuser permission to owner user","logging_pod":"postgresql-test-1-import","owner":"app"}
{"level":"info","ts":"2024-09-23T11:52:46Z","msg":"executing database importing section","logging_pod":"postgresql-test-1-import","databaseName":"airflow-test-k8s","section":"pre-data"}
{"level":"info","ts":"2024-09-23T11:52:46Z","msg":"Running pg_restore","logging_pod":"postgresql-test-1-import","cmd":"pg_restore","options":["-U","postgres","--no-owner","--no-privileges","--role=app","-d","app","--section","pre-data","/var/lib/postgresql/data/pgdata/dumps/airflow-test-k8s.dump"]}
{"level":"info","ts":"2024-09-23T11:52:46Z","msg":"executing database importing section","logging_pod":"postgresql-test-1-import","databaseName":"airflow-test-k8s","section":"data"}
{"level":"info","ts":"2024-09-23T11:52:46Z","msg":"Running pg_restore","logging_pod":"postgresql-test-1-import","cmd":"pg_restore","options":["-U","postgres","--no-owner","--no-privileges","--role=app","-d","app","--section","data","/var/lib/postgresql/data/pgdata/dumps/airflow-test-k8s.dump"]}
{"level":"info","ts":"2024-09-23T11:52:47Z","msg":"executing database importing section","logging_pod":"postgresql-test-1-import","databaseName":"airflow-test-k8s","section":"post-data"}
{"level":"info","ts":"2024-09-23T11:52:47Z","msg":"Running pg_restore","logging_pod":"postgresql-test-1-import","cmd":"pg_restore","options":["-U","postgres","--no-owner","--no-privileges","--role=app","-d","app","--section","post-data","/var/lib/postgresql/data/pgdata/dumps/airflow-test-k8s.dump"]}
{"level":"info","ts":"2024-09-23T11:52:48Z","msg":"removing superuser permission from owner user","logging_pod":"postgresql-test-1-import","owner":"app"}
{"level":"info","ts":"2024-09-23T11:52:48Z","msg":"running analyze for database: app","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:48Z","logger":"pg_ctl","msg":"pg_ctl: server is running (PID: 36)\n/usr/lib/postgresql/15/bin/postgres \"-D\" \"/var/lib/postgresql/data/pgdata\" \"-c\" \"port=5432\" \"-c\" \"unix_socket_directories=/controller/run\" \"-c\" \"listen_addresses=127.0.0.1\"\n","pipe":"stdout","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:48Z","msg":"Shutting down instance","logging_pod":"postgresql-test-1-import","pgdata":"/var/lib/postgresql/data/pgdata","mode":"fast","timeout":null}
{"level":"info","ts":"2024-09-23T11:52:48Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:48.616 UTC","process_id":"36","session_id":"66f1568b.24","session_line_num":"6","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"received fast shutdown request","backend_type":"postmaster","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:48Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:48.616 UTC","process_id":"36","session_id":"66f1568b.24","session_line_num":"7","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"aborting any active transactions","backend_type":"postmaster","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:48Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:48.618 UTC","process_id":"36","session_id":"66f1568b.24","session_line_num":"8","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"background worker \"logical replication launcher\" (PID 43) exited with exit code 1","backend_type":"postmaster","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:48Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:48.618 UTC","process_id":"38","session_id":"66f1568b.26","session_line_num":"1","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"shutting down","backend_type":"checkpointer","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:48Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:48.618 UTC","process_id":"38","session_id":"66f1568b.26","session_line_num":"2","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"checkpoint starting: shutdown immediate","backend_type":"checkpointer","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:48Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:48.642 UTC","process_id":"38","session_id":"66f1568b.26","session_line_num":"3","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"checkpoint complete: wrote 3610 buffers (22.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.024 s, sync=0.001 s, total=0.024 s; sync files=0, longest=0.000 s, average=0.000 s; distance=73658 kB, estimate=73658 kB","backend_type":"checkpointer","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:48Z","logger":"postgres","msg":"record","logging_pod":"postgresql-test-1-import","record":{"log_time":"2024-09-23 11:52:48.651 UTC","process_id":"36","session_id":"66f1568b.24","session_line_num":"9","session_start_time":"2024-09-23 11:52:43 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"database system is shut down","backend_type":"postmaster","query_id":"0"}}
{"level":"info","ts":"2024-09-23T11:52:48Z","logger":"pg_ctl","msg":"waiting for server to shut down.... done","pipe":"stdout","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:48Z","logger":"pg_ctl","msg":"server stopped","pipe":"stdout","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:48Z","msg":"Exited log pipe","fileName":"/controller/log/postgres.csv","logging_pod":"postgresql-test-1-import"}
{"level":"info","ts":"2024-09-23T11:52:48Z","msg":"Updated replication settings","logging_pod":"postgresql-test-1-import","filename":"override.conf"}
{"level":"info","ts":"2024-09-23T11:52:48Z","msg":"Running initdb --sync-only","logging_pod":"postgresql-test-1-import","pgdata":"/var/lib/postgresql/data/pgdata"}
{"level":"info","ts":"2024-09-23T11:52:48Z","logger":"initdb","msg":"syncing data to disk ... ok\n","pipe":"stdout","logging_pod":"postgresql-test-1-import"}

We can then connect to the PG master and make sure that the data is there:

brouberol@deploy1003:~/cloudnative-pg-import$ k exec -it postgresql-test-1 psql
kubectl exec [POD] [COMMAND] is DEPRECATED and will be removed in a future version. Use kubectl exec [POD] -- [COMMAND] instead.
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
psql (15.8 (Debian 15.8-0+deb12u1))
Type "help" for help.

postgres=# \c app
You are now connected to database "app" as user "postgres".

app=# \dt
                       List of relations
 Schema |                 Name                 | Type  | Owner
--------+--------------------------------------+-------+-------
 public | ab_permission                        | table | app
 public | ab_permission_view                   | table | app
 public | ab_permission_view_role              | table | app
 public | ab_register_user                     | table | app
 public | ab_role                              | table | app
 public | ab_user                              | table | app
 public | ab_user_role                         | table | app
 public | ab_view_menu                         | table | app
 public | alembic_version                      | table | app
 public | callback_request                     | table | app
 public | connection                           | table | app
 public | dag                                  | table | app
 public | dag_code                             | table | app
 public | dag_owner_attributes                 | table | app
 public | dag_pickle                           | table | app
 public | dag_priority_parsing_request         | table | app
 public | dag_run                              | table | app
 public | dag_run_note                         | table | app
 public | dag_schedule_dataset_alias_reference | table | app
 public | dag_schedule_dataset_reference       | table | app
 public | dag_tag                              | table | app
 public | dag_warning                          | table | app
 public | dagrun_dataset_event                 | table | app
 public | dataset                              | table | app
 public | dataset_alias                        | table | app
 public | dataset_alias_dataset                | table | app
 public | dataset_alias_dataset_event          | table | app
 public | dataset_dag_run_queue                | table | app
 public | dataset_event                        | table | app
 public | import_error                         | table | app
 public | job                                  | table | app
 public | log                                  | table | app
 public | log_template                         | table | app
 public | rendered_task_instance_fields        | table | app
 public | serialized_dag                       | table | app
 public | session                              | table | app
 public | sla_miss                             | table | app
 public | slot_pool                            | table | app
 public | task_fail                            | table | app
 public | task_instance                        | table | app
 public | task_instance_history                | table | app
 public | task_instance_note                   | table | app
 public | task_map                             | table | app
 public | task_outlet_dataset_reference        | table | app
 public | task_reschedule                      | table | app
 public | trigger                              | table | app
 public | variable                             | table | app
 public | xcom                                 | table | app
(48 rows)

app=# select pid, task_id, task_display_name from task_instance order by pid desc limit 1;
  pid   |     task_id     | task_display_name
--------+-----------------+-------------------
 100622 | add_one_and_two | add_one_and_two
(1 row)

If we connect to an-db1001.eqiad.wmnet directly and run the same query, we see

airflow-test-k8s=> select pid, task_id, task_display_name from task_instance order by pid desc limit 1;
  pid   |     task_id     | task_display_name
--------+-----------------+-------------------
 100622 | add_one_and_two | add_one_and_two
(1 row)

Bingo!

The process has been documented here https://wikitech.wikimedia.org/wiki/Data_Platform/Systems/CloudnativePG/Clusters#Import_data_from_an_external_database

brouberol moved this task from Backlog - project to Done on the Data-Platform-SRE (2024.09.06 - 2024.09.27) board.Sep 23 2024, 12:06 PM

Change #1074968 merged by Brouberol:

[operations/deployment-charts@master] cloudnative-pg-cluster: facilitate the import of an external database

https://gerrit.wikimedia.org/r/1074968

Maintenance_bot removed a project: Patch-For-Review.Sep 23 2024, 3:30 PM

Change #1075138 had a related patch set uploaded (by Brouberol; author: Brouberol):

[operations/deployment-charts@master] Redeploy postgresql-airflow-test-k8s

https://gerrit.wikimedia.org/r/1075138

gerritbot added a project: Patch-For-Review.Sep 24 2024, 7:32 AM

Change #1075138 merged by Brouberol:

[operations/deployment-charts@master] Redeploy postgresql-airflow-test-k8s

https://gerrit.wikimedia.org/r/1075138

Change #1075942 had a related patch set uploaded (by Brouberol; author: Brouberol):

[operations/deployment-charts@master] allow airflow-deploy to deploy PG clusters

https://gerrit.wikimedia.org/r/1075942

Change #1075942 merged by Brouberol:

[operations/deployment-charts@master] allow airflow-deploy to deploy PG clusters

https://gerrit.wikimedia.org/r/1075942

Change #1075952 had a related patch set uploaded (by Brouberol; author: Brouberol):

[operations/deployment-charts@master] airflow-test-k8s: run PG normally now that the data has been imported

https://gerrit.wikimedia.org/r/1075952

Change #1075952 merged by Brouberol: