Created
January 3, 2022 21:23
-
-
Save cabecada/4517af13245383b888833cfc69d741be to your computer and use it in GitHub Desktop.
pg_upgrade 11 to 13 with replica
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
db1 | |
112 sudo systemctl stop postgresql@11-main.service (port=5432) | |
113 sudo systemctl stop postgresql@13-main.service (port=5433) | |
db2 | |
112 sudo systemctl stop postgresql@11-main.service (port=5432) | |
113 sudo systemctl stop postgresql@13-main.service (port=5433) | |
114 rm -rf /var/lib/postgresql/11/main | |
115 /usr/lib/postgresql/11/bin/pg_basebackup -D /var/lib/postgresql/11/main -R -X stream -c fast -C -S db2 -d "host=db1.mshome.net user=postgres password=1234 dbname=postgres" | |
db1 | |
118 export OLD_PSQL=11; export NEW_PSQL=13 | |
119 /usr/lib/postgresql/${NEW_PSQL}/bin/pg_upgrade -b /usr/lib/postgresql/${OLD_PSQL}/bin/ -B /usr/lib/postgresql/${NEW_PSQL}/bin/ -d /var/lib/postgresql/${OLD_PSQL}/main/ -D /var/lib/postgresql/${NEW_PSQL}/main/ -o "-c config-file=/etc/postgresql/${OLD_PSQL}/main/postgresql.conf" -O "-c config-file=/etc/postgresql/${NEW_PSQL}/main/postgresql.conf" --link --check | |
Performing Consistency Checks | |
----------------------------- | |
Checking cluster versions ok | |
Checking database user is the install user ok | |
Checking database connection settings ok | |
Checking for prepared transactions ok | |
Checking for system-defined composite types in user tables ok | |
Checking for reg* data types in user tables ok | |
Checking for contrib/isn with bigint-passing mismatch ok | |
Checking for tables WITH OIDS ok | |
Checking for invalid "sql_identifier" user columns ok | |
Checking for presence of required libraries ok | |
Checking database user is the install user ok | |
Checking for prepared transactions ok | |
Checking for new cluster tablespace directories ok | |
*Clusters are compatible* | |
120 /usr/lib/postgresql/${NEW_PSQL}/bin/pg_upgrade -b /usr/lib/postgresql/${OLD_PSQL}/bin/ -B /usr/lib/postgresql/${NEW_PSQL}/bin/ -d /var/lib/postgresql/${OLD_PSQL}/main/ -D /var/lib/postgresql/${NEW_PSQL}/main/ -o "-c config-file=/etc/postgresql/${OLD_PSQL}/main/postgresql.conf" -O "-c config-file=/etc/postgresql/${NEW_PSQL}/main/postgresql.conf" --link | |
Performing Consistency Checks | |
----------------------------- | |
Checking cluster versions ok | |
Checking database user is the install user ok | |
Checking database connection settings ok | |
Checking for prepared transactions ok | |
Checking for system-defined composite types in user tables ok | |
Checking for reg* data types in user tables ok | |
Checking for contrib/isn with bigint-passing mismatch ok | |
Checking for tables WITH OIDS ok | |
Checking for invalid "sql_identifier" user columns ok | |
Creating dump of global objects ok | |
Creating dump of database schemas | |
ok | |
Checking for presence of required libraries ok | |
Checking database user is the install user ok | |
Checking for prepared transactions ok | |
Checking for new cluster tablespace directories ok | |
If pg_upgrade fails after this point, you must re-initdb the | |
new cluster before continuing. | |
Performing Upgrade | |
------------------ | |
Analyzing all rows in the new cluster ok | |
Freezing all rows in the new cluster ok | |
Deleting files from new pg_xact ok | |
Copying old pg_xact to new server ok | |
Setting oldest XID for new cluster ok | |
Setting next transaction ID and epoch for new cluster ok | |
Deleting files from new pg_multixact/offsets ok | |
Copying old pg_multixact/offsets to new server ok | |
Deleting files from new pg_multixact/members ok | |
Copying old pg_multixact/members to new server ok | |
Setting next multixact ID and offset for new cluster ok | |
Resetting WAL archives ok | |
Setting frozenxid and minmxid counters in new cluster ok | |
Restoring global objects in the new cluster ok | |
Restoring database schemas in the new cluster | |
ok | |
Adding ".old" suffix to old global/pg_control ok | |
If you want to start the old cluster, you will need to remove | |
the ".old" suffix from /var/lib/postgresql/11/main/global/pg_control.old. | |
Because "link" mode was used, the old cluster cannot be safely | |
started once the new cluster has been started. | |
Linking user relation files | |
ok | |
Setting next OID for new cluster ok | |
Sync data directory to disk ok | |
Creating script to analyze new cluster ok | |
Creating script to delete old cluster ok | |
Checking for extension updates ok | |
Upgrade Complete | |
---------------- | |
Optimizer statistics are not transferred by pg_upgrade so, | |
once you start the new server, consider running: | |
./analyze_new_cluster.sh | |
Running this script will delete the old cluster's data files: | |
./delete_old_cluster.sh | |
db2 | |
#copy *conf from 11 and 13 into /tmp/ | |
sudo cp -rf /etc/postgresql /etc/postgresql_back | |
stop 11 and 13 on replica | |
112 sudo systemctl stop postgresql@11-main.service (port=5432) | |
113 sudo systemctl stop postgresql@13-main.service (port=5433) | |
#remove data directory for *new* cluster only | |
rm -rf /var/lib/postgresql/13/main | |
#run rsync on db1 to db2 | |
db1 | |
122 rsync --archive --delete --hard-links --size-only --no-inc-recursive /var/lib/postgresql/11 /var/lib/postgresql/13 db2.mshome.net:/var/lib/postgresql/ -v --dry-run | |
127 rsync --archive --delete --hard-links --size-only --no-inc-recursive /var/lib/postgresql/11 /var/lib/postgresql/13 db2.mshome.net:/var/lib/postgresql/ -v | |
copy content of recovery.conf on 11 to 13 postgresql.auto.conf | |
postgres@db2:~/13/main$ cat /tmp/11_back/main/recovery.conf | |
standby_mode = 'on' | |
primary_conninfo = 'user=postgres password=1234 channel_binding=prefer host=db1.mshome.net port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any' | |
primary_slot_name = 'db2' | |
postgres@db2:~/13/main$ cat /var/lib/postgresql/13/main/postgresql.auto.conf | |
# Do not edit this file manually! | |
# It will be overwritten by the ALTER SYSTEM command. | |
primary_conninfo = 'user=postgres password=1234 channel_binding=prefer host=db1.mshome.net port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any' | |
primary_slot_name = 'db2' | |
#touch standby.signal on replica data_dir | |
touch standby.signal | |
# create new replication slot for new pg13 | |
psql -c "select pg_create_physical_replication_slot('db2');" | |
start pg13 on db1 | |
./analyze_new_cluster.sh | |
#./delete_old_cluster.sh | |
start 13 on db2 | |
sudo systemctl start postgresql@13-main.service | |
#check logs | |
postgres@db2:~/13/main$ tail -50 /var/log/postgresql/postgresql-13-main.log | |
2022-01-04 02:24:22.777 IST [12287] LOG: starting PostgreSQL 13.5 (Ubuntu 13.5-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit | |
2022-01-04 02:24:22.778 IST [12287] LOG: listening on IPv4 address "0.0.0.0", port 5432 | |
2022-01-04 02:24:22.778 IST [12287] LOG: listening on IPv6 address "::", port 5432 | |
2022-01-04 02:24:22.780 IST [12287] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" | |
2022-01-04 02:24:22.785 IST [12288] LOG: database system was shut down at 2022-01-04 02:15:47 IST | |
2022-01-04 02:24:22.785 IST [12288] LOG: entering standby mode | |
2022-01-04 02:24:22.789 IST [12288] LOG: consistent recovery state reached at 0/9000150 | |
2022-01-04 02:24:22.789 IST [12287] LOG: database system is ready to accept read only connections | |
2022-01-04 02:24:22.789 IST [12288] LOG: invalid record length at 0/9000150: wanted 24, got 0 | |
2022-01-04 02:24:22.800 IST [12292] LOG: started streaming WAL from primary at 0/9000000 on timeline 1 | |
2022-01-04 02:24:22.802 IST [12288] LOG: redo starts at 0/9000150 | |
all good. | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment