DB Wiki

User Tools

Site Tools

Trace: • terraformpostgresql • mysqlcommands • postgresqlreplication
postgresqlupgrade

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
postgresqlupgrade [2024/03/01 15:09] – z0hpvkpostgresqlupgrade [2025/03/08 22:24] (current) – external edit 127.0.0.1
Line 4: Line 4:
 https://www.postgresql.org/docs/14/pgupgrade.html https://www.postgresql.org/docs/14/pgupgrade.html
  
-<code SHELL>+<code bash>
 /usr/lib/postgresql/14/bin/pg_upgrade   --old-datadir=/var/lib/postgresql/10/main    /usr/lib/postgresql/14/bin/pg_upgrade   --old-datadir=/var/lib/postgresql/10/main   
                                         --new-datadir=/var/lib/postgresql/14/main                                            --new-datadir=/var/lib/postgresql/14/main   
Line 15: Line 15:
  
 Upgrade Streaming Replication server. \\ Upgrade Streaming Replication server. \\
-<code SHELL>+<code bash>
 rsync --verbose --archive --delete --hard-links --size-only --no-inc-recursive --human-readable  rsync --verbose --archive --delete --hard-links --size-only --no-inc-recursive --human-readable 
       10 14 postgres@postgres02-upgrade:/var/lib/postgresql       10 14 postgres@postgres02-upgrade:/var/lib/postgresql
Line 24: Line 24:
  
 If necessary, create a new instance … \\ If necessary, create a new instance … \\
-<code>+<code bash>
 pg_createcluster -d /var/lib/postgresql/14/doob 14 doob pg_createcluster -d /var/lib/postgresql/14/doob 14 doob
 </code> </code>
Line 30: Line 30:
 On postgres-test, this used port 5434 for the new instance. \\ On postgres-test, this used port 5434 for the new instance. \\
 Copy the pg_hba.conf and postgresql.conf files from the old instance to the new one … \\ Copy the pg_hba.conf and postgresql.conf files from the old instance to the new one … \\
-<code SHELL>+<code bash>
 cp /var/lib/postgresql/10/main/postgresql.conf /var/lib/postgresql/14/doob cp /var/lib/postgresql/10/main/postgresql.conf /var/lib/postgresql/14/doob
 cp /var/lib/postgresql/10/main/pg_hba.conf /var/lib/postgresql/14/doob cp /var/lib/postgresql/10/main/pg_hba.conf /var/lib/postgresql/14/doob
Line 37: Line 37:
 Edit the postgresql.conf and update relevant entries to 14/doob. \\ Edit the postgresql.conf and update relevant entries to 14/doob. \\
 Restart the new PostgreSQL 14 instance … \\ Restart the new PostgreSQL 14 instance … \\
-<code SHELL>+<code bash>
 sudo systemctl restart postgresql@14-doob sudo systemctl restart postgresql@14-doob
 </code> </code>
  
 Copy data from the old instance to the new using the following command … \\ Copy data from the old instance to the new using the following command … \\
-<code SHELL>+<code bash>
 pg_dumpall -p 5432 -U postgres -d postgres | psql -p 5434 -U postgres -d postgres pg_dumpall -p 5432 -U postgres -d postgres | psql -p 5434 -U postgres -d postgres
 </code> </code>
  
 If you wish to test the above process again then delete the cluster and begin again from the start … \\ If you wish to test the above process again then delete the cluster and begin again from the start … \\
-<code SHELL>+<code bash>
 pg_dropcluster 14 doob --stop pg_dropcluster 14 doob --stop
 </code> </code>
 Otherwise, stop the old instance and then configure the new instance to use port 5432 and restart.  Otherwise, stop the old instance and then configure the new instance to use port 5432 and restart. 
postgresqlupgrade.1709305785.txt.gz · Last modified: 2025/03/08 22:23 (external edit)