Bootstrap from arbitrary/external postgres cluster? #2625

jonathon2nd · 2024-05-06T19:11:47Z

jonathon2nd
May 6, 2024

Hello!

I was wondering if it was possible to create replica cluster from a postgres cluster not managed by postgres operator? I have wal shipped to s3 via barman, so was wondering if I could point it there. Not seeing anything in the doc.

Specifically, this is a patroni cluster on vms.

jonathon2nd · 2024-05-07T21:11:27Z

jonathon2nd
May 7, 2024
Author

Hmm, hitting a bit of a wall.

So I scaled down the new cluster to one, and modified the turned the cluster into a replica. Did so by adding the following with patronictl edit-config

standby_cluster:
  host: 10.1.1.36

I took a pg_basebackup of the current master.

I connected to the node:

Stopped patroni with sv stop patroni.
Deleted everthing with rm -rf /home/postgres/pgdata/pgroot/data/*
Copied contents of pg_basebackup into above folder.
Then started patroni again.

No matter what I do, I keep running into CRITICAL: system ID mismatch, node acid-example-0 belongs to a different cluster: 7366331207860006983 != 6771440915972115090

0 replies

jonathon2nd · 2024-05-07T22:58:58Z

jonathon2nd
May 7, 2024
Author

What I get when I try the following

standby_cluster:
  host: 10.1.1.36
  create_replica_methods:
    - bootstrap_standby_with_wale
    - basebackup_fast_xlog
  restore_command: envdir "/home/postgres/etc/wal-e.d/env-standby" /scripts/restore_command.sh
     "%f" "%p"

2024-05-07 22:57:00,685 - bootstrapping - INFO - Figuring out my environment (Google? AWS? Openstack? Local?)
2024-05-07 22:57:02,689 - bootstrapping - INFO - Could not connect to 169.254.169.254, assuming local Docker setup
2024-05-07 22:57:02,691 - bootstrapping - INFO - No meta-data available for this provider
2024-05-07 22:57:02,692 - bootstrapping - INFO - Looks like you are running local
2024-05-07 22:57:02,734 - bootstrapping - INFO - Configuring standby-cluster
2024-05-07 22:57:02,734 - bootstrapping - INFO - Configuring crontab
2024-05-07 22:57:02,734 - bootstrapping - INFO - Skipping creation of renice cron job due to lack of SYS_NICE capability
2024-05-07 22:57:02,742 - bootstrapping - INFO - Configuring pgbouncer
2024-05-07 22:57:02,742 - bootstrapping - INFO - No PGBOUNCER_CONFIGURATION was specified, skipping
2024-05-07 22:57:02,742 - bootstrapping - INFO - Configuring patroni
2024-05-07 22:57:02,755 - bootstrapping - INFO - Writing to file /run/postgres.yml
2024-05-07 22:57:02,755 - bootstrapping - INFO - Configuring pgqd
2024-05-07 22:57:02,755 - bootstrapping - INFO - Configuring bootstrap
2024-05-07 22:57:02,756 - bootstrapping - INFO - Configuring wal-e
2024-05-07 22:57:02,756 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/WALE_S3_PREFIX
2024-05-07 22:57:02,756 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/WALG_S3_PREFIX
2024-05-07 22:57:02,757 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/AWS_ACCESS_KEY_ID
2024-05-07 22:57:02,757 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/AWS_SECRET_ACCESS_KEY
2024-05-07 22:57:02,757 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/WALE_S3_ENDPOINT
2024-05-07 22:57:02,758 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/AWS_ENDPOINT
2024-05-07 22:57:02,758 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/AWS_REGION
2024-05-07 22:57:02,758 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/WALE_DISABLE_S3_SSE
2024-05-07 22:57:02,758 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/WALG_DISABLE_S3_SSE
2024-05-07 22:57:02,759 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/AWS_S3_FORCE_PATH_STYLE
2024-05-07 22:57:02,759 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/WALG_DOWNLOAD_CONCURRENCY
2024-05-07 22:57:02,759 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/WALG_UPLOAD_CONCURRENCY
2024-05-07 22:57:02,759 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/USE_WALG_BACKUP
2024-05-07 22:57:02,759 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/USE_WALG_RESTORE
2024-05-07 22:57:02,760 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/WALE_LOG_DESTINATION
2024-05-07 22:57:02,760 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/PGPORT
2024-05-07 22:57:02,760 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/BACKUP_NUM_TO_RETAIN
2024-05-07 22:57:02,761 - bootstrapping - INFO - Writing to file /run/etc/wal-e.d/env/TMPDIR
2024-05-07 22:57:02,761 - bootstrapping - INFO - Configuring certificate
2024-05-07 22:57:02,761 - bootstrapping - INFO - Generating ssl self-signed certificate
2024-05-07 22:57:02,885 - bootstrapping - INFO - Configuring log
2024-05-07 22:57:02,885 - bootstrapping - INFO - Configuring pam-oauth2
2024-05-07 22:57:02,885 - bootstrapping - INFO - Writing to file /etc/pam.d/postgresql
2024-05-07 22:57:04,271 WARNING: Kubernetes RBAC doesn't allow GET access to the 'kubernetes' endpoint in the 'default' namespace. Disabling 'bypass_api_service'.
2024-05-07 22:57:04,303 INFO: No PostgreSQL configuration items changed, nothing to reload.
2024-05-07 22:57:04,325 WARNING: Postgresql is not running.
2024-05-07 22:57:04,325 INFO: Lock owner: ; I am acid-example-0
2024-05-07 22:57:04,328 INFO: pg_controldata:
  pg_control version number: 1201
  Catalog version number: 201909212
  Database system identifier: 7366374102655144007
  Database cluster state: shut down in recovery
  pg_control last modified: Tue May  7 22:56:46 2024
  Latest checkpoint location: 0/9000108
  Latest checkpoint's REDO location: 0/90000D0
  Latest checkpoint's REDO WAL file: 000000020000000000000009
  Latest checkpoint's TimeLineID: 2
  Latest checkpoint's PrevTimeLineID: 2
  Latest checkpoint's full_page_writes: on
  Latest checkpoint's NextXID: 0:697
  Latest checkpoint's NextOID: 16704
  Latest checkpoint's NextMultiXactId: 1
  Latest checkpoint's NextMultiOffset: 0
  Latest checkpoint's oldestXID: 480
  Latest checkpoint's oldestXID's DB: 1
  Latest checkpoint's oldestActiveXID: 697
  Latest checkpoint's oldestMultiXid: 1
  Latest checkpoint's oldestMulti's DB: 1
  Latest checkpoint's oldestCommitTsXid: 0
  Latest checkpoint's newestCommitTsXid: 0
  Time of latest checkpoint: Tue May  7 22:50:06 2024
  Fake LSN counter for unlogged rels: 0/3E8
  Minimum recovery ending location: 0/A000000
  Min recovery ending loc's timeline: 2
  Backup start location: 0/0
  Backup end location: 0/0
  End-of-backup record required: no
  wal_level setting: replica
  wal_log_hints setting: on
  max_connections setting: 100
  max_worker_processes setting: 8
  max_wal_senders setting: 10
  max_prepared_xacts setting: 0
  max_locks_per_xact setting: 64
  track_commit_timestamp setting: off
  Maximum data alignment: 8
  Database block size: 8192
  Blocks per segment of large relation: 131072
  WAL block size: 8192
  Bytes per WAL segment: 16777216
  Maximum length of identifiers: 64
  Maximum columns in an index: 32
  Maximum size of a TOAST chunk: 1996
  Size of a large-object chunk: 2048
  Date/time type storage: 64-bit integers
  Float4 argument passing: by value
  Float8 argument passing: by value
  Data page checksum version: 0
  Mock authentication nonce: f7c03b3f56a6c1b2488c78c9a6e8cb72302a0e0d476c81dd53271f2944c6a54b

2024-05-07 22:57:04,329 INFO: Lock owner: ; I am acid-example-0
2024-05-07 22:57:04,347 INFO: Local timeline=2 lsn=0/A000000
2024-05-07 22:57:04,374 INFO: primary_timeline=30
2024-05-07 22:57:04,377 INFO: primary: history=1	0/40000A0	no recovery target specified
2	0/60000A0	no recovery target specified
3	0/A0000A0	no recovery target specified
...
29	127/560000A0	no recovery target specified
2024-05-07 22:57:04,540 INFO: running pg_rewind from remote_member:1f351f4c-0cc5-11ef-9551-a296f511a8c2
2024-05-07 22:57:04,612 INFO: running pg_rewind from dbname=postgres user=postgres host=10.1.1.36 target_session_attrs=read-write
2024-05-07 22:57:04,665 INFO: pg_rewind exit code=1
2024-05-07 22:57:04,665 INFO:  stdout=
2024-05-07 22:57:04,665 INFO:  stderr=pg_rewind: fatal: source and target clusters are from different systems

2024-05-07 22:57:04,679 ERROR: Failed to rewind from healty primary: remote_member:1f351f4c-0cc5-11ef-9551-a296f511a8c2
2024-05-07 22:57:04,680 WARNING: Postgresql is not running.
2024-05-07 22:57:04,680 INFO: Lock owner: ; I am acid-example-0
2024-05-07 22:57:04,683 INFO: pg_controldata:
  pg_control version number: 1201
  Catalog version number: 201909212
  Database system identifier: 7366374102655144007
  Database cluster state: shut down in recovery
  pg_control last modified: Tue May  7 22:56:46 2024
  Latest checkpoint location: 0/9000108
  Latest checkpoint's REDO location: 0/90000D0
  Latest checkpoint's REDO WAL file: 000000020000000000000009
  Latest checkpoint's TimeLineID: 2
  Latest checkpoint's PrevTimeLineID: 2
  Latest checkpoint's full_page_writes: on
  Latest checkpoint's NextXID: 0:697
  Latest checkpoint's NextOID: 16704
  Latest checkpoint's NextMultiXactId: 1
  Latest checkpoint's NextMultiOffset: 0
  Latest checkpoint's oldestXID: 480
  Latest checkpoint's oldestXID's DB: 1
  Latest checkpoint's oldestActiveXID: 697
  Latest checkpoint's oldestMultiXid: 1
  Latest checkpoint's oldestMulti's DB: 1
  Latest checkpoint's oldestCommitTsXid: 0
  Latest checkpoint's newestCommitTsXid: 0
  Time of latest checkpoint: Tue May  7 22:50:06 2024
  Fake LSN counter for unlogged rels: 0/3E8
  Minimum recovery ending location: 0/A000000
  Min recovery ending loc's timeline: 2
  Backup start location: 0/0
  Backup end location: 0/0
  End-of-backup record required: no
  wal_level setting: replica
  wal_log_hints setting: on
  max_connections setting: 100
  max_worker_processes setting: 8
  max_wal_senders setting: 10
  max_prepared_xacts setting: 0
  max_locks_per_xact setting: 64
  track_commit_timestamp setting: off
  Maximum data alignment: 8
  Database block size: 8192
  Blocks per segment of large relation: 131072
  WAL block size: 8192
  Bytes per WAL segment: 16777216
  Maximum length of identifiers: 64
  Maximum columns in an index: 32
  Maximum size of a TOAST chunk: 1996
  Size of a large-object chunk: 2048
  Date/time type storage: 64-bit integers
  Float4 argument passing: by value
  Float8 argument passing: by value
  Data page checksum version: 0
  Mock authentication nonce: f7c03b3f56a6c1b2488c78c9a6e8cb72302a0e0d476c81dd53271f2944c6a54b

2024-05-07 22:57:04,684 INFO: Lock owner: ; I am acid-example-0
2024-05-07 22:57:04,685 INFO: trying to follow a remote member because standby cluster is unhealthy
2024-05-07 22:57:05,031 INFO: postmaster pid=73
/var/run/postgresql:5432 - no response
2024-05-07 22:57:05 UTC [73]: [1-1] 663ab1c1.49 0     LOG:  Auto detecting pg_stat_kcache.linux_hz parameter...
2024-05-07 22:57:05 UTC [73]: [2-1] 663ab1c1.49 0     LOG:  pg_stat_kcache.linux_hz is set to 1000000
2024-05-07 22:57:05 UTC [73]: [3-1] 663ab1c1.49 0     LOG:  starting PostgreSQL 12.18 (Ubuntu 12.18-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2024-05-07 22:57:05 UTC [73]: [4-1] 663ab1c1.49 0     LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-05-07 22:57:05 UTC [73]: [5-1] 663ab1c1.49 0     LOG:  listening on IPv6 address "::", port 5432
2024-05-07 22:57:05 UTC [73]: [6-1] 663ab1c1.49 0     LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-05-07 22:57:05 UTC [73]: [7-1] 663ab1c1.49 0     LOG:  redirecting log output to logging collector process
2024-05-07 22:57:05 UTC [73]: [8-1] 663ab1c1.49 0     HINT:  Future log output will appear in directory "../pg_log".
/var/run/postgresql:5432 - accepting connections
/var/run/postgresql:5432 - accepting connections
2024-05-07 22:57:06,100 INFO: establishing a new patroni heartbeat connection to postgres
2024-05-07 22:57:06,295 INFO: promoted self to a standby leader by acquiring session lock
2024-05-07 22:57:06,356 INFO: no action. I am (acid-example-0), the standby leader with the lock
2024-05-07 22:57:16,361 INFO: no action. I am (acid-example-0), the standby leader with the lock
2024-05-07 22:57:26,353 INFO: no action. I am (acid-example-0), the standby leader with the lock
2024-05-07 22:57:36,354 INFO: no action. I am (acid-example-0), the standby leader with the lock

0 replies

jonathon2nd · 2024-05-08T17:57:40Z

jonathon2nd
May 8, 2024
Author

...

https://github.com/zalando/postgres-operator/blob/master/docs/reference/cluster_manifest.md#standby-cluster
Using standby_host works

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Bootstrap from arbitrary/external postgres cluster? #2625

{{title}}

Replies: 3 comments

{{title}}

{{title}}

{{title}}

Select a reply

Bootstrap from arbitrary/external postgres cluster? #2625

jonathon2nd May 6, 2024

Replies: 3 comments

jonathon2nd May 7, 2024 Author

jonathon2nd May 7, 2024 Author

jonathon2nd May 8, 2024 Author

jonathon2nd
May 6, 2024

jonathon2nd
May 7, 2024
Author

jonathon2nd
May 7, 2024
Author

jonathon2nd
May 8, 2024
Author