Last active
January 30, 2024 20:53
-
-
Save cabecada/7d8d2f5a46802ab5b3fa4bd2f4a0e787 to your computer and use it in GitHub Desktop.
postgres failover, convert existing primary to replica controldata REDO
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| postgres@pg:~/udemy/15/project$ initdb -D db1 >/dev/null 2>/dev/null | |
| postgres@pg:~/udemy/15/project$ cp postgresql.auto.conf_db1 db1/postgresql.auto.conf | |
| postgres@pg:~/udemy/15/project$ vim db1/postgresql.auto.conf | |
| postgres@pg:~/udemy/15/project$ pg_ctl -D db1 -l db1.log start | |
| waiting for server to start.... done | |
| server started | |
| postgres@pg:~/udemy/15/project$ psql -c 'create table t(col1 int); insert into t select generate_series(1, 10000) x;' | |
| CREATE TABLE | |
| INSERT 0 10000 | |
| postgres@pg:~/udemy/15/project$ psql -c 'checkpoint' | |
| CHECKPOINT | |
| postgres@pg:~/udemy/15/project$ pg_basebackup -D db2 -R -c fast -d "port=5432" | |
| postgres@pg:~/udemy/15/project$ cp postgresql.auto.conf_db2 db2/postgresql.auto.conf | |
| postgres@pg:~/udemy/15/project$ vim db2/postgresql.auto.conf | |
| postgres@pg:~/udemy/15/project$ pg_ctl -D db2 -l db2.log start | |
| waiting for server to start.... done | |
| server started | |
| postgres@pg:~/udemy/15/project$ psql -c 'table pg_stat_replication' | |
| postgres@pg:~/udemy/15/project$ psql -c '\x; table pg_stat_replication' | |
| invalid command \x; | |
| postgres@pg:~/udemy/15/project$ psql -c 'table pg_stat_replication' | more | |
| pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | |
| | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | rep | |
| lay_lag | sync_priority | sync_state | reply_time | |
| -------+----------+----------+------------------+-------------+-----------------+-------------+----------------------------- | |
| -----+--------------+-----------+-----------+-----------+-----------+------------+-----------------+-----------------+------ | |
| -----------+---------------+------------+---------------------------------- | |
| 12744 | 10 | postgres | walreceiver | | | -1 | 2024-01-31 01:12:58.954658+0 | |
| 5:30 | | streaming | 0/303E2E0 | 0/303E2E0 | 0/303E2E0 | 0/303E2E0 | 00:00:00.000099 | 00:00:00.001496 | 00:00 | |
| :00.001568 | 0 | async | 2024-01-31 01:13:32.522112+05:30 | |
| (1 row) | |
| postgres@pg:~/udemy/15/project$ pg_controldata -D db1 | grep REDO | |
| Latest checkpoint's REDO location: 0/2000028 | |
| Latest checkpoint's REDO WAL file: 000000010000000000000002 | |
| postgres@pg:~/udemy/15/project$ pg_controldata -D db2 | grep REDO | |
| Latest checkpoint's REDO location: 0/2000028 | |
| Latest checkpoint's REDO WAL file: 000000010000000000000002 | |
| postgres@pg:~/udemy/15/project$ psql | |
| psql (15.5) | |
| Type "help" for help. | |
| postgres=# checkpoint; | |
| CHECKPOINT | |
| postgres=# select pg_switch_wal(); | |
| pg_switch_wal | |
| --------------- | |
| 0/303E3A8 | |
| (1 row) | |
| postgres=# | |
| \q | |
| postgres@pg:~/udemy/15/project$ pg_controldata -D db1 | grep REDO | |
| Latest checkpoint's REDO location: 0/303E2E0 | |
| Latest checkpoint's REDO WAL file: 000000010000000000000003 | |
| postgres@pg:~/udemy/15/project$ pg_controldata -D db2 | grep REDO | |
| Latest checkpoint's REDO location: 0/2000028 | |
| Latest checkpoint's REDO WAL file: 000000010000000000000002 | |
| postgres@pg:~/udemy/15/project$ pg_ctl -D db1 -l db1.log stop | |
| waiting for server to shut down.... done | |
| server stopped | |
| postgres@pg:~/udemy/15/project$ pg_ctl -D db1 -l db1.log stop | |
| pg_ctl: PID file "db1/postmaster.pid" does not exist | |
| Is server running? | |
| postgres@pg:~/udemy/15/project$ pg_controldata -D db1 | grep REDO | |
| Latest checkpoint's REDO location: 0/5000028 | |
| Latest checkpoint's REDO WAL file: 000000010000000000000005 | |
| postgres@pg:~/udemy/15/project$ pg_controldata -D db2 | grep REDO | |
| Latest checkpoint's REDO location: 0/2000028 | |
| Latest checkpoint's REDO WAL file: 000000010000000000000002 | |
| (ensure by checkpoints all wals are archived,else copy manually) | |
| postgres@pg:~/udemy/15/project$ for i in {1..30} | |
| > do | |
| > psql -c 'checkpoint;' | |
| > done | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| CHECKPOINT | |
| postgres@pg:~/udemy/15/project$ pg_controldata -D db1 | grep REDO | |
| Latest checkpoint's REDO location: 0/30F23B0 | |
| Latest checkpoint's REDO WAL file: 000000010000000000000003 | |
| postgres@pg:~/udemy/15/project$ pg_controldata -D db2 | grep REDO | |
| Latest checkpoint's REDO location: 0/30B2B90 | |
| Latest checkpoint's REDO WAL file: 000000010000000000000003 | |
| ---- | |
| else copy manually | |
| postgres@pg:~/udemy/15/project$ cp db1/pg_wal/00000001000000000000000 | |
| 000000010000000000000002.00000028.backup 000000010000000000000006 | |
| 000000010000000000000005 000000010000000000000007 | |
| postgres@pg:~/udemy/15/project$ cp db2/pg_wal/00000001000000000000000 | |
| 000000010000000000000002 000000010000000000000003 000000010000000000000004 | |
| postgres@pg:~/udemy/15/project$ cp db1/pg_wal/000000010000000000000005 /tmp/nfs/ | |
| postgres@pg:~/udemy/15/project$ cp db1/pg_wal/000000010000000000000006 /tmp/nfs/ | |
| postgres@pg:~/udemy/15/project$ cp db1/pg_wal/000000010000000000000007 /tmp/nfs/ | |
| postgres@pg:~/udemy/15/project$ pg_controldata -D db2 | grep REDO | |
| Latest checkpoint's REDO location: 0/2000028 | |
| Latest checkpoint's REDO WAL file: 000000010000000000000002 | |
| postgres@pg:~/udemy/15/project$ psql -p 5433 | |
| psql (15.5) | |
| Type "help" for help. | |
| postgres=# checkpoint; | |
| CHECKPOINT | |
| postgres=# checkpoint; | |
| CHECKPOINT | |
| postgres=# checkpoint; | |
| CHECKPOINT | |
| postgres=# checkpoint; | |
| CHECKPOINT | |
| postgres=# | |
| \q | |
| postgres@pg:~/udemy/15/project$ pg_controldata -D db2 | grep REDO | |
| Latest checkpoint's REDO location: 0/5000028 | |
| Latest checkpoint's REDO WAL file: 000000010000000000000005 | |
| postgres@pg:~/udemy/15/project$ pg_controldata -D db1 | grep REDO | |
| Latest checkpoint's REDO location: 0/5000028 | |
| Latest checkpoint's REDO WAL file: 000000010000000000000005 | |
| postgres@pg:~/udemy/15/project$ pg_ctl -D db2 promote | |
| waiting for server to promote.... done | |
| server promoted | |
| postgres@pg:~/udemy/15/project$ #note same redo, we can then convert existing primary to replica since no divergence | |
| postgres@pg:~/udemy/15/project$ vim db1/postgresql.auto.conf | |
| postgres@pg:~/udemy/15/project$ touch db1/standby.signal | |
| postgres@pg:~/udemy/15/project$ #touch signal file, else starts on a diff timeline as primary | |
| postgres@pg:~/udemy/15/project$ pg_ctl -D db1 -l db1.log start | |
| waiting for server to start.... done | |
| server started | |
| postgres@pg:~/udemy/15/project$ psql -p 5433 -c 'table pg_stat_replication' | more | |
| pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | |
| | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync | |
| _priority | sync_state | reply_time | |
| -------+----------+----------+------------------+-------------+-----------------+-------------+----------------------------- | |
| -----+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+----- | |
| ----------+------------+---------------------------------- | |
| 13000 | 10 | postgres | walreceiver | | | -1 | 2024-01-31 01:17:40.505413+0 | |
| 5:30 | | streaming | 0/50001B8 | 0/50001B8 | 0/50001B8 | 0/50001B8 | | | | | |
| 0 | async | 2024-01-31 01:17:50.589741+05:30 | |
| (1 row) | |
| postgres@pg:~/udemy/15/project$ psql -c 'select pg_is_in_recovery();' | |
| pg_is_in_recovery | |
| ------------------- | |
| t | |
| (1 row) | |
| postgres@pg:~/udemy/15/project$ cat db1/postgresql.auto.conf | |
| # Do not edit this file manually! | |
| # It will be overwritten by the ALTER SYSTEM command. | |
| archive_mode = on | |
| archive_command = 'cp %p /tmp/nfs/%f' | |
| restore_command = 'cp /tmp/nfs/%f %p' | |
| recovery_target_timeline = 'latest' | |
| port=5432 | |
| hot_standby=on | |
| wal_level = replica | |
| primary_conninfo = 'user=postgres passfile=''/var/lib/postgresql/.pgpass'' channel_binding=disable port=5433 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any' | |
| postgres@pg:~/udemy/15/project$ cat db2/postgresql.auto.conf | |
| # Do not edit this file manually! | |
| # It will be overwritten by the ALTER SYSTEM command. | |
| archive_mode = on | |
| archive_command = 'cp %p /tmp/nfs/%f' | |
| restore_command = 'cp /tmp/nfs/%f %p' | |
| recovery_target_timeline = 'latest' | |
| port=5433 | |
| hot_standby=on | |
| wal_level = replica | |
| primary_conninfo = 'user=postgres passfile=''/var/lib/postgresql/.pgpass'' channel_binding=disable port=5432 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment